LTV в SQL: как посчитать Lifetime Value пользователя

LTV (Lifetime Value, Customer Lifetime Value) — сколько денег приносит один пользователь за всё время взаимодействия с продуктом. Один из самых важных показателей для оценки юнит-экономики.

Простой исторический LTV

Самый прямолинейный способ — сложить всё что пользователь заплатил:

SELECT
    user_id,
    SUM(amount) AS historical_ltv,
    COUNT(*) AS total_orders,
    MIN(created_at) AS first_order,
    MAX(created_at) AS last_order,
    MAX(created_at)::date - MIN(created_at)::date AS lifespan_days
FROM orders
WHERE status = 'completed'
GROUP BY user_id
ORDER BY historical_ltv DESC;

Средний LTV по когортам

LTV пользователей, зарегистрированных в определённый период:

WITH cohorts AS (
    SELECT user_id, DATE_TRUNC('month', created_at)::date AS cohort_month
    FROM users
),
revenue_by_user AS (
    SELECT user_id, SUM(amount) AS total_revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
)
SELECT
    c.cohort_month,
    COUNT(DISTINCT c.user_id) AS cohort_size,
    COALESCE(SUM(r.total_revenue), 0) AS total_ltv,
    ROUND(COALESCE(AVG(r.total_revenue), 0), 2) AS avg_ltv,
    ROUND(COALESCE(SUM(r.total_revenue), 0) / COUNT(DISTINCT c.user_id), 2) AS ltv_per_user
FROM cohorts c
LEFT JOIN revenue_by_user r ON r.user_id = c.user_id
GROUP BY c.cohort_month
ORDER BY c.cohort_month;

LTV нарастающим итогом по месяцам жизни

Классическая когортная таблица: сколько денег принесла когорта через 1, 3, 6, 12 месяцев.

WITH cohorts AS (
    SELECT user_id, DATE_TRUNC('month', created_at) AS cohort_month
    FROM users
    WHERE created_at >= '2025-01-01'
),
orders_with_age AS (
    SELECT
        c.user_id,
        c.cohort_month,
        o.amount,
        o.created_at,
        FLOOR(EXTRACT(EPOCH FROM (DATE_TRUNC('month', o.created_at) - c.cohort_month)) / 2592000)::int AS months_since_join
    FROM cohorts c
    JOIN orders o ON o.user_id = c.user_id AND o.status = 'completed'
),
cohort_sizes AS (
    SELECT cohort_month, COUNT(*) AS size
    FROM cohorts GROUP BY 1
)
SELECT
    oa.cohort_month,
    cs.size,
    oa.months_since_join AS month,
    ROUND(SUM(SUM(oa.amount)) OVER (
        PARTITION BY oa.cohort_month
        ORDER BY oa.months_since_join
    ) / cs.size, 2) AS cumulative_ltv_per_user
FROM orders_with_age oa
JOIN cohort_sizes cs ON cs.cohort_month = oa.cohort_month
GROUP BY oa.cohort_month, oa.months_since_join, cs.size
ORDER BY oa.cohort_month, oa.months_since_join;

Предиктивный LTV (простая формула)

Для прогноза используют формулу на основе средних значений:

LTV = ARPU × Retention_Rate / (1 - Retention_Rate)

или через churn:

LTV = ARPU / Churn_Rate

WITH base AS (
    SELECT
        -- Средняя выручка на пользователя в месяц
        SUM(amount) / COUNT(DISTINCT user_id) / COUNT(DISTINCT DATE_TRUNC('month', created_at)) AS arpu,

        -- Процент пользователей, купивших повторно (упрощённый retention)
        COUNT(DISTINCT CASE WHEN purchase_number > 1 THEN user_id END)::numeric
        / COUNT(DISTINCT CASE WHEN purchase_number = 1 THEN user_id END) AS retention_rate
    FROM (
        SELECT
            user_id,
            amount,
            created_at,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS purchase_number
        FROM orders WHERE status = 'completed'
    ) o
)
SELECT
    ROUND(arpu, 2) AS monthly_arpu,
    ROUND(retention_rate * 100, 1) AS retention_pct,
    ROUND(arpu / NULLIF(1 - retention_rate, 0), 2) AS predicted_ltv
FROM base;

LTV по сегментам пользователей

SELECT
    CASE
        WHEN orders_count = 1          THEN 'one-time'
        WHEN orders_count BETWEEN 2 AND 5 THEN 'occasional'
        ELSE 'loyal'
    END AS segment,
    COUNT(*) AS users,
    ROUND(AVG(total_revenue), 2) AS avg_ltv,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_revenue), 2) AS median_ltv
FROM (
    SELECT
        user_id,
        COUNT(*) AS orders_count,
        SUM(amount) AS total_revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
) u
GROUP BY 1
ORDER BY avg_ltv DESC;

LTV/CAC: окупаемость привлечения

WITH user_ltv AS (
    SELECT user_id, SUM(amount) AS ltv
    FROM orders WHERE status = 'completed'
    GROUP BY user_id
),
acquisition_costs AS (
    SELECT user_id, acquisition_cost AS cac, channel
    FROM user_acquisition
),
combined AS (
    SELECT
        a.channel,
        COUNT(*) AS users,
        ROUND(AVG(l.ltv), 2) AS avg_ltv,
        ROUND(AVG(a.cac), 2) AS avg_cac,
        ROUND(AVG(l.ltv) / NULLIF(AVG(a.cac), 0), 2) AS ltv_cac_ratio
    FROM acquisition_costs a
    LEFT JOIN user_ltv l ON l.user_id = a.user_id
    GROUP BY a.channel
)
SELECT *,
    CASE
        WHEN ltv_cac_ratio >= 3 THEN '✅ Хорошо'
        WHEN ltv_cac_ratio >= 1 THEN '⚠️ На грани'
        ELSE '❌ Убыточно'
    END AS verdict
FROM combined
ORDER BY ltv_cac_ratio DESC;

Ориентир: LTV/CAC > 3 — хороший результат для большинства бизнесов.

Медиана vs Среднее в LTV

Среднее LTV сильно искажается крупными покупателями:

SELECT
    ROUND(AVG(total_revenue), 2)                                          AS mean_ltv,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_revenue), 2)  AS median_ltv,
    ROUND(PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total_revenue), 2)  AS p90_ltv,
    ROUND(PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY total_revenue), 2) AS p99_ltv,
    MAX(total_revenue)                                                     AS max_ltv
FROM (
    SELECT user_id, SUM(amount) AS total_revenue
    FROM orders WHERE status = 'completed'
    GROUP BY user_id
) u;

Если mean >> median — у вас есть «киты» (whale users), которые тянут среднее вверх. Медиана честнее отражает типичного пользователя.

Срок окупаемости (Payback Period)

-- Через сколько месяцев средний пользователь окупает CAC
SELECT
    channel,
    avg_cac,
    monthly_arpu,
    ROUND(avg_cac / NULLIF(monthly_arpu, 0), 1) AS payback_months
FROM (
    SELECT
        a.channel,
        AVG(a.acquisition_cost) AS avg_cac,
        SUM(o.amount) / COUNT(DISTINCT o.user_id) / 12.0 AS monthly_arpu
    FROM user_acquisition a
    LEFT JOIN orders o ON o.user_id = a.user_id AND o.status = 'completed'
    GROUP BY a.channel
) t
ORDER BY payback_months;

Итог: ключевые формулы

LTV — метрика, которая связывает маркетинг, продукт и финансы. Считайте его по когортам, сравнивайте каналы привлечения и следите за динамикой — это покажет, становится ли бизнес более или менее эффективным со временем.