1. 前言：能跑出數字就好？每次分析都在重寫 JOIN 的真實代價

Day 06 把 90 天、約 52 萬筆合成資料灌進 martech_dw，五張 raw 表和本機逐項對帳全部一致，資料本身已經可以放心使用，但如果接下來每一篇分析都直接查這五張表，很快就會遇到三個問題：

• 每次都掃全表：raw 表沒有分區，只想看最近兩週的購買事件，BigQuery 也得把 90 天的資料全部讀過一遍
• 粒度混在一起：廣告花費是一天一則素材一列，事件是一個動作一列，訂單是一筆交易一列，素材的屬性又散在另一張表，每次分析都要重新想一次怎麼 JOIN 才不會重複計算
• 真實資料和合成資料形狀不同：Day 04 的 Live Demo 站每天都有 GA4 匯出表進來，但 GA4 的事件參數藏在巢狀陣列裡，和 Day 05 設計的扁平欄位完全不同，無法直接和合成資料放在一起查

這三個問題都不會讓查詢報錯，只會讓每一篇分析多繞一段路，而且繞法每次都可能不一樣，今天要做的就是在 raw 表和分析之間加一層整理好的倉儲模型，讓 Day 08 之後的歸因、ROAS 診斷與顧客分群都從同一個地方取資料。

今日核心目標：

1. 依查詢的粒度把五張 raw 表整理成三張事實表與四張維度表
2. 把 GA4 每日匯出表攤平成和合成資料相同的欄位，用 data_source 區分來源後合併
3. 替事實表設定分區與叢集，並用實測的讀取量說明它在小表上的真實效益
4. 合併前後逐項對帳，確認列數與關鍵指標在整理之後完全沒有走樣

2. 系統架構全景與設計理念

星狀綱要的形狀很單純，中間是記錄「發生了什麼」的事實表，外圍是描述「是誰、是什麼、在哪一天」的維度表，事實表只存數字和指向維度的 ID，維度表存屬性，分析時從事實表出發，需要什麼屬性就 JOIN 哪一張維度表。

要分清兩者的差別，關鍵在於搞懂粒度（Granularity），也就是這張表裡的一列（Row），代表發生的最小單位到底是什麼？

弄清楚一列代表什麼，才是決定資料怎麼建、怎麼查的第一步，3.1 會用一個實際的例子說明粒度沒對齊時會發生什麼事。

今天建出來的七張表如下：

事實表和維度表的差別，可以從下面四個地方一眼看出來：

查詢的寫法也跟著固定下來，先在事實表上篩日期、加總數字，最後才 JOIN 維度表取屬性，兩張事實表之間不直接 JOIN，要合在一起看時各自先加總到同一個粒度再相接。

💡 核心工程理念：

1. raw 表不動：raw 表維持 Day 06 載入時的原貌，所有整理都寫在新表，哪一天轉換邏輯寫錯了，刪掉新表重跑就好，來源資料永遠在
2. 先定粒度再定欄位：每張事實表先寫死「一列代表什麼」，欄位才跟著決定，粒度不同的資料不硬塞進同一張表
3. 來源寫在資料裡：合成資料與 GA4 真實事件放在同一張 fct_events，靠 data_source 欄位區分，任何查詢都可以一個條件就只看其中一邊
4. 可以重跑：建表與轉換都寫成可以重複執行的腳本，每天 GA4 多一張匯出表，重跑一次就會併進來

3. 核心技術深度拆解

3.1 粒度與切分：為什麼是三張事實表

最直覺的做法是把所有東西 JOIN 成一張大表，查詢時不用再 JOIN，但粒度沒對齊的代價很快就會出現，舉個最常踩坑的例子：

廣告花費表的粒度是「每天每則素材一列」，但使用者事件表的粒度是「單次動作一列」，如果某則素材今天帶來 10 個事件，而在沒對齊粒度的情況下直接用日期與素材 ID 把兩張表硬 JOIN 算成本，那天這則素材的花費就會瞬間被重複放大 10 倍。

