Анализ удержания когорты в SQL, шаг за шагом

1. Что такое когорта и решение, которое ее определяет

Когорта — это группа клиентов, у которых есть общая отправная точка, и почти всегда этой отправной точкой является момент их прибытия. Сгруппируйте по месяцам прибытия, а затем наблюдайте за каждой группой с течением времени. Причина, по которой это вообще нужно делать, заключается в том, что в одном общем числе удержанных смешиваются люди, присоединившиеся на прошлой неделе, и люди, присоединившиеся три года назад, и это разные группы населения, имеющие разный опыт.

Перед пояснением: в вашей таблице указаны дата первой покупки, дата регистрации и дата первого входа в систему. Скажите, какой из них определяет когорту.

Какой бы из них вы ни смогли защитить на собрании, и вам придется выбрать его перед написанием SQL, потому что впоследствии ответ меняется на каждое число. Первая покупка подходит для удержания вопроса о платежном поведении. Регистрация подойдет, если вы измеряете, конвертирует ли продукт людей. Это не те же самые клиенты и не те же месяцы, и таблица, в которой написано «когорта», не говоря уже об этом, не является выводом. Записать это - это все определение метрики, применяется здесь.

На этой странице используется первая покупка в таблице покупок с тремя столбцами: клиент, дата, сумма. Девятнадцать рядов, двенадцать клиентов.

2. Шаг первый: первый месяц для всех

Один агрегат дает каждому клиенту группу, и это единственное место, где слово MIN появляется.

SELECT customer_id,
       MIN(purchase_date)                              AS first_purchase,
       DATE_TRUNC('month', MIN(purchase_date))::DATE   AS cohort
FROM purchases
GROUP BY 1;

C1  | 2026-01-06 | 2026-01-01
C2  | 2026-01-09 | 2026-01-01
C3  | 2026-01-14 | 2026-01-01
C4  | 2026-01-21 | 2026-01-01
C5  | 2026-01-28 | 2026-01-01
C6  | 2026-02-03 | 2026-02-01
...

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

Подсчет дает знаменатели для всего анализа:

2026-01-01 | 5
2026-02-01 | 4
2026-03-01 | 3

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

3. Шаг второй: сколько месяцев спустя

Соедините таблицу первых месяцев с каждой покупкой и вычтите месяцы.

SELECT p.customer_id,
       f.cohort,
       DATE_TRUNC('month', p.purchase_date)::DATE AS activity_month,
       DATEDIFF('month', f.cohort, DATE_TRUNC('month', p.purchase_date)) AS month_no
FROM purchases p
JOIN first_p f USING (customer_id);

C1 | 2026-01-01 | 2026-01-01 | 0
C1 | 2026-01-01 | 2026-02-01 | 1
C1 | 2026-01-01 | 2026-03-01 | 2
C3 | 2026-01-01 | 2026-01-01 | 0
C3 | 2026-01-01 | 2026-02-01 | 1
C6 | 2026-02-01 | 2026-02-01 | 0
C6 | 2026-02-01 | 2026-03-01 | 1
...

что month_no столбец — вот и вся хитрость. Он преобразует календарное время во время с момента прибытия, поэтому клиента, присоединившегося в январе, и клиента, присоединившегося в марте, можно сравнить в одном возрасте. Без него вы можете сравнивать только календарные месяцы, что смешивает возрасты и является причиной того, что простой ежемесячный подсчет активности никогда не отвечает на вопрос об удержании.

Две детали. Усеките обе части до месяца перед вычитанием, иначе покупка 2-го числа следующего месяца и покупка 30-го числа того же месяца окажутся в разных корзинах с разницей в день. А месяц 0 существует по своей конструкции: все активны в том месяце, в котором они прибыли, поэтому первый столбец когортной таблицы всегда указывает размер когорты.

4. Шаг третий: треугольник

Теперь посчитайте отдельных клиентов на когорту за месяц. Здесь он изложен так, как его читают люди, с номерами месяцев вверху:

