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

Зачем Engagement Rate

DAU/MAU показывают, кто пришёл. Engagement Rate показывает, насколько активно используют. Низкий ER = «зомби-юзеры», открывают, не делают ничего, churning soon.

Формула

Engagement Rate (product) = engaged_users / total_users × 100%
Engagement Rate (social) = (likes + comments + shares) / impressions × 100%

«Engaged» — выполнил key actions (post created, message sent, file uploaded).

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

Product engagement (binary):

WITH user_events AS (
    SELECT
        user_id,
        BOOL_OR(event_type IN ('post_created', 'message_sent', 'file_uploaded')) AS engaged
    FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    COUNT(*) AS total_users,
    COUNT(*) FILTER (WHERE engaged) AS engaged_users,
    COUNT(*) FILTER (WHERE engaged)::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS engagement_rate_pct
FROM user_events;

Product Engagement Rate

Гранулярно:

WITH user_engagement AS (
    SELECT
        user_id,
        COUNT(*) FILTER (WHERE event_type = 'session_start') AS sessions,
        COUNT(*) FILTER (WHERE event_type = 'post_created') AS posts,
        COUNT(*) FILTER (WHERE event_type = 'comment') AS comments,
        COUNT(*) FILTER (WHERE event_type = 'LIKE') AS likes
    FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    AVG(sessions) AS avg_sessions,
    AVG(posts + comments + likes) AS avg_actions
FROM user_engagement
WHERE sessions > 0;

Social Engagement Rate

Для social-постов (Instagram-like):

SELECT
    post_id,
    impressions,
    likes,
    comments,
    shares,
    (likes + comments + shares)::NUMERIC * 100 / NULLIF(impressions, 0) AS engagement_rate_pct
FROM post_stats
WHERE published_at >= CURRENT_DATE - INTERVAL '30 days'
HAVING impressions >= 1000
ORDER BY engagement_rate_pct DESC;

Avg post engagement → benchmark для будущих.

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

Ошибка 1. Confuse engagement vs DAU. DAU — кто пришёл. ER — кто действовал. Юзер может открыть app + ничего не делать → DAU yes, engaged no.

Ошибка 2. Веса actions. View ≠ Comment. Если считаете total actions без весов — overweight cheap actions.

Ошибка 3. Engagement на social vs product. Разные определения. Social — relative to impressions. Product — relative to active users.

Ошибка 4. Bot inflation. Боты накручивают likes/views. Sanitize.

Ошибка 5. Cross-device. Counts engagement как ER × 2 если юзер на двух девайсах.

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

• Как посчитать engagement в SQL
• Как посчитать DAU в SQL
• Как посчитать MAU в SQL
• Stickiness и DAU/MAU

FAQ

Какой ER считается ok?

Social (Instagram): 1-3% average, 5%+ excellent. Product (B2C app): 30-50% engaged DAU. SaaS: 60-80%.

Engagement vs Engagement Rate?

Engagement — total actions. ER — % users active.

ER падает — что делать?

Декомпозиция by cohort + segment. Newer cohort engagement обычно ниже (still learning).

Bot-protected engagement?

Filter by ip, user-agent, behavioral patterns. Use captcha + anti-bot.

ER vs Stickiness?

Stickiness (DAU/MAU) — frequency. ER — depth of action. Related but different.