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

Зачем композитный score

Один числовой engagement score удобнее десятка отдельных метрик. Combined active days × sessions × actions даёт «насколько глубоко юзер вовлечён». Применяется для:

• сегментации в CRM
• триггеров для retention-кампаний
• определения risk-юзеров

Формула

Простейший вариант — weighted sum нормированных компонент:

score = w1 × normalized(active_days)
      + w2 × normalized(sessions)
      + w3 × normalized(actions)

Веса w1, w2, w3 — сумма 1. Часто 0.4/0.3/0.3 или 0.5/0.3/0.2.

Альтернатива — decile-based: каждая метрика → decile (1-10), score = sum деципей.

Score в SQL

WITH user_metrics AS (
    SELECT
        u.user_id,
        COUNT(DISTINCT DATE(e.event_timestamp)) AS active_days,
        COUNT(DISTINCT e.session_id) AS sessions,
        SUM(CASE WHEN e.event_name IN ('click', 'submit', 'share', 'save') THEN 1 ELSE 0 END) AS actions
    FROM users u
    LEFT JOIN events e ON e.user_id = u.user_id
       AND e.event_timestamp >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY u.user_id
),
deciles AS (
    SELECT
        user_id,
        NTILE(10) OVER (ORDER BY active_days) AS days_decile,
        NTILE(10) OVER (ORDER BY sessions) AS sessions_decile,
        NTILE(10) OVER (ORDER BY actions) AS actions_decile
    FROM user_metrics
)
SELECT
    user_id,
    days_decile,
    sessions_decile,
    actions_decile,
    (days_decile + sessions_decile + actions_decile) AS engagement_score
FROM deciles
ORDER BY engagement_score DESC;

Score 30 (max) = top decile по всем 3 метрикам. Score 3 (min) = bottom по всем.

Сегментация юзеров

WITH scored AS (
    SELECT user_id, engagement_score FROM engagement_table
)
SELECT
    CASE
        WHEN engagement_score >= 25 THEN 'champion'
        WHEN engagement_score >= 18 THEN 'engaged'
        WHEN engagement_score >= 12 THEN 'casual'
        WHEN engagement_score >= 6  THEN 'low'
        ELSE 'at_risk'
    END AS segment,
    COUNT(*) AS users
FROM scored
GROUP BY 1
ORDER BY 1;

Champions = top пользователи, target для retention. At_risk = кандидаты на churn-prevention email.

Score в динамике

WITH weekly_score AS (
    SELECT
        user_id,
        DATE_TRUNC('week', period_start)::DATE AS week,
        engagement_score
    FROM weekly_engagement
)
SELECT
    week,
    AVG(engagement_score) AS avg_score,
    AVG(engagement_score) FILTER (WHERE user_id IN (SELECT user_id FROM weekly_engagement WHERE week = CURRENT_DATE - INTERVAL '12 weeks')) AS old_cohort_avg
FROM weekly_score
GROUP BY week
ORDER BY week;

Растущий score = улучшается продукт. Падающий = что-то идёт не так.

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

Ошибка 1. Веса наугад. 0.5/0.3/0.2 — стандарт, но для каждого продукта оптимальные веса разные. Calibrate по correlation с revenue.

Ошибка 2. Не нормировать. Active days [0,30], sessions [0,500], actions [0,10000] — без нормировки sessions/actions подавляют active_days.

Ошибка 3. Decile на маленьких группах. < 100 юзеров → decile = noise. Используйте binary cutoffs.

Ошибка 4. Не учитывать новых. Новый юзер с 5 active days неделя1 — это потенциально power. Но в decile он скорее всего low.

Ошибка 5. Score без actionable threshold. Если score 15 — «средне» — никто не реагирует. Tie thresholds к campaigns.

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

• Как посчитать engagement в SQL
• Как посчитать power users в SQL
• Как посчитать stickiness в SQL
• Как посчитать churn risk score в SQL

FAQ

Какие компоненты включить?

Стандарт: active days, sessions, actions. Можно revenue, social shares.

Сколько decile?

10 — стандарт. 5 для intuitive segments.

Score меняется быстро?

В неделю — да. Используйте rolling 4-week для стабильности.

Можно ли ML для весов?

Да — regression engagement_score vs target (revenue, retention). Получите optimal weights.

Score = predictor для churn?

Часто да. Низкий score 2-3 недели подряд → high churn risk.