Churn-анализ в SQL: считаем отток пользователей

Churn (отток) — процент пользователей или выручки, которые вы теряете за период. Для SaaS и подписочных сервисов churn — одна из ключевых метрик выживания. Разберём как считать его в SQL.

Что такое churn

User Churn Rate = пользователи отказавшиеся в периоде / пользователи в начале периода × 100%

Revenue Churn Rate = выручка потерянная в периоде / выручка в начале периода × 100%

Ключевой вопрос: что значит «отказался»? Зависит от модели:

• Подписка: явная отмена или истечение без продления
• Транзакционный: не купил за N дней (inactive churn)
• Мобильное приложение: не открыл за 30 дней

Ежемесячный churn для подписочного сервиса

WITH monthly_subscribers AS (
    -- Активные подписчики на начало каждого месяца
    SELECT
        DATE_TRUNC('month', gs.month)::date AS month,
        COUNT(DISTINCT s.user_id) AS subscribers_at_start
    FROM generate_series(
        '2025-01-01'::date,
        '2026-03-01'::date,
        INTERVAL '1 month'
    ) AS gs(month)
    JOIN subscriptions s
        ON s.started_at < gs.month
        AND (s.cancelled_at IS NULL OR s.cancelled_at >= gs.month)
    GROUP BY 1
),
churned AS (
    -- Кто отменил подписку в каждый месяц
    SELECT
        DATE_TRUNC('month', cancelled_at)::date AS month,
        COUNT(DISTINCT user_id) AS churned_users
    FROM subscriptions
    WHERE cancelled_at IS NOT NULL
    GROUP BY 1
)
SELECT
    ms.month,
    ms.subscribers_at_start,
    COALESCE(c.churned_users, 0) AS churned,
    ROUND(
        COALESCE(c.churned_users, 0)::numeric
        / NULLIF(ms.subscribers_at_start, 0) * 100,
        2
    ) AS churn_rate_pct
FROM monthly_subscribers ms
LEFT JOIN churned c ON c.month = ms.month
ORDER BY ms.month;

Inactive Churn: поведенческий отток

Для приложений без подписки «отток» — пользователь не вернулся за 30 дней:

-- Пользователи, которые были активны в марте, но не активны в апреле
WITH active_march AS (
    SELECT DISTINCT user_id
    FROM user_events
    WHERE event_date BETWEEN '2026-03-01' AND '2026-03-31'
),
active_april AS (
    SELECT DISTINCT user_id
    FROM user_events
    WHERE event_date BETWEEN '2026-04-01' AND '2026-04-30'
)
SELECT
    COUNT(*) AS active_in_march,
    COUNT(a.user_id) AS returned_in_april,
    COUNT(*) - COUNT(a.user_id) AS churned,
    ROUND(
        (COUNT(*) - COUNT(a.user_id))::numeric / COUNT(*) * 100,
        1
    ) AS monthly_churn_pct
FROM active_march m
LEFT JOIN active_april a ON a.user_id = m.user_id;

Когортный churn: отток по когортам регистрации

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', created_at)::date AS cohort_month
    FROM users
),
activity AS (
    SELECT
        e.user_id,
        DATE_TRUNC('month', e.event_date)::date AS activity_month
    FROM user_events e
    GROUP BY 1, 2
),
cohort_activity AS (
    SELECT
        c.cohort_month,
        a.activity_month,
        COUNT(DISTINCT c.user_id) AS active_users,
        (EXTRACT(YEAR FROM AGE(a.activity_month, c.cohort_month)) * 12
         + EXTRACT(MONTH FROM AGE(a.activity_month, c.cohort_month)))::int AS months_since_reg
    FROM cohorts c
    JOIN activity a ON a.user_id = c.user_id
    WHERE a.activity_month >= c.cohort_month
    GROUP BY 1, 2
),
cohort_sizes AS (
    SELECT cohort_month, COUNT(*) AS cohort_size
    FROM cohorts
    GROUP BY 1
)
SELECT
    ca.cohort_month,
    cs.cohort_size,
    ca.months_since_reg,
    ca.active_users,
    ROUND(ca.active_users::numeric / cs.cohort_size * 100, 1) AS retention_pct,
    100 - ROUND(ca.active_users::numeric / cs.cohort_size * 100, 1) AS churn_pct
FROM cohort_activity ca
JOIN cohort_sizes cs ON cs.cohort_month = ca.cohort_month
ORDER BY ca.cohort_month, ca.months_since_reg;

Revenue Churn и Net Revenue Retention

WITH period_revenue AS (
    -- Выручка за два периода
    SELECT
        user_id,
        SUM(CASE WHEN period = 'current' THEN amount END) AS current_rev,
        SUM(CASE WHEN period = 'previous' THEN amount END) AS prev_rev
    FROM (
        SELECT user_id, amount, 'current' AS period
        FROM subscriptions WHERE month = '2026-03-01'::date

        UNION ALL

        SELECT user_id, amount, 'previous'
        FROM subscriptions WHERE month = '2026-02-01'::date
    ) t
    GROUP BY user_id
)
SELECT
    SUM(prev_rev) AS mrr_start,
    SUM(current_rev) AS mrr_end,

    -- Revenue Churn: потерянная выручка от уходящих
    SUM(CASE WHEN current_rev IS NULL THEN prev_rev END) AS lost_revenue,

    -- Expansion: дополнительная выручка от существующих
    SUM(CASE WHEN current_rev > prev_rev THEN current_rev - prev_rev END) AS expansion,

    -- Net Revenue Retention (>100% = рост без новых)
    ROUND(
        SUM(COALESCE(current_rev, 0))::numeric
        / NULLIF(SUM(prev_rev), 0) * 100, 1
    ) AS nrr_pct,

    ROUND(
        SUM(CASE WHEN current_rev IS NULL THEN prev_rev END)::numeric
        / NULLIF(SUM(prev_rev), 0) * 100, 1
    ) AS gross_revenue_churn_pct
FROM period_revenue;

Предсказание оттока: сигналы риска

-- Пользователи с признаками скорого оттока
SELECT
    u.id,
    u.email,
    u.last_activity_date,
    CURRENT_DATE - u.last_activity_date AS days_inactive,
    COUNT(DISTINCT e.event_date) AS active_days_last_30,
    s.end_date AS subscription_ends
FROM users u
LEFT JOIN user_events e ON e.user_id = u.id
    AND e.event_date >= CURRENT_DATE - 30
LEFT JOIN subscriptions s ON s.user_id = u.id AND s.is_active = true
WHERE
    u.is_active = true
    AND (
        u.last_activity_date < CURRENT_DATE - 14   -- не заходили 2 недели
        OR s.end_date BETWEEN CURRENT_DATE AND CURRENT_DATE + 14  -- подписка истекает
    )
GROUP BY u.id, u.email, u.last_activity_date, s.end_date
ORDER BY days_inactive DESC;

Нормальный уровень churn

Если churn > нормы → ищите в данных паттерн: какие сегменты уходят быстрее, что происходит перед оттоком.

Итог

Churn считается от правильной базы: кто был активен в начале периода. Главные виды:

Снижение churn на 1% в месяц — огромный эффект для SaaS: удерживаете больше, тратите меньше на привлечение.