在這次的合成資料裡，一則素材一天平均帶來兩百多個事件，花費就會被重複加總兩百多次，ROAS 從此算不準而且表面上看不出來。

所以判斷的原則是粒度，只要兩份資料「一列代表的東西」不同，就分成兩張事實表：

• 廣告花費的粒度是「一天一則素材」，來自廣告平台的日報
• 事件的粒度是「一個動作」，來自網站的追蹤碼
• 訂單的粒度是「一筆交易」，來自訂單系統

三張表都指向同一組維度表，Day 08 要算歸因時從 fct_events 串出每位訪客的路徑，再用 fct_orders 對上成交金額，Day 09 算 ROAS 時則是 fct_ad_daily 的花費除以歸因後的營收，每一步都在自己的粒度上加總完才相遇，不會互相放大。

維度表這邊有一個刻意的決定，dim_customer 只放顧客 ID、縣市與首購日，合成器產生的假姓名、email 與手機號碼留在 raw_customers，不往下帶，分析顧客分群用不到這些欄位，少一份複本就少一個外洩的地方，Day 23 談個資遮蔽時會再回來處理 raw 表這一層。

另外 fct_ad_daily 保留了 channel 欄位，照星狀綱要的教科書寫法，通路屬於素材的屬性，應該只放在 dim_creative，但叢集欄位必須是表本身的欄位，後面幾篇查詢幾乎都會依通路篩選，所以這裡選擇多存一個字串欄位換取叢集效果。

3.2 GA4 攤平與合併：形狀一樣才能放在一起

GA4 匯出到 BigQuery 的每日表長得和 Day 05 設計的合成資料很不一樣，事件本身的欄位是扁平的，但真正有用的資訊都放在兩個巢狀陣列裡，event_params 放事件參數，items 放商品清單，每個參數還依型別分成字串、整數、浮點數幾個欄位，必須先攤平成一般欄位，才能和合成資料放進同一張表。

實際打開 Live Demo 站匯出的 9/18 與 9/19 兩天資料，共 99 個事件，攤平時遇到四個一開始沒有預期到的狀況：

第一個狀況最容易踩到，Live Demo 站送出的金額是 180、260 這種整數，GA4 就把它存在整數欄位，如果攤平時只讀浮點數欄位，金額會全部變成 NULL，而且查詢不會報錯，只會讓營收莫名其妙歸零，這和 Day 06 的自動偵測綱要是同一類問題，型別在資料裡看起來都對，只有實際加總時才發現少了一塊。

第四個狀況則是兩邊資料的設計不同，合成器在 Day 05 讓每一個事件都帶著所屬工作階段的來源，GA4 卻只在少數帶著 UTM 參數的事件上記錄，其他事件留空，攤平時用視窗函式在同一個工作階段內找出第一個帶來源的事件，把來源、媒介與活動三個欄位一起補到整個工作階段，三個欄位要從同一個事件取值，分開補的話可能拼出一組實際不存在的組合，完全沒有 UTM 的工作階段則和合成資料一樣，來源、媒介、活動分別標成 (direct)、(none)、(direct)。

攤平之後兩邊的欄位就一致了，合併時再加上 data_source，合成資料是 synthetic，GA4 是 ga4，另外也確認了兩件事，合成資料的期間是 6/19 到 9/16，GA4 從 9/18 開始，日期完全不重疊，兩邊的訪客 ID 也沒有任何重複，合併後不會有同一位訪客同時出現在兩個來源的情況。

GA4 目前只有兩天、99 個事件，數量遠少於合成資料，今天的目的不是拿它做分析，而是證明真實資料可以用同一套欄位接進倉儲，之後 Live Demo 站累積的事件只要重跑一次建表腳本就會併入，完整的攤平 SQL 放在儲存庫的 warehouse/build.sql。

3.3 分區與叢集：小表上的真實效益

七張表都是實體表而不是一般的檢視表（VIEW），檢視表只存一段 SQL，每次查詢都回頭讀 raw 表重算一次，好處是資料永遠最新，但分區與叢集只能沿用底下 raw 表的設定，raw 表沒有分區，後面要談的分區與叢集就全部用不上：

