RFM-сегментация: кто покупает недавно, часто и много

Мира · маркетолог магазина настолок «Кубик»

У нас одна рассылка на всех: и тем, кто вчера купил три игры, и тем, кто был один раз год назад. Хочу хотя бы пять групп и отдельное письмо каждой. Кого сколько, и кто приносит деньги?

Мы уже возвращались к «Кубику» в блоке D, когда искали лучших клиентов. Тогда «лучший» значило «потратил больше всех». Но клиент, который потратил много год назад и пропал, и клиент, который вчера сделал третий заказ, требуют разных писем. Сегодня собираем классическую сегментацию маркетологов, RFM, и считаем её на SQL целиком.

Три вопроса о каждом клиенте

Представь продавца в маленьком магазине. Про постоянного покупателя он помнит три вещи: когда тот заходил последний раз, как часто вообще заходит и сколько оставляет. По этим трём вещам он и решает, кому позвонить, когда привезут новинку.

RFM это те же три вопроса:

• Recency, давность: сколько дней прошло с последней покупки. Чем меньше, тем лучше;
• Frequency, частота: сколько было покупок;
• Monetary, деньги: сколько клиент принёс.

Считаем только доставленные заказы (status = 'delivered') и вычитаем возвраты. Точка отсчёта давности 1 июля 2026, первый день после конца данных.

WITH orders_net AS (
  SELECT o.customer_id, o.created_at,
         (SELECT sum(oi.qty * oi.price) FROM order_items oi WHERE oi.order_id = o.id)
         - coalesce((SELECT sum(r.refund_rub) FROM returns r WHERE r.order_id = o.id), 0) AS amount
  FROM orders o
  WHERE o.status = 'delivered'
)
SELECT customer_id,
       DATE '2026-07-01' - max(created_at)::date AS recency_days,
       count(*) AS frequency,
       sum(amount) AS monetary
FROM orders_net
GROUP BY customer_id;

Сумму заказа считаем подзапросами внутри строки заказа, а не JOIN с позициями: так одна строка CTE остаётся одним заказом и count(*) считает заказы, а не позиции. Это лекарство от размножения строк из урока 26.

Таких клиентов 2923 из 3534 зарегистрированных. Медиана давности 167 дней, медиана частоты 2 заказа, медиана денег 4960 ₽.

Баллы через NTILE

По абсолютным числам трудно судить: «давность 167 дней» это хорошо или плохо? Проще сравнить клиента с остальными. Для этого каждому признаку ставят балл, например от 1 до 3, где 3 это лучшая треть.

Оконная функция ntile(3) из урока 44 делает это сама: сортирует клиентов и режет на три равные части.

ntile(3) OVER (ORDER BY recency_days DESC, customer_id) AS r,
ntile(3) OVER (ORDER BY monetary, customer_id) AS m

Обрати внимание на направление. ntile раздаёт номера по возрастанию, а нам нужно, чтобы 3 получили самые свежие. Поэтому давность сортируем по убыванию: сначала давно ушедшие, в конце вчерашние покупатели. С деньгами наоборот, сортировка обычная. customer_id вторым ключом делает раскладку однозначной.

Границы давности в базе вышли такие: балл 3 у клиентов, купивших за последние 92 дня, балл 1 у тех, кого не было 216 дней и дольше.