1. 前言：ROAS 掉了，先別急著砍預算

週一早上打開廣告報表，發現上週的 ROAS 掉了一截，第一個念頭通常是哪支廣告壞了？接著就是一連串的猜測：是不是競爭對手在搶關鍵字、是不是素材看膩了、是不是網站又改版把什麼弄壞了，每一種猜測的處理方式都不一樣，猜錯了就會把預算砍在不該砍的地方。

麻煩的是這幾種狀況在報表上看起來都一樣，都是「成效變差」，要分辨它們得把點擊成本、點擊率、網站追蹤到的購買、後台的訂單一項一項對照著看，資料量一大，光是找出哪裡變了就要花掉一個早上。

今天要做的事是讓 SQL 先把哪裡變了整理成一張很短的表，再把這張表直接在 BigQuery 裡交給 Gemini 判斷原因，Day 05 產生合成資料時先寫好了一份答案表，在資料裡刻意藏了幾個狀況，其中三種在報表上看起來都像成效下跌，剛好拿來考 AI：

答案表只用來對答案不會洩漏給 AI 看，另外 S7（9/1–9/16 秋日棉織專案）：預期專案商品銷售佔比上升，這題無需 AI 分析，後續在 3.1 階段直接用 SQL 驗證即可。

今日核心目標：

1. 用 SQL 把 52 萬筆事件壓成一張只有幾列的異常摘要表
2. 在 BigQuery 建立連到 Gemini 的遠端模型，用 AI.GENERATE_TEXT 讓 AI 逐列判讀原因，結果存成一張表
3. 拿 AI 的答案和答案表對照，再故意少給資料看它會不會亂猜

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

整個流程分成四步，只有第三步會花到 Gemini 的錢：

💡 核心工程理念：

1. 算數字交給 SQL，判讀交給 AI：比較前後差異、對帳這些事 SQL 算得最正確且完整，AI 只負責看完數字之後說出最可能的原因
2. 只給 AI 看摘要：52 萬筆事件直接丟進去既貴又容易失焦，先壓成 4 列，每列不到 500 個 Token
3. 答案限定在選項裡：原因只能從六個選項挑一個，回傳格式鎖成固定的 JSON，結果才能直接存成表、拿來統計
4. 允許 AI 說不知道：選項裡放一個「資料不足」，並寫明數字不夠就不要硬猜

3. 核心技術深度拆解

3.1 先讓 SQL 找出哪裡變了

找異常用了三個角度，每個角度抓的東西不一樣：

素材要和自己剛上線時比而不是和上週比，因為疲乏是一週掉一點慢慢累積，每週只差幾個百分點，和上週比永遠不會超過門檻。

一開始我也想在廣告群組層級直接算 ROAS，但每個群組每週追蹤到的購買只有 2 到 13 筆，多一筆少一筆 ROAS 就差很多，實際算出來在 0.1 到 2.5 之間跳動沒辦法拿來比。

點擊成本和點擊率就穩定多了，每個群組每週有數百到一千多次點擊撐著，所以群組和素材改看這兩個數字，購買有沒有追蹤到則拉到全站每天來對帳。

廣告群組和素材只留第一次超過門檻的那一週，全站則是每一天各自判斷，跑完的結果只有 4 列：

前三列正好是 S1、S2、S3，第四列是 S3 那支素材所在的群組，推測是素材越來越差把整個群組的點擊率也拉下來了，沒有多抓到不相干的東西。

另外補上 Day 08 留下來的 S7 秋日專案，這題用 SQL 就算得出來不需要 AI：專案期間（9/1 到 9/16）專案商品的件數占比從前三週的 53.8% 升到 66.6%，營收占比從 47.7% 升到 56.8%。

3.2 在 SQL 裡呼叫 Gemini

BigQuery 要呼叫 Gemini 得先建一個「遠端模型」，它其實只是一個指向 Gemini 的捷徑，Day 03 用 Terraform 建好的連線 vertex_ai_conn 這時派上用場：

CREATE OR REPLACE MODEL martech_dw.gemini_flash_lite
  REMOTE WITH CONNECTION `us.vertex_ai_conn`
  OPTIONS (ENDPOINT = 'gemini-3.5-flash-lite');

