LTV когорты в SQL: одинаковое окно наблюдения

Январские пользователи успели провести в продукте три месяца, мартовские — только две недели. Если просто поделить всю накопленную выручку каждой когорты на число зарегистрированных, январь почти наверняка получит преимущество по времени наблюдения. Для честного сравнения надо смотреть одинаковый возраст пользователей, например первые 30 дней после регистрации.

Есть и вторая ловушка. Пользователь, зарегистрированный 15 марта, к дате отчёта 1 апреля ещё не прожил 30 дней в продукте. Если оставить его с нулём или неполной выручкой, мартовская когорта будет искусственно занижена. Если оставить только ранних мартовских пользователей, изменится состав когорты. Поэтому весь месяц регистрации участвует в сравнении лишь после того, как 30-дневное окно завершилось даже у его последних зарегистрированных.

Сначала пользователь, потом когорта

В CTE eligible отбираются люди из полностью созревших месячных когорт. Выражение с первым и последним днём месяца проверяет окно последнего возможного участника когорты. Заказы присоединяются по customer_id и датам от регистрации включительно до 30-го дня исключительно. LEFT JOIN сохраняет человека без покупки; его вклад равен нулю. Затем получаем одну строку на пользователя и лишь после этого среднее по месяцу регистрации.

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, signed_on TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, paid_on TEXT, amount INTEGER);
INSERT INTO customers VALUES (1,'2025-01-01'),(2,'2025-01-10'),(3,'2025-02-01'),(4,'2025-03-15');
INSERT INTO orders VALUES
 (11,1,'2025-01-01',10),(12,1,'2025-01-20',20),(13,1,'2025-02-15',100),
 (14,2,'2025-02-05',30),(15,3,'2025-02-20',40),(16,4,'2025-03-20',50);
WITH eligible AS (
 SELECT customer_id,signed_on,substr(signed_on,1,7) AS cohort
 FROM customers
 WHERE date(substr(signed_on,1,7)||'-01','+1 month','-1 day','+30 days') <= '2025-04-01'
), user_value AS (
 SELECT e.customer_id,e.cohort,COALESCE(SUM(o.amount),0) AS first_30_days
 FROM eligible AS e LEFT JOIN orders AS o
   ON o.customer_id=e.customer_id
  AND o.paid_on >= e.signed_on
  AND o.paid_on < date(e.signed_on,'+30 days')
 GROUP BY e.customer_id,e.cohort
)
SELECT cohort,COUNT(*) AS users, SUM(first_30_days) AS revenue_30d,
       ROUND(AVG(first_30_days * 1.0),2) AS ltv_30d
FROM user_value GROUP BY cohort ORDER BY cohort;

Январь: два пользователя, выручка первых 30 дней 60, LTV30 = 30 на человека. Заказ клиента 1 от 15 февраля на 100 не входит в его 30-дневное окно. Февраль: один пользователь, выручка и LTV30 равны 40. Мартовский пользователь не попадает в сравнение: его окно ещё открыто.

Термин LTV здесь означает конкретный горизонт и конкретный знаменатель: выручка за первые 30 дней на зарегистрированного пользователя. Это не прогноз полной пожизненной ценности и не маржа. Если бизнес принимает решения по прибыли, в числителе нужны согласованные затраты и возвраты; если по платящим пользователям, изменится знаменатель и смысл показателя.

Граница окна должна быть однозначной

Интервал [signed_on, signed_on + 30 дней) исключает заказ ровно в начале 31-го дня. Так окна соседних горизонтов не пересекаются. В примере используются даты без времени, а регистрация считается в начале дня. Для реальных timestamp согласуйте временную зону и считайте 30×24 часов либо 30 календарных дней согласно договорённости.

Если нужен отчёт за текущий месяц, можно показать незрелую когорту отдельно с количеством дней наблюдения, но сравнивать её LTV30 со зрелыми когортами нельзя. В идеале проверка отчёта содержит пользователя с покупкой на последней допустимой дате, пользователя с покупкой за границей и пользователя без покупок: все три меняют среднее по-разному.

Код ждёт созревания всей календарной когорты, а не только каждого пользователя по отдельности. Поэтому февраль появляется после завершения окна даже у зарегистрировавшихся 28 февраля. Это сохраняет состав полной февральской когорты. Если показать только первых зарегистрировавшихся в ещё незрелом месяце, сравнение будет зависеть от дня регистрации внутри месяца.

Возвраты требуют отдельного решения. Если заказ из первых 30 дней вернули на 40-й день, отчёт по исходным платежам всё ещё покажет его как выручку. Для метрики чистой выручки нужно определить, приписывать ли возврат к дате покупки и как долго ждать окончательной суммы. Это меняет дату, когда когорта считается зрелой для финансового отчёта, хотя окно поведения пользователя осталось 30-дневным.

Для проверки знаменателя сверьте число зарегистрированных в каждой включённой когорте с отдельным реестром регистраций. Человек с нулём покупок должен оставаться в группе. Если среднее неожиданно выросло после перехода с LEFT JOIN на INNER JOIN, причина может быть в исчезновении неплатящих пользователей, а не в росте дохода.

На курсе «Продвинутый SQL» когорты и retention сочетаются с временными условиями, JOIN и витриной интернет-магазина. Практические запросы проверяются на разных наборах данных. Откройте программу, чтобы строить когортные метрики с ясным знаменателем и окном.

Для показателя возвращения без денежного числителя полезен разбор ошибок в retention когорт.