Resku
廣告活動
建議
策略
調整紀錄
示範資料
策略
/ 編輯 #7
編輯策略
用 SQL 寫策略,每一列就是一筆提醒。SQL 只能查、不會改到資料。先「試跑」看結果,再儲存。
名稱
說明
SQL(每一列 = 一筆提醒)
WITH spend AS ( SELECT campaign_id, ts, value FROM observations WHERE metric='spend' ), stats AS ( -- 基準排除「今天」,暴衝那天才不會稀釋自己的標準差 SELECT s.campaign_id, AVG(s.value) AS mu, SQRT(AVG(s.value*s.value) - AVG(s.value)*AVG(s.value)) AS sigma FROM spend s WHERE s.ts < (SELECT MAX(ts) FROM spend WHERE campaign_id = s.campaign_id) GROUP BY s.campaign_id ), latest AS ( SELECT s.campaign_id, s.value AS today FROM spend s WHERE s.ts = (SELECT MAX(ts) FROM spend WHERE campaign_id = s.campaign_id) ) SELECT c.id AS campaign_id, c.name, ROUND(l.today,0) AS spend_today, ROUND(st.mu,0) AS avg_spend, ROUND((l.today - st.mu)/st.sigma, 2) AS z_score FROM latest l JOIN stats st ON st.campaign_id = l.campaign_id JOIN campaigns c ON c.id = l.campaign_id WHERE (l.today - st.mu)/st.sigma > 2.5 ORDER BY z_score DESC;
想讓策略給建議
— SQL 多回一個
action
欄,每列就能一鍵套用;不回就只當提醒。
可用動作:
budget_up
budget_down
roas_up
roas_down
pause
選填欄
param
(幅度/目標值)、
detail
(說明)。
▷ 試跑(預覽)
儲存