cohort     |  m0 |  m1 |  m2
2026-01-01 |   5 |   3 |   2
2026-02-01 |   4 |   2 |   .
2026-03-01 |   3 |   . |   .

Прочтите первую строку: пять клиентов пришли в январе, трое из них купили снова в феврале, двое купили в марте. Сверьте его вручную со списком на третьем этапе, и вы найдете C1, C2 и C3 в первом месяце, а также C1 и C2 во втором месяце.

Пробелы в правом нижнем углу не являются недостающими данными. Февральская когорта существует всего два месяца, поэтому у нее еще нет месяца 2, а у марта нет месяца 1. Это самая важная вещь, которую нужно сделать правильно при представлении одного из них, потому что заполнение этих ячеек нулями превращает «мы еще не знаем» в «никто не вернулся», а затем среднее значение по столбцу падает по причине, не имеющей ничего общего с продуктом.

Запрос один CASE за столбец, что подходит для трех и громоздко для двенадцати. Версия, которая масштабируется, вместо этого возвращает одну строку на ячейку и позволяет инструменту создания отчетов расположить ее:

SELECT cohort, month_no, COUNT(DISTINCT customer_id) AS active
FROM act
GROUP BY 1, 2
ORDER BY 1, 2;

5. Учитывается в ставках

Подсчеты — это то, что вы проверяете; цены - это то, что вы сравниваете. Объединение размеров когорт обратно дает и то, и другое.

cohort     | size | month_no | active | retention_pct
2026-01-01 |    5 |        0 |      5 |         100.0
2026-01-01 |    5 |        1 |      3 |          60.0
2026-01-01 |    5 |        2 |      2 |          40.0
2026-02-01 |    4 |        0 |      4 |         100.0
2026-02-01 |    4 |        1 |      2 |          50.0
2026-03-01 |    3 |        0 |      3 |         100.0

Месяц 0 по определению равен 100% в каждой строке, что является скорее полезной проверкой, чем выводом: если он не равен 100%, объединение теряет строки.

Теперь сравнение, для которого существует таблица. Январь сохранил 60% в первом месяце, а февраль сохранил 50%. Скажите вслух, действительно ли это снижение, прежде чем читать дальше.

Это три клиента против двух в когортах из пяти и четырех человек. Перемещение одного клиента меняет январь на 20 пунктов, а февраль — на 25. На основе этих данных честным выводом является то, что когорты слишком малы для сравнения, и вы говорите об этом, публикуя подсчеты рядом со ставками, поэтому в таблице выше есть и то, и другое. Проценты в крошечных знаменателях — это наиболее явно неправильные цифры в аналитике.

6. Клиенты, а не покупки

Перед пояснением: один январский покупатель покупает в январе дважды. Скажите, что это делает с ячейкой месяца 0, если вы считаете с помощью COUNT(*).

Добавление именно этого клиента в данные и параллельное выполнение обоих подсчетов:

cohort     | month_no | purchases | customers
2026-01-01 |        0 |         7 |         6
2026-01-01 |        1 |         3 |         3
2026-01-01 |        2 |         2 |         2

Семь покупок, шесть клиентов. В когорте шесть человек и COUNT(*) утверждает семь, поэтому месяц 0 получается со значением 116,7%, а все, что ниже него, разделено на неверный знаменатель.

Удержание — это вопрос о людях, поэтому каждый показатель в когортной таблице COUNT(DISTINCT customer_id). Единственное исключение — это когда вы действительно имеете в виду частоту заказов, и тогда столбец следует называть иначе, чем «Удержание».

7. Два определения оставшегося

Это та часть, которая решает, согласны ли две команды, и она невидима в SQL, если вы ее не ищете.

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

cohort     | size | month_no | active | retention_pct
2026-01-01 |    6 |        0 |      6 |         100.0
2026-01-01 |    6 |        1 |      3 |          50.0
2026-01-01 |    6 |        2 |      3 |          50.0

