設定
建議動作
目前沒有要處理的活動。
進階:SQL
-- 由設定表單自動產生。新手用表單填,進階可直接改下面的 SQL。
WITH params AS (
SELECT 100 AS trigger_pct, 5 AS step, 3 AS max_per_day, 80 AS overspend_pct, 1 AS quiet_start, 7 AS quiet_end
),
d AS (
SELECT campaign_id, ts,
SUM(CASE WHEN metric='revenue' THEN value END) AS rev,
SUM(CASE WHEN metric='spend' THEN value END) AS sp
FROM observations GROUP BY campaign_id, ts
),
r AS (
SELECT campaign_id, SUM(rev)/NULLIF(SUM(sp),0) AS roas7, AVG(sp) AS avg_sp
FROM d WHERE ts >= (SELECT MAX(ts) FROM d) - 7*86400 GROUP BY campaign_id
)
SELECT c.id AS campaign_id, c.name,
'budget_up' AS action,
(SELECT step FROM params) AS param,
'ROAS ' || ROUND(r.roas7,2) || ' ≥ 門檻 ' ||
ROUND(c.target_roas*(SELECT trigger_pct FROM params)/100.0,2) || ' → +' ||
CAST((SELECT step FROM params) AS INT) || '%' AS detail,
'roas' AS chart_metric,
ROUND(c.target_roas*(SELECT trigger_pct FROM params)/100.0,2) AS threshold
FROM r JOIN campaigns c ON c.id = r.campaign_id
WHERE c.status='active'
AND r.roas7 >= c.target_roas * (SELECT trigger_pct FROM params)/100.0
AND CAST(EXTRACT('hour' FROM current_localtimestamp()) AS INTEGER) NOT BETWEEN (SELECT quiet_start FROM params) AND (SELECT quiet_end FROM params)
AND r.avg_sp / c.daily_budget * 100 < (SELECT overspend_pct FROM params)
AND (SELECT COUNT(*) FROM actions a WHERE a.campaign_id = c.id AND a.action='budget_up'
AND a.ts > (CAST(FLOOR(epoch_ms(now())/1000/86400) AS BIGINT)*86400)) < (SELECT max_per_day FROM params)
ORDER BY r.roas7 DESC;