RFM-сегментация
в SQL за 30 минут

Самая популярная клиентская сегментация — отсортировать всех по выручке и взять «топ-100». Иногда это работает (выбрать VIP-список для рассылки от CEO), но как операционная сегментация это плохо. Один и тот же «миллион выручки» в топе может быть у:

• клиента, который купил всё в первый месяц и больше не приходил,
• клиента, который покупает каждую неделю по чуть-чуть три года подряд,
• клиента, который раньше покупал часто, но последние полгода тихий.

Стратегия работы с этими тремя — разная. С первым надо понять, можно ли вернуть. Второго не трогать, чтобы не сломать. Третьего срочно поднимать win-back-кампанией, пока не ушёл совсем. На сортировке «по сумме» эти три случая выглядят одинаково.

RFM решает это, давая каждому клиенту три балла вместо одного:

Шкала 1–5 — это перцентили базы: 5 = верхние 20%, 1 = нижние 20%. Идея пришла из direct marketing 1990-х (Miglautsch, 1995) и до сих пор работает в e-com и ретейле, потому что три измерения дают плотный сигнал о клиенте, который читается с одного взгляда.

Кодировка простая: соединяем три балла подряд. 555 — лучший клиент: недавний, частый, крупный. 111 — худший. Между ними — 125 ячеек, из которых складываются 10–11 практических сегментов.

Считаем RFM на нашем маркетплейс-датасете. Структура простая: таблица orders с user_id, created_at, amount. Делаем за три шага.

Шаг 1: агрегаты на юзера

WITH user_agg AS (
    SELECT
        user_id,
        MAX(created_at) AS last_order_dt,
        COUNT(*)        AS orders_cnt,
        SUM(amount)     AS total_revenue
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY user_id
)
SELECT * FROM user_agg;

Окно — 12 месяцев. Меньше — потеряем «спящих» с длинным циклом покупок (мебель, электроника). Больше — притянем юзеров, которые уже точно не вернутся (тогда они должны быть в Lost, а не в Hibernating). Для FMCG-кейсов берут 90–180 дней.

Шаг 2: NTILE(5) на каждую ось

WITH user_agg AS (/* как выше */),
rfm_scores AS (
    SELECT
        user_id,
        last_order_dt,
        orders_cnt,
        total_revenue,
        -- R: чем недавнее, тем выше балл
        NTILE(5) OVER (ORDER BY last_order_dt DESC) AS r_score,
        -- F: чем больше заказов, тем выше балл
        NTILE(5) OVER (ORDER BY orders_cnt   DESC) AS f_score,
        -- M: чем больше revenue, тем выше балл
        NTILE(5) OVER (ORDER BY total_revenue DESC) AS m_score
    FROM user_agg
)
SELECT * FROM rfm_scores;

Три NTILE(5) на трёх разных ORDER BY — каждый юзер получает три балла независимо. Это квинтили: 20% юзеров с самой свежей покупкой получают R=5, следующие 20% — R=4, и так далее. Аналогично для F и M.

Шаг 3: RFM-код и сегмент

WITH user_agg AS (/* шаг 1 */),
rfm_scores AS (/* шаг 2 */),
rfm_final AS (
    SELECT
        user_id,
        r_score,
        f_score,
        m_score,
        -- Объединяем в код вида '555'
        CONCAT(r_score, f_score, m_score) AS rfm_code,
        -- Усреднённый FM-балл — пригодится для сегментации
        (f_score + m_score) / 2.0     AS fm_avg,
        CASE
            WHEN r_score >= 4 AND (f_score + m_score) >= 8
                THEN 'Champions'
            WHEN r_score >= 3 AND (f_score + m_score) >= 6
                THEN 'Loyal'
            WHEN r_score >= 4 AND (f_score + m_score) <= 4
                THEN 'New customers'
            WHEN r_score >= 3 AND (f_score + m_score) <= 4
                THEN 'Promising'
            WHEN r_score = 3 AND (f_score + m_score) BETWEEN 5 AND 7
                THEN 'Need attention'
            WHEN r_score = 2 AND (f_score + m_score) >= 5
                THEN 'At risk'
            WHEN r_score <= 2 AND (f_score + m_score) >= 8
                THEN 'Cannot lose them'
            WHEN r_score = 2 AND (f_score + m_score) BETWEEN 3 AND 4
                THEN 'About to sleep'
            WHEN r_score = 1 AND (f_score + m_score) BETWEEN 3 AND 5
                THEN 'Hibernating'
            WHEN r_score = 1 AND (f_score + m_score) <= 2
                THEN 'Lost'
            ELSE 'Other'
        END AS segment
    FROM rfm_scores
)
SELECT segment, COUNT(*) AS users, SUM(*) OVER () AS total
FROM rfm_final
GROUP BY segment
ORDER BY users DESC;

Готово. На маркетплейс-датасете (~80к юзеров с заказами за 12 месяцев) этот запрос отрабатывает за пару секунд и сразу даёт сводку: «12% — Champions, 18% — Loyal, 9% — At risk, 6% — Cannot lose them, 14% — Hibernating». Можно сразу строить из этого треугольник миграций сегментов от месяца к месяцу и считать revenue per segment.

125 ячеек 5×5×5 неудобно держать в голове. Их сводят в 10–11 практических сегментов. Деления у разных источников немного отличаются (Putler, Optimove, Authority Hacker — варианты), но скелет один и тот же.

Сегментация без действий — упражнение в Excel. Реальная ценность RFM в том, что под каждый сегмент привязан конкретный сценарий работы. Ниже — что обычно делают в e-com.

Эта таблица — не «правда», а отправная точка. На зрелой команде CRM экономика по каждому сегменту считается отдельно: cost per touch, CR в покупку, revenue per touch. Если At risk даёт ROI 5× а Hibernating 0.4× — на At risk идёт бюджет, на Hibernating — нет.

Миграция сегментов: главный KPI RFM-команды

Самая ценная картинка из RFM — не один срез на дату, а матрица переходов между сегментами от месяца к месяцу. Если Champions массово утекают в At risk, а not New customers → Promising / Potential loyalists, продукт «протекает» там, где у тебя пробоина.

WITH rfm_curr AS (/* rfm_final за последние 30 дней */),
     rfm_prev AS (/* rfm_final за период 30–60 дней назад */)
SELECT
    prev.segment AS from_segment,
    curr.segment AS to_segment,
    COUNT(*)   AS users,
    SUM(curr.total_revenue) AS revenue_at_destination
FROM rfm_prev prev
JOIN rfm_curr curr USING (user_id)
GROUP BY prev.segment, curr.segment
ORDER BY users DESC;

Через эту матрицу видно, что реально работает в CRM: если после win-back-кампании At risk → Loyal вырос с 2% до 8%, кампания окупилась. Если Cannot lose them → Lost не уменьшился — менять стратегию.

Метод универсальный, но не везде. Есть три типа продуктов, на которых RFM в классической форме либо ничего не показывает, либо вводит в заблуждение.

RFM спрашивают и на технических собесах (по SQL и оконкам), и на продуктовых (когда обсуждают сегментацию и CRM). Сильный ответ — это не пересказать 11 сегментов, а показать, что ты понимаешь логику и границы.