Как посчитать email list decay в SQL

Зачем list decay

Email-лист стареет: подписчики меняют адреса, отписываются, mailbox-провайдеры начинают резать. Industry-стандарт: 22-30% decay per year. Если acquisition не покрывает decay, размер активного списка падает. List decay rate — главный KPI для email marketing команды.

Формула

decay_rate = (hard_bounces + unsubscribes + spam_complaints + inactive)
            / start_of_period_size

Месячная decay × 12 ≠ annual (компаунд). Лучше считать annual напрямую через cohort.

Decay в SQL

Помесячный decay rate:

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', event_date)::DATE AS month,
        COUNT(*) FILTER (WHERE event_type = 'hard_bounce') AS hard_bounces,
        COUNT(*) FILTER (WHERE event_type = 'unsubscribe') AS unsubscribes,
        COUNT(*) FILTER (WHERE event_type = 'spam_complaint') AS spam_complaints
    FROM email_events
    WHERE event_date >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY DATE_TRUNC('month', event_date)
),
list_size AS (
    SELECT
        DATE_TRUNC('month', subscribed_at)::DATE AS month,
        COUNT(*) AS active_at_start
    FROM subscribers
    WHERE subscribed_at < CURRENT_DATE - INTERVAL '12 months'
    GROUP BY DATE_TRUNC('month', subscribed_at)
)
SELECT
    m.month,
    m.hard_bounces + m.unsubscribes + m.spam_complaints AS lost,
    l.active_at_start,
    (m.hard_bounces + m.unsubscribes + m.spam_complaints)::NUMERIC
    / NULLIF(l.active_at_start, 0) * 100 AS monthly_decay_pct
FROM monthly m
JOIN list_size l USING (month)
ORDER BY m.month;

2-3% monthly = ~25-30% annual decay.

Прогноз размера списка

Без acquisition:

WITH config AS (
    SELECT 100000 AS current_size, 0.025 AS monthly_decay_rate
)
SELECT
    month_offset,
    ROUND(current_size * POWER(1 - monthly_decay_rate, month_offset)) AS projected_size
FROM config, generate_series(0, 12) month_offset;

100k → 75k через год при 2.5% monthly decay.

Decay по сегментам

SELECT
    s.acquisition_source,
    COUNT(*) FILTER (WHERE e.event_type IN ('hard_bounce', 'unsubscribe', 'spam_complaint'))::NUMERIC
    * 100 / NULLIF(COUNT(DISTINCT s.subscriber_id), 0) AS decay_pct
FROM subscribers s
LEFT JOIN email_events e ON e.subscriber_id = s.subscriber_id
   AND e.event_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY s.acquisition_source
ORDER BY decay_pct DESC;

Каналы с paid lead-magnet часто декай 5-8%/мес — низкое качество.

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

Ошибка 1. Смешивать hard+soft bounces. Soft bounce — временно (full mailbox). Hard — permanent. Decay = hard only.

Ошибка 2. Считать inactive как permanently lost. Если юзер 90 дней не открывал — это inactive, но возможно открывается раз в полгода.

Ошибка 3. Compound vs simple. Annual decay = 1 - (1 - monthly)^12. Не 12 × monthly.

Ошибка 4. Decay без cohort. Cohort 2024 vs 2025 имеют разный decay. Mix вводит в заблуждение.

Ошибка 5. Игнорировать spam complaints. Spam — strong signal: sender reputation падает, ESP начинает резать deliverability.

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

• Как посчитать unsubscribe rate в SQL
• Как посчитать email bounce rate в SQL
• Как посчитать email deliverability в SQL
• Как посчитать email list hygiene в SQL

FAQ

Какой decay норма?

B2C: 22-30%/год. B2B: 15-25%. Newsletters: до 40%.

Acquisition должна покрывать?

В net growth — да. Иначе список уменьшается несмотря на active campaigns.

Inactive vs hard bounce?

Inactive — не открывает 90+ дней (есть надежда). Hard bounce — адрес не существует.

Можно ли возродить inactive?

Re-engagement кампания: 15-30% возвращаются. Остальные — на removal.

Когда чистить?

Раз в quarter — стандарт. Чаще для high-volume.