Как построить когортный анализ retention или LTV в BI-инструменте или таблице, и какая структура данных для этого нужна?

Короткий ответ

Нужна таблица на уровне отдельного пользователя или заказа с датой первого события (когорта) и датами последующих событий, чтобы сгруппировать пользователей по когорте и посчитать метрику по «возрасту» после входа.

Как это работает подробнее

Базовая структура — таблица user_id, cohort_date (дата первого визита или покупки), event_date, event_type, value; далее для каждого пользователя считается «неделя/месяц от входа» (event_date минус cohort_date), и метрика агрегируется по когорте и этому возрасту. Для продуктов с длинным циклом покупки горизонт когортного анализа стоит продлевать до нескольких месяцев, иначе ранние выводы о retention могут быть преждевременными и неточными.

Пример кода

cohort_retention_bigquery.sqlКопировать

WITH user_events AS (
  SELECT user_id,
         DATE_TRUNC(first_purchase_date, MONTH) AS cohort_month,
         DATE_TRUNC(event_date, MONTH) AS activity_month
  FROM customer_events
), cohort_sizes AS (
  SELECT cohort_month, COUNT(DISTINCT user_id) AS cohort_size
  FROM user_events
  GROUP BY cohort_month
), cohort_activity AS (
  SELECT cohort_month, activity_month,
         DATE_DIFF(activity_month, cohort_month, MONTH) AS month_number,
         COUNT(DISTINCT user_id) AS active_users
  FROM user_events
  GROUP BY cohort_month, activity_month
)
SELECT a.cohort_month, a.month_number,
       SAFE_DIVIDE(a.active_users, s.cohort_size) AS retention
FROM cohort_activity AS a
JOIN cohort_sizes AS s USING (cohort_month)
ORDER BY a.cohort_month, a.month_number;

Что использовать на практике

• Практика. На практике важно решить, что считать «входом в когорту» (первый визит, первая покупка, регистрация) — выбор влияет на интерпретацию и должен быть явно задокументирован в отчёте. Полезно визуализировать когортную таблицу как тепловую карту и дополнительно считать средний retention по всем когортам одного периода, чтобы видеть общий тренд, а не только отдельные строки.
• Пример. Сгруппируйте заказы по месяцу первой покупки и числу месяцев после входа; показывайте выручку или долю активных клиентов в каждой ячейке когортной таблицы.

Подводные камни

• Риск. Частая ошибка — смешивать разные определения когорты в одном отчёте, например для одной кампании когорта по первому визиту, для другой — по первой покупке, без явного указания, что метрики не сопоставимы между собой.