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

Зачем cohort decay

Cohort decay — скорость, с которой когорта «истончается» во времени. Простая retention curve смотрится по N (день/неделя), а decay — производная: «теряем 10% в неделю», «half-life 12 недель». Полезно для LTV-прогнозов и сравнения когорт.

Базовая retention-таблица

Сначала строим cohort × week retention:

WITH cohort_week AS (
    SELECT
        DATE_TRUNC('week', u.created_at)::DATE AS cohort_week,
        u.user_id
    FROM users u
    WHERE u.created_at >= CURRENT_DATE - INTERVAL '24 weeks'
),
activity AS (
    SELECT
        cw.cohort_week,
        cw.user_id,
        FLOOR((a.event_date - cw.cohort_week) / 7)::INT AS week_offset
    FROM cohort_week cw
    JOIN training_events a ON a.user_id = cw.user_id
    WHERE a.event_date >= cw.cohort_week
)
SELECT
    cohort_week,
    week_offset,
    COUNT(DISTINCT user_id) AS active_users,
    COUNT(DISTINCT user_id)::NUMERIC
    / FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (PARTITION BY cohort_week ORDER BY week_offset) AS retention
FROM activity
WHERE week_offset BETWEEN 0 AND 12
GROUP BY cohort_week, week_offset
ORDER BY cohort_week, week_offset;

Decay rate

Weekly decay = 1 - retention(t) / retention(t-1):

WITH retention_table AS (
    -- из предыдущего CTE
    SELECT cohort_week, week_offset, retention FROM cohort_retention
),
decay AS (
    SELECT
        cohort_week,
        week_offset,
        retention,
        LAG(retention) OVER (PARTITION BY cohort_week ORDER BY week_offset) AS prev_retention
    FROM retention_table
)
SELECT
    cohort_week,
    week_offset,
    retention,
    1 - retention / NULLIF(prev_retention, 0) AS weekly_decay_rate
FROM decay
WHERE prev_retention IS NOT NULL
ORDER BY cohort_week, week_offset;

Если decay стабильно ~10% — рост retention уровневый. Если decay растёт — что-то ломается на поздних этапах.

Half-life когорты

Точка, в которой retention падает до 50%:

WITH retention_table AS (
    SELECT cohort_week, week_offset, retention FROM cohort_retention
),
half_life AS (
    SELECT
        cohort_week,
        MIN(week_offset) AS half_life_weeks
    FROM retention_table
    WHERE retention <= 0.5
    GROUP BY cohort_week
)
SELECT *
FROM half_life
ORDER BY cohort_week;

Если half-life растёт по cohort'ам — улучшение retention, когорта-к-когорте.

Прогноз выручки

Если decay стабилен, выручка следующих месяцев прогнозируется через geometric formula:

WITH cohort_stats AS (
    SELECT
        cohort_week,
        AVG(weekly_decay_rate) AS avg_decay,
        AVG(weekly_revenue_per_user) AS arpu
    FROM cohort_retention
    GROUP BY cohort_week
)
SELECT
    cohort_week,
    arpu,
    avg_decay,
    -- Geometric series: ARPU * Σ (1 - decay)^t
    arpu / NULLIF(avg_decay, 0) AS ltv_estimate
FROM cohort_stats;

ARPU / decay — упрощённая LTV формула. Точнее — fit exponential на retention curve.

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

Ошибка 1. Decay на маленьких когортах. Когорта 50 юзеров и декай прыгает на 20% в неделю — шум, не реальный тренд.

Ошибка 2. Считать decay на cumulative retention. Cumulative ≠ retention. Cumulative monotonically растёт, retention — падает.

Ошибка 3. Half-life не достигнут. Молодые когорты могут не дожить до 50%. Сообщайте «>12 недель» вместо null.

Ошибка 4. Экстраполировать decay infinitely. Реальные продукты имеют «floor retention» (10-20% супер-loyal). LTV растёт чуть выше, чем geometric formula.

Ошибка 5. Не сегментировать. Когорты по каналу могут иметь радикально разный decay. Среднее — обман.

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

• Как посчитать cohort retention в SQL
• Как посчитать Kaplan-Meier в SQL
• Как посчитать LTV в SQL
• Как посчитать rolling retention в SQL

FAQ

Что считается «хорошим» decay?

Зависит от продукта. SaaS week-1 decay 30-40% типично, потом 5-10% в неделю.

Half-life 12 недель — это много?

Для consumer apps — отлично. Для SaaS B2B — норма 26+ недель.

Decay растёт — это плохо?

Обычно да: означает «поздний churn» — юзеры медленно отваливаются после первого месяца.

Можно ли decay для DAU?

Да: weekly decay активных пользователей одной cohort'ы. Аналог retention.

Geometric LTV норм?

Прибл. на 30-90 дней. На годовом horizon недооценивает «floor».