代價是資料不會自己更新，GA4 每多一天就要重跑一次建表腳本，這也是前面一直強調可以重跑的原因，Day 27 會用 Cloud Workflows 把這一步排進每日排程。

分區是把一張表依日期切成很多小塊，查詢條件有指定日期範圍時，BigQuery 只讀需要的那幾塊，叢集則是在每一塊裡依指定的欄位排序存放，查詢篩選這些欄位時可以再跳過一部分資料，三張事實表都依日期分區，叢集欄位則選後面幾篇最常拿來篩選的欄位。

fct_events 另外開啟了「必須指定分區條件」的選項，查詢沒有帶日期條件就直接拒絕執行，這是替之後的自己設的防呆，事件表是整個倉儲最大的一張，一旦忘了寫日期條件就會掃全表，這個選項讓錯誤在執行前就被擋下來。

實際比較同一個查詢，統計 9/3 到 9/16 這兩週每天的 purchase 事件數，分別查 raw 表與整理後的事實表：

讀取量少了約八成，兩週只占 90 天的一小部分，分區把其他日期整塊跳過，但計費量只從 13 MiB 降到 10 MiB，原因是 BigQuery 的隨選計費每張被查詢的表最少收 10 MiB，資料量還這麼小的時候，分區省下來的讀取量大部分被這個最低計費吃掉了。

所以以目前約 70 MiB 的事件表來看，分區與叢集省下的費用幾乎可以忽略，今天就先設定的理由有兩個，第一是分區只能在建表時決定，等資料累積到需要時再改，就得整張表重建一次，第二是必須指定分區條件這個防呆從第一天就開始保護查詢，比省下的幾 MiB 更有價值。

4. FinOps 成本防護實踐：三道防線體系

1. 第一道防線：善用 Google Cloud 每月免費額度：七張新表約 71.5 MiB，連同 raw 表約 63.5 MiB，合計約 135 MiB，只占每月 10 GiB 免費儲存額度的 1.3%，建表、轉換與對帳全部加起來的查詢量也遠低於每月 1 TiB 的免費查詢額度
2. 第二道防線：架構層被動成本防護：DDL 不收費，事件表開啟必須指定分區條件，忘了寫日期的查詢會直接被擋下，另外要記得對小表頻繁下查詢時，每次每張表都至少以 10 MiB 計費，今天沒有呼叫 Gemini，沒有任何 Token 成本
3. 第三道防線：Cloud Billing 預算警報：沿用 Day 03 由 Terraform 建立的預算警報（新台幣帳戶 NT$ 300／美元帳戶 US$ 10），50%、80%、100% 三段通知，今天的操作不會觸發

5. Cloud Shell 實戰演練：一行指令建好星狀綱要

5.1 事前準備

• 已完成 Day 06，martech_dw 裡有五張 raw 表
• 已完成 Day 04 的 GA4 每日匯出，BigQuery 裡有 analytics_<資源 ID> 資料集，而且至少有一張 events_YYYYMMDD 每日表
• 確認 gcloud 有登入中的帳號，輸入 gcloud auth list，帳號前面要有星號

5.2 路線 A｜懶人包：一行指令建表並對帳

cd ~/ai-driven-martech-pipeline && git pull && bash warehouse/build.sh

最後看到「0 項不一致」就完成了，GA4 的列數會隨著每天新增的匯出表變多，所以一致的項目數與列數可能和本文略有不同。

5.3 路線 B｜逐步教學：理解每一個步驟

步驟 1：取得最新程式碼

cd ~/ai-driven-martech-pipeline
git pull
ls warehouse

會看到 ddl.sql、build.sql、check.sql 與 build.sh 四個檔案。

步驟 2：建立七張空表

bq query --nouse_legacy_sql < warehouse/ddl.sql

這一步只建表不寫資料，每個欄位的型別、說明、分區與叢集都寫在這份檔案裡。

步驟 3：轉換與合併

build.sql 裡用 @@GA4_DATASET@@ 代表 GA4 匯出資料集的名稱，先把自己的資料集名稱存進 GA4 變數再替換執行，步驟 4 也會用到這個變數，請在同一個終端機裡操作：

