SQL для RFM-анализа: построение и сегментация

Что такое RFM

RFM — одна из старейших и простейших моделей сегментации клиентов. Работает в ecommerce, retail, подписочных сервисах. Три компонента:

Recency — как давно клиент совершал действие (покупку, вход). Чем свежее — тем лучше.

Frequency — как часто клиент совершает действие. Чем чаще — тем ценнее.

Monetary — сколько денег приносит клиент. Чем больше — тем важнее.

Каждого клиента оценивают по трём осям, присваивают score, на основе которого делят на сегменты: «чемпионы», «лояльные», «под угрозой оттока» и так далее. Дальше маркетинг работает с каждым сегментом по-разному.

Базовый SQL для RFM

Пошагово посчитаем три метрики для каждого клиента:

WITH rfm_base AS (
    SELECT
        user_id,
        EXTRACT(DAY FROM (CURRENT_DATE - MAX(created_at)::DATE)) AS recency_days,
        COUNT(*) AS frequency,
        SUM(amount) AS monetary
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
)
SELECT * FROM rfm_base
LIMIT 10;

Получаем табличку:

user_id | recency_days | frequency | monetary
1       | 5            | 15        | 45000
2       | 120          | 3         | 8000
3       | 2            | 30        | 120000

Это сырые данные. Дальше их нужно привести к scores.

RFM scores через NTILE

Классический подход — разбить каждую метрику на 5 групп (квантилей) и присвоить score 1-5:

WITH rfm_base AS (
    SELECT
        user_id,
        EXTRACT(DAY FROM (CURRENT_DATE - MAX(created_at)::DATE)) AS recency_days,
        COUNT(*) AS frequency,
        SUM(amount) AS monetary
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
)
SELECT
    user_id,
    recency_days,
    frequency,
    monetary,
    -- Recency: меньше = лучше (score 5)
    NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
    -- Frequency: больше = лучше
    NTILE(5) OVER (ORDER BY frequency) AS f_score,
    -- Monetary: больше = лучше
    NTILE(5) OVER (ORDER BY monetary) AS m_score
FROM rfm_base;

Обратите внимание на ORDER BY recency_days DESC для recency — чем меньше дней с последней покупки, тем выше должен быть score.

После этого у каждого клиента тройка вроде (5, 4, 5) — отличный recency, хорошая frequency, отличный monetary.

Классические сегменты

Стандартная классификация на основе RFM score:

WITH rfm_scored AS (
    -- предыдущий запрос
),
segments AS (
    SELECT
        user_id,
        r_score,
        f_score,
        m_score,
        CASE
            WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Чемпионы'
            WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN 'Лояльные'
            WHEN r_score >= 4 AND f_score <= 2 THEN 'Новые'
            WHEN r_score <= 2 AND f_score >= 3 AND m_score >= 3 THEN 'Под риском оттока'
            WHEN r_score <= 2 AND f_score <= 2 AND m_score >= 4 THEN 'Потерянные VIP'
            WHEN r_score <= 2 AND f_score <= 2 AND m_score <= 2 THEN 'Потерянные'
            ELSE 'Обычные'
        END AS segment
    FROM rfm_scored
)
SELECT segment, COUNT(*) AS customers
FROM segments
GROUP BY segment
ORDER BY customers DESC;

Теперь видно распределение: сколько чемпионов, сколько под риском оттока, и так далее.

Каждый сегмент требует своей стратегии:

Чемпионы — поощрять за loyalty, просить рекомендации, тестировать новые продукты.

Лояльные — поддерживать engagement, предлагать upsell.

Под риском оттока — активная retention-кампания, персональные предложения.

Потерянные VIP — winback-кампания с сильным оффером (они приносили много, стоит постараться).

Новые — онбординг, первая покупка → вторая покупка.

Концентрированный single score

Иногда хочется одно число вместо трёх:

SELECT
    user_id,
    r_score * 100 + f_score * 10 + m_score AS rfm_code,
    (r_score + f_score + m_score) AS rfm_sum
FROM rfm_scored;

rfm_code — уникальный код (например, 545 — R=5, F=4, M=5). Всего 125 комбинаций.

rfm_sum — простое среднее, от 3 до 15. Меньше уровней, удобнее для дашбордов.

Для конкретных решений первый вариант гибче, второй — для upper-level просмотра.

