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

Зачем list hygiene

Чем выше доля inactive в списке, тем хуже sender reputation. Mailbox-провайдеры (Gmail, Yahoo) учитывают engagement — низкий avg open rate = throttling. List hygiene — регулярное удаление inactive чтобы сохранить deliverability.

Health сегменты

• Active: открыл за last 90 days
• At-risk: 90-180 days без open
• Dormant: 180+ days без open
• Hard bounced / spam complaints: removed automatically

Hygiene в SQL

WITH subscriber_health AS (
    SELECT
        s.subscriber_id,
        MAX(e.event_date) FILTER (WHERE e.event_type = 'open') AS last_open,
        EXTRACT(EPOCH FROM (CURRENT_DATE - MAX(e.event_date) FILTER (WHERE e.event_type = 'open'))) / 86400 AS days_since_open,
        BOOL_OR(e.event_type = 'hard_bounce') AS has_hard_bounce,
        BOOL_OR(e.event_type = 'spam_complaint') AS has_spam_complaint
    FROM subscribers s
    LEFT JOIN email_events e USING (subscriber_id)
    GROUP BY s.subscriber_id
)
SELECT
    CASE
        WHEN has_hard_bounce OR has_spam_complaint THEN 'remove'
        WHEN days_since_open IS NULL THEN 'never_opened'
        WHEN days_since_open <= 90  THEN 'active'
        WHEN days_since_open <= 180 THEN 'at_risk'
        ELSE 'dormant'
    END AS health_segment,
    COUNT(*) AS subscribers,
    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS pct
FROM subscriber_health
GROUP BY 1
ORDER BY 1;

Active > 60% — здоровый. < 40% — нужен cleanup.

Candidates to remove

SELECT subscriber_id, last_open, days_since_open
FROM subscriber_health
WHERE has_hard_bounce
   OR has_spam_complaint
   OR days_since_open > 365
ORDER BY days_since_open DESC NULLS FIRST;

Эти кандидаты — на suppress / soft-delete (не отправляем, но не удаляем для compliance).

Re-engagement программа

Перед удалением:

WITH at_risk AS (
    SELECT subscriber_id
    FROM subscriber_health
    WHERE days_since_open BETWEEN 90 AND 180
),
last_30d_campaign AS (
    SELECT DISTINCT subscriber_id
    FROM email_events
    WHERE event_type = 'open'
      AND event_date >= CURRENT_DATE - INTERVAL '30 days'
      AND campaign_name = 'we_miss_you'
)
SELECT
    'reactivated' AS status,
    COUNT(*) AS users
FROM last_30d_campaign

UNION ALL

SELECT
    'didnt_reactivate',
    (SELECT COUNT(*) FROM at_risk) - (SELECT COUNT(*) FROM last_30d_campaign);

15-30% reactivate — норма. Остальные — suppress.

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

Ошибка 1. Удалять inactive сразу. Иногда зашёл раз в полгода и купил. Re-engagement first.

Ошибка 2. Hard delete вместо suppression. Compliance (GDPR) требует трекать opt-out. Suppression list, не hard delete.

Ошибка 3. Не различать new vs old inactive. Новый подписчик ни разу не открыл = новичок, ещё не привык. Старый с 180 дней без open = real dormant.

Ошибка 4. Игнорировать spam complaints. Spam complaint = немедленный removal. Reputation страдает быстро.

Ошибка 5. Один cutoff для всех. Newsletter weekly: 30 дней без open — at_risk. Monthly: 90 дней. Калибруйте под cadence.

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

• Как посчитать email list decay в SQL
• Как посчитать unsubscribe rate в SQL
• Как посчитать email deliverability в SQL
• Как посчитать email engagement score в SQL

FAQ

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

Quarterly как стандарт. Чаще для high-volume.

Hard delete или suppress?

Suppress всегда. GDPR требует трекать opt-out история.

Re-engagement сколько emails?

3-5 emails в течение 30 дней. Дальше — suppress.

Cleanup убивает deliverability?

Краткосрочно — иногда падение open rate. Долгосрочно — улучшение reputation.

Хорошая active share?

60-80% для weekly newsletter. 40-60% для monthly.