Как посчитать RFM-сегментацию в SQL

Зачем RFM

RFM-сегментация — простой и эффективный способ разделить клиентов на группы для маркетинга. «Champions» (высокие R+F+M) ≠ «At Risk» (низкий R, но раньше были высокие F+M). Для каждого сегмента — свои действия.

Формула

Recency  = дней с последней покупки (меньше = лучше)
Frequency = кол-во покупок за период
Monetary  = revenue за период

R/F/M score = NTILE(5) каждого
RFM_score   = R*100 + F*10 + M (например, 555 — best)

Базовый расчёт

WITH user_rfm AS (
    SELECT
        user_id,
        EXTRACT(DAY FROM CURRENT_DATE - MAX(created_at))::INT AS recency_days,
        COUNT(*) AS frequency,
        SUM(total) AS monetary
    FROM orders
    WHERE status = 'paid'
      AND created_at >= CURRENT_DATE - INTERVAL '365 days'
    GROUP BY user_id
),
rfm_scored AS (
    SELECT
        user_id,
        recency_days,
        frequency,
        monetary,
        NTILE(5) OVER (ORDER BY recency_days)         AS r_score,
        NTILE(5) OVER (ORDER BY frequency DESC)       AS f_score,
        NTILE(5) OVER (ORDER BY monetary DESC)        AS m_score
    FROM user_rfm
)
SELECT
    user_id,
    r_score,
    f_score,
    m_score,
    r_score * 100 + f_score * 10 + m_score AS rfm_score
FROM rfm_scored
ORDER BY rfm_score DESC;

NTILE: 1 — лучшие 20%, 5 — худшие 20%. Важно: для recency меньше = лучше, поэтому сортируем по recency_days по возрастанию (ASC).

Сегменты

Стандартные RFM-сегменты:

WITH scored AS (
    -- ... (RFM scoring выше)
)
SELECT
    user_id,
    CASE
        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
        WHEN r_score >= 4 AND f_score >= 3 THEN 'Loyal'
        WHEN r_score >= 3 AND f_score <= 3 THEN 'Potential'
        WHEN r_score <= 3 AND f_score >= 4 THEN 'At Risk'
        WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost'
        ELSE 'Other'
    END AS segment
FROM scored;

Применение

• Champions: VIP-программа, эксклюзивные предложения.
• Loyal: бонусы за приглашения друзей.
• Potential: допродажи, увеличение частоты покупок.
• At Risk: реактивационные кампании, скидки.
• Lost: агрессивная реактивация либо списание в архив.

Частые ошибки

Ошибка 1. NTILE на одинаковых значениях. Если у многих пользователей одинаковая сумма покупок, NTILE распределит их по корзинам неоптимально — граница между сегментами пройдёт посреди одинаковых значений.

Ошибка 2. RFM score без контекста. 255 и 552 — одни и те же цифры, но совершенно разные сегменты. Опирайтесь на именованные сегменты, а не на сырой RFM score.

Ошибка 3. Несовпадение окон. Recency считается от сегодняшней даты. Если данные «застыли» и не обновляются, recency растёт сам по себе, хотя реального изменения в поведении клиента нет.

Ошибка 4. Период для F и M. 30, 90 или 365 дней? Период должен соответствовать циклу покупки в вашем продукте.

Ошибка 5. Применять без персонализации. Champions заваливают спам-скидками — и их LTV падает. Топ-клиентам нужны эксклюзивные предложения, а не ширпотреб.

Связанные темы

• RFM-анализ
• SQL для RFM-анализа
• Как посчитать frequency в SQL
• Как посчитать LTV в SQL

FAQ

Почему NTILE(5), а не NTILE(10)?

NTILE(5) проще интерпретировать: всего 5 корзин по каждому измерению. NTILE(10) даёт больше детализации, но тогда получается до 1000 комбинаций сегментов — работать с ними на практике неудобно.

RFM для B2B?

Работает, но нужна поправка: частоту и сумму покупок считайте по аккаунтам (компаниям), а не по отдельным пользователям.

RFM — статичный или динамичный?

Динамичный. Пересчитывайте сегменты регулярно — раз в неделю или раз в месяц, потому что клиенты постоянно перетекают из одного сегмента в другой.

Как часто обновлять сегменты?

Стандарт — раз в неделю. Для продуктов с высокой частотой покупок имеет смысл пересчитывать сегменты ежедневно.

RFM vs ML-сегментация?

RFM — простой и прозрачный: сразу видно, почему клиент попал в тот или иной сегмент. ML-сегментация обычно точнее, но работает как чёрный ящик, и объяснить её логику бизнесу заметно сложнее.