建模型本身不收費，之後在 SQL 裡用 AI.GENERATE_TEXT 把題目交給它，下面是節錄，{...} 的完整內容在 diagnose.sql：

SELECT anomaly_id, result, statistics
FROM AI.GENERATE_TEXT(
  MODEL martech_dw.gemini_flash_lite,
  (SELECT anomaly_id, prompt FROM martech_dw.diag_prompt),
  STRUCT('''{"generation_config": {...}}''' AS model_params));

有三個規定要注意：

• 題目那一欄一定要叫 prompt，其他欄位會原封不動跟著帶出來，方便對回是哪一筆異常
• AI 的回答放在 result，Token 用量放在 statistics，status 空白只代表 Gemini 有回應，不代表回答是完整的
• model_params 裡用 response_schema 把回答鎖成四個欄位：原因、證據、信心分數、下一步要查什麼，其中原因只能是六個選項之一

每一題長這樣，只放數字和候選原因，沒有告訴 AI「點擊成本變高就是競價變貴」這種判斷規則，這樣才測得出它自己會不會判斷：

對象：廣告群組 meta-trn-prospecting（通路 meta）
期間：2026-08-10 到 2026-08-16，這週有資料 7 天，對照前四週
- 每天平均點擊：246.7 次（前四週 230.1 次）
- 點擊率：2.39%（前四週 2.28%）
- 平均每次點擊花費：13.42 元（前四週 7.55 元），變化 +78%
- 這週花費：23169 元
- 同通路的網站追蹤 ROAS：0.62（前四週 0.56）

請判斷最可能的原因，只能從這六個選一個：競價變貴、追蹤碼失效、素材疲乏、需求或季節變化、其他、資料不足

完整的題目模板在儲存庫的 diagnosis/prompt.sql，參數說明放在同一個目錄的 README.md。

3.3 AI 的答案和答案表對得上嗎

同一份摘要交給兩個模型，一個是最便宜的 gemini-3.5-flash-lite，一個是 gemini-3.6-flash：

三個植入的狀況兩個模型的答案都和答案表一致，給的理由也引用了對的數字，例如 8/27 那一筆 flash-lite 寫的是「網站追蹤到的購買為 0 筆但後台實際成立了 37 筆訂單，且廣告點擊數 1386 次與前七天平均相當」，3.6-flash 寫的是「網站追蹤到的購買降至 0 筆（前七天平均 43.9 筆），但後台仍有 37 筆實際成立訂單」，這正是分辨追蹤碼壞掉和真的沒生意的關鍵。

差別出在第四筆，flash-lite 認為只有 3 天資料、花費也少，選了資料不足，3.6-flash 則根據點擊率從 1.34% 掉到 1.06% 而點擊成本只差 2% 選了素材疲乏，但這一題是廣告群組的數字，裡面沒有任何一支素材的資料，所以 3.6-flash 的答案帶有推測成分，flash-lite 選資料不足反而比較守題目的規則。

這個結果要保留兩個前提來看：一是合成資料的訊號是刻意植入的，比真實世界乾淨很多，二是每個模型只跑了一次，AI 的回答每次可能略有不同，拿到真實資料上用之前要多跑幾次、多看幾個案例。

3.4 資料給不夠，AI 就會開始猜

為了看 AI 在資料不足時的反應，我把同樣四筆異常只留下老闆最常看的那幾個數字，例如廣告群組只給花費和 ROAS，素材只給這週的曝光和點擊，全站只給追蹤到的購買，拿掉點擊成本、點擊率、素材上線初期的比較和後台訂單，再分兩組問 flash-lite，A 組只能五選一，B 組多了「資料不足」這個選項，也多了「數字不夠就不要硬猜」的規則：

A 組最值得注意的是素材那一筆，題目只有這週的曝光和點擊，沒有可以比較的基期，看不出點擊率有沒有下降，它的理由卻寫了「點擊率與互動狀況下降」，這是自己補上去的，答案雖然對，但這次猜對不代表下次也會猜對。

B 組有了出口，三筆都老實說資料不夠，但全站那一筆兩組都選了追蹤碼失效，只憑購買歸零就下結論，沒看到後台訂單其實分不出是追蹤壞掉還是網站整個掛掉沒人下單，這也提醒我們 3.3 裡 S2 答對，不一定代表它真的有拿後台訂單來比，所以摘要一定要同時放追蹤數字和後台數字，給 AI 足夠的對照組比什麼都重要。

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

