CTR 比上週下滑超過 30% · 偵測

提醒

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;