Когортный анализ, LTV и RFM в SQL

Исходные данные

Работать будем с таблицей orders — реальной историей всех покупок. 

Предварительные данные 

| 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     |

Первая покупка

Определяем, когда пользователь в первый раз проявил активность 

Или другой вариант: 

Так можно получить все данные с сохранением первых покупок 

Построение когорт

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

Соединяем с заказами:

Отлично! Теперь у нас на каждый заказ есть своя когорта

Удержание по месяцам

Сейчас переходим к самому интересному — будем смотреть, сколько пользователей из каждой когорты продолжают покупать со временем. Конкретно нас интересует: какой процент клиентов возвращается через месяц, два, три и так далее после первой покупки. Это и есть классический retention-анализ, который показывает «живучесть» клиентской базы.

Если нужны не просто количества, а именно доли (проценты) — используем COUNT(*) OVER (PARTITION BY cohort_month).

Edge-кейсы

1. Проблемы с часовыми поясами
   Всегда обрезайте даты до месяца через DATE_TRUNC(‘month’, order_date::date). TIMESTAMP WITH TIME ZONE — опасная штука, если вам не нужна точность до секунд с учётом поясов.
2. Нюансы расчёта месяцев
   Функция AGE иногда возвращает 0 месяцев для периодов до 29 дней. Проверьте, подходит ли вам такое округление — возможно, нужен более точный расчёт.
3. Учёт повторных регистраций
   Когда пользователь заводит новый аккаунт (с другим ID или email), его история покупок начинается заново. Стоит это учитывать 

LTV по когортам

LTV (пожизненная ценность клиента) показывает, сколько денег в среднем приносит один пользователь из когорты за весь период взаимодействия с бизнесом. Для когорт: 

Теперь, когда у нас есть динамика LTV по месяцам, можно посчитать кумулятивное значение — просто добавьте оконную функцию: SUM() OVER (…).

RFM-анализ с сегментацией

Переходим к RFM-анализу

Считаем ключевые показатели: 

Делим по квантилям:

RFM_segment = «555» — идеальный пользователь! : недавно совершал покупку, приносит много денег и покупки совершает регулярно 

Архитектура

Чтобы ваш когортный анализ не превратился в беспорядочную коллекцию SQL-запросов, нужно продумать архитектуру. Начните с создания материализованных представлений для ключевых данных:

1. user_first_order — хранит дату первой покупки каждого клиента
2. orders_with_cohort — все заказы с указанием когорты и временного смещения
3. rfm_scores — результаты RFM-анализа с готовой сегментацией

Дальше выстраиваем стандартный pipeline:

• Автоматическое обновление представлений (например, раз в день через Airflow/Dagster)
• Визуализация в Metabase/Superset
• Настройка алертов по ключевым показателям

Такой подход превращает разрозненные запросы в полноценную систему мониторинга, где вы сразу заметите, если:

• Ухудшился retention
• Снизился LTV в отдельных когортах
• Изменилось качество клиентской базы

Вот что у нас получилось в результате:

1. Готовый SQL-пайплайн для когортного анализа
2. Все ключевые метрики: Retention, LTV и RFM-сегментация
3. Возможность глубокой аналитики по разным группам пользователей
4. Решение для неочевидных кейсов и подводных камней

Куда двигаться дальше?

• Продуктовая аналитика: анализ воронок и конверсий
• Прогнозная аналитика: машинное обучение на основе когорт
• Скоринг клиентов: расширение RFM внешними данными