1. 第一道防線：善用 Google Cloud 每月免費額度：今天在 BigQuery 跑了 34 個查詢，實際讀了 80.7 MB，因為每個查詢每讀一張表至少算 10 MiB，計費量是 482.3 MB，不到每月 1 TiB 免費額度的萬分之五，Gemini 則是另外計費，正式跑一輪 4 題 flash-lite 約 US$0.0017、3.6-flash 約 US$0.0036，今天連同試跑、對照與實驗共呼叫 22 次，照官方單價估算約 US$0.02，遠端模型的 ENDPOINT 只寫模型名稱時 BigQuery 會送到非 global 的端點，單價比 global 高一成，這裡用的是非 global 的單價（發表時誤用 global 單價，9/29 更正）
2. 第二道防線：架構層被動成本防護：呼叫前先用 AI.COUNT_TOKENS 數題目長度，4 題共 1,178 個 Token，實際計費的輸入是 1,779 個，每題多出約 150 個，是規定回答格式的那段 response_schema，這段設定也會算錢，run.sh 會先印出兩個模型各跑一輪的最壞情況費用，要輸入 yes 才會真的呼叫 Gemini
3. 第三道防線：Cloud Billing 預算警報：沿用 Day 03 由 Terraform 建立的預算警報（新台幣帳戶 NT$ 300／美元帳戶 US$ 10），50%、80%、100% 三段通知，今天的操作不會觸發

5. Cloud Shell 實戰演練：一行指令請 AI 診斷

5.1 事前準備

• 已完成 Day 07，martech_dw 裡有 fct_ad_daily、fct_events、fct_orders
• 已完成 Day 03 的 Terraform，連線 us.vertex_ai_conn 存在，而且連線的服務帳號有 Vertex AI User 角色
• 確認 gcloud 有登入中的帳號，輸入 gcloud auth list，帳號前面要有星號

5.2 路線 A｜懶人包：一行指令從找異常到出診斷

cd ~/ai-driven-martech-pipeline && git pull && bash diagnosis/run.sh

畫面印出預估費用後輸入 yes，看到「16 項通過、0 項不通過」與兩段診斷報表就完成了。

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

步驟 1：取得最新程式碼

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

會看到 summary.sql、prompt.sql、cost.sql、diagnose.sql、check.sql、report.sql、experiment.sql、promo.sql、run.sh 與說明文件 README.md。

步驟 2：找出異常並寫成題目

bq query --nouse_legacy_sql < diagnosis/summary.sql
bq query --nouse_legacy_sql < diagnosis/prompt.sql
bq query --nouse_legacy_sql --format=pretty "SELECT anomaly_id FROM martech_dw.diag_prompt"

最後一行會列出 4 筆異常，想調整門檻就改 summary.sql WHERE 條件裡的 0.30、-0.20、-0.25、0.5 再跑一次。

步驟 3：呼叫前先估費用

bq query --nouse_legacy_sql --format=pretty < diagnosis/cost.sql

worst_case_usd 是每一題都把輸出上限用滿的最壞情況，今天兩個模型合計估出 US$0.0162，實際花了約 US$0.0053，大約是三分之一。

步驟 4：請 Gemini 判讀

bq query --nouse_legacy_sql < diagnosis/diagnose.sql

這一步會用 flash-lite 和 3.6-flash 各跑一輪，結果寫進 mart_diagnosis，約一分鐘內完成。

步驟 5：檢查回答並看結果

bq query --nouse_legacy_sql --format=pretty --max_rows=100 < diagnosis/check.sql
sed -n '/-- Day 09 ①/,/;/p' diagnosis/report.sql | bq query --nouse_legacy_sql --format=pretty
sed -n '/-- Day 09 ②/,/;/p' diagnosis/report.sql | bq query --nouse_legacy_sql --format=pretty

檢查項目包括兩個模型都有跑、每一列都有回答、API 沒有回報錯誤、JSON 解析得出來、原因在六個選項內、信心分數在 0 到 1 之間、理由不是空白、沒有用到思考 Token，ok 欄位全部是 OK 就代表回答可以直接使用，後面兩行分別印出兩個模型的對照表，以及 flash-lite 的理由與下一步建議。

