Привет, Хабр!
Сегодня рассмотрим, как на голом SQL построить полноценный когортный анализ: определим дату первой покупки, сгруппируем пользователей по когортам, посчитаем удержание (retention), оценим LTV по месяцам жизни и сделаем RFM‑сегментацию.
Исходные данные
Таблица orders, она же боевой лог покупок:
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
order_date DATE NOT NULL,
amount NUMERIC NOT NULL
);Примерные данные:
| user_id | order_date | amount |
|---------|------------|--------|
| 1 | 2024-01-10 | 200 |
| 1 | 2024-02-05 | 150 |
| 2 | 2024-01-15 | 300 |
| 3 | 2024-02-10 | 120 |
| 3 | 2024-04-10 | 80 |Первая покупка
Определяем, когда юзер впервые «засветился»:
SELECT
user_id,
MIN(order_date) AS first_order_date
FROM orders
GROUP BY user_id;Более гибко — с оконной функцией:
SELECT
user_id,
order_date,
MIN(order_date) OVER (PARTITION BY user_id) AS first_order_date
FROM orders;Так можно получить все строки с сохранением первой покупки.
Построение когорт
Когорта — это группа пользователей, пришедших в один период. Обычно — месяц первой покупки.
SELECT
user_id,
DATE_TRUNC('month', MIN(order_date)) AS cohort_month
FROM orders
GROUP BY user_id;
Соединяем с заказами:
WITH first_orders AS (
SELECT user_id, MIN(order_date) AS first_order_date
FROM orders
GROUP BY user_id
)
SELECT
o.user_id,
o.order_date,
DATE_TRUNC('month', f.first_order_date) AS cohort_month
FROM orders o
JOIN first_orders f ON o.user_id = f.user_id;Теперь у каждого заказа есть когорта.
Удержание по месяцам
Цель: понять, сколько юзеров из когорты продолжают делать заказы через 0, 1, 2... месяцев.
WITH first_orders AS (
SELECT user_id, MIN(order_date) AS first_order_date
FROM orders
GROUP BY user_id
),
orders_with_cohort AS (
SELECT
o.user_id,
DATE_TRUNC('month', o.order_date) AS order_month,
DATE_TRUNC('month', f.first_order_date) AS cohort_month,
EXTRACT(MONTH FROM AGE(o.order_date, f.first_order_date)) AS month_offset
FROM orders o
JOIN first_orders f ON o.user_id = f.user_id
)
SELECT
cohort_month,
month_offset,
COUNT(DISTINCT user_id) AS active_users
FROM orders_with_cohort
GROUP BY cohort_month, month_offset
ORDER BY cohort_month, month_offset;| cohort_month | month_offset | active_users |
|--------------|--------------|---------------|
| 2024-01-01 | 0 | 125 |
| 2024-01-01 | 1 | 97 |
| 2024-01-01 | 2 | 65 |Если хочется доли, а не абсолюты — добавляем COUNT(*) OVER (PARTITION BY cohort_month).
Edge-кейсы
Прыгающий timezone: всегда используйте
DATE_TRUNC('month', order_date::date)и убивайтеTIMESTAMP WITH TIME ZONE, если он не нужен.Нулевая разница в месяцах:
AGEможет вернуть0даже если прошло 29 дней. Убедитесь, что округление устроит вас.Повторные регистрации: если юзер сменил ID или начал с нового email — его история обнулится. В аналитике это естественные потери, но в некоторых B2B системах стоит их учитывать.
LTV по когортам
Lifetime Value — это сколько денег принёс пользователь за всё время. Для когорт:
WITH first_orders AS (
SELECT user_id, MIN(order_date) AS first_order_date
FROM orders
GROUP BY user_id
),
orders_with_cohort AS (
SELECT
o.user_id,
o.amount,
DATE_TRUNC('month', f.first_order_date) AS cohort_month,
EXTRACT(MONTH FROM AGE(o.order_date, f.first_order_date)) AS month_offset
FROM orders o
JOIN first_orders f ON o.user_id = f.user_id
)
SELECT
cohort_month,
month_offset,
ROUND(SUM(amount), 2) AS total_ltv
FROM orders_with_cohort
GROUP BY cohort_month, month_offset
ORDER BY cohort_month, month_offset;Теперь есть LTV в динамике. Можно превратить это в кумулятивную метрику — добавив SUM() OVER (...).
RFM-анализ с сегментацией
Считаем метрики:
WITH rfm_base AS (
SELECT
user_id,
MAX(order_date) AS last_order_date,
COUNT(*) AS frequency,
SUM(amount) AS monetary
FROM orders
GROUP BY user_id
)
SELECT
user_id,
CURRENT_DATE - last_order_date AS recency,
frequency,
monetary
FROM rfm_base;Делим по квантилям:
WITH rfm_ranked AS (
SELECT *,
NTILE(5) OVER (ORDER BY CURRENT_DATE - last_order_date DESC) AS r_score,
NTILE(5) OVER (ORDER BY frequency) AS f_score,
NTILE(5) OVER (ORDER BY monetary) AS m_score
FROM (
SELECT
user_id,
MAX(order_date) AS last_order_date,
COUNT(*) AS frequency,
SUM(amount) AS monetary
FROM orders
GROUP BY user_id
) base
)
SELECT
user_id,
r_score || f_score || m_score AS rfm_segment,
r_score,
f_score,
m_score
FROM rfm_ranked;RFM_segment = "555" — идеальный юзер: недавно покупал, покупает часто и много тратит.
Архитектура
Чтобы всё это не превратилось в хаотичный зоопарк SQL‑запросов, стоит сразу выделить основные CTE в materialized views — это ускорит построение отчётов и упростит интеграцию с BI‑инструментами. Как минимум стоит завести три представления: user_first_order (дата первой покупки по каждому пользователю), orders_with_cohort (все заказы с прикреплённой когортой и смещением по времени) и rfm_scores (результат RFM‑анализ с квантильной сегментацией).
Дальше — классический пайплайн: обновление этих materialized views по расписанию через Airflow или Dagster (например, раз в сутки), визуализация в Metabase или Superset, настройка алертов по основным метрикам. Если retention упал или LTV по какой‑то когорте просел — вы сразу это увидите. Так из набора SQL‑запросов рождается рабочая аналитическая система.
Заключение
Что получили:
Полноценный SQL‑пайплайн когортного анализа
Метрики: Retention, LTV, RFM
Сегментация и групповая аналитика
Разбор edge‑кейсов и как не сесть в лужу
Что дальше? Можно пойти в:
Product Analytics (конверсия, funnel'ы)
Prediction (ML на основе когорт)
Customer scoring (через RFM + external data)
Если вы занимаетесь проектированием систем и хотите избежать типичных ошибок при создании API, рекомендую посетить открытый урок в Otus. На нём вы сможете разобрать, как проектировать REST API с учетом всех технических и практических аспектов. Урок пройдет уже сегодня, 16 апреля, в 20:00. Запись — на странице курса