Глубокое погружение в продуктовую сегментацию — важная часть senior-роли в ecommerce. В тренажёре Карьерник есть задачи на построение сегментов и метрик для retention-команд.

RFM для SaaS

Для подписочной модели немного другой RFM.

Recency — как давно логинился / использовал продукт.

Frequency — число active sessions за период.

Monetary — MRR (monthly recurring revenue) клиента.

WITH saas_rfm AS (
    SELECT
        u.user_id,
        EXTRACT(DAY FROM CURRENT_DATE - MAX(e.event_time)::DATE) AS recency_days,
        COUNT(DISTINCT e.event_time::DATE) AS active_days,  -- за последние 90 дней
        MAX(s.monthly_amount) AS current_mrr
    FROM users u
    LEFT JOIN events e ON u.user_id = e.user_id
        AND e.event_time >= CURRENT_DATE - INTERVAL '90 day'
    LEFT JOIN subscriptions s ON u.user_id = s.user_id
        AND s.status = 'active'
    GROUP BY u.user_id
)
SELECT ... -- применяем NTILE как раньше

Для B2B — agregate на уровне аккаунта, не пользователя.

Dynamic RFM

RFM — это снимок. Для динамики полезно смотреть, как клиенты перемещаются между сегментами.

WITH rfm_current AS (
    -- RFM сегменты на сегодня
),
rfm_month_ago AS (
    -- RFM на месяц назад (та же логика, но с фильтром created_at <= CURRENT_DATE - 30)
),
transitions AS (
    SELECT
        COALESCE(c.user_id, m.user_id) AS user_id,
        c.segment AS current_segment,
        m.segment AS previous_segment
    FROM rfm_current c
    FULL OUTER JOIN rfm_month_ago m USING (user_id)
)
SELECT
    previous_segment,
    current_segment,
    COUNT(*) AS users
FROM transitions
GROUP BY previous_segment, current_segment
ORDER BY previous_segment, current_segment;

Матрица переходов. Видно, сколько клиентов «чемпионов» стали «под риском», сколько «новых» стали «лояльными» — что-то работает.

Продвинутая сегментация

Классический RFM — это 3 измерения. Но сегментация может быть богаче:

• Product category affinity — что человек покупает (электроника, одежда, еда).
• Device — mobile vs desktop vs both.
• Geography — столица vs регионы.
• Source — как пришёл (organic, paid, referral).

Полная сегментация — это RFM × product × device × source. В результате получаются сотни микросегментов. Для production обычно делают RFM + 1-2 важных разреза.

Когда RFM не работает

RFM плох для:

Подписочных моделей с одинаковой ценой. Все платят одинаково — Monetary становится proxy длительности подписки.

Продуктов с редкими покупками. Авто, недвижимость. Там каждая покупка — событие, frequency=1 для всех.

Сильно сезонных бизнесов. Летом все «под риском», зимой все «лояльные». Нужна normalization.

В этих случаях используют другие модели сегментации — behavioral, cohort-based, cluster analysis.

Применение в маркетинге

Практический workflow в ecommerce:

Раз в неделю SQL-скрипт пересчитывает RFM-сегменты.

Экспорт в CRM (Mindbox, CleverTap, собственная система).

Автоматические триггеры: для сегмента «Под риском» — SMS с персональным промокодом. Для «Чемпионов» — access к новой коллекции первыми. Для «Потерянных VIP» — звонок менеджера.

Retention-команда отслеживает переходы между сегментами. Если «Под риском» → «Чемпионы» растёт, кампании работают.

Читайте также

• RFM-анализ
• Сегментация пользователей
• SQL для e-commerce
• Когортный анализ

FAQ

5 или 10 квантилей?

5 — стандарт, понятно маркетингу. 10 — больше градаций, но усложняет. Для большинства задач 5 достаточно.

Как часто пересчитывать?

Еженедельно обычно. Для бизнесов с быстрым циклом (fastfood) — ежедневно. Для SaaS с годовой подпиской — ежемесячно.

RFM-сегменты меняются сами?

Да. Клиент ничего не делает — через месяц он «под риском». Покупает — становится «чемпионом». Динамика — часть логики.

Можно ли без M (monetary)?

RF-сегментация возможна. Используется для продуктов без прямой монетизации (контент-платформы). Но M добавляет business context.