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

Что такое LTV

LTV (Lifetime Value) — суммарная выручка, которую приносит один клиент за всё время жизни в продукте.

Варианты формулы зависят от модели:

Для e-commerce:
LTV = AOV × purchase frequency × customer lifespan

Для подписки/SaaS:
LTV = ARPU × (1 / churn rate) = ARPU / churn

1. Исторический LTV (фактический)

Самый простой вариант: «сколько уже принёс каждый клиент».

SELECT
    user_id,
    SUM(total) AS ltv_historical,
    COUNT(*) AS orders_cnt,
    MIN(created_at) AS first_order,
    MAX(created_at) AS last_order
FROM orders
WHERE status = 'paid'
GROUP BY user_id
ORDER BY ltv_historical DESC;

Плюс: простота. Минус: не учитывает будущие платежи.

2. Средний LTV по когорте

Когортный LTV — выручка на одного клиента в разрезе времени регистрации:

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort_month
    FROM orders
    GROUP BY user_id
),
user_revenue AS (
    SELECT
        user_id,
        SUM(total) AS total_revenue
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
)
SELECT
    c.cohort_month,
    COUNT(*) AS cohort_size,
    SUM(ur.total_revenue) AS total_revenue,
    AVG(ur.total_revenue) AS avg_ltv
FROM cohorts c
JOIN user_revenue ur ON ur.user_id = c.user_id
GROUP BY c.cohort_month
ORDER BY c.cohort_month;

3. LTV по месяцам с момента регистрации

Накопленный LTV: как растёт со временем.

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort_month
    FROM orders
    GROUP BY user_id
),
revenue_by_month AS (
    SELECT
        o.user_id,
        c.cohort_month,
        DATE_TRUNC('month', o.created_at) AS order_month,
        SUM(o.total) AS revenue
    FROM orders o
    JOIN cohorts c ON c.user_id = o.user_id
    WHERE o.status = 'paid'
    GROUP BY 1, 2, 3
),
ltv_cumulative AS (
    SELECT
        user_id,
        cohort_month,
        order_month,
        EXTRACT(MONTH FROM AGE(order_month, cohort_month))::INT AS months_since_signup,
        SUM(revenue) OVER (
            PARTITION BY user_id
            ORDER BY order_month
        ) AS cumulative_ltv
    FROM revenue_by_month
)
SELECT
    cohort_month,
    months_since_signup,
    AVG(cumulative_ltv) AS avg_cumulative_ltv,
    COUNT(DISTINCT user_id) AS users_alive
FROM ltv_cumulative
GROUP BY cohort_month, months_since_signup
ORDER BY cohort_month, months_since_signup;

4. LTV для SaaS (подписка)

-- простая формула: LTV = ARPU / churn
WITH arpu AS (
    SELECT AVG(monthly_revenue) AS arpu
    FROM (
        SELECT
            user_id,
            DATE_TRUNC('month', created_at) AS month,
            SUM(amount) AS monthly_revenue
        FROM payments
        GROUP BY 1, 2
    ) t
),
churn AS (
    SELECT
        COUNT(*) FILTER (WHERE status = 'churned')::FLOAT
        / COUNT(*) AS monthly_churn_rate
    FROM subscriptions
    WHERE created_at > NOW() - INTERVAL '3 months'
)
SELECT
    arpu.arpu,
    churn.monthly_churn_rate,
    arpu.arpu / churn.monthly_churn_rate AS ltv
FROM arpu, churn;

5. LTV через формулу e-commerce

LTV = AOV × frequency × lifespan

WITH user_stats AS (
    SELECT
        user_id,
        AVG(total) AS aov,
        COUNT(*) / NULLIF(
            EXTRACT(MONTH FROM AGE(MAX(created_at), MIN(created_at))), 0
        ) AS orders_per_month,
        EXTRACT(MONTH FROM AGE(MAX(created_at), MIN(created_at)))::NUMERIC AS lifespan_months
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
    HAVING COUNT(*) > 1  -- только многократные для расчёта frequency
)
SELECT
    AVG(aov) AS avg_order_value,
    AVG(orders_per_month) AS avg_monthly_frequency,
    AVG(lifespan_months) AS avg_lifespan,
    AVG(aov * orders_per_month * lifespan_months) AS ltv
