15 апреля 2026 г.

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

Что такое Churn

Churn Rate (отток) — процент пользователей, которые перестали быть активными / отменили подписку за период.

Churn = (Ушедшие за период) / (Активные в начале периода) × 100%

Связан с retention: Churn + Retention = 1.

Monthly Churn для подписки

WITH active_start AS (
    SELECT COUNT(*) AS active
    FROM subscriptions
    WHERE status = 'active'
      AND started_at <= '2026-03-01'
      AND (ended_at IS NULL OR ended_at > '2026-03-01')
),
churned AS (
    SELECT COUNT(*) AS cnt
    FROM subscriptions
    WHERE ended_at >= '2026-03-01' AND ended_at < '2026-04-01'
)
SELECT c.cnt * 1.0 / a.active AS churn_rate
FROM active_start a, churned c;

Behavioral Churn (активность, не подписка)

Для продуктов без подписки: пользователь «churned», если не заходил N дней.

-- Churn = не заходил последние 30 дней, хотя раньше заходил
SELECT COUNT(DISTINCT user_id) AS churned
FROM users u
WHERE EXISTS (
    SELECT 1 FROM events WHERE user_id = u.user_id
)
AND NOT EXISTS (
    SELECT 1 FROM events
    WHERE user_id = u.user_id
      AND created_at >= CURRENT_DATE - INTERVAL '30 day'
);

Voluntary vs Involuntary Churn

Voluntary — пользователь сам отменил

SELECT COUNT(*) AS voluntary
FROM subscriptions
WHERE ended_at BETWEEN '2026-04-01' AND '2026-04-30'
  AND cancel_reason = 'user_cancel';

Involuntary — карточка не прошла

SELECT COUNT(*) AS involuntary
FROM subscriptions
WHERE ended_at BETWEEN '2026-04-01' AND '2026-04-30'
  AND cancel_reason = 'payment_failed';

Разделение важно — это разные проблемы с разными решениями.

Churn по когортам

WITH cohort AS (
    SELECT user_id, DATE_TRUNC('month', started_at)::DATE AS cohort_month
    FROM subscriptions
),
ended AS (
    SELECT user_id, DATE_TRUNC('month', ended_at)::DATE AS end_month
    FROM subscriptions WHERE ended_at IS NOT NULL
)
SELECT c.cohort_month,
    COUNT(*) AS cohort_size,
    COUNT(e.user_id) FILTER (
        WHERE e.end_month <= c.cohort_month + INTERVAL '3 month'
    ) AS churned_in_3m,
    COUNT(e.user_id) FILTER (
        WHERE e.end_month <= c.cohort_month + INTERVAL '3 month'
    ) * 1.0 / COUNT(*) AS churn_3m
FROM cohort c
LEFT JOIN ended e USING (user_id)
GROUP BY c.cohort_month
ORDER BY c.cohort_month;

Прокачать тему на реальных задачах удобно в боте @kariernik_bot — база вопросов собрана с собеседований в Яндексе, Авито, Ozon, Тинькофф.

Monthly Churn Rate по месяцам

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', dt)::DATE AS month,
        COUNT(DISTINCT user_id) FILTER (WHERE event = 'subscribed') AS subs,
        COUNT(DISTINCT user_id) FILTER (WHERE event = 'cancelled') AS cancels
    FROM events
    GROUP BY 1
),
active_start AS (
    SELECT month,
        SUM(subs - cancels) OVER (ORDER BY month) AS net_active
    FROM monthly
)
SELECT m.month, m.subs, m.cancels,
    m.cancels * 1.0 / NULLIF(LAG(a.net_active) OVER (ORDER BY m.month), 0) AS churn_rate
FROM monthly m JOIN active_start a USING (month)
ORDER BY m.month;

Churn по тарифам

SELECT tariff,
    COUNT(*) FILTER (WHERE ended_at IS NOT NULL) * 1.0 / COUNT(*) AS overall_churn
FROM subscriptions
WHERE started_at >= CURRENT_DATE - INTERVAL '6 month'
GROUP BY tariff
ORDER BY overall_churn DESC;

Dollar Churn (revenue churn)

Churn в деньгах, не в пользователях:

SELECT
    SUM(arpu_lost) AS dollar_churn_amount,
    SUM(arpu_lost) * 1.0 / SUM(arpu_start) AS dollar_churn_rate
FROM (
    SELECT
        CASE WHEN ended_at BETWEEN '2026-04-01' AND '2026-04-30'
            THEN monthly_price ELSE 0 END AS arpu_lost,
        monthly_price AS arpu_start
    FROM subscriptions
    WHERE started_at <= '2026-04-01'
) t;

Если ушедшие были премиум-тарифами, dollar churn может быть сильно выше user churn.

Churn по причинам (exit survey)

SELECT cancel_reason, COUNT(*) AS cnt,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct
FROM subscriptions
WHERE ended_at >= CURRENT_DATE - INTERVAL '30 day'
GROUP BY cancel_reason
ORDER BY cnt DESC;

Ответы пользователей при отмене: «слишком дорого», «не пользуюсь», «конкурент».

Churn в Python

import pandas as pd

subs = pd.read_sql("SELECT * FROM subscriptions", conn)
active_start = (subs['started_at'] <= '2026-04-01').sum()
churned = subs[
    (subs['ended_at'] >= '2026-04-01') &
    (subs['ended_at'] < '2026-05-01')
].shape[0]
churn_rate = churned / active_start

На собесе такие штуки часто спрашивают. Быстрый способ довести до автоматизма — тренажёр в Telegram с задачами из реальных интервью.

Типичные ошибки

1. Неправильная база для процента

  • Правильно: churned / active_at_start_of_period.
  • Неправильно: churned / total_users (разные масштабы).

2. Смешивать типы churn

Voluntary + involuntary — разные причины. Анализируйте отдельно.

3. Не учитывать trial

Пользователи на free trial «уходят» естественно. Отделяйте от paid churn.

4. Annual vs Monthly churn

Годовой churn ≠ 12 × месячный. Правильно: (1 - (1 - monthly_churn)^12).

Нормы Churn

Monthly Churn

  • B2B SaaS: 1–3%.
  • B2C Subscription: 5–10%.
  • E-commerce (первая покупка → вторая): 60–80%.
  • Mobile games: D30 churn = 70–90% — это норма.

Как снизить Churn

  1. Involuntary: retry-логика, SMS-напоминания о карте.
  2. Voluntary: улучшить продукт, onboarding, engagement.
  3. Price-sensitive: grace период, скидки retention.
  4. Конкуренты: новые features, коммуникация.

Подробнее в кейсе.

Читайте также

FAQ

Customer churn или revenue churn?

Customer — процент ушедших пользователей. Revenue — процент потерянной выручки. Показывайте оба.

Что важнее — retention или churn?

Одна и та же метрика в разных проекциях. Выберите что удобнее команде (retention — позитив, churn — проблема).

Как предсказать Churn?

ML-модели: логрег, XGBoost, survival analysis. Features: engagement, recency, поведение. Подробнее.

Когда пользователь считается churned?

Зависит от продукта. SaaS: отменил подписку. Приложение: не заходил 30/60/90 дней. E-commerce: не покупал 180 дней. Определяйте по бизнесу.

Тренироваться в Telegram