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

Зачем Customer Lifetime

LTV — money. Customer Lifetime — time. Среднее юзер использует продукт 18 месяцев. Если новые юзеры churn быстрее (lifetime 10 месяцев) → segment-разрез показывает где проблема.

Формула

Customer Lifetime = (churn_date OR today) - signup_date
                       для churned vs active

Avg Lifetime = 1 / Monthly Churn Rate (для exponential decay).

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

Данные: users(user_id, signup_date), subscriptions(user_id, ended_at).

WITH lifetime AS (
    SELECT
        u.user_id,
        u.signup_date,
        COALESCE(s.ended_at, CURRENT_DATE) AS lifetime_end,
        s.ended_at IS NOT NULL AS churned,
        EXTRACT(EPOCH FROM (COALESCE(s.ended_at, CURRENT_DATE) - u.signup_date)) / 86400 AS days_alive
    FROM users u
    LEFT JOIN subscriptions s ON s.user_id = u.user_id
)
SELECT
    AVG(days_alive) AS avg_lifetime_days,
    AVG(days_alive) FILTER (WHERE churned) AS avg_lifetime_churned,
    AVG(days_alive) FILTER (WHERE NOT churned) AS avg_lifetime_still_alive,
    COUNT(*) FILTER (WHERE churned)::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS churn_pct
FROM lifetime;

Survival analysis

Kaplan-Meier estimate в SQL:

WITH user_lifetimes AS (
    SELECT
        u.user_id,
        EXTRACT(EPOCH FROM (COALESCE(s.ended_at, CURRENT_DATE) - u.signup_date)) / 86400 AS days_alive,
        s.ended_at IS NOT NULL AS churned
    FROM users u
    LEFT JOIN subscriptions s ON s.user_id = u.user_id
),
months AS (
    SELECT generate_series(0, 24, 1) AS month
),
at_risk AS (
    SELECT
        m.month,
        COUNT(*) FILTER (WHERE u.days_alive >= m.month * 30) AS still_in
    FROM months m
    CROSS JOIN user_lifetimes u
    GROUP BY m.month
)
SELECT
    month,
    still_in,
    still_in::NUMERIC * 100 / NULLIF(MAX(still_in) OVER (), 0) AS survival_pct
FROM at_risk
ORDER BY month;

Customer Lifetime vs LTV

LTV = Lifetime × ARPU.

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

Ошибка 1. Включить still-alive в average. Юзеры, ещё не churned, имеют lifetime = current date. Это lower bound, не actual.

Ошибка 2. Censored data. Right-censoring: not yet churned. Standard survival analysis это учитывает.

Ошибка 3. Confuse with retention. Lifetime — это total. Retention — fraction still active в момент. Связаны.

Ошибка 4. Churn definition. Subscription ended_at — clear. Но «free user» — когда считать churned? (30 days inactive?).

Ошибка 5. Avg на skewed. Survival distribution often heavy-tail. Median > mean часто.

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

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

FAQ

Какой Customer Lifetime ok?

SaaS B2B: 24-60 months. SaaS B2C: 12-24. Mobile freemium: 3-12.

Avg vs Median lifetime?

Median — more representative. Heavy-tail churn искажает avg.

Survival analysis обязательно?

Для precision — да. Linear avg недооценивает still-alive юзеров.

Customer Lifetime растёт — хорошо?

Yes. Меньше churn, dolgozhitelstvo.

Cross-product lifetime?

Юзер с 5 продуктами — лidoss-cumulative lifetime. Сложнее, но more accurate.