Как написать запрос для когортного анализа удержания пользователей по месяцу первой покупки?

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

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

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

шаг 1 — CTE с MIN(order_date) по клиенту для определения когорты; шаг 2 — присоединение этой когорты к каждой покупке и вычисление разницы в месяцах между покупкой и датой когорты (например, через DATE_PART или DATE_DIFF в зависимости от диалекта); шаг 3 — GROUP BY cohort_month, month_number с COUNT(DISTINCT customer_id).

Пример кода

question-44.sqlКопировать

WITH cohorts AS (
  SELECT customer_id, DATE_TRUNC('month', MIN(order_date)) AS cohort_month
  FROM orders
  GROUP BY customer_id
), activity AS (
  SELECT DISTINCT o.customer_id, c.cohort_month,
         DATE_PART('year', o.order_date) * 12 + DATE_PART('month', o.order_date)
         - (DATE_PART('year', c.cohort_month) * 12 + DATE_PART('month', c.cohort_month)) AS month_number
  FROM orders AS o
  JOIN cohorts AS c ON c.customer_id = o.customer_id
), cohort_sizes AS (
  SELECT cohort_month, COUNT(*) AS cohort_size
  FROM cohorts
  GROUP BY cohort_month
), retention AS (
  SELECT cohort_month, month_number, COUNT(*) AS active_customers
  FROM activity
  GROUP BY cohort_month, month_number
)
SELECT r.cohort_month, r.month_number, r.active_customers,
       ROUND(100.0 * r.active_customers / NULLIF(s.cohort_size, 0), 1) AS retention_pct
FROM retention AS r
JOIN cohort_sizes AS s USING (cohort_month)
ORDER BY r.cohort_month, r.month_number;

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

• удержание обычно представляют как таблицу «когорта × номер месяца» с процентом от исходного размера когорты (месяц 0 = 100%). Важно явно решить, что считается «активностью» — любая покупка, определённое действие, вход в приложение — и зафиксировать это определение, так как оно сильно влияет на итоговые цифры. Проверка: для month_number = 0 количество активных клиентов должно равняться полному размеру когорты.

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

• считают просто количество покупок, а не количество уникальных клиентов в месяце, что задваивает удержание для клиентов с несколькими покупками в одном месяце; не учитывают, что последние когорты (например, когорта текущего месяца) ещё физически не могли «дожить» до месяца 3, 6 и т.д. — их нужно явно исключать из сравнения, а не трактовать как «0% удержания».