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

Зачем аналитику считать LTV в SQL

LTV определяет всю маркетинговую стратегию: сколько можно тратить на привлечение, в какие каналы инвестировать, когда запускать дорогой upsell. Если LTV посчитан криво, CAC-бюджеты распределяются неверно — компания либо перезакапывает деньги в маркетинг, либо недоинвестирует и отдаёт долю рынка конкурентам.

На словах LTV кажется простой метрикой, но дьявол в деталях. Учитываем ли мы отменённые заказы? Считаем выручку или маржу? Смотрим историю или пытаемся спрогнозировать? Берём среднее или медиану? В зависимости от ответов цифра может отличаться в два-три раза — а решения принимаются разные.

В статье — восемь готовых SQL-запросов под частые задачи:

• исторический LTV по пользователю и по когорте
• LTV для SaaS через ARPU / churn
• LTV для e-commerce через AOV × frequency × lifespan
• накопительный LTV по месяцам с момента регистрации
• упрощённый predictive LTV
• LTV с учётом маржи
• отношение LTV / CAC по каналам

Схема таблиц в примерах: orders(user_id, total, status, created_at) и users(user_id, attribution_channel).

Что такое LTV

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

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

Для e-commerce:
LTV = AOV × purchase_frequency × customer_lifespan

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

