SQL: Расчет LTV по когортам

Решение: Расчет LTV по когортам

Понимание метрики

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

LTV по когортам — средняя выручка на пользователя по месяцам его регистрации.

Пример:

• Когорта январь 2024: 1000 пользователей, выручка 500,000 руб → LTV = 500 руб/пользователя
• Когорта февраль 2024: 1200 пользователей, выручка 840,000 руб → LTV = 700 руб/пользователя

SQL-запрос (базовый вариант)

SELECT
  DATE_TRUNC('month', u.registration_date)::date as registration_month,
  COUNT(DISTINCT u.user_id) as users_count,
  ROUND(SUM(COALESCE(p.amount, 0)), 2) as total_revenue,
  ROUND(SUM(COALESCE(p.amount, 0)) / COUNT(DISTINCT u.user_id), 2) as ltv
FROM users u
LEFT JOIN payments p ON u.user_id = p.user_id
GROUP BY DATE_TRUNC('month', u.registration_date)
ORDER BY registration_month;

Объяснение

Ключевые элементы:

1. DATE_TRUNC('month', u.registration_date)::date — берём первый день месяца регистрации

   • Результат: 2024-01-01, 2024-02-01 и т.д.

2. LEFT JOIN payments — важно левое соединение, потому что есть пользователи без платежей

   • COALESCE(p.amount, 0) — если нет платежей, считаем 0

3. COUNT(DISTINCT u.user_id) — считаем уникальных пользователей в когорте

4. SUM(p.amount) — общая выручка когорты

5. SUM(p.amount) / COUNT(u.user_id) — делим выручку на количество пользователей = LTV

Пример вывода

registration_month | users_count | total_revenue | ltv
-------------------|-------------|---------------|------
2024-01-01         | 1000        | 500000.00     | 500.00
2024-02-01         | 1200        | 840000.00     | 700.00
2024-03-01         | 900         | 315000.00     | 350.00

Вариант с дополнительной статистикой

SELECT
  DATE_TRUNC('month', u.registration_date)::date as registration_month,
  COUNT(DISTINCT u.user_id) as users_count,
  COUNT(DISTINCT CASE WHEN p.amount > 0 THEN u.user_id END) as paying_users,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN p.amount > 0 THEN u.user_id END) / COUNT(DISTINCT u.user_id), 2) as conversion_to_paying,
  ROUND(SUM(COALESCE(p.amount, 0)), 2) as total_revenue,
  ROUND(SUM(COALESCE(p.amount, 0)) / COUNT(DISTINCT u.user_id), 2) as ltv,
  ROUND(SUM(COALESCE(p.amount, 0)) / COUNT(DISTINCT CASE WHEN p.amount > 0 THEN u.user_id END), 2) as avg_customer_value,
  COUNT(DISTINCT p.payment_id) as total_payments
FROM users u
LEFT JOIN payments p ON u.user_id = p.user_id
GROUP BY DATE_TRUNC('month', u.registration_date)
ORDER BY registration_month;

Дополнительно показывает:

• Процент пользователей, которые сделали хотя бы один платёж
• Среднюю выручку на платящего пользователя (Average Customer Value)
• Количество платежей

Вариант с временным периодом

-- LTV только для пользователей, которые были активны минимум N дней
WITH user_cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', registration_date)::date as registration_month,
    CURRENT_DATE - registration_date as days_since_registration
  FROM users
)
SELECT
  uc.registration_month,
  COUNT(DISTINCT uc.user_id) as users_count,
  ROUND(SUM(COALESCE(p.amount, 0)), 2) as total_revenue,
  ROUND(SUM(COALESCE(p.amount, 0)) / COUNT(DISTINCT uc.user_id), 2) as ltv
FROM user_cohorts uc
LEFT JOIN payments p ON uc.user_id = p.user_id
WHERE uc.days_since_registration >= 30  -- Считаем LTV только для тех, кто зарегистрировался минимум месяц назад
GROUP BY uc.registration_month
ORDER BY uc.registration_month;

Это важно, так как свежие когорты не имели достаточно времени для накопления выручки.

LTV по неделям

