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

Зачем Churn Risk Score

Churn Risk Score — это оценка вероятности того, что пользователь уйдёт в ближайшие N дней. Смысл в том, чтобы не констатировать отток постфактум, а поймать пользователя, пока он ещё с вами: retention-кампании (письмо, скидка, звонок CSM) бьют по тем, у кого риск высокий, а не по всей базе подряд. Метрика ложится в основу проактивного удержания.

Score можно строить двумя путями: обучить ML-модель или собрать rule-based скоринг прямо в SQL из простых сигналов. На старте почти всегда выигрывает второй вариант — он прозрачный, объяснимый и разворачивается за час. Ниже — как собрать такой score, как проверить, что он вообще предсказывает отток, и как отдать список рисковых пользователей в кампанию.

Rule-based scoring

Идея простая: берём несколько поведенческих сигналов и начисляем за каждый «тревожный» баллы. Чем выше сумма — тем выше риск.

WITH user_signals AS (
    SELECT
        u.user_id,
        CURRENT_DATE - COALESCE(MAX(a.DATE), u.created_at::DATE) AS days_inactive,
        COUNT(a.DATE) FILTER (WHERE a.DATE >= CURRENT_DATE - INTERVAL '7 days') AS active_last_7d,
        COUNT(a.DATE) FILTER (WHERE a.DATE >= CURRENT_DATE - INTERVAL '30 days') AS active_last_30d,
        -- индикатор падения активности неделя к неделе
        COUNT(a.DATE) FILTER (WHERE a.DATE >= CURRENT_DATE - INTERVAL '7 days')::NUMERIC
        / NULLIF(COUNT(a.DATE) FILTER (WHERE a.DATE BETWEEN CURRENT_DATE - INTERVAL '14 days' AND CURRENT_DATE - INTERVAL '8 days'), 0) AS week_over_week_ratio,
        EXISTS (SELECT 1 FROM support_tickets WHERE user_id = u.user_id AND created_at >= CURRENT_DATE - INTERVAL '30 days' AND priority = 'high') AS recent_complaint
    FROM users u
    LEFT JOIN activity a USING (user_id)
    WHERE u.active = TRUE
    GROUP BY u.user_id, u.created_at
)
SELECT
    user_id,
    days_inactive,
    active_last_7d,
    week_over_week_ratio,
    -- аддитивный score: суммируем баллы за каждый тревожный сигнал
    (CASE WHEN days_inactive > 14 THEN 30 ELSE 0 END) +
    (CASE WHEN days_inactive > 30 THEN 30 ELSE 0 END) +
    (CASE WHEN week_over_week_ratio < 0.5 THEN 20 ELSE 0 END) +
    (CASE WHEN active_last_7d = 0 THEN 30 ELSE 0 END) +
    (CASE WHEN recent_complaint THEN 20 ELSE 0 END) AS churn_risk_score
FROM user_signals
ORDER BY churn_risk_score DESC;

Score получается в диапазоне 0–100, где всё, что выше 70, считаем высоким риском. Логика сигналов читаемая: давно не заходил, активность за неделю просела вдвое, за последние 7 дней ни одного захода, была жалоба в поддержку с высоким приоритетом. Веса (30, 20) здесь заданы вручную — их потом подбирают под данные на этапе калибровки.

Композитный score

Аддитивные баллы удобны, но грубоваты. Если сигналы уже приведены к сопоставимым шкалам, можно считать взвешенный композитный score и сразу резать пользователей на сегменты по децилям риска:

WITH user_score AS (
    SELECT
        user_id,
        days_inactive,
        engagement_trend,
        complaint_score,
        usage_decline,
        -- взвешенная сумма нормализованных сигналов
        days_inactive * 0.4
        + engagement_trend * 0.3
        + complaint_score * 0.2
        + usage_decline * 0.1 AS raw_score
    FROM user_signals
)
SELECT
    user_id,
    raw_score,
    NTILE(10) OVER (ORDER BY raw_score) AS risk_decile,
    CASE
        WHEN NTILE(10) OVER (ORDER BY raw_score) >= 9 THEN 'CRITICAL'
        WHEN NTILE(10) OVER (ORDER BY raw_score) >= 7 THEN 'HIGH'
        WHEN NTILE(10) OVER (ORDER BY raw_score) >= 4 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS risk_segment
FROM user_score;

NTILE(10) делит пользователей на 10 равных групп по величине score. Верхние децили — CRITICAL и HIGH — это те, к кому идут дорогие механики удержания вроде звонка менеджера; нижние получают дешёвую автоматику или ничего.

Калибровка

Главный вопрос к любому rule-based score: а он вообще предсказывает отток? Проверяют это на истории — берут score месячной давности и смотрят, кто из этих пользователей реально ушёл:

