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

Зачем Engagement

DAU/MAU/sessions говорят: «зашёл / не зашёл». Engagement отвечает: «насколько активно зашёл». Это глубинная метрика — отличает поверхностного юзера от лояльного.

Что такое Engagement

Engagement — составная метрика взаимодействия пользователя с продуктом за период.

Универсальной формулы нет. Чаще всего:

Engagement Score = w1·sessions + w2·duration + w3·key_events + ...

где w* — веса действий по важности.

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

Простой engagement: число key events за неделю на пользователя.

WITH user_engagement AS (
    SELECT
        user_id,
        COUNT(*) FILTER (WHERE event_type = 'session_start') AS sessions,
        COUNT(*) FILTER (WHERE event_type = 'click') AS clicks,
        COUNT(*) FILTER (WHERE event_type = 'comment') AS comments,
        COUNT(*) FILTER (WHERE event_type = 'share') AS shares
    FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY user_id
)
SELECT
    user_id,
    sessions,
    clicks,
    comments,
    shares,
    sessions + 0.5 * clicks + 2 * comments + 5 * shares AS engagement_score
FROM user_engagement
ORDER BY engagement_score DESC;

Веса: session 1, click 0.5 (легко), comment 2 (усилие), share 5 (advocacy).

Weighted Engagement Score

WITH event_weights AS (
    SELECT * FROM (VALUES
        ('session_start', 1),
        ('click', 0.5),
        ('LIKE', 1),
        ('comment', 2),
        ('share', 5),
        ('purchase', 10)
    ) AS w(event_type, weight)
),
user_score AS (
    SELECT
        e.user_id,
        SUM(w.weight) AS engagement_score
    FROM events e
    JOIN event_weights w ON w.event_type = e.event_type
    WHERE e.event_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY e.user_id
)
SELECT
    user_id,
    engagement_score,
    NTILE(10) OVER (ORDER BY engagement_score DESC) AS decile
FROM user_score
ORDER BY engagement_score DESC;

NTILE(10) делит пользователей на 10 групп по engagement.

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

WITH user_score AS (
    SELECT
        e.user_id,
        u.platform,
        u.acquisition_channel,
        COUNT(*) FILTER (WHERE e.event_type = 'comment') * 2
            + COUNT(*) FILTER (WHERE e.event_type = 'share') * 5 AS engagement
    FROM events e
    JOIN users u ON u.user_id = e.user_id
    WHERE e.event_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY e.user_id, u.platform, u.acquisition_channel
)
SELECT
    platform,
    acquisition_channel,
    AVG(engagement) AS avg_eng,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY engagement) AS median_eng
FROM user_score
GROUP BY platform, acquisition_channel
ORDER BY avg_eng DESC;

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

Ошибка 1. Универсальная формула без обоснования. Веса должны отражать ценность action для бизнеса. Спросите PM, что важно.

Ошибка 2. Считать engagement как сумму events. Без весов 100 кликов = 1 покупка. Несправедливо.

Ошибка 3. Не нормализовать по периоду. Юзер был активен 7 дней vs 1 день — разная база. Per-day или per-session нормализация.

Ошибка 4. Outliers. Боты / power-users искажают average. Используйте median + decile.

Ошибка 5. Engagement-score как black box. Если команда не понимает формулу, метрика бесполезна.

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

• Как посчитать DAU в SQL
• Как посчитать MAU в SQL
• Как посчитать sessions в SQL
• Что такое engagement rate

FAQ

Какие веса для events?

Зависит от продукта. Соцсеть: like 1, comment 3, share 5, post 10. E-com: view 1, add-to-cart 5, purchase 20.

Engagement vs Retention?

Retention — кто остался. Engagement — насколько активны. Идеально вместе.

Engagement score 100 — это хорошо?

Без референсной точки — ничего не значит. Сравните с medianой / decile-распределением.

Как обновлять веса?

Раз в квартал. Если product priorities меняются (новая фича) — пересчитайте.

Engagement для B2B?

Считают по аккаунту, не юзеру. + специфические actions: «logged in admin», «invited teammate», «integrated webhook».