Как посчитать Email Click Rate в SQL

Зачем Email Click Rate

Email CTR — % recipients, кто кликнул на ссылку в email. Глубже engagement metric чем open rate. Драйвер конверсии в downstream actions.

Формула

CTR = unique_clickers / total_recipients × 100%
CTOR (Click-to-Open Rate) = unique_clickers / unique_openers × 100%

CTR — overall efficiency. CTOR — quality content (учитывая что user открыл).

CTR vs CTOR

CTOR cleaner для A/B-тестов content.

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

WITH campaign_stats AS (
    SELECT
        campaign_id,
        COUNT(DISTINCT recipient_id) AS recipients,
        COUNT(DISTINCT recipient_id) FILTER (WHERE opened) AS unique_opens,
        COUNT(DISTINCT recipient_id) FILTER (WHERE clicked) AS unique_clicks,
        SUM(CASE WHEN clicked THEN total_clicks ELSE 0 END) AS total_clicks
    FROM email_events
    WHERE sent_at >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY campaign_id
)
SELECT
    campaign_id,
    recipients,
    unique_opens,
    unique_clicks,
    unique_opens::NUMERIC * 100 / NULLIF(recipients, 0) AS open_rate_pct,
    unique_clicks::NUMERIC * 100 / NULLIF(recipients, 0) AS ctr_pct,
    unique_clicks::NUMERIC * 100 / NULLIF(unique_opens, 0) AS ctor_pct
FROM campaign_stats
ORDER BY ctr_pct DESC;

По сегментам

SELECT
    u.segment,
    COUNT(DISTINCT e.recipient_id) AS sent_to,
    COUNT(DISTINCT e.recipient_id) FILTER (WHERE e.clicked) AS clicked,
    COUNT(DISTINCT e.recipient_id) FILTER (WHERE e.clicked)::NUMERIC * 100
    / NULLIF(COUNT(DISTINCT e.recipient_id), 0) AS ctr_pct
FROM email_events e
JOIN users u ON u.user_id = e.recipient_id
WHERE e.campaign_id = 'newsletter_2026_04'
GROUP BY u.segment
ORDER BY ctr_pct DESC;

По link / position

«Что в письме clicked»:

SELECT
    link_position,
    link_url,
    COUNT(*) AS clicks,
    COUNT(DISTINCT recipient_id) AS unique_clickers
FROM email_clicks
WHERE campaign_id = 'newsletter_2026_04'
GROUP BY link_position, link_url
ORDER BY clicks DESC;

Top-of-email links обычно have higher CTR.

A/B test subject lines

SELECT
    ab_variant,
    COUNT(DISTINCT recipient_id) AS recipients,
    COUNT(DISTINCT recipient_id) FILTER (WHERE opened)::NUMERIC * 100 / COUNT(DISTINCT recipient_id) AS open_rate,
    COUNT(DISTINCT recipient_id) FILTER (WHERE clicked)::NUMERIC * 100 / COUNT(DISTINCT recipient_id) AS ctr,
    COUNT(DISTINCT recipient_id) FILTER (WHERE clicked)::NUMERIC * 100 / NULLIF(COUNT(DISTINCT recipient_id) FILTER (WHERE opened), 0) AS ctor
FROM email_events
WHERE campaign_id = 'newsletter_2026_04'
  AND ab_variant IS NOT NULL
GROUP BY ab_variant;

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

Ошибка 1. Bot clicks. Email scanners (Gmail link prefetch) клики. False positive. Filter user-agent.

Ошибка 2. Unique vs total clicks. Same user clicks 3 times — total 3, unique 1. Pick metric per use.

Ошибка 3. CTR without context. 3% CTR — good или bad? Compare to benchmark.

Ошибка 4. Image-disable. Some clients не download images. Open tracking broken. CTOR искажен.

Ошибка 5. Apple Mail Privacy. iOS 15+ pre-opens. False opens inflate denominator for CTOR.

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

• Как посчитать email open rate в SQL
• Как посчитать push open rate в SQL
• Как посчитать unsubscribe rate в SQL
• Как посчитать CTR в SQL

FAQ

Какой CTR / CTOR ok?

CTR: 2-5% — average, 5-10% — strong. CTOR: 10-20% — average, 25%+ — excellent.

CTR vs CTOR — что использовать?

CTOR — controlling для open rate (cleaner). CTR — bottom-line metric.

Bot clicks как filter?

User-agent, time of click (<1 sec after open = bot). Email validation tools.

Apple Mail Privacy fix?

Use CTR over CTOR. Or use clicks directly без opens denominator.

CTR падает — причины?

1. List fatigue. 2) Poor segmentation. 3) Subject ≠ content. 4) Mobile UX issues.