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

Зачем Unsubscribe Rate

Unsubscribe Rate — % recipients, кто отписался после конкретного email или per period. High rate → content mismatch / frequency. Combined со spam complaints — major deliverability risk.

Формула

Unsubscribe Rate (per campaign) = unsubs_from_campaign / recipients × 100%
Unsubscribe Rate (period) = unsubs_in_period / total_subscribers × 100%

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

WITH campaign_stats AS (
    SELECT
        campaign_id,
        COUNT(DISTINCT recipient_id) AS recipients,
        COUNT(DISTINCT recipient_id) FILTER (WHERE unsubscribed) AS unsubs
    FROM email_events
    WHERE sent_at >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY campaign_id
)
SELECT
    campaign_id,
    recipients,
    unsubs,
    unsubs::NUMERIC * 100 / NULLIF(recipients, 0) AS unsub_rate_pct
FROM campaign_stats
ORDER BY unsub_rate_pct DESC;

По campaigns

SELECT
    c.campaign_id,
    c.campaign_name,
    c.subject,
    COUNT(DISTINCT e.recipient_id) AS sent,
    COUNT(DISTINCT e.recipient_id) FILTER (WHERE e.unsubscribed) AS unsubs,
    COUNT(DISTINCT e.recipient_id) FILTER (WHERE e.unsubscribed)::NUMERIC * 100
    / NULLIF(COUNT(DISTINCT e.recipient_id), 0) AS unsub_pct
FROM email_events e
JOIN campaigns c USING (campaign_id)
WHERE e.sent_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY c.campaign_id, c.campaign_name, c.subject
HAVING COUNT(DISTINCT e.recipient_id) >= 100
ORDER BY unsub_pct DESC
LIMIT 20;

Campaigns с high unsub rate — identify pattern (subject, content type, send time).

Net list growth

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', event_date) AS month,
        COUNT(*) FILTER (WHERE action = 'subscribed') AS new_subs,
        COUNT(*) FILTER (WHERE action = 'unsubscribed') AS unsubs
    FROM list_events
    WHERE event_date >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY 1
)
SELECT
    month,
    new_subs,
    unsubs,
    new_subs - unsubs AS net_growth,
    -- Cumulative list size
    SUM(new_subs - unsubs) OVER (ORDER BY month) AS list_size_cumulative
FROM monthly
ORDER BY month;

По segment

SELECT
    u.acquisition_source,
    COUNT(*) AS total_subscribers,
    COUNT(*) FILTER (WHERE unsubscribed) AS unsubs,
    COUNT(*) FILTER (WHERE unsubscribed)::NUMERIC * 100 / COUNT(*) AS unsub_pct
FROM email_subscribers s
JOIN users u USING (user_id)
WHERE s.subscribed_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY u.acquisition_source
ORDER BY unsub_pct DESC;

Channels с high unsub → reconsider что offers были к acquisition.

Time-to-Unsubscribe

WITH timings AS (
    SELECT
        user_id,
        subscribed_at,
        unsubscribed_at,
        EXTRACT(EPOCH FROM (unsubscribed_at - subscribed_at)) / 86400 AS days_subscribed
    FROM email_subscribers
    WHERE unsubscribed_at IS NOT NULL
)
SELECT
    AVG(days_subscribed) AS avg_days,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_subscribed) AS median_days,
    COUNT(*) FILTER (WHERE days_subscribed <= 7) AS unsub_first_week,
    COUNT(*) FILTER (WHERE days_subscribed <= 7)::NUMERIC * 100 / COUNT(*) AS pct_first_week
FROM timings;

First-week unsubs — onboarding promise mismatch.

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

Ошибка 1. Spam complaints не tracked. Some unsubscribes — actual spam reports. ISP impact bigger. Track separately.

Ошибка 2. Acceptable threshold. < 0.2% — excellent. 0.2-0.5% — average. > 1% — alarming.

Ошибка 3. Aggregate hides patterns. Average 0.3% normal, но certain campaign 2% — drill.

Ошибка 4. Re-subscribe handling. User unsub, re-sub. Counted? Per period — yes.

Ошибка 5. Soft unsubscribe. User stopped opening, didn't formally unsub. «Implicit unsub» — track separately.

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

• Как посчитать email open rate в SQL
• Как посчитать email click rate в SQL
• Как посчитать email bounce rate в SQL
• Как посчитать churn в SQL

FAQ

Какой Unsubscribe Rate ok?

< 0.2% per email — excellent. 0.5% threshold. > 1% — issue.

Spam complaints vs unsubs?

Spam complaints много worse — direct ISP reputation impact.

Unsubscribe one-click?

GDPR / industry best practice. One-click required для bulk senders.

Soft unsub какой track?

Inactive subscribers (no opens 6 months) — «cold». Re-engagement campaign or remove.

Unsubs spike — что делать?

Check: 1) Subject mismatch content. 2) Frequency. 3) Segment misalignment. 4) Recent send list quality.