Как собрать отчёт по LTV когорты в BigQuery за 30 минут

Как собрать отчёт по LTV когорты в BigQuery за 30 минут

Если вы работаете с e-com или подпиской, в 2026 году одного отчёта по первой покупке уже мало: маржа сжимается, а удержание и повторные заказы решают больше, чем разовый трафик. Ниже — практический способ собрать когортный отчёт по LTV в BigQuery без лишней ручной работы.

1. Подготовьте таблицу заказов с полями: `user_id`, `order_id`, `order_date`, `revenue`, `channel`.
2. Зафиксируйте дату первой покупки каждого пользователя:
```sql
with first_order as (
select
user_id,
min(order_date) as cohort_date
from `project.dataset.orders`
group by user_id
)
```
3. Присоедините все заказы к когорте и посчитайте возраст пользователя в неделях или месяцах:
```sql
select
f.cohort_date,
date_diff(o.order_date, f.cohort_date, month) as age_month,
o.user_id,
o.revenue
from first_order f
join `project.dataset.orders` o using(user_id)
```
4. Сверните выручку по когорте и возрасту:
```sql
select
cohort_date,
age_month,
count(distinct user_id) as buyers,
sum(revenue) as revenue
from joined_data
group by 1,2
```
5. Добавьте LTV на пользователя:
`ltv = revenue / buyers`
6. Для сравнения каналов добавьте `channel` из первого заказа, а не из последнего. Иначе получите искажение по атрибуции.
7. Выведите матрицу: строки — когорта по месяцу первой покупки, столбцы — возраст, значения — LTV.

Что это даёт на практике:
— видно, какие каналы приводят не просто покупки, а деньги на горизонте 3–6 месяцев;
— можно сравнить когорты до и после изменения оффера, креатива или цен;
— проще защищать бюджет перед sales и finance, когда маркетинг отвечает за выручку, а не только за MQL.

На этой неделе достаточно собрать одну витрину и проверить 3 вещи: корректность даты первой покупки, отсутствие дублей заказов, совпадение суммы с финансовым отчётом. Եթե нужно, следующим постом дам готовый запрос для матрицы LTV в разрезе каналов.

— @BigQuery4MarketingPro