Как посчитать Win-Back Rate в SQL

Зачем Win-Back Rate

Reactivating churned customers cheaper, чем acquire new ones (3-5x cheaper по статистике). Win-Back Rate = % churned, returned thanks to campaign.

Формула

Win-Back Rate = reactivated_churned / total_churned_targeted × 100%

«Churned» = не активен за X дней (определи threshold).

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

Данные: users(user_id, churned_at), orders(user_id, created_at).

WITH churned AS (
    SELECT DISTINCT
        u.user_id,
        u.churned_at
    FROM users u
    WHERE u.churned_at BETWEEN '2026-01-01' AND '2026-02-01'
),
won_back AS (
    SELECT DISTINCT c.user_id
    FROM churned c
    JOIN orders o ON o.user_id = c.user_id
    WHERE o.created_at > c.churned_at
      AND o.created_at <= c.churned_at + INTERVAL '90 days'
)
SELECT
    COUNT(c.user_id) AS total_churned,
    COUNT(w.user_id) AS won_back,
    COUNT(w.user_id)::NUMERIC * 100 / NULLIF(COUNT(c.user_id), 0) AS win_back_rate_pct
FROM churned c
LEFT JOIN won_back w ON w.user_id = c.user_id;

По кампаниям

WITH targeted AS (
    SELECT
        campaign_id,
        user_id,
        sent_at
    FROM win_back_campaigns
    WHERE sent_at BETWEEN '2026-01-01' AND '2026-02-01'
),
returned AS (
    SELECT DISTINCT t.campaign_id, t.user_id
    FROM targeted t
    JOIN orders o ON o.user_id = t.user_id
    WHERE o.created_at BETWEEN t.sent_at AND t.sent_at + INTERVAL '30 days'
)
SELECT
    t.campaign_id,
    COUNT(DISTINCT t.user_id) AS targeted,
    COUNT(DISTINCT r.user_id) AS returned,
    COUNT(DISTINCT r.user_id)::NUMERIC * 100 / NULLIF(COUNT(DISTINCT t.user_id), 0) AS win_back_rate_pct
FROM targeted t
LEFT JOIN returned r ON r.campaign_id = t.campaign_id AND r.user_id = t.user_id
GROUP BY t.campaign_id
ORDER BY win_back_rate_pct DESC;

LTV win-back vs new

Win-Back customers часто spend меньше new customers:

SELECT
    CASE
        WHEN ev.event_type = 'new_signup' THEN 'new'
        WHEN ev.event_type = 'win_back' THEN 'win-back'
    END AS source,
    AVG(SUM(o.total)) OVER (PARTITION BY ev.event_type) AS avg_revenue_per_user
FROM events ev
JOIN orders o ON o.user_id = ev.user_id
WHERE o.created_at > ev.event_at
  AND o.created_at <= ev.event_at + INTERVAL '6 months'
GROUP BY ev.event_type, ev.user_id;

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

Ошибка 1. Без control group. Some churned return organically. Без holdout не знаешь true campaign lift.

Ошибка 2. Window choice. 30 days vs 90 days vs unlimited — разные numbers.

Ошибка 3. Definition of «churned». 30 days inactive vs 60 vs 90. Зависит от business.

Ошибка 4. Multiple touches. User got 3 emails + 1 push notification. Attribution?

Ошибка 5. Quality of return. Win-back to free tier ≠ win-back to paid. Track separately.

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

• Как посчитать reactivation в SQL
• Как посчитать churn в SQL
• Как посчитать cohort retention в SQL
• Как посчитать LTV в SQL

FAQ

Какой Win-Back Rate ok?

5-15% — нормально. 20%+ — отличная campaign. Зависит от incentive value.

Win-Back vs Reactivation?

Часто синонимы. Win-back акцент на campaign-driven, reactivation — any return.

Control group для win-back?

Holdout 10% churned cohort, no message. Compare return rates.

Win-back to lower tier?

Common. Discounted entry → upgrade later. Track full LTV.

Win-back economics?

CAC win-back должен быть < ½ CAC new customer. Иначе кампания неэффективна.