Lifetime Value (LTV)

Зачем нужна

Отвечает, сколько можно тратить на привлечение (CAC) и когда окупится реклама.

Формула

LTV = Сумма оплаченных заказов когорты / Размер когорты

Как считать

Строим когорты по дате регистрации или другой дате, суммируем оплаченные заказы когорты и делим на размер когорты.

SQL-запрос

WITH cohort AS (
  SELECT user_id, created_at::date AS cohort_date
  FROM users
  WHERE created_at >= CURRENT_DATE - INTERVAL '180 day'
),
revenue AS (
  SELECT c.cohort_date, SUM(o.amount) AS revenue
  FROM cohort c
  LEFT JOIN orders o ON o.user_id = c.user_id AND o.status = 'paid'
  GROUP BY 1
),
cohort_size AS (
  SELECT cohort_date, COUNT(*) AS users
  FROM cohort
  GROUP BY 1
)
SELECT
  cs.cohort_date,
  cs.users,
  r.revenue,
  ROUND(r.revenue / NULLIF(cs.users, 0), 2) AS ltv
FROM cohort_size cs
LEFT JOIN revenue r ON r.cohort_date = cs.cohort_date
ORDER BY cs.cohort_date DESC;