自動調預算

-- 由設定表單自動產生。新手用表單填,進階可直接改下面的 SQL。
WITH params AS (
  SELECT 'standard' AS sensitivity, 80 AS overspend_pct, 1 AS quiet_start, 7 AS quiet_end, 7 AS cooldown_days
),
daily AS (   -- 只看近 14 天,趨勢才不會被太舊的資料拖住
  SELECT campaign_id, ts,
         ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY ts) - 1 AS x,
         SUM(CASE WHEN metric='revenue' THEN value END) /
           NULLIF(SUM(CASE WHEN metric='spend' THEN value END),0) AS roas,
         SUM(CASE WHEN metric='orders' THEN value END) AS orders,
         SUM(CASE WHEN metric='spend'  THEN value END) AS spend
  FROM observations
  WHERE ts >= (SELECT MAX(ts) FROM observations) - 14*86400
  GROUP BY campaign_id, ts
),
slope AS (
  SELECT campaign_id,
         (COUNT(*)*SUM(x*roas)   - SUM(x)*SUM(roas))   / (COUNT(*)*SUM(x*x)-SUM(x)*SUM(x)) AS roas_s,
         (COUNT(*)*SUM(x*orders) - SUM(x)*SUM(orders)) / (COUNT(*)*SUM(x*x)-SUM(x)*SUM(x)) AS ord_s
  FROM daily GROUP BY campaign_id
),
util AS (   -- 近 7 日平均花費(算預算花了幾成,給超花保護用)
  SELECT campaign_id, AVG(spend) AS avg_sp FROM daily
  WHERE ts >= (SELECT MAX(ts) FROM daily) - 7*86400 GROUP BY campaign_id
),
quad AS (   -- 四象限:ROAS 走勢 × 訂單走勢
  SELECT campaign_id, roas_s, ord_s,
    CASE
      WHEN roas_s > 0 AND ord_s > 0  THEN 'accelerate'  -- 都走高 → 加碼
      WHEN roas_s > 0 AND ord_s <= 0 THEN 'maintain'    -- ROAS 高但訂單沒增 → 維持
      WHEN roas_s <= 0 AND ord_s > 0 THEN 'fine_tune'   -- 訂單增但 ROAS 走低 → 微調
      ELSE 'shrink'                                      -- 都走低 → 縮減
    END AS quadrant,
    CASE (SELECT sensitivity FROM params)
      WHEN 'conservative' THEN 10 WHEN 'aggressive' THEN 35 ELSE 20 END AS step
  FROM slope
)
SELECT c.id AS campaign_id, c.name,
   CASE q.quadrant WHEN 'shrink' THEN 'budget_down' ELSE 'budget_up' END AS action,
   CASE q.quadrant WHEN 'fine_tune' THEN q.step / 2 ELSE q.step END AS param,
   CASE q.quadrant WHEN 'accelerate' THEN '加碼' WHEN 'shrink' THEN '縮減' ELSE '微調' END
     || ' · ROAS ' || (CASE WHEN q.roas_s>0 THEN '走高' ELSE '走低' END)
     || ' · 訂單 ' || (CASE WHEN q.ord_s>0 THEN '增加' ELSE '減少' END) AS detail,
   'roas' AS chart_metric, c.target_roas AS threshold,
   q.quadrant
FROM quad q
JOIN campaigns c ON c.id = q.campaign_id
JOIN util u      ON u.campaign_id = q.campaign_id
WHERE c.status='active' AND q.quadrant <> 'maintain'
  AND CAST(EXTRACT('hour' FROM current_localtimestamp()) AS INTEGER) NOT BETWEEN (SELECT quiet_start FROM params) AND (SELECT quiet_end FROM params)
  AND NOT (q.quadrant <> 'shrink' AND u.avg_sp / c.daily_budget * 100 >= (SELECT overspend_pct FROM params))
  AND NOT EXISTS (SELECT 1 FROM actions a WHERE a.campaign_id = c.id
        AND a.action IN ('budget_up','budget_down')
        AND a.ts > CAST(FLOOR(epoch_ms(now())/1000) AS BIGINT) - (SELECT cooldown_days FROM params)*86400)
ORDER BY q.quadrant;