5.4 驗證成果

• diag_summary 共 4 列，對應 S1、S2、S3 與 S3 所在的廣告群組
• 三個植入的狀況兩個模型的答案都和答案表一致
• check.sql 16 項全部通過

5.5 不用了？指令全部清除

bq rm -f -t martech_dw.diag_prompt
bq rm -f -t martech_dw.diag_summary
bq rm -f -m martech_dw.gemini_flash_lite
bq rm -f -m martech_dw.gemini_flash
# 有跑 3.4 小實驗（experiment.sql）才需要這一行
bq rm -f -t martech_dw.diag_experiment

mart_diagnosis 在 Day 21 讓 AI 查資料庫時會用到，建議保留，真的不需要時再用 bq rm -f -t martech_dw.mart_diagnosis 刪除，這幾張表都很小，遠端模型只是一個捷徑，放著不會產生費用。

6. 工程實務避坑指南

1. 思考會吃掉回答的長度上限：gemini-3.6-flash 預設會先想一遍再回答，想的部分也算在 max_output_tokens 裡，第一次跑時每題光想就用掉約 488 個 Token，真正的答案只寫出 5 到 10 個就被切斷，但 status 還是顯示成功，解法是把 thinking_budget 設成 0，另外 thinking_level 目前會被 BigQuery 的參數檢查擋下
2. status 空白不代表回答能用：它只代表 Gemini 有回應，回答完不完整要另外檢查，check.sql 會確認 JSON 解析得出來、原因在選項內、信心分數有值
3. 不要把整張事實表丟給 AI：52 萬筆事件以每筆約 30 個 Token 粗估就超過一千萬個，今天 4 題加起來只有 1,178 個，而且 AI 在一大堆數字裡反而容易抓錯重點
4. 題目裡不要寫判斷規則：寫了「點擊成本變高就是競價」，AI 只是在照規則分類，看不出它有沒有判斷力，也等於把答案洩漏給它
5. 給 AI 對照組：只給追蹤到的購買，AI 分不出追蹤壞掉和真的沒生意，摘要裡要同時放兩種來源的數字
6. 重跑也會重新計費：AI.GENERATE_TEXT 每執行一次就呼叫一次 Gemini，結果先存成表再拿來查，不要每次看報表都重跑

7. 總結與明日預告

今天先用 SQL 把 52 萬筆事件壓成 4 列異常摘要，再透過遠端模型在 BigQuery 裡請 Gemini 逐列判讀，在這份合成資料上，三個植入的狀況 flash-lite 和 3.6-flash 的答案都和答案表一致，理由也引用了對的數字，4 題的正式診斷用 flash-lite 跑一輪約 US$0.0017，約新台幣 0.05 元。

回頭看前言的問題，ROAS 掉了不一定是廣告不好，這次的三種狀況一個是競價變貴、一個是追蹤碼壞掉、一個是素材看膩了，處理方式完全不同，AI 能幫忙分辨的前提是 SQL 先把對的數字擺在它面前，資料給不夠它就會開始猜，所以摘要要設計好，也要留一個讓它說「資料不足」的出口。

不過今天還有一件事需要提醒，3.3 提到拿到真實資料上之前要多跑幾次，但每多跑一次就多付一次錢，今天只有 4 題、跑一輪不到 0.35 新台幣，真實的廣告帳戶一天可能有幾十筆異常要判讀，同一批異常為了確認答案穩不穩還要各跑好幾次，費用就會跟著題數和次數一起往上乘。

仔細看今天正式診斷的題目會發現，每一題真正不一樣的只有中間那幾行數字，前面的角色說明、後面的六個候選原因和四條規則每一題都一模一樣，另外每次呼叫還會多出約 150 個 Token 的回答格式設定，這些內容每次都重新計費，今天的題目很短，重複的部分還不多，但之後只要附上商品清單、過去幾個月的成效這類背景資料，每一題都要重送一大段一樣的內容，這才是明天要解決的痛點。

明日預告：Day 10《同一份資料問 AI 十次，不用付十次錢的快取機制》，我們將把每次都一樣的說明與背景資料集中放到題目最前面，先存進 Gemini 的快取，之後每一題只送出會變的部分，實測 Context Caching 能把重複的輸入費用省下多少，以及資料要多長才划得來！