RFM-анализ на SQL: от заказов к сегментам

SQL Studio / Разборы SQL / RFM-анализ на SQL: от заказов к сегментам

RFM — три характеристики клиента: Recency (сколько дней с последней покупки), Frequency (сколько покупок) и Monetary (их денежная сумма). Для сравнимости сначала договоримся об определениях. В этой статье «покупка» — заказ со статусом delivered, дата среза — 8 сентября 2026 года, а денежный показатель — валовая сумма доставленных заказов до учёта возвратов.

1. Собрать RFM на одной дате среза

В учебной базе интернет-магазина есть четыре клиента и заказы с датами. Сначала агрегируем заказы до одной строки на покупателя, затем присоединяем всех клиентов — включая тех, кто ничего не покупал.

WITH delivered AS (
  SELECT customer_id,
         COUNT(*) AS frequency,
         MAX(date(created_at)) AS last_day,
         SUM(gross_kopecks) / 100.0 AS monetary_rub
  FROM orders
  WHERE status = 'delivered'
  GROUP BY customer_id
)
SELECT c.customer_id, c.name,
       CAST(julianday('2026-09-08') - julianday(d.last_day) AS INTEGER) AS recency_days,
       COALESCE(d.frequency, 0) AS frequency,
       COALESCE(d.monetary_rub, 0) AS monetary_rub
FROM customers AS c
LEFT JOIN delivered AS d ON d.customer_id = c.customer_id
ORDER BY c.customer_id;

У Данила нет доставок, поэтому Recency = NULL, а не 0: нельзя приравнивать отсутствие покупки к покупке сегодня. COALESCE уместен для количества и суммы, но не для времени с последнего заказа.

2. Превратить показатели в понятные группы

На четырёх клиентах неразумно строить пятибалльные RFM-квантили: группы будут нестабильными. Возьмём прозрачные, заранее заданные правила. Это демонстрация бизнес-логики, а не универсальные пороги для любого магазина.

WITH delivered AS (
  SELECT customer_id,
         COUNT(*) AS frequency,
         MAX(date(created_at)) AS last_day,
         SUM(gross_kopecks) / 100.0 AS monetary_rub
  FROM orders
  WHERE status = 'delivered'
  GROUP BY customer_id
),
metrics AS (
  SELECT c.customer_id,
         CAST(julianday('2026-09-08') - julianday(d.last_day) AS INTEGER) AS recency_days,
         COALESCE(d.frequency, 0) AS frequency,
         COALESCE(d.monetary_rub, 0) AS monetary_rub
  FROM customers AS c
  LEFT JOIN delivered AS d ON d.customer_id = c.customer_id
)
SELECT customer_id,
       CASE
         WHEN frequency = 0 THEN 'Нет доставок'
         WHEN frequency >= 2 AND monetary_rub >= 700 THEN 'Ценные повторные'
         WHEN frequency >= 2 THEN 'Повторные'
         WHEN recency_days <= 3 AND monetary_rub >= 300 THEN 'Недавние с крупной суммой'
         WHEN recency_days <= 3 THEN 'Недавние'
         ELSE 'Давние разовые'
       END AS segment
FROM metrics
ORDER BY customer_id;

Анна попадает в «Ценные повторные» (F = 2, M = 750 ₽), Борис — в «Недавние с крупной суммой» (R = 2, M = 300 ₽), Вера — в «Недавние», Данил — в «Нет доставок». Здесь участвуют R, F и M, но пороги 3 дня, 300 ₽ и 700 ₽ выбраны для учебного примера, а не рассчитаны на реальной аудитории магазина.

3. Почему Monetary здесь не равно чистой выручке

Сумма стоимости доставленных заказов — не то же самое, что подтверждённые оплаты за вычетом возвратов. Платёжных попыток может быть несколько, а возвраты могут относиться к отдельным позициям. Проверим три разных показателя без опасного JOIN нескольких таблиц с отношением «один ко многим».

SELECT
  (SELECT SUM(gross_kopecks) / 100.0
   FROM orders WHERE status = 'delivered') AS delivered_gross_rub,
  (SELECT SUM(amount_kopecks) / 100.0
   FROM payments WHERE status = 'captured') AS captured_rub,
  (SELECT SUM(amount_kopecks) / 100.0
   FROM refunds) AS refund_rub;

Результат: доставленные заказы — 1 250 ₽, подтверждённые платежи — 1 250 ₽, возвраты — 90 ₽. В этом маленьком наборе чистые поступления после возвратов составляют 1 160 ₽, но это не превращает delivered_gross_rub в бухгалтерскую выручку. Для настоящих данных требуется определить, на какую дату признаются продажи и возвраты.

4. Что проверить на реальной клиентской базе

Фиксируйте дату среза, фильтруйте только нужные статусы, отдельно учитывайте отмены и возвраты. Если один клиент может иметь несколько аккаунтов, простая группировка по customer_id не решит проблему идентичности. Для сравнения сегментов во времени используйте одинаковые окна наблюдения.

Попрактиковаться на SQL Studio

Начните с подсчёта доставленных заказов и нумерации покупок, затем выполните RFM-запрос целиком в бесплатной песочнице.

Смежные материалы: повторные покупки, конверсия покупателей и завышение сумм после JOIN.

Справка по функциям SQLite: julianday() и дата и COUNT, SUM и MAX. Определение RFM и пороги сегментов в этой статье — учебные, не универсальный отраслевой стандарт.

Для последовательной практики на MySQL 8 — курс «SQL для аналитика» на Stepik с итоговой работой и возможностью получить сертификат.