Когорты

Отто · операционный директор доставки «Гонец»

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

В прошлом уроке мы мерили время между двумя событиями одного заказа. Сегодня время мерим иначе: не от заказа, а от старта человека. Когда курьер пришёл на работу, когда клиент получил первый заказ. Людей, которые стартовали в одно время, сравнивают группами. Такие группы называют когортами.

Выпускники одного года

На встрече выпускников никто не сравнивает класс 2010 года с классом 2024-го по числу свадеб. У первых было пятнадцать лет, у вторых пара. Сравнивают честно: «сколько из класса через пять лет после выпуска работает по профессии».

Когорта в аналитике устроена так же. Это все, у кого стартовое событие пришлось на один период: наняты в марте, получили первый заказ на неделе 6 апреля. Дальше у каждой когорты смотрят, что с ней стало спустя одинаковое время.

Рецепт всегда из двух шагов:

1. Для каждого человека найти дату старта и округлить её до периода. Это метка когорты.
2. Сгруппировать по метке и посчитать, что нужно.

Курьеры по месяцу найма

У курьеров старт лежит прямо в таблице: hired_on. Метку когорты даёт date_trunc('month', …) из урока про DATE_TRUNC. Кто ещё работает, видно по fired_on: пустое значение значит, что курьер не уволен.

SELECT date_trunc('month', hired_on)::date AS cohort,
       count(*) AS hired,
       count(*) FILTER (WHERE fired_on IS NULL) AS still_working
FROM couriers
GROUP BY 1
ORDER BY 1;

 cohort     | hired | still_working
------------+-------+---------------
 2025-06-01 |    10 |             7
 2025-07-01 |     7 |             6
 ...
 2026-03-01 |    22 |            16
 2026-04-01 |    15 |            14
 2026-05-01 |    21 |            17
 2026-06-01 |    21 |            20

date_trunc от даты возвращает timestamptz с полуночью, поэтому ::date в конце: метке когорты время суток не нужно. Весной наняли 58 курьеров, из них 47 работают до сих пор. Из мартовского набора ушли шестеро из 22.

Клиенты по неделе первой доставки

У клиентов старта в отдельном столбце нет. Его надо вычислить: первая доставка это самый ранний доставленный заказ клиента. Здесь пригодится CTE: сначала шаг «старт каждого клиента», потом шаг «когорты».

WITH first AS (
  SELECT customer_id,
         date_trunc('week', min(created_at))::date AS cohort_week
  FROM orders
  WHERE status = 'delivered'
  GROUP BY customer_id
)
SELECT f.cohort_week,
       count(DISTINCT f.customer_id) AS customers,
       count(o.id) AS orders
FROM first f
JOIN orders o ON o.customer_id = f.customer_id AND o.status = 'delivered'
GROUP BY f.cohort_week
ORDER BY f.cohort_week;

 cohort_week | customers | orders
-------------+-----------+--------
 2026-02-23  |       106 |    957
 2026-03-02  |       737 |   4514
 2026-03-09  |       683 |   3395
 ...
 2026-06-22  |       734 |   1066
 2026-06-29  |       218 |    262

date_trunc('week', …) округляет до понедельника. 1 марта, начало данных, это воскресенье, поэтому первая когорта помечена 23 февраля. Условие status = 'delivered' стоит и в CTE, и в соединении. У 426 клиентов первый заказ отменили: без фильтра в CTE их стартом стал бы отменённый заказ, а не первая доставка.

Мартовская когорта 2 марта сделала 4514 заказов, по 6,1 на клиента. Апрельская когорта 13 апреля уже по 3,4. Маркетинг прав? Не спеши.