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

Зачем аналитику уметь считать CTR в SQL

CTR (Click-Through Rate) — базовая метрика любого performance-маркетинга и продуктовых коммуникаций. Она входит в формулу CAC: CAC = CPM / (CTR × CR). Если CTR проседает — CAC растёт, бюджет сгорает, маркетинг команда паникует.

На практике аналитика просят посчитать CTR не один раз, а десятки: по каналам, кампаниям, креативам, сегментам аудитории, дням. BI-отчёт в Google Ads или Я.Метрике часто не даёт нужной нарезки — проще написать SQL и получить именно тот разрез, который нужен.

В статье — готовые запросы для разных сценариев:

• Общий CTR за период
• По каналам и кампаниям
• Динамика WoW с изменением
• Blended (взвешенный) CTR по кампаниям
• Сравнение A/B вариантов
• Доверительный интервал

Типичная схема данных — таблица ad_events(user_id, event_type, channel, campaign, event_at) или агрегированная ad_stats(channel, campaign, day, impressions, clicks).

Формула

CTR = Clicks / Impressions × 100%

1. Общий CTR

SELECT
    COUNT(*) FILTER (WHERE event_type = 'click')::FLOAT /
    COUNT(*) FILTER (WHERE event_type = 'impression') AS ctr
FROM ad_events;

Или через CASE:

SELECT
    100.0 * SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) /
    NULLIF(SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END), 0) AS ctr_pct
FROM ad_events;

2. CTR по каналам

SELECT
    channel,
    SUM(impressions) AS imps,
    SUM(clicks) AS clicks,
    100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr_pct
FROM ad_stats
GROUP BY channel
ORDER BY ctr_pct DESC;

3. CTR по дням

SELECT
    DATE(event_at) AS day,
    100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr_pct
FROM ad_stats
GROUP BY 1
ORDER BY 1;

4. WoW change

WITH weekly AS (
    SELECT
        DATE_TRUNC('week', event_at) AS week,
        100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr
    FROM ad_stats
    GROUP BY 1
)
SELECT
    week,
    ctr,
    LAG(ctr) OVER (ORDER BY week) AS prev_ctr,
    ctr - LAG(ctr) OVER (ORDER BY week) AS wow_diff
FROM weekly;

5. CTR по кампаниям с traffic share

SELECT
    campaign,
    SUM(impressions) AS imps,
    SUM(clicks) AS clicks,
    100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr,
    100.0 * SUM(impressions) / SUM(SUM(impressions)) OVER () AS traffic_share
FROM ad_stats
GROUP BY campaign;

6. Blended CTR (weighted)

Если CTR считать как простое среднее по кампаниям — неверно. Нужно weighted:

-- неверно (simple mean)
SELECT AVG(ctr) FROM campaigns;

-- верно (weighted by impressions)
SELECT SUM(clicks) / SUM(impressions) AS blended_ctr
FROM campaigns;

7. CTR с доверительным интервалом

SELECT
    channel,
    clicks,
    impressions,
    ctr,
    ctr - 1.96 * SQRT(ctr * (1 - ctr) / impressions) AS ci_lower,
    ctr + 1.96 * SQRT(ctr * (1 - ctr) / impressions) AS ci_upper
FROM (
    SELECT
        channel,
        SUM(clicks) AS clicks,
        SUM(impressions) AS impressions,
        SUM(clicks)::FLOAT / SUM(impressions) AS ctr
    FROM ad_stats
    GROUP BY channel
) t;

8. Сравнение A/B

SELECT
    variant,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    100.0 * SUM(clicks) / SUM(impressions) AS ctr_pct
FROM experiment
GROUP BY variant;

Типичные CTR benchmarks

• Search ads: 3-5%
• Display: 0.1-0.5%
• Social: 1-2%
• Email: 2-5%
• Push: 1-7%

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

Деление без NULLIF

/ 0 → ошибка.

Integer division

100 * 5 / 200 → 2 (integer). Используйте 100.0 * 5 / 200 → 2.5.

Average of averages

Простое среднее CTR по campaigns ≠ blended CTR. Нужно weighted.

Bot clicks

Bot filter важен перед подсчётом.

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

• Как посчитать конверсию в SQL
• Как посчитать процент в SQL
• Кейс: CTR упал

FAQ

CTR на impressions или на delivered?

Обычно impressions (подсчёт показанных). Для email — delivered.

Blended CTR правильно?

Weighted average по impressions. Simple mean — ошибочно.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.