RFM в SQL: как задать границы сегментов без случайных ничьих

Маркетолог просит список постоянных покупателей для отдельной кампании. Выражение «постоянный» звучит понятно, пока двое аналитиков не собрали разные списки: один взял всех с двумя заказами, другой — всех, кто покупал за последний месяц. RFM помогает записать правило точнее. Три числа на клиента описывают давность последней покупки, частоту заказов и общую сумму.

Сначала выбирают дату среза. В примере это 1 июля 2025 года, и покупки после неё не попадают в расчёт. Давность, или Recency, измеряем в днях от последней оплаченной покупки до среза. Frequency — число разных заказов. Monetary — сумма по ним. Если исходная таблица содержит позиции, перед этим шагом нужно собрать один итог на заказ; иначе COUNT(*) посчитает товары вместо покупок.

Порог превращает описание в сегмент

Здесь «постоянный» означает: покупка в последние 30 дней, минимум два заказа и сумма от 100 рублей. Это учебное бизнес-правило, а не универсальная граница. Остальные недавно покупавшие попадают в recent, более давние — в dormant. Порядок веток CASE важен: проверка строгого сегмента идёт первой, иначе все недавние клиенты будут перехвачены широкой второй веткой.

CREATE TABLE purchases (customer_id INTEGER, order_id INTEGER, paid_on TEXT, amount INTEGER);
INSERT INTO purchases VALUES
 (7,101,'2025-06-01',60),(7,102,'2025-06-20',50),
 (8,103,'2025-01-01',200),(9,104,'2025-06-25',20);
WITH customer AS (
  SELECT customer_id,
         CAST(julianday('2025-07-01') - julianday(MAX(paid_on)) AS INTEGER) AS recency_days,
         COUNT(DISTINCT order_id) AS frequency,
         SUM(amount) AS monetary
  FROM purchases
  WHERE paid_on < '2025-07-01'
  GROUP BY customer_id
)
SELECT customer_id, recency_days, frequency, monetary,
       CASE
         WHEN recency_days <= 30 AND frequency >= 2 AND monetary >= 100 THEN 'loyal'
         WHEN recency_days <= 30 THEN 'recent'
         ELSE 'dormant'
       END AS segment
FROM customer ORDER BY customer_id;

Выход для клиента 7: 11 дней, 2 заказа, 110, loyal. Клиент 8: 181 день, 1 заказ, 200, dormant. Клиент 9: 6 дней, 1 заказ, 20, recent. Большая историческая сумма сама по себе не делает клиента активным: RFM сохраняет три измерения, а сегмент сводит их по выбранному правилу.

Перед использованием проверьте условия на границах. recency_days <= 30 включает ровно тридцатый день; frequency >= 2 включает ровно два заказа. Если дата покупки хранится со временем и часовым поясом, приводите события и дату среза к одной календарной зоне. Разница около полуночи может перебросить клиента через порог.

Кого не видно в результате

Запрос начинается с покупок, поэтому клиент без оплаченных заказов вообще не появится. Для кампании по всей клиентской базе надо начать с таблицы клиентов и присоединить агрегат покупок слева. У таких людей Recency будет неизвестна, Frequency и Monetary можно определить как ноль, а сегмент — явно назвать never_purchased. Смешивать их с давно не покупавшими нельзя: первая группа ещё ни разу не совершала покупку.

Если бизнес предпочитает сегменты по квантилям, сначала договоритесь, как делить людей с одинаковым значением у границы: NTILE может развести равные суммы по разным корзинам ради равного размера. Фиксированные пороги легче воспроизводить во времени, но требуют пересмотра при изменении цен и частоты покупок. Выбор зависит от того, будут ли сегменты сравниваться между месяцами.

Денежная сумма требует отдельного договора. Возврат за старый заказ уменьшает Monetary в периоде или отражается как новое отрицательное событие? Купон уменьшает фактический платёж, но не обязательно ценность клиента для кампании. Здесь amount уже считается согласованной оплаченной суммой заказа. Если в таблице есть платежи и возвраты отдельными строками, сначала соберите итог на заказ, иначе Frequency и Monetary начнут отвечать на разные единицы учёта.

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

Полезная ручная проверка — три клиента около каждой границы: последняя покупка ровно 30 и 31 день назад, один и два заказа, сумма 99 и 100. Если им присваиваются ожидаемые сегменты, условия <= и >= записаны в соответствии с бизнес-правилом. Именно пограничные случаи показывают ошибку раньше, чем средние по сегментам.

В «Продвинутом SQL» RFM стоит рядом с когортами, retention и сложными соединениями. Студент пишет расчёты на SQLite и проверяет их на случаях с пропусками и дублями, а затем собирает витрину интернет-магазина. Откройте программу, чтобы связать сегменты с исходными заказами и дальнейшим отчётом.

Для отдельного вопроса о возвращении покупателей есть статья как различать заказы и повторные покупки.