GA4=$(bq ls --format=json | python3 -c 'import sys,json;print([d["datasetReference"]["datasetId"] for d in json.load(sys.stdin) if d["datasetReference"]["datasetId"].startswith("analytics_")][0])')
sed "s/@@GA4_DATASET@@/${GA4}/g" warehouse/build.sql | bq query --nouse_legacy_sql

步驟 4：合併前後對帳

sed "s/@@GA4_DATASET@@/${GA4}/g" warehouse/check.sql | bq query --nouse_legacy_sql --format=pretty --max_rows=100

每一列會列出檢查項目、合併前的值與合併後的值，ok 欄位全部是 OK 或 INFO 就代表沒有問題。

步驟 5：試試分區條件的防呆

bq query --nouse_legacy_sql "SELECT COUNT(*) FROM martech_dw.fct_events WHERE event_name = 'purchase'"

這個查詢沒有指定日期，會直接收到 Cannot query over table ... without a filter over column(s) 'event_dt' 的錯誤，加上 AND event_dt >= '2026-09-01' 再執行一次就會成功。

5.4 驗證成果

• 對帳報表顯示 54 項一致、0 項不一致
• fct_events 的列數等於 raw_events 加上 GA4 每日表的列數，目前是 524,020 加 99 等於 524,119
• 廣告花費 945,974.84 元、客單價約 622 元，和 Day 06 的對帳數字相同
• dim_customer 沒有姓名、email 與手機欄位

5.5 不用了？指令全部清除

for t in fct_ad_daily fct_events fct_orders dim_date dim_creative dim_customer dim_product; do bq rm -f -t martech_dw.$t; done

這七張表明天 Day 08 的歸因會用到，建議保留，約 71.5 MiB 的儲存量在免費額度內不會產生費用，raw 表不受影響，想重建隨時再跑一次 build.sh 即可。

6. 工程實務避坑指南

1. 不要把不同粒度的資料塞進同一張表：一天一個數字的花費掛到每一個事件旁邊，加總時就會被重複計算，而且查詢完全不會報錯，先寫死每張事實表一列代表什麼再決定欄位
2. GA4 的參數不要假設型別：同一個參數可能存在整數欄位，也可能存在字串欄位，只讀其中一種的話資料會悄悄變成 NULL，攤平時把幾種型別一起看
3. (not set) 不是 NULL：GA4 對沒有值的電商欄位填入字串 (not set)，直接計算交易數或尺寸分佈會把它當成一個真的值
4. 萬用字元會連 intraday 表一起讀：events_* 同時會對到每日表與 events_intraday_*，開啟串流匯出之後同一個事件可能被讀兩次，改用 events_2* 只對到日期開頭的每日表
5. GA4 每日表不是隔天一早就有：這次 9/18 的表 9/19 下午才出現，9/19 的表 9/20 中午才出現，兩天都比事件發生晚了一天以上，建表腳本要能重跑，而不是假設昨天的資料一定已經在了

7. 總結與明日預告

今天把五張 raw 表與 GA4 真實事件整理成星狀綱要：三張事實表依粒度切開，四張維度表集中存放屬性，GA4 的巢狀事件攤平成和合成資料一樣的欄位後合併，合併前後逐項對帳 54 項全部一致。

回頭看前言的三個問題，每次都掃全表靠分區與強制分區條件解決，粒度混在一起靠先定粒度、三張事實表各自獨立、屬性集中到維度表，真實資料和合成資料形狀不同靠攤平與 data_source，而分區在小表上省下的費用有限這件事，實測數字也一併記錄下來，之後資料量變大時可以回來比較。

明日預告：Day 08《客人看了三支廣告才下單，功勞到底該算誰的？》，我們將從 fct_events 串出每位訪客的造訪路徑，實作第一次接觸、最後接觸與時間衰減三種歸因模型，看看當初埋在旅程起點的 Meta 流量，在切換不同歸因模型時，功勞會被放大還是稀釋！