CPM по кампаниям месяц к месяцу, вымышленный мебельный магазин
| Кампания | Рост CPM | CPM последнего месяца, руб. | CPM прошлого месяца, руб. |
|---|---|---|---|
| Поиск Кровати | 9,2 % | 2 724 ₽ | 2 495 ₽ |
| VK Диваны | 4,9 % | 193,69 ₽ | 184,62 ₽ |
| Поиск Диваны | 4,5 % | 3 358 ₽ | 3 214 ₽ |
| WB Диваны | 3,6 % | 1 447 ₽ | 1 397 ₽ |
| VK Освещение | 2,6 % | 149,92 ₽ | 146,16 ₽ |
| РСЯ Диваны | 2,2 % | 133,63 ₽ | 130,77 ₽ |
| Бренд | 1,2 % | 817,85 ₽ | 807,92 ₽ |
| Ozon Шкафы | 0,2 % | 1 112 ₽ | 1 110 ₽ |
| РСЯ Шкафы | -0,4 % | 108,44 ₽ | 108,89 ₽ |
| Ретаргетинг | -1,8 % | 1 588 ₽ | 1 617 ₽ |
| WB Освещение | -4,2 % | 880,83 ₽ | 919,29 ₽ |
| VK Кухни | -4,9 % | 199,23 ₽ | 209,41 ₽ |
| Ozon Кровати | -8,4 % | 1 107 ₽ | 1 208 ₽ |
| Ozon Освещение | -9,5 % | 556,22 ₽ | 614,33 ₽ |
| Поиск Кухни | -9,5 % | 3 808 ₽ | 4 208 ₽ |
SQL, по которому посчитано
WITH bounds AS (
SELECT CAST(date_trunc('month', max(date)) AS DATE) AS last_month_start,
CAST(date_trunc('month', max(date)) - INTERVAL '1 month' AS DATE) AS prev_month_start,
CAST(date_trunc('month', max(date)) - INTERVAL '2 months' AS DATE) AS quarter_start
FROM ads
),
monthly AS (
SELECT a.campaign,
CAST(date_trunc('month', a.date) AS DATE) AS month,
SUM(a.cost) AS cost,
SUM(a.impressions) AS impressions
FROM ads a
CROSS JOIN bounds b
WHERE a.date >= b.quarter_start
AND a.date < CAST(b.last_month_start + INTERVAL '1 month' AS DATE)
GROUP BY a.campaign, CAST(date_trunc('month', a.date) AS DATE)
),
pivoted AS (
SELECT m.campaign,
MAX(CASE WHEN m.month = b.prev_month_start THEN m.cost END) AS prev_cost,
MAX(CASE WHEN m.month = b.prev_month_start THEN m.impressions END) AS prev_impressions,
MAX(CASE WHEN m.month = b.last_month_start THEN m.cost END) AS last_cost,
MAX(CASE WHEN m.month = b.last_month_start THEN m.impressions END) AS last_impressions
FROM monthly m
CROSS JOIN bounds b
GROUP BY m.campaign
),
cpm AS (
SELECT campaign,
CASE WHEN prev_impressions > 0 THEN prev_cost / prev_impressions * 1000 END AS cpm_prev_month,
CASE WHEN last_impressions > 0 THEN last_cost / last_impressions * 1000 END AS cpm_last_month
FROM pivoted
WHERE prev_impressions > 0 AND last_impressions > 0
)
SELECT campaign,
round(cpm_prev_month, 2) AS cpm_prev_month,
round(cpm_last_month, 2) AS cpm_last_month,
round(cpm_last_month / cpm_prev_month - 1, 4) AS cpm_growth
FROM cpm
WHERE cpm_prev_month > 0
ORDER BY cpm_growth DESC
LIMIT 30