自動調目標 ROAS

設定

各活動目標 ROAS

夏季涼感衣・主推

機能運動褲・衝量

無線藍牙耳機

露營折疊椅・季節

保溫瓶・長銷

兒童書包・開學

寵物自動餵食器

廚房收納組・走量

建議動作

露營折疊椅・季節調高目標 ROAS 目標 3.0 → 3.5(實際 4.82 高於目標)

無線藍牙耳機調高目標 ROAS 目標 3.5 → 4.0(實際 5.72 高於目標)

兒童書包・開學調高目標 ROAS 目標 3.5 → 4.0(實際 4.98 高於目標)

進階:SQL

-- 由設定表單自動產生。新手用表單填,進階可直接改下面的 SQL。
WITH params AS (
  SELECT 'ratio' AS method, 2 AS min_roas, 9 AS max_roas, 0.5 AS step, 7 AS cooldown_days
),
d 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) AS rev,
         SUM(CASE WHEN metric='spend'   THEN value END) AS sp
  FROM observations
  WHERE ts >= (SELECT MAX(ts) FROM observations) - 14*86400
  GROUP BY campaign_id, ts
),
agg 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
),
slp AS (   -- ROAS 每日走勢斜率(趨勢法用)
  SELECT campaign_id,
         (COUNT(*)*SUM(x * (rev/NULLIF(sp,0))) - SUM(x)*SUM(rev/NULLIF(sp,0)))
           / (COUNT(*)*SUM(x*x)-SUM(x)*SUM(x)) AS roas_s
  FROM d GROUP BY campaign_id
),
decide AS (
  SELECT a.campaign_id, c.name, a.roas7, c.target_roas, s.roas_s,
         CASE WHEN c.min_roas > 0 THEN c.min_roas ELSE (SELECT min_roas FROM params) END AS lo,
         CASE WHEN c.max_roas > 0 THEN c.max_roas ELSE (SELECT max_roas FROM params) END AS hi,
    CASE (SELECT method FROM params)
      WHEN 'trend' THEN CASE WHEN s.roas_s > 0.02 THEN 'up' WHEN s.roas_s < -0.02 THEN 'down' ELSE 'hold' END
      ELSE              CASE WHEN a.roas7 > c.target_roas*1.3 THEN 'up' WHEN a.roas7 < c.target_roas*0.7 THEN 'down' ELSE 'hold' END
    END AS dir
  FROM agg a JOIN campaigns c ON c.id = a.campaign_id JOIN slp s ON s.campaign_id = a.campaign_id
  WHERE c.status='active'
)
SELECT campaign_id, name,
   CASE dir WHEN 'up' THEN 'roas_up' WHEN 'down' THEN 'roas_down' END AS action,
   CASE dir
     WHEN 'up'   THEN ROUND(LEAST(target_roas + (SELECT step FROM params), hi), 1)
     WHEN 'down' THEN ROUND(GREATEST(target_roas - (SELECT step FROM params), lo), 1)
   END AS param,
   CASE dir
     WHEN 'up'   THEN '目標 ' || target_roas || ' → ' || ROUND(LEAST(target_roas + (SELECT step FROM params), hi),1) || '(' ||
        CASE (SELECT method FROM params) WHEN 'trend' THEN 'ROAS 走高' ELSE '實際 ' || ROUND(roas7,2) || ' 高於目標' END || ')'
     WHEN 'down' THEN '目標 ' || target_roas || ' → ' || ROUND(GREATEST(target_roas - (SELECT step FROM params), lo),1) || '(' ||
        CASE (SELECT method FROM params) WHEN 'trend' THEN 'ROAS 走低' ELSE '實際 ' || ROUND(roas7,2) || ' 低於目標' END || ')'
   END AS detail,
   'roas' AS chart_metric, target_roas AS threshold
FROM decide
WHERE dir <> 'hold'
  AND NOT EXISTS (SELECT 1 FROM actions a WHERE a.campaign_id = decide.campaign_id
        AND a.ts > CAST(FLOOR(epoch_ms(now())/1000) AS BIGINT) - (SELECT cooldown_days FROM params)*86400)
ORDER BY dir;