Как посчитать LTV по когортам в SQL

Зачем LTV по cohort

Один LTV на всё бесполезен: разные периоды дают разные customers (изменилась цена, канал, продукт). LTV по cohort — кривая «сколько revenue даёт средний customer от cohort N через k недель». Сравнение каналов: какие cohort растут, какие плато.

Структура

cohort_month | months_since_signup | cumulative_revenue_per_user
2025-01      | 0                   | 10
2025-01      | 1                   | 18
2025-01      | 2                   | 25
2025-01      | 12                  | 95

Cumulative — каждая cell суммирует все revenue с месяца signup до месяца k.

Cumulative LTV в SQL

WITH cohorts AS (
    SELECT user_id, DATE_TRUNC('month', created_at)::DATE AS cohort_month
    FROM users
    WHERE created_at >= '2024-01-01'
),
user_revenue AS (
    SELECT
        c.cohort_month,
        c.user_id,
        EXTRACT(YEAR FROM AGE(o.order_date, c.cohort_month)) * 12
        + EXTRACT(MONTH FROM AGE(o.order_date, c.cohort_month)) AS months_since,
        o.amount
    FROM cohorts c
    JOIN orders o USING (user_id)
    WHERE o.order_date >= c.cohort_month
),
monthly_revenue AS (
    SELECT
        cohort_month,
        months_since,
        SUM(amount) AS revenue_at_month
    FROM user_revenue
    GROUP BY cohort_month, months_since
),
cohort_size AS (
    SELECT cohort_month, COUNT(*) AS users FROM cohorts GROUP BY cohort_month
)
SELECT
    mr.cohort_month,
    mr.months_since,
    cs.users,
    SUM(mr.revenue_at_month) OVER (PARTITION BY mr.cohort_month ORDER BY mr.months_since)
        / NULLIF(cs.users, 0) AS cumulative_ltv
FROM monthly_revenue mr
JOIN cohort_size cs USING (cohort_month)
ORDER BY mr.cohort_month, mr.months_since;

SUM(...) OVER (... ORDER BY ...) — running total.

По каналу

WITH cohorts AS (
    SELECT user_id, utm_source, DATE_TRUNC('month', created_at)::DATE AS cohort_month
    FROM users
)
SELECT
    cohort_month,
    utm_source,
    months_since,
    cumulative_ltv
FROM cohort_ltv_data
WHERE months_since IN (0, 3, 6, 12)
ORDER BY cohort_month, utm_source, months_since;

Канал с самым большим LTV в month 12 — main growth driver.

Прогноз LTV молодых cohort

Чтобы оценить LTV для cohort, которой только 3 месяца, экстраполируйте из старых cohort:

WITH old_cohorts AS (
    SELECT
        months_since,
        AVG(cumulative_ltv) AS mean_ltv
    FROM cohort_ltv_data
    WHERE cohort_month BETWEEN '2024-01-01' AND '2024-12-31'  -- закрытые когорты
      AND months_since BETWEEN 0 AND 12
    GROUP BY months_since
),
new_cohort AS (
    SELECT
        cumulative_ltv AS ltv_at_3
    FROM cohort_ltv_data
    WHERE cohort_month = '2026-02-01' AND months_since = 3
)
SELECT
    nc.ltv_at_3,
    oc.mean_ltv AS old_mean_at_3,
    nc.ltv_at_3 / NULLIF((SELECT mean_ltv FROM old_cohorts WHERE months_since = 3), 0)
        * (SELECT mean_ltv FROM old_cohorts WHERE months_since = 12) AS projected_ltv_12
FROM new_cohort nc, old_cohorts oc
WHERE oc.months_since = 12;

Это грубо: предполагает, что новый cohort развивается как старый. На большой product change прогноз неверен.

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

Ошибка 1. LTV среднее vs медиана. Whales перекосят average. Использовать median per user или log-mean.

Ошибка 2. Не закрывать когорту. Молодые cohort имеют меньше времени накопить — их LTV искусственно ниже. Не сравнивайте напрямую.

Ошибка 3. Включать refunds в revenue. LTV с refunds — net revenue per user. Без — gross.

Ошибка 4. Cumulative и retention путать. Retention — % active. LTV — total $ per user.

Ошибка 5. Игнорировать ARPU growth. Старые cohorts могли upgrade. Если 2024-01 cohort до сих пор активный — её LTV растёт.

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

• Как посчитать LTV в SQL
• Как посчитать CAC по каналам в SQL
• Как посчитать cohort retention в SQL
• Как посчитать payback period в SQL

FAQ

Как сравнить cohort разной длины?

Только в одной и той же точке months_since. Никогда «cohort A в month 12 vs cohort B в month 3».

LTV на mean или median?

Median для устойчивости к whales. Сообщайте оба.

Включать ли refunds?

В net LTV — да. В gross — нет.

Какой horizon LTV?

12-24 месяца для SaaS. Зависит от типичной длительности customer.

LTV/CAC сравнить с cohort CAC?

Yes. Cohort × channel — самая полезная разбивка.