夜間關廣告

半夜到清晨這段沒人買的時間,自動建議把廣告關掉省預算,過了時段再開回來。

設定

建議動作

目前沒有要處理的活動。

進階:SQL

-- 由設定表單自動產生。新手用表單填,進階可直接改下面的 SQL。
WITH params AS (
  SELECT 0 AS off_start, 6 AS off_end
),
nowh AS (SELECT CAST(EXTRACT('hour' FROM current_localtimestamp()) AS INTEGER) AS h),
win AS (SELECT (SELECT off_start FROM params) AS s, (SELECT off_end FROM params) AS e),
flag AS (   -- 現在是否落在關閉時段(支援跨午夜)
  SELECT CASE WHEN s <= e
              THEN (SELECT h FROM nowh) >= s AND (SELECT h FROM nowh) < e
              ELSE (SELECT h FROM nowh) >= s OR  (SELECT h FROM nowh) < e END AS in_window
  FROM win
)
SELECT c.id AS campaign_id, c.name,
   'pause' AS action, NULL AS param,
   '現在 ' || (SELECT h FROM nowh) || ' 點 · 關閉時段 ' ||
     (SELECT s FROM win) || '–' || (SELECT e FROM win) || ' 點 → 關廣告' AS detail
FROM campaigns c
WHERE (SELECT in_window FROM flag) AND c.status='active'
UNION ALL
SELECT c.id AS campaign_id, c.name,
   'resume' AS action, NULL AS param,
   '已過關閉時段 → 開回廣告' AS detail
FROM campaigns c
WHERE NOT (SELECT in_window FROM flag) AND c.status='paused'
  -- 只開回「夜間關廣告」自己關掉、且之後沒人再動過的活動(避免把止血暫停的也開回來)
  AND (SELECT a.action        FROM actions a WHERE a.campaign_id=c.id ORDER BY a.ts DESC LIMIT 1)='pause'
  AND (SELECT a.strategy_name FROM actions a WHERE a.campaign_id=c.id ORDER BY a.ts DESC LIMIT 1)='夜間關廣告'
ORDER BY 1;