Удержание не упало между месяцем 1 и месяцем 2. Согласно этому определению, его вообще нельзя рассматривать как кривую выживания, потому что клиент, который терпит неудачу и возвращается, учитывается снова. У этого определения есть имя, которое стоит использовать: активен в месяце N.

Другое определение спрашивает, кто проявлял активность каждый месяц с момента прибытия. По тем же шести клиентам:

cohort_size | active in m2 | active every month
          6 |            3 |                  2

Трое против двух, что составляет 50% против 33,3% для той же когорты в том же месяце. Ни один из запросов не является неправильным. Они отвечают на разные вопросы, и разница — один клиент, который взял отпуск на месяц.

Что использовать, зависит от бизнеса. Для подписки, срок действия которой означает отмену, фраза «активна каждый месяц с тех пор» ближе к истине. Для магазина, где люди покупают, когда им что-то нужно, «активен в месяце N» является разумным, и пробел не является убытком. Запишите выбор в заголовок столбца, а не в комментарий, поскольку заголовок столбца — единственная перемещаемая часть.

8. Сохранение доходов

Тот же скелет с SUM(amount) вместо подсчета отвечает на другой и зачастую более полезный вопрос: не сколько вернулось, а сколько.

cohort     | month_no | revenue
2026-01-01 |        0 |     610
2026-01-01 |        1 |     380
2026-01-01 |        2 |     250
2026-02-01 |        0 |     460
2026-02-01 |        1 |     310
2026-03-01 |        0 |     420

Эти шесть ячеек в сумме дают 2430, то есть общую сумму таблицы покупок, поэтому никакие деньги не были потеряны или пересчитаны на пути через объединение. Это сверка является проверкой, которую необходимо сохранить: каждая ячейка таблицы доходов когорты, сложенная вместе, должна равняться исходной сумме.

Если прочитать его рядом со таблицей клиентов, он заслуживает своего места. Январь сохранила 60% клиентов в первый месяц и 62,3% выручки, 380 из 610, поэтому те, кто остался, тратили примерно на том же уровне. Когда эти два процента резко расходятся, получается следующее: сохранение половины ваших клиентов и 90% ваших денег — это совсем другое дело, чем сохранение половины каждого из них.

Представьте на мгновение свой собственный стол для клиентов. Если бы вы построили это сегодня, что бы вы ожидали упасть быстрее: количество или доход? Это ожидание стоит записать, прежде чем запускать его.

Пограничные случаи, нарушающие когортную таблицу

Шесть, которые пройдут.

Нули в будущих ячейках. A LEFT JOIN в полную сетку пар когорт по месяцам поможет заполнить все пробелы нулями, включая месяцы, которые еще не произошли. Фильтровать по cohort + month_no <= the last complete month прежде чем кто-либо усреднит столбец.

Неполный текущий месяц. Самый последний столбец всегда является неполным, поэтому он всегда выглядит как свернувшийся. Либо исключите это, либо назовите это, та же дисциплина, что и неполный месяц в тренде.

Ключ клиента — это не один клиент. Если люди могут создать две учетные записи, вторая создает новую когорту, а первая, похоже, уходит. То есть Разрешение объекта, и это одновременно льстит оттоку и снижает удержание клиентов.

Возвраты и отмены учитываются как активность. Строка возврата — это строка покупки в большинстве схем. Решите, считается ли клиент, который только что-то вернул, удержанным, и отфильтруйте его до этапа когорты, а не после него.

Заполненная история. Если старые данные были перенесены с указанием даты загрузки, а не даты события, каждый перенесенный клиент присоединяется к одной и той же фальшивой когорте, которая выглядит как одна огромная группа со странным сроком хранения. Прежде чем верить чему-либо, проверьте размеры когорт на предмет подозрительного всплеска.

Крошечные когорты. Когорта из четырех человек не может дать процент, достойный сравнения. Опубликуйте подсчеты и установите нижний предел, ниже которого вы будете показывать только подсчеты, тот же аргумент, что и нижний предел для малых групп в GROUP BY и HAVING.