Для долгосрочных решений имеет смысл пересчитывать LTV через маржу: 1000 ₽ LTV с 20% гросс-маржей — это 200 ₽ прибыли, и именно эту цифру справедливо сравнивать с CAC.

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,
        -- общее число месяцев между cohort_month и order_month
        (EXTRACT(YEAR  FROM AGE(order_month, cohort_month)) * 12
         + 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;

Обратите внимание на формулу months_since_signup: EXTRACT(MONTH FROM AGE(...)) возвращает только компонент месяцев (0–11), поэтому отдельно добавляем EXTRACT(YEAR FROM AGE(...)) * 12. Иначе данные за полтора года склеятся в «6 месяцев» и когорта будет выглядеть странно.

4. LTV для SaaS через ARPU / churn

Классическая формула для подписочных продуктов:

WITH arpu_calc 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_calc AS (
    SELECT
        COUNT(*) FILTER (WHERE status = 'churned')::FLOAT
        / NULLIF(COUNT(*), 0) AS monthly_churn_rate
    FROM subscriptions
    WHERE created_at > NOW() - INTERVAL '3 months'
)
SELECT
    arpu_calc.arpu,
    churn_calc.monthly_churn_rate,
    arpu_calc.arpu / NULLIF(churn_calc.monthly_churn_rate, 0) AS ltv
FROM arpu_calc, churn_calc;

Ограничения у формулы серьёзные: она предполагает стабильный ARPU и постоянный churn. На практике эти числа плавают, поэтому результат имеет смысл перепроверять когортной картиной из запроса №2.

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

LTV = AOV × purchase_frequency × customer_lifespan. Реализуем напрямую:

WITH user_stats AS (
    SELECT
        user_id,
        AVG(total) AS aov,
        -- общее число месяцев между первой и последней покупкой
        (EXTRACT(YEAR  FROM AGE(MAX(created_at), MIN(created_at))) * 12
         + EXTRACT(MONTH FROM AGE(MAX(created_at), MIN(created_at))))::NUMERIC AS lifespan_months,
        COUNT(*) / NULLIF(
            EXTRACT(YEAR  FROM AGE(MAX(created_at), MIN(created_at))) * 12
            + EXTRACT(MONTH FROM AGE(MAX(created_at), MIN(created_at))),
        0) AS orders_per_month
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
    HAVING COUNT(*) > 1  -- только многократные покупатели
)
SELECT
    AVG(aov)                                          AS avg_order_value,
    AVG(orders_per_month)                             AS avg_monthly_frequency,
    AVG(lifespan_months)                              AS avg_lifespan_months,
    AVG(aov * orders_per_month * lifespan_months)     AS ltv
FROM user_stats;

Формула красивая, но чувствительна к выбросам — одна сверхкрупная когорта старых пользователей может завысить среднее. В отчётах имеет смысл показывать и медиану.

6. Упрощённый predictive LTV

Для новых клиентов, которые ещё не ушли, нужен прогноз. Самый простой подход — взять давнюю когорту, у которой «жизнь» уже завершилась, и использовать её как ориентир:

WITH old_cohort AS (
    SELECT user_id
    FROM users
    WHERE created_at < NOW() - INTERVAL '12 months'
),
user_revenue AS (
    SELECT user_id, SUM(total) AS total_revenue
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
)
SELECT
    AVG(ur.total_revenue) AS avg_ltv_old_cohort
FROM old_cohort oc
JOIN user_revenue ur USING (user_id);

Это грубая оценка: она подходит для sanity check и как baseline для моделей. Серьёзный predictive LTV обычно строят отдельно — через модели выживания, BG/NBD + Gamma-Gamma или ML-регрессию.

7. LTV с учётом маржи

Сравнивать с CAC надо прибыль, а не выручку:

SELECT
    AVG(ltv * 0.70) AS ltv_with_margin  -- подставьте свой gross margin
FROM (
    SELECT user_id, SUM(total) AS ltv
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
) t;

Если маржинальность разная у категорий — лучше посчитать её на уровне строки заказа и уже потом суммировать.

8. LTV / CAC по каналам

Ключевая метрика юнит-экономики: есть ли смысл в выбранном маркетинговом миксе:

WITH user_ltv AS (
    SELECT user_id, SUM(total) * 0.70 AS ltv  -- с учётом маржи
    FROM orders
    WHERE status = 'paid'
    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) / NULLIF(AVG(uc.cac), 0)               AS ltv_cac_ratio
FROM user_ltv ul
JOIN user_cac uc USING (user_id);

Ориентиры:

• 3:1 — здоровая юнит-экономика.
• 5:1 и выше — часто признак недоинвестирования в маркетинг (можно вливать больше, пока отдача остаётся).
• <2:1 — приобретение стоит слишком дорого относительно ценности клиента, нужно искать причины.

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

1. Включать отменённые заказы

-- завышает LTV, потому что возвраты и отмены тоже попадают в SUM
SELECT SUM(total) FROM orders

-- правильно — только оплаченные
SELECT SUM(total) FROM orders WHERE status = 'paid'

-- либо явно вычитаем возвраты
SELECT SUM(total - refund_amount) FROM orders

2. Путать выручку и маржу

Revenue ≠ Profit. 1000 ₽ LTV при 20% марже — это 200 ₽ прибыли. Для сравнения с CAC считать нужно именно прибыль.

3. LTV «всех» vs «активных»

• «LTV всех» включает тех, кто ушёл после одного заказа, — цифра честная, но пессимистичная.
• «LTV активных» искусственно улучшает картину, отсекая «не прижившихся».

Оптимально держать обе метрики и явно подписывать, какую где показываете.

4. Исторический LTV вместо прогнозного

Исторический LTV честно говорит, что уже случилось. Для принятия решений о маркетинговом бюджете нужен хотя бы когортный взгляд на зрелые когорты — он даёт лучшее приближение будущего.

5. Среднее вместо медианы

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

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

• LTV простыми словами
• SQL для когортного анализа
• Как посчитать CAC в SQL
• Как посчитать churn в SQL
• NRR (Net Revenue Retention) для SaaS
• Contribution margin — формула и расчёт
• ACV (Annual Contract Value) для B2B

FAQ

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

Для отчётов о том, что уже случилось, — исторический. Для решений о маркетинговом бюджете и запуска новых каналов — прогнозный (или хотя бы когортный).

LTV только по оплаченным и доставленным заказам?

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

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

Для сравнения с CAC — в той же валюте, что и spend. Для внутренних трендов можно использовать любую единицу, главное — единообразно.

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

Считайте net revenue (после скидок, до маржи). Полезно держать рядом две метрики — «LTV gross» и «LTV net», — тогда видны и величина скидок, и их влияние на юнит-экономику.

Нужно ли дисконтировать будущий LTV?

На горизонтах меньше года — обычно нет. На длинных горизонтах (годы подписки) имеет смысл дисконтировать будущие потоки к текущему моменту, особенно при нестабильной ставке.