WITH historical_scores AS (
    SELECT user_id, churn_risk_score AS score_30d_ago
    FROM user_score_snapshots
    WHERE snapshot_date = CURRENT_DATE - INTERVAL '30 days'
),
churn_outcomes AS (
    SELECT user_id, churned_at IS NOT NULL AS churned
    FROM users
    WHERE created_at < CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    NTILE(10) OVER (ORDER BY score_30d_ago) AS risk_decile,
    COUNT(*) AS users,
    SUM(CASE WHEN churned THEN 1 ELSE 0 END) AS churned,
    SUM(CASE WHEN churned THEN 1 ELSE 0 END)::NUMERIC * 100 / COUNT(*) AS churn_rate_pct
FROM historical_scores
JOIN churn_outcomes USING (user_id)
GROUP BY 1
ORDER BY 1;

Хороший score виден сразу: в верхнем дециле фактический отток должен быть заметно выше, чем в нижнем — в идеале в разы. Если разницы нет, score бесполезен, и веса или пороги надо пересобирать. Прогнать такие вопросы про метрики удержания с разбором можно в Карьернике.

Таргетинг

Когда score откалиброван, финальный шаг — выгрузить рисковых пользователей в кампанию, не забыв про согласие на рассылку:

SELECT
    user_id,
    email,
    churn_risk_score,
    days_inactive
FROM user_scores
WHERE churn_risk_score >= 70
  AND email_opt_in = TRUE
ORDER BY churn_risk_score DESC
LIMIT 1000;

Дальше сегмент разводят по каналам: критическим — персональный контакт, высоким — скидка или промо-письмо, средним — автоматический re-engagement.

Как это спрашивают на собесе

Тему обычно раскручивают от практики удержания, а не от формул.

«Как без ML понять, кто вот-вот уйдёт?» Сильный ответ — rule-based score из поведенческих сигналов: дни без активности, падение вовлечённости неделя к неделе, жалобы в поддержку, даунгрейды. Прозрачно, объяснимо, разворачивается в SQL за час.

«Как проверить, что твой score рабочий?» Через калибровку на истории: взять score месячной давности, разбить пользователей на децили и сравнить фактический отток в верхнем и нижнем дециле. Если топ-дециль не даёт заметно больший отток — score не работает.

«Порог 70 — откуда цифра?» Не с потолка: порог выбирают по калибровке и ёмкости кампании. Если под ретеншн-письма бюджет на 1000 человек — берут топ-1000 по score, а не фиксированное число.

«Rule-based или ML?» ML точнее, но дороже в поддержке и менее прозрачен. Rule-based быстрее стартует и объясним стейкхолдерам. На раннем этапе почти всегда начинают с правил, ML добавляют, когда данных и ресурсов достаточно.

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

• Слишком чувствительный score. Если «высокий риск» получают почти все, score бесполезен — кампания бьёт по всей базе. Пороги надо калибровать, а не выставлять на глаз.
• Статичные правила навсегда. Поведение пользователей и продукт меняются, а веса остаются с прошлого года. Скоринг нужно пересобирать хотя бы раз в квартал.
• Score без валидации. Пока вы не сверили score с фактическим оттоком на истории, это не предсказание, а гадание. Сначала калибровка — потом кампании.
• Один сигнал вместо композита. Только «дни без активности» — слабый предиктор: активный пользователь мог просто уехать в отпуск. Композит из нескольких сигналов устойчивее.
• Survivorship bias при обучении. Если считать только по пользователям, доживших до конца окна, вы теряете тех, кто ушёл в процессе. Нужно аккуратно работать с правым цензурированием и брать завершённые когорты.

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

• Как посчитать churn в SQL
• Как посчитать days since last login в SQL
• Как посчитать win-back rate в SQL
• Churn prediction

FAQ

Rule-based или ML — что выбрать?

ML обычно даёт выше точность, но требует данных, инфраструктуры и постоянной поддержки, а его решения сложнее объяснить бизнесу. Rule-based скоринг прозрачен, объясним и разворачивается за часы. На старте берут правила, а к ML переходят, когда точности правил перестаёт хватать и есть ресурсы на модель.

Какой порог считать высоким риском?

Порог выбирают не по «красивому числу», а по калибровке и ёмкости кампании: обычно берут верхний дециль или топ-20% по score. Если ретеншн-механика рассчитана на N человек — берут топ-N по риску.

Как часто пересчитывать score?

Зависит от скорости продукта: для высокочастотных приложений score считают ежедневно, для стабильных подписок достаточно раза в неделю. Отдельно раз в квартал стоит пересобирать сами правила и веса.

Какие сигналы брать в score?

Рабочий набор: дни без активности, падение вовлечённости неделя к неделе, обращения в поддержку (особенно с высоким приоритетом), даунгрейды тарифа, падение использования ключевых фич. Важно комбинировать несколько сигналов, а не полагаться на один.

Как связать score с действием?

По сегментам риска: критический — персональный контакт (звонок CSM), высокий — скидка или промо-письмо, средний — автоматический re-engagement. Дешёвые механики отдают массовому сегменту, дорогие — узкому топу по риску.