整店每日預算上限

所有進行中活動的每日預算加起來超過你設的上限,就建議每個活動按比例調降到上限以內。

設定

建議動作

目前沒有要處理的活動。

進階:SQL

-- 由設定表單自動產生。新手用表單填,進階可直接改下面的 SQL。
WITH params AS (
  SELECT 18000 AS cap, 7 AS cooldown_days
),
tot AS (   -- 整店進行中活動的每日預算總和
  SELECT SUM(daily_budget) AS total FROM campaigns WHERE status='active'
)
SELECT c.id AS campaign_id, c.name,
   'budget_down' AS action,
   CAST(ROUND((1 - (SELECT cap FROM params) / (SELECT total FROM tot)) * 100) AS INT) AS param,
   '整店日預算 $' || CAST((SELECT total FROM tot) AS INT) || ' 超過上限 $' || CAST((SELECT cap FROM params) AS INT) ||
     ' → 各活動一起降 ' || CAST(ROUND((1 - (SELECT cap FROM params) / (SELECT total FROM tot)) * 100) AS INT) || '%' AS detail,
   CAST(c.daily_budget AS INT) AS daily_budget
FROM campaigns c
WHERE c.status='active' AND (SELECT total FROM tot) > (SELECT cap FROM params)
  AND NOT EXISTS (SELECT 1 FROM actions a WHERE a.campaign_id = c.id AND a.action='budget_down'
        AND a.ts > CAST(FLOOR(epoch_ms(now())/1000) AS BIGINT) - (SELECT cooldown_days FROM params)*86400)
ORDER BY c.daily_budget DESC;