Когортный анализ — это сравнение групп клиентов (когорт), объединенных месяцем первой покупки: как часто они возвращаются и сколько приносят в следующие периоды. LTV (lifetime value) — накопленная выручка или маржа на одного клиента когорты за выбранный горизонт. RFM — другой срез: сегментация отдельных клиентов по давности последней покупки (Recency), числу покупок (Frequency) и сумме (Monetary).
Содержание
Все запросы — на одной учебной таблице orders в PostgreSQL (выводы проверены прогоном в PostgreSQL 18). Замены функций дат для SQLite, MySQL и SQL Server — в таблице в конце.
Учебная таблица orders
Шесть клиентов, 11 заказов; дата анализа — 1 апреля 2026.
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
order_date DATE NOT NULL,
amount NUMERIC(10, 2) NOT NULL CHECK (amount > 0)
);
INSERT INTO orders (order_id, user_id, order_date, amount) VALUES
(1, 1, '2026-01-10', 200),
(2, 1, '2026-02-05', 150),
(3, 1, '2026-03-20', 100),
(4, 2, '2026-01-15', 300),
(5, 3, '2026-01-28', 120),
(6, 3, '2026-03-02', 80),
(7, 4, '2026-02-03', 500),
(8, 4, '2026-02-25', 90),
(9, 4, '2026-03-15', 60),
(10, 5, '2026-02-18', 40),
(11, 6, '2026-03-05', 250);CHECK (amount > 0) не пускает в таблицу возвраты; в реальных данных их вычитают явно до расчета LTV.
Шаг 1. Когорта и смещение в месяцах
Когорта клиента — месяц его первой покупки. Для каждого заказа нужны cohort_month и month_offset — число календарных месяцев от месяца первой покупки. Оформим представлением.
CREATE VIEW orders_with_cohort AS
WITH first_order AS (
SELECT user_id, MIN(order_date) AS first_date
FROM orders
GROUP BY user_id
)
SELECT o.user_id,
o.amount,
DATE_TRUNC('month', f.first_date)::date AS cohort_month,
((EXTRACT(YEAR FROM o.order_date) - EXTRACT(YEAR FROM f.first_date)) * 12
+ (EXTRACT(MONTH FROM o.order_date) - EXTRACT(MONTH FROM f.first_date)))::int AS month_offset
FROM orders o
JOIN first_order f ON f.user_id = o.user_id;Смещение считается по календарным месяцам: заказ 28 января и заказ 2 марта дают month_offset = 2, хотя прошло 33 дня. Функция AGE считает иначе: вычитает даты поле за полем и возвращает «символьный» интервал из месяцев и дней:
SELECT AGE(DATE '2026-03-02', DATE '2026-01-28') AS age_interval; age_interval
--------------
1 mon 5 days
Здесь 1 месяц, а не 2. Дни тоже не «фактические»: при заеме месяца PostgreSQL берет длину месяца более ранней даты (январь, 31 день), поэтому 5 дней, хотя от 28 февраля до 2 марта всего 2 дня. Для когортной таблицы нужна календарная разница, иначе мартовские заказы клиентов из начала и конца января попадут в разные столбцы.
Шаг 2. Retention по когортам
Retention месяца N здесь — доля клиентов когорты, которые сделали хотя бы одну покупку в месяце N после первого. Это не «доля оставшихся до месяца N»: клиент может пропустить месяц и вернуться.
WITH cohort_size AS (
SELECT cohort_month, COUNT(DISTINCT user_id) AS users
FROM orders_with_cohort
WHERE month_offset = 0
GROUP BY cohort_month
)
SELECT a.cohort_month,
a.month_offset,
COUNT(DISTINCT a.user_id) AS active_users,
ROUND(100.0 * COUNT(DISTINCT a.user_id) / c.users, 1) AS retention_pct
FROM orders_with_cohort a
JOIN cohort_size c ON c.cohort_month = a.cohort_month
GROUP BY a.cohort_month, a.month_offset, c.users
ORDER BY a.cohort_month, a.month_offset; cohort_month | month_offset | active_users | retention_pct
--------------+--------------+--------------+---------------
2026-01-01 | 0 | 3 | 100.0
2026-01-01 | 1 | 1 | 33.3
2026-01-01 | 2 | 2 | 66.7
2026-02-01 | 0 | 2 | 100.0
2026-02-01 | 1 | 1 | 50.0
2026-03-01 | 0 | 1 | 100.0
У январской когорты месяц 2 выше месяца 1: клиент 3 пропустил февраль и вернулся в марте. У мартовской когорты только столбец 0: пустая ячейка здесь — «месяц еще не наступил», а не «0% удержания».
Частая ошибка: размер когорты через COUNT(*)
Размер когорты по числу заказов, а не клиентов:
SELECT cohort_month, COUNT(*) AS cohort_size
FROM orders_with_cohort
WHERE month_offset = 0
GROUP BY cohort_month
ORDER BY cohort_month; cohort_month | cohort_size
--------------+-------------
2026-01-01 | 3
2026-02-01 | 3
2026-03-01 | 1
В февральской когорте два клиента, но клиент 4 купил в феврале дважды — знаменатель стал 3, retention месяца 1 занизится с 50% до 33,3%. Исправление — COUNT(DISTINCT user_id), как выше: февраль дает 2.
Шаг 3. LTV по когортам
Здесь LTV — кумулятивная выручка когорты к месяцу N, деленная на число клиентов когорты. Это упрощенное определение с двумя границами:
- считается выручка, а не маржа; для бюджета на привлечение обычно нужна маржа (минус себестоимость, скидки, возвраты);
- горизонт равен наблюдаемому периоду: это фактический LTV за N месяцев, а не прогноз «за всю жизнь», для которого нужна модель оттока.
WITH cohort_size AS (
SELECT cohort_month, COUNT(DISTINCT user_id) AS users
FROM orders_with_cohort
WHERE month_offset = 0
GROUP BY cohort_month
),
revenue AS (
SELECT cohort_month, month_offset, SUM(amount) AS revenue
FROM orders_with_cohort
GROUP BY cohort_month, month_offset
)
SELECT r.cohort_month,
r.month_offset,
r.revenue,
ROUND(SUM(r.revenue) OVER (PARTITION BY r.cohort_month
ORDER BY r.month_offset) / c.users, 2) AS cum_ltv
FROM revenue r
JOIN cohort_size c ON c.cohort_month = r.cohort_month
ORDER BY r.cohort_month, r.month_offset; cohort_month | month_offset | revenue | cum_ltv
--------------+--------------+---------+---------
2026-01-01 | 0 | 620.00 | 206.67
2026-01-01 | 1 | 150.00 | 256.67
2026-01-01 | 2 | 180.00 | 316.67
2026-02-01 | 0 | 630.00 | 315.00
2026-02-01 | 1 | 60.00 | 345.00
2026-03-01 | 0 | 250.00 | 250.00
Оконный SUM накапливает выручку внутри когорты. Сравнивать когорты можно только на одинаковом горизонте: к месяцу 1 январская дает 256.67, февральская — 345.00; итоговые 316.67 и 345.00 несравнимы. Месяц без покупок не даст строки — для отчета пропуски достраивают через generate_series.
Шаг 4. RFM-сегментация
Три показателя на клиента на дату анализа: давность последней покупки в днях, число заказов, сумма. Вычитание дат в PostgreSQL дает целое число дней.
CREATE VIEW rfm AS
SELECT user_id,
DATE '2026-04-01' - MAX(order_date) AS recency_days,
COUNT(*) AS frequency,
SUM(amount) AS monetary
FROM orders
WHERE order_date < DATE '2026-04-01'
GROUP BY user_id;Баллы — от 1 до 3 (в бизнесе чаще 1-5, на шести клиентах это лишнее). Первый вариант — квантили через NTILE(3):
SELECT user_id, recency_days, frequency, monetary,
NTILE(3) OVER (ORDER BY recency_days DESC) AS r,
NTILE(3) OVER (ORDER BY frequency) AS f,
NTILE(3) OVER (ORDER BY monetary) AS m
FROM rfm
ORDER BY user_id; user_id | recency_days | frequency | monetary | r | f | m
---------+--------------+-----------+----------+---+---+---
1 | 12 | 3 | 450.00 | 3 | 3 | 3
2 | 76 | 1 | 300.00 | 1 | 2 | 2
3 | 30 | 2 | 200.00 | 2 | 2 | 1
4 | 17 | 3 | 650.00 | 3 | 3 | 3
5 | 42 | 1 | 40.00 | 1 | 1 | 1
6 | 27 | 1 | 250.00 | 2 | 1 | 2
Проблема в столбце f: у клиентов 2, 5 и 6 по одному заказу, но клиент 2 получил 2, остальные — 1. NTILE делит строки на равные по числу группы и разрывает одинаковые значения, а порядок равных СУБД не гарантирует.
Исправление для давности и частоты — пороги, заданные бизнесом:
SELECT user_id, r, f, m, CONCAT(r, f, m) AS rfm_segment
FROM (
SELECT user_id,
CASE WHEN recency_days <= 30 THEN 3
WHEN recency_days <= 60 THEN 2
ELSE 1 END AS r,
CASE WHEN frequency >= 3 THEN 3
WHEN frequency = 2 THEN 2
ELSE 1 END AS f,
NTILE(3) OVER (ORDER BY monetary) AS m
FROM rfm
) scored
ORDER BY rfm_segment DESC, user_id; user_id | r | f | m | rfm_segment
---------+---+---+---+-------------
1 | 3 | 3 | 3 | 333
4 | 3 | 3 | 3 | 333
3 | 3 | 2 | 1 | 321
6 | 3 | 1 | 2 | 312
5 | 2 | 1 | 1 | 211
2 | 1 | 1 | 2 | 112
Сегмент 333 — недавние, частые и крупные клиенты; клиент 2 (112) купил давно, но на заметную сумму — кандидат на возвращающую рассылку. Пороги 30/60 дней и 2/3 заказа подобраны под учебные данные, в бизнесе их выбирают по циклу покупки. Для суммы NTILE допустим: одинаковых сумм нет.
Функции дат в других диалектах
| Задача | PostgreSQL | SQLite | MySQL 8 | SQL Server |
|---|---|---|---|---|
| Начало месяца | DATE_TRUNC('month', d) |
strftime('%Y-%m-01', d) |
DATE_FORMAT(d, '%Y-%m-01') |
DATETRUNC(month, d) (2022+) |
| Календарная разница в месяцах | через EXTRACT (как выше) |
strftime('%Y')*12 + strftime('%m'), разность |
через YEAR/MONTH |
DATEDIFF(month, a, b) — считает пересеченные границы месяцев |
| Разница в днях | b - a |
julianday(b) - julianday(a) |
DATEDIFF(b, a) |
DATEDIFF(day, a, b) |
NTILE, оконный SUM |
есть | с 3.25 | с 8.0 | есть |
В SQLite strftime возвращает текст: в арифметике он сам приводится к числу, но в сравнении текст всегда больше числа (strftime('%m', d) > 9 истинно и для марта), поэтому надежнее CAST(... AS INTEGER); julianday дает дробное число (33.0). А NUMERIC(10, 2) не дает фиксированной точки — деньги там храните целыми копейками. В MySQL TIMESTAMPDIFF(MONTH, a, b) считает полные месяцы, как AGE.
Выводы
- Когорта задается месяцем первой покупки, смещение для когортной таблицы — календарными месяцами, а не
AGE. - Размер когорты считается
COUNT(DISTINCT user_id):COUNT(*)считает заказы и занижает retention. - LTV в примере — фактическая кумулятивная выручка на клиента за наблюдаемый горизонт; сравнивать когорты можно только на одинаковом смещении.
NTILEразрывает одинаковые значения по разным баллам; для частоты и давности надежнее пороги по циклу покупки.- Функции дат различаются по СУБД, логика запросов переносится.
Где применяется / связь с практикой
Освойте тему на практике
Когорты, retention, LTV и RFM — базовый набор продуктового аналитика: по ним оценивают онбординг, окупаемость каналов и адресность рассылок. Системно эти темы разбирают на курсе «Продуктовая аналитика», а короткие практические разборы бывают на открытых уроках Otus.
FAQ
Когорты строить по месяцу регистрации или первой покупки?
По регистрации — чтобы видеть конверсию в покупку, по первой покупке — чтобы видеть повторные покупки. В одной таблице их не смешивают.
Как быть с часовыми поясами?
Если дата хранится как timestamptz, месяц заказа зависит от пояса сессии. Приведите время к поясу бизнеса (AT TIME ZONE) до DATE_TRUNC.
Что делать, если клиент завел второй аккаунт?
Для SQL это два клиента. Склеивайте аккаунты до расчета по устойчивому идентификатору, иначе retention занижается.