FROM user_stats;

6. Predictive LTV (простейший подход)

Для клиентов, которые ещё не ушли, нужно предсказать будущую выручку:

-- берём когорту давно активных (>= 6 месяцев) и считаем их средний LTV
WITH old_cohort AS (
    SELECT user_id
    FROM users
    WHERE created_at < NOW() - INTERVAL '12 months'
)
SELECT
    AVG(ur.total_revenue) AS avg_ltv_old_cohort
FROM old_cohort oc
JOIN (
    SELECT user_id, SUM(total) AS total_revenue
    FROM orders WHERE status = 'paid'
    GROUP BY user_id
) ur ON ur.user_id = oc.user_id;

Этот показатель — ориентир для «средней жизни клиента».

7. LTV с учётом margin

Не все деньги = прибыль. Для реальной экономики:

SELECT
    AVG(ltv * 0.70) AS ltv_with_margin  -- 70% gross margin
FROM (
    SELECT user_id, SUM(total) AS ltv
    FROM orders WHERE status = 'paid'
    GROUP BY user_id
) t;

8. LTV / CAC

Связка — главная unit-экономическая метрика:

WITH user_ltv AS (
    SELECT user_id, SUM(total) * 0.70 AS ltv  -- margin
    FROM orders GROUP BY user_id
),
user_cac AS (
    SELECT user_id, spend AS cac
    FROM marketing_attribution
)
SELECT
    AVG(ul.ltv) AS avg_ltv,
    AVG(uc.cac) AS avg_cac,
    AVG(ul.ltv) / AVG(uc.cac) AS ltv_cac_ratio
FROM user_ltv ul
JOIN user_cac uc ON uc.user_id = ul.user_id;

Целевой LTV/CAC:

• 3:1 — здоровая unit-экономика
• 5:1+ — недоинвестируете в маркетинг
• <2:1 — переплачиваете за привлечение

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

Ошибка 1. Учитывать отменённые заказы

-- включает возвраты → завышает LTV
SUM(total) FROM orders

-- правильно
SUM(total) FROM orders WHERE status = 'paid'
-- или
SUM(total - refund_amount)

Ошибка 2. LTV без учёта margin

Revenue ≠ Profit. 100 ₽ LTV при 20% марже = 20 ₽ profit. Сравнение с CAC должно быть через margin.

Ошибка 3. LTV всех клиентов vs только активных

• LTV «всех» включает тех, кто ушёл за 1 заказ
• LTV «активных» исключает — искажает картину в «лучшую» сторону

Ошибка 4. Исторический LTV ≠ прогнозный

Исторический LTV показывает, что уже случилось. Для CAC-сравнения нужен прогнозный (или по крайней мере по старой когорте).

Ошибка 5. Средняя vs медиана

LTV обычно с тяжёлым хвостом. Топ-1% клиентов могут давать 30% выручки. Среднее обманывает — смотрите и медиану.

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

• Формула LTV и пример расчёта
• Когортный анализ в SQL
• Как считать MAU / DAU в SQL
• Как считать churn в SQL

FAQ

Какой LTV нужен — исторический или прогнозный?

Для отчётов — исторический. Для сравнения с CAC и принятия решений — прогнозный.

LTV только по deliverted orders?

Да, только по оплаченным и не возвращённым. Иначе метрика завышена.

Считать LTV в рублях или в «условных единицах»?

Для сравнения с CAC — в рублях (или валюте spend). Для внутренних трендов — unit agnostic.

Как учесть скидки?

LTV = net revenue (после скидок, до margin). Отдельно держите LTV «gross» и «net».

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.