Resku
廣告活動
建議
策略
調整紀錄
示範資料
策略
/ 編輯 #8
編輯策略
用 SQL 寫策略,每一列就是一筆提醒。SQL 只能查、不會改到資料。先「試跑」看結果,再儲存。
名稱
說明
SQL(每一列 = 一筆提醒)
WITH weekly AS ( SELECT campaign_id, CAST(FLOOR(ts / (7.0*86400)) AS INTEGER) AS wk, SUM(CASE WHEN metric='clicks' THEN value END) AS clicks, SUM(CASE WHEN metric='impressions' THEN value END) AS impr FROM observations GROUP BY campaign_id, wk ), ctr AS ( SELECT campaign_id, wk, clicks / NULLIF(impr,0) AS ctr, LAG(clicks / NULLIF(impr,0)) OVER (PARTITION BY campaign_id ORDER BY wk) AS prev_ctr FROM weekly ), latest AS ( SELECT campaign_id, ctr, prev_ctr FROM ctr WHERE wk = (SELECT MAX(wk) FROM ctr cc WHERE cc.campaign_id = ctr.campaign_id) AND prev_ctr IS NOT NULL ) SELECT c.id AS campaign_id, c.name, ROUND(l.ctr*100, 2) AS ctr_pct, ROUND(l.prev_ctr*100, 2) AS prev_ctr_pct, ROUND((l.ctr - l.prev_ctr)/l.prev_ctr*100, 1) AS change_pct FROM latest l JOIN campaigns c ON c.id = l.campaign_id WHERE (l.ctr - l.prev_ctr)/l.prev_ctr < -0.30 ORDER BY change_pct;
想讓策略給建議
— SQL 多回一個
action
欄,每列就能一鍵套用;不回就只當提醒。
可用動作:
budget_up
budget_down
roas_up
roas_down
pause
選填欄
param
(幅度/目標值)、
detail
(說明)。
▷ 試跑(預覽)
儲存