暫停活動 · 止血

近 7 天的 ROAS 掉到目標的設定比例以下,就建議暫停這個活動止血。

設定

建議動作

目前沒有要處理的活動。

進階:SQL

-- 由設定表單自動產生。新手用表單填,進階可直接改下面的 SQL。
WITH params AS (
  SELECT 70 AS ratio, 7 AS cooldown_days
),
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 FROM d
  WHERE ts >= (SELECT MAX(ts) FROM d) - 7*86400 GROUP BY campaign_id
)
SELECT c.id AS campaign_id, c.name,
       'pause' AS action, NULL AS param,
       'ROAS ' || ROUND(r.roas7,2) || ' < 門檻 ' ||
         ROUND(c.target_roas*(SELECT ratio FROM params)/100.0,2) AS detail,
       'roas' AS chart_metric,
       ROUND(c.target_roas*(SELECT ratio 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 ratio FROM params)/100.0
  AND NOT EXISTS (SELECT 1 FROM actions a WHERE a.campaign_id = c.id
        AND a.ts > CAST(FLOOR(epoch_ms(now())/1000) AS BIGINT) - (SELECT cooldown_days FROM params)*86400)
ORDER BY r.roas7;