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

Зачем Email Bounce Rate

Bounce = email returned не delivered. High bounce rate (>5%) — reputation hit от ISPs (Gmail, Yahoo). Дальнейшая deliverability снижается. Email list hygiene — critical.

Формула

Bounce Rate = bounced_emails / sent_emails × 100%

Soft vs Hard bounce

SELECT
    bounce_type,
    COUNT(*) AS bounces,
    COUNT(*)::NUMERIC * 100 / SUM(COUNT(*)) OVER () AS share_pct
FROM email_events
WHERE bounced = TRUE
  AND sent_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY bounce_type;

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

SELECT
    DATE_TRUNC('week', sent_at) AS week,
    campaign_id,
    COUNT(*) AS sent,
    COUNT(*) FILTER (WHERE bounce_type = 'hard') AS hard_bounces,
    COUNT(*) FILTER (WHERE bounce_type = 'soft') AS soft_bounces,
    COUNT(*) FILTER (WHERE bounced)::NUMERIC * 100 / COUNT(*) AS bounce_rate_pct,
    COUNT(*) FILTER (WHERE bounce_type = 'hard')::NUMERIC * 100 / COUNT(*) AS hard_bounce_pct
FROM email_events
WHERE sent_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1, 2
ORDER BY 1 DESC, hard_bounce_pct DESC;

По domain

Иногда whole domain blocks:

SELECT
    SUBSTRING(recipient_email FROM '@(.*)') AS domain,
    COUNT(*) AS sent,
    COUNT(*) FILTER (WHERE bounced) AS bounces,
    COUNT(*) FILTER (WHERE bounced)::NUMERIC * 100 / COUNT(*) AS bounce_rate
FROM email_events
WHERE sent_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
HAVING COUNT(*) >= 50
ORDER BY bounce_rate DESC;

Deliverability score

SELECT
    DATE_TRUNC('week', sent_at) AS week,
    COUNT(*) AS sent,
    COUNT(*) FILTER (WHERE bounced) AS bounced,
    COUNT(*) FILTER (WHERE marked_spam) AS spam_complaints,
    COUNT(*) FILTER (WHERE NOT bounced AND NOT marked_spam) AS deliverable,
    COUNT(*) FILTER (WHERE NOT bounced AND NOT marked_spam)::NUMERIC * 100 / COUNT(*) AS deliverability_pct
FROM email_events
WHERE sent_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

List hygiene query

Identify dead addresses:

SELECT
    recipient_email,
    COUNT(*) AS sent,
    COUNT(*) FILTER (WHERE bounce_type = 'hard') AS hard_bounces
FROM email_events
WHERE sent_at >= CURRENT_DATE - INTERVAL '180 days'
GROUP BY recipient_email
HAVING COUNT(*) FILTER (WHERE bounce_type = 'hard') >= 2
ORDER BY hard_bounces DESC;

Hard-bouncing addresses ≥ 2 times → remove.

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

Ошибка 1. Mix soft / hard bounce. Hard — permanent. Soft — temporary. Treat differently.

Ошибка 2. Не remove bouncers. Continued sending to bouncers → ISP downgrades reputation.

Ошибка 3. Acceptable threshold.

Ошибка 4. Compare new list to mature. New list — high bounce expected. Established — should be < 2%.

Ошибка 5. Bulk-purchase lists. Bought list = high bounce + spam complaints. Don't.

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

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

FAQ

Какой Bounce Rate ok?

< 2% — excellent. 2-5% — moderate. > 5% — needs cleaning.

Hard vs Soft?

Hard permanent (typo, deleted). Soft temporary (server full).

Auto-remove hard bouncers?

Yes — после first hard bounce typically.

Soft bounce retry?

3-5 attempts. After persistent — treat as hard.

Bounce от ISP throttling?

Some ISPs deliver slowly. Treat as soft.