Расчет RFM-модели в чистом SQL на примере магазина котиков: коротко

Привет, Хабр!

Сегодня мы рассмотрим, как реализовать RFM‑модель в чистом SQL на примере магазина котиков.

RFM — это:

• Recency: когда последний раз юзер что‑то купил;

• Frequency: сколько покупок в целом;

• Monetary: сколько потрачено денег.

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

Подготовка схемы

Представим simple таблицы:

-- список покупателей CREATE TABLE customers (   customer_id   INT PRIMARY KEY,   name          TEXT );  -- покупки CREATE TABLE orders (   order_id      INT PRIMARY KEY,   customer_id   INT REFERENCES customers(customer_id),   order_date    DATE,   amount        NUMERIC(10,2) );

И пара строк для тестирования:

INSERT INTO customers VALUES  (1,'Мурзик'), (2,'Барсик'), (3,'Кеша');  INSERT INTO orders VALUES  (10, 1,'2025-07-20', 300),  (11, 1,'2025-07-10', 150),  (12, 2,'2025-01-01', 200),  (13, 3,'2024-12-01', 100),  (14, 3,'2024-07-01', 50);

Готовим базу: собираем Recency, Frequency, Monetary

В RFM‑модели три ключевых признака пользователя: когда он покупал в последний раз (recency), как часто покупает (frequency) и сколько денег приносит (monetary). Все три — это характеристики поведения, которые можно извлечь из сырых заказов. Суть в том, чтобы превратить простой лог покупок в сжатую, но выразительную цифровую модель клиента.

Создаём CTE, где сгруппируем заказы по каждому пользователю и посчитаем нужные значения. Заодно используем LEFT JOIN, чтобы не потерять пользователей с нулём заказов (да, и такие бывают — например, кто‑то только зарегистрировался или просто оформил заказ, но не оплатил).

Вот так это выглядит:

WITH rfm AS (   SELECT     c.customer_id,     MAX(o.order_date)::date AS last_order,     COUNT(o.order_id) AS freq,     COALESCE(SUM(o.amount), 0) AS monetary   FROM customers c   LEFT JOIN orders o ON c.customer_id = o.customer_id   GROUP BY c.customer_id ) SELECT * FROM rfm;

Bзвлекаем три ключевые метрики поведения клиента: MAX(o.order_date) определяет дату последней покупки, COUNT(o.order_id) показывает, сколько заказов он совершил, а SUM(o.amount) — сколько денег потратил (с COALESCE на случай отсутствия покупок). Используем LEFT JOIN, чтобы сохранить в выборке даже тех клиентов, у которых не было заказов вовсе — это важно для полной картины. В результате получаем базовую таблицу, где каждый клиент представлен как набором сырых чисел, так и потенциальной моделью поведения, пригодной для дальнейшей сегментации.

Расчёт Recency в днях

Просто дата последней покупки — это не метрика. С ней нельзя делать арифметику. Мы хотим понимать, сколько дней назад был последний заказ. Это и будет recency, от которого отталкивается большинство стратегий: пушить новых, возвращать уснувших, удерживать лояльных и т. д.

Добавляем колонку recency_days как разницу между CURRENT_DATE и MAX(order_date).

WITH rfm AS (   SELECT     c.customer_id,     MAX(o.order_date)::date AS last_order,     CURRENT_DATE - MAX(o.order_date)::date AS recency_days,     COUNT(o.order_id) AS freq,     COALESCE(SUM(o.amount), 0) AS monetary   FROM customers c   LEFT JOIN orders o ON c.customer_id = o.customer_id   GROUP BY c.customer_id ) SELECT * FROM rfm;

Используем CURRENT_DATE, а не NOW(), поскольку он возвращает только дату без времени — это делает расчёты более стабильными и предсказуемыми, особенно при вычитании дат. Если у клиента нет заказов, MAX(order_date) даст NULL, а значит recency_days тоже станет NULL — с этим мы разберёмся позже при сегментации. Зато теперь появляется выразительная и удобная для анализа метрика: сколько дней прошло с последней покупки.

Нормируем данные: присваиваем RFM-баллы

Абсолютные значения — это хорошо, но мы хотим сравнивать клиентов между собой. Один потратил 5000, другой 10 000 — как их сравнивать? Мы можем использовать квантильную нормализацию: разбиваем клиентов на группы, например, на 3 (терцилями), и каждому даём балл от 1 до 3.

Используем NTILE(3) — встроенную функцию оконной агрегации, которая делит отсортированный список на равные части и присваивает номер группы.

