Рост CPA по кампаниям в последнем месяце к предыдущему
SQL, по которому посчитано
WITH monthly AS (
SELECT
campaign,
CAST(date_trunc('month', date) AS DATE) AS month,
sum(cost) AS cost,
sum(orders) AS orders
FROM ads
WHERE date >= (
SELECT CAST(date_trunc('month', max(date)) - INTERVAL '1 month' AS DATE)
FROM ads
)
GROUP BY campaign, CAST(date_trunc('month', date) AS DATE)
),
calc AS (
SELECT
campaign,
month,
CASE WHEN orders = 0 THEN NULL ELSE cost / orders END AS cpa
FROM monthly
),
current_prev AS (
SELECT
campaign,
max(CASE WHEN month = (SELECT CAST(date_trunc('month', max(date)) AS DATE) FROM ads) THEN cpa END) AS cpa_current_raw,
max(CASE WHEN month = (SELECT CAST(date_trunc('month', max(date)) - INTERVAL '1 month' AS DATE) FROM ads) THEN cpa END) AS cpa_prev_raw
FROM calc
GROUP BY campaign
)
SELECT
campaign,
round(cpa_current_raw, 2) AS cpa_current,
round(cpa_prev_raw, 2) AS cpa_prev,
round((cpa_current_raw - cpa_prev_raw) / cpa_prev_raw, 4) AS cpa_growth
FROM current_prev
WHERE cpa_current_raw IS NOT NULL
AND cpa_prev_raw IS NOT NULL
AND cpa_prev_raw > 0
ORDER BY cpa_growth DESC
LIMIT 30