Кампании с высоким CPM и низким CTR за последний месяц
| CPM, руб. | CTR | Расход, руб. | Показы | Кампания |
|---|---|---|---|---|
| 3 808 ₽ | 5,3 % | 297 831 ₽ | 78 209 | Яндекс Директ / Поиск Кухни |
| 199,23 ₽ | 0,5 % | 108 299 ₽ | 543 593 | VK Реклама / VK Кухни |
SQL, по которому посчитано
WITH last_month AS (
SELECT date_trunc('month', max(date)) AS month_start
FROM ads
),
campaign_stats AS (
SELECT
source,
campaign,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
SUM(cost) AS cost,
ROUND(SUM(cost) / NULLIF(SUM(impressions), 0) * 1000, 2) AS cpm,
ROUND(SUM(clicks) * 1.0 / NULLIF(SUM(impressions), 0), 4) AS ctr
FROM ads
WHERE date >= (SELECT month_start FROM last_month)
AND date < (SELECT month_start + INTERVAL 1 MONTH FROM last_month)
GROUP BY source, campaign
HAVING SUM(impressions) > 0
),
source_bench AS (
SELECT
source,
MEDIAN(cpm) AS median_cpm,
MEDIAN(ctr) AS median_ctr
FROM campaign_stats
GROUP BY source
)
SELECT
c.source || ' / ' || c.campaign AS campaign_label,
c.impressions,
c.clicks,
ROUND(c.cost, 2) AS cost,
c.cpm,
c.ctr
FROM campaign_stats c
JOIN source_bench b USING (source)
WHERE c.cpm > b.median_cpm
AND c.ctr < b.median_ctr
ORDER BY c.cost DESC
LIMIT 30