SELECT
  DATE_TRUNC('week', u.registration_date)::date as registration_week,
  COUNT(DISTINCT u.user_id) as users_count,
  ROUND(SUM(COALESCE(p.amount, 0)), 2) as total_revenue,
  ROUND(SUM(COALESCE(p.amount, 0)) / COUNT(DISTINCT u.user_id), 2) as ltv
FROM users u
LEFT JOIN payments p ON u.user_id = p.user_id
GROUP BY DATE_TRUNC('week', u.registration_date)
ORDER BY registration_week;

LTV по кварталам

SELECT
  TO_CHAR(u.registration_date, 'YYYY-Q') as registration_quarter,
  COUNT(DISTINCT u.user_id) as users_count,
  ROUND(SUM(COALESCE(p.amount, 0)), 2) as total_revenue,
  ROUND(SUM(COALESCE(p.amount, 0)) / COUNT(DISTINCT u.user_id), 2) as ltv
FROM users u
LEFT JOIN payments p ON u.user_id = p.user_id
GROUP BY TO_CHAR(u.registration_date, 'YYYY-Q')
ORDER BY registration_quarter;

Сегментация по LTV

WITH cohort_ltv AS (
  SELECT
    DATE_TRUNC('month', u.registration_date)::date as registration_month,
    COUNT(DISTINCT u.user_id) as users_count,
    ROUND(SUM(COALESCE(p.amount, 0)) / COUNT(DISTINCT u.user_id), 2) as ltv
  FROM users u
  LEFT JOIN payments p ON u.user_id = p.user_id
  GROUP BY DATE_TRUNC('month', u.registration_date)
)
SELECT
  registration_month,
  users_count,
  ltv,
  CASE
    WHEN ltv >= 1000 THEN 'High Value'
    WHEN ltv >= 500 THEN 'Medium Value'
    WHEN ltv > 0 THEN 'Low Value'
    ELSE 'No Revenue'
  END as ltv_segment
FROM cohort_ltv
ORDER BY registration_month;

Сравнение когорт со скользящим средним

WITH cohort_ltv AS (
  SELECT
    DATE_TRUNC('month', u.registration_date)::date as registration_month,
    COUNT(DISTINCT u.user_id) as users_count,
    ROUND(SUM(COALESCE(p.amount, 0)) / COUNT(DISTINCT u.user_id), 2) as ltv
  FROM users u
  LEFT JOIN payments p ON u.user_id = p.user_id
  GROUP BY DATE_TRUNC('month', u.registration_date)
)
SELECT
  registration_month,
  users_count,
  ltv,
  ROUND(
    AVG(ltv) OVER (
      ORDER BY registration_month 
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2
  ) as ltv_3month_moving_avg,
  LAG(ltv) OVER (ORDER BY registration_month) as ltv_previous_month
FROM cohort_ltv
ORDER BY registration_month;

Это позволит увидеть тренд LTV по когортам.

Анализ с разбивкой по типу платежа

SELECT
  DATE_TRUNC('month', u.registration_date)::date as registration_month,
  COUNT(DISTINCT u.user_id) as users_count,
  ROUND(SUM(COALESCE(p.amount, 0)), 2) as total_revenue,
  ROUND(SUM(COALESCE(p.amount, 0)) / COUNT(DISTINCT u.user_id), 2) as ltv,
  -- Если есть колонка payment_type в payments
  ROUND(SUM(CASE WHEN p.payment_type = 'subscription' THEN p.amount ELSE 0 END) / COUNT(DISTINCT u.user_id), 2) as ltv_subscription,
  ROUND(SUM(CASE WHEN p.payment_type = 'one-time' THEN p.amount ELSE 0 END) / COUNT(DISTINCT u.user_id), 2) as ltv_one_time
FROM users u
LEFT JOIN payments p ON u.user_id = p.user_id
GROUP BY DATE_TRUNC('month', u.registration_date)
ORDER BY registration_month;

Важные замечания

1. Полнота данных по новым когортам

• Свежие когорты (менее месяца назад) имеют неполные данные
• Рекомендуется считать LTV только для когорт старше 30-90 дней

2. Учёт возвратов

• Если есть возвраты в таблице payments, используйте абсолютное значение после дебита
• Или фильтруйте: WHERE p.amount > 0

3. Валюта

• Если платежи в разных валютах, конвертируйте к единой

4. Сезонность

• Когорты могут отличаться по сезону
• Сравнивайте когорты из одного и того же периода