Как посчитать email engagement score в SQL

Зачем engagement score

Open rate сам по себе ничего не говорит про субъективное «активность». Engagement score = композит opens, clicks, replies с весом по recency. Высокий score → купят, низкий → к re-engagement или suppression. Это база для smart sending — посылать только тем, кто открывает.

Компоненты

Стандартный композит:

• opens последние 30 дней
• clicks последние 30 дней
• replies (для transactional)
• weighted by recency (свежие важнее)

Score в SQL

WITH subscriber_events AS (
    SELECT
        subscriber_id,
        COUNT(*) FILTER (WHERE event_type = 'open' AND event_date >= CURRENT_DATE - INTERVAL '7 days') AS opens_7d,
        COUNT(*) FILTER (WHERE event_type = 'open' AND event_date >= CURRENT_DATE - INTERVAL '30 days') AS opens_30d,
        COUNT(*) FILTER (WHERE event_type = 'click' AND event_date >= CURRENT_DATE - INTERVAL '30 days') AS clicks_30d,
        COUNT(*) FILTER (WHERE event_type = 'reply' AND event_date >= CURRENT_DATE - INTERVAL '30 days') AS replies_30d
    FROM email_events
    GROUP BY subscriber_id
)
SELECT
    subscriber_id,
    opens_7d * 3
    + opens_30d * 1
    + clicks_30d * 5
    + replies_30d * 10 AS engagement_score
FROM subscriber_events
ORDER BY engagement_score DESC;

Веса 3/1/5/10 — стартовые. Calibrate под бизнес (revenue correlation).

Сегментация

WITH scored AS (
    SELECT subscriber_id, engagement_score FROM engagement_table
)
SELECT
    CASE
        WHEN engagement_score >= 30 THEN 'champion'
        WHEN engagement_score >= 10 THEN 'engaged'
        WHEN engagement_score >= 3  THEN 'casual'
        WHEN engagement_score >= 1  THEN 'at_risk'
        ELSE 'dormant'
    END AS segment,
    COUNT(*) AS subscribers
FROM scored
GROUP BY 1
ORDER BY 1;

Dormant — кандидаты на re-engagement или suppress. Champions — высокая частота / VIP-кампании.

Time decay

Свежие события важнее. Простой decay через exponential weight:

WITH weighted AS (
    SELECT
        subscriber_id,
        SUM(
            CASE event_type
                WHEN 'open' THEN 1
                WHEN 'click' THEN 5
                WHEN 'reply' THEN 10
            END
            * EXP(-(CURRENT_DATE - event_date)::NUMERIC / 30)
        ) AS time_decayed_score
    FROM email_events
    WHERE event_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY subscriber_id
)
SELECT * FROM weighted
ORDER BY time_decayed_score DESC;

EXP(-days/30) — half-life ~21 день. Open 5 дней назад весит больше, чем 60 дней назад.

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

Ошибка 1. Apple Mail Privacy Protection (MPP). С 2021 года Apple предзагружает images → всё показывается как «open». Open rate inflated. Используйте click как primary signal.

Ошибка 2. Веса = guess. 0/1/5/10 — стартовые. Оптимизируйте через regression на revenue.

Ошибка 3. Не учитывать tenure. Свежий подписчик с 0 opens — нормально. Старый с 0 opens — at_risk.

Ошибка 4. Без time decay. Open 6 месяцев назад = open вчера в чистом count. Не информативно.

Ошибка 5. Игнорировать unsubscribe rate в сегменте. Dormant юзеры с unsubscribe history — не «sleeping», а активно избегают. Suppress.

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

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

FAQ

MPP — что делать?

Открытия от Apple Mail = unreliable. Полагайтесь на click rate.

Какой score чемпион?

Top decile в вашей базе. Зависит от sending frequency.

Score как North Star?

Часто да — особенно для smart sending automation.

Можно ли ML?

Yes — gradient boosting на revenue. Но rules-based хорошо стартует.

Update score каждый день?

Daily — норм. Real-time не нужно.