Как посчитать CPM в SQL

Зачем CPM

CPM = Cost Per Mille = стоимость 1000 показов. Brand awareness campaigns optimize for CPM. Без CPC bidding model.

Формула

CPM = total_spend / total_impressions × 1000

Базовый расчёт

SELECT
    DATE_TRUNC('week', DATE) AS week,
    SUM(spend) AS spend,
    SUM(impressions) AS impressions,
    SUM(spend)::NUMERIC * 1000 / NULLIF(SUM(impressions), 0) AS cpm
FROM ad_performance
WHERE DATE >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

По каналам

SELECT
    channel,
    placement,
    SUM(spend) AS spend,
    SUM(impressions) AS impressions,
    SUM(spend)::NUMERIC * 1000 / NULLIF(SUM(impressions), 0) AS cpm,
    SUM(clicks) AS clicks,
    SUM(clicks)::NUMERIC * 100 / NULLIF(SUM(impressions), 0) AS ctr_pct
FROM ad_performance
WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY channel, placement
ORDER BY cpm;

eCPM

«Effective CPM» — used когда campaign оплачивается по CPC, но измеряется в CPM:

SELECT
    campaign_id,
    SUM(spend) AS spend,
    SUM(impressions) AS impressions,
    SUM(clicks) AS clicks,
    -- Actual CPC paid
    SUM(spend)::NUMERIC / NULLIF(SUM(clicks), 0) AS cpc,
    -- eCPM derived
    SUM(spend)::NUMERIC * 1000 / NULLIF(SUM(impressions), 0) AS ecpm
FROM ad_performance
WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY campaign_id
ORDER BY ecpm DESC;

eCPM сравнивает CPM- и CPC-campaigns на одной шкале.

Trend

SELECT
    DATE_TRUNC('week', DATE) AS week,
    SUM(spend)::NUMERIC * 1000 / NULLIF(SUM(impressions), 0) AS cpm,
    AVG(SUM(spend)::NUMERIC * 1000 / NULLIF(SUM(impressions), 0)) OVER (
        ORDER BY DATE_TRUNC('week', DATE) ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS cpm_ma4w
FROM ad_performance
WHERE DATE >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY 1
ORDER BY 1;

Частые ошибки

Ошибка 1. Impression definition. «Viewable impression» vs «served impression» — different counts. MRC standards: 50% on-screen for 1s.

Ошибка 2. Frequency. High CPM, но same person seen 50 times — wasted. Track reach + frequency.

Ошибка 3. Bot traffic. IVT (Invalid Traffic). Filters by platform vary.

Ошибка 4. CPM × 1000 confusion. ×1000 в knownler. Forgot — got CPM в copies / cents.

Ошибка 5. Brand vs performance. Low CPM не всегда лучше. Cheap impressions могут быть низкого quality.

Связанные темы

• Как посчитать CPC в SQL
• Как посчитать CTR в SQL
• Как посчитать CAC в SQL
• Как посчитать marketing ROI в SQL

FAQ

Какой CPM ok?

Display: $1-10. Premium video: $20-50. CTV / OTT: $15-40. LinkedIn: $20-60.

CPM vs CPC vs CPA?

CPM — pay per impression. CPC — pay per click. CPA — pay per acquisition. Risk shifts to advertiser at CPM.

eCPM зачем?

Compare CPC/CPM campaigns. eCPM normalize.

Brand vs performance CPM?

Brand CPM higher — premium placements. Performance — lowest CPM acceptable.

CPM растёт — почему?

Auction competition. Better quality scores reduce. Out-of-season slot premium.