WITH base AS (   SELECT     c.customer_id,     CURRENT_DATE - MAX(o.order_date)::date AS recency_days,     COUNT(o.order_id) AS freq,     COALESCE(SUM(o.amount), 0) AS monetary   FROM customers c   LEFT JOIN orders o USING(customer_id)   GROUP BY c.customer_id ), scored AS (   SELECT     customer_id,     recency_days, freq, monetary,     NTILE(3) OVER (ORDER BY recency_days ASC)  AS r_score,     NTILE(3) OVER (ORDER BY freq DESC)         AS f_score,     NTILE(3) OVER (ORDER BY monetary DESC)     AS m_score   FROM base ) SELECT * FROM scored;

Нормализуем метрики через NTILE(3): для recency_days сортируем по возрастанию, чтобы клиенты с недавними покупками получили высокий балл (то есть ближе к 1), а для freq и monetary — по убыванию, ведь большее значение считается лучше.

Формируем итоговый RFM-код и сегмент

Когда у нас есть отдельные баллы R, F, M — удобно их объединить в строку: например, 111, 123, 321. Такой RFM‑код — краткая характеристика клиента. На этом этапе можно строить любые сегменты, под которые затачивать акции, рекомендации, ретаргетинг и всё, что угодно.

Создаём итоговый rfm_code и назначаем сегмент.

WITH base AS (...), scored AS (...), combined AS (   SELECT *,     r_score * 100 + f_score * 10 + m_score AS rfm_code,     CONCAT(r_score, f_score, m_score) AS rfm_str   FROM scored ) SELECT   customer_id, recency_days, freq, monetary,   r_score, f_score, m_score, rfm_str,   CASE     WHEN r_score = 1 AND f_score = 1 AND m_score = 1 THEN 'Champions'     WHEN r_score <= 2 AND f_score <= 2 AND m_score <= 2 THEN 'Potential'     WHEN r_score = 3 THEN 'At Risk'     ELSE 'Others'   END AS segment FROM combined ORDER BY customer_id;

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

Мтериализуем и оптимизируем

Если запускать эти запросы каждый раз — будет не очень. На больших объёмах NTILE, оконные функции и агрегации не самые лёгкие. Мы хотим, чтобы BI‑аналитик мог в любой момент открыть дашборд и мгновенно увидеть RFM‑сегменты. Поэтому сохраняем всё как materialized view, который можно обновлять раз в день или по крону.

CREATE MATERIALIZED VIEW rfm_summary AS WITH base AS (...), scored AS (...), combined AS (   SELECT *,     r_score * 100 + f_score * 10 + m_score AS rfm_code,     CONCAT(r_score, f_score, m_score) AS rfm_str   FROM scored ) SELECT   customer_id, recency_days, freq, monetary,   r_score, f_score, m_score, rfm_str,   CASE     WHEN r_score = 1 AND f_score = 1 AND m_score = 1 THEN 'Champions'     WHEN r_score <= 2 AND f_score <= 2 AND m_score <= 2 THEN 'Potential'     WHEN r_score = 3 THEN 'At Risk'     ELSE 'Others'   END AS segment FROM combined;

И не забываем:

REFRESH MATERIALIZED VIEW rfm_summary;

Можно настроить через pg_cron или планировщик вне базы. Индексируем segment, rfm_code, customer_id для ускорения BI‑запросов.

Итоги

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

Что можно докрутить в нашем примере? Например, добавить временные окна — последние 3/6 месяцев, если бизнес сезонный. Вместо NTILE(3) — использовать бизнесовые пороги custom ranges, завязанные на абсолютные значения. Можно посчитать retention после применения сегментации, использовать веса для каждого из R/F/M (если, например, деньги важнее частоты), связать с рекомендациями или фидить это всё в кластеризацию или ML‑модель. Возможностей — масса. Всё зависит от того, как глубоко вы хотите погрузиться и что именно для вас важно.

Если вы работаете с требованиями, моделируете процессы или взаимодействуете с командами разработки, обратите внимание на серию открытых уроков курса «Системный аналитик. Advanced». Это возможность последовательно разобрать прикладные аспекты работы аналитика на практике:

— 30 июля в 20:00 — Data Governance: бюрократия или суперспособность? Обсудим, как сделать управление данными не формальностью, а рабочим инструментом аналитика.

— 31 июля в 18:00 — Пользовательские сценарии (Use Cases). Рассмотрим, как превращать бизнес‑требования заказчика в понятные задачи для команды разработки.

— 13 августа в 20:00 — События в BPMN 2.0. Подробно разберёмся, как с ними работать и как не допустить типичных ошибок в диаграммах.

— 20 августа в 20:00 — Разработка тест‑кейсов на основе требований. Поговорим о том, как формировать проверяемые сценарии уже на этапе описания требований.

Также вы можете пройти вступительное тестирование — оно поможет понять, насколько вам подойдёт продвинутый уровень программы и на какие темы стоит обратить внимание в первую очередь.