Как посчитать 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-изменение

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 по кампаниям с долей трафика

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 (взвешенный)

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

-- неверно (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-бенчмарки

• Поисковая реклама: 3-5%
• Медийная реклама: 0.1-0.5%
• Соцсети: 1-2%
• Email-рассылки: 2-5%
• Push-уведомления: 1-7%

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

Деление без NULLIF

/ 0 → ошибка.

Целочисленное деление

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

Среднее из средних

Простое среднее CTR по кампаниям ≠ blended CTR. Нужно считать взвешенный.

Клики ботов

Отсекать ботов важно перед подсчётом.

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

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

FAQ

CTR считать от показов или от доставленных?

Обычно CTR считают от показов (impressions) — это знаменатель по умолчанию в рекламных системах. В email-маркетинге база другая: там CTR обычно считают от доставленных писем (delivered), а не от отправленных.

Как правильно считать blended CTR?

Blended CTR — это взвешенное по числу показов среднее, а не простое среднее CTR отдельных кампаний. Если усреднить проценты напрямую, маленькая кампания с высоким CTR исказит общую картину, поэтому нужно делить суммарные клики на суммарные показы.