Коротко про RFM-анализ на SQL26.03.2025 15:01

9fab0ae3a0070ae64f2946e9e009a5a7.jpg

Привет, Хабр!

Сегодня поговорим про RFM‑анализ на SQL.

Простыми словами: RFM‑анализ — это способ понять, насколько ценные у тебя пользователи. По факту, это старый добрый способ взглянуть на клиентскую базу и выделить:

  • Кто покупает часто

  • Кто покупает дорого

  • Кто давно не заходил, но когда‑то приносил тебе деньги

RFM расшифровывается так:

  • Recency — когда последний раз был активен пользователь

  • Frequency — как часто он делает покупки/действия

  • Monetary — сколько он потратил денег (или сделал заказов, если у тебя freemium)

Модель старая как мир (её активно юзали ещё в 90-е), но до сих пор прекрасно живёт в email‑рассылках, пуш‑уведомлениях, дешбордах и таргетинге.

Как будем строить RFM

Пошагово:

  1. Выберем дату анализа (RFM зависит от «сейчас»)

  2. Считаем Recency, Frequency, Monetary

  3. Разобьём пользователей по квантилям

  4. Сделаем итоговый RFM‑сегмент

  5. Назовём сегменты по‑человечески (а не »543»)

И всё это — на SQL.

Исходная таблица — orders

Допустим, есть таблица заказов: 

CREATE TABLE orders (
    user_id     BIGINT,
    order_id    BIGINT,
    order_date  TIMESTAMP,
    amount      NUMERIC(10, 2)
);

Это самый минимальный набор, который нужен для RFM. Если есть модель подписки или freemium, amount может быть просто количеством или даже «ценностью действия» (например, сколько лайков поставил).

Установим точку отсчёта

Всё измеряется «от текущей даты». А текущая дата — это параметр в пайплайне, а не CURRENT_DATE, потому что иначе будем отлаживать свои запросы только один день в году — сегодня.

WITH analysis_date AS (
    SELECT DATE '2025-03-19' AS today
)

Считаем сырые метрики R, F, M

Тут начинается основная работа.

WITH analysis_date AS (
    SELECT DATE '2025-03-19' AS today
),
rfm_base AS (
    SELECT
        user_id,
        MAX(order_date) AS last_order_date,
        COUNT(*)        AS frequency,
        SUM(amount)     AS monetary
    FROM orders
    GROUP BY user_id
),
rfm_metrics AS (
    SELECT
        b.user_id,
        DATE_PART('day', a.today - b.last_order_date) AS recency,
        b.frequency,
        b.monetary
    FROM rfm_base b
    CROSS JOIN analysis_date a
)
SELECT * FROM rfm_metrics;

last_order_date — последняя дата заказа. frequency — сколько заказов. monetary — сумма заказов (в рублях или баллах, зависит от продукта). recency — сколько дней прошло с последнего заказа

recency считается в днях. Если продукт с другими периодами активности (например, подписка на месяц), переводим в нужную тебе метрику.

Разбиваем пользователей на квантильные группы (1–5)

Хотим превратить «число дней», «количество заказов» и «сумму» — в оценки от 1 до 5, чтобы потом удобно сегментировать.

Как это сделать? С помощью PERCENTILE_CONT: 

WITH percentiles AS (
    SELECT
        PERCENTILE_CONT(ARRAY[0.2, 0.4, 0.6, 0.8])
            WITHIN GROUP (ORDER BY recency)   AS recency_pct,
        PERCENTILE_CONT(ARRAY[0.2, 0.4, 0.6, 0.8])
            WITHIN GROUP (ORDER BY frequency) AS frequency_pct,
        PERCENTILE_CONT(ARRAY[0.2, 0.4, 0.6, 0.8])
            WITHIN GROUP (ORDER BY monetary)  AS monetary_pct
    FROM rfm_metrics
),
rfm_binned AS (
    SELECT
        r.user_id,
        -- Чем меньше recency, тем лучше, поэтому инверсия
        CASE
            WHEN recency <= p.recency_pct[1] THEN 5
            WHEN recency <= p.recency_pct[2] THEN 4
            WHEN recency <= p.recency_pct[3] THEN 3
            WHEN recency <= p.recency_pct[4] THEN 2
            ELSE 1
        END AS r_score,
        CASE
            WHEN frequency <= p.frequency_pct[1] THEN 1
            WHEN frequency <= p.frequency_pct[2] THEN 2
            WHEN frequency <= p.frequency_pct[3] THEN 3
            WHEN frequency <= p.frequency_pct[4] THEN 4
            ELSE 5
        END AS f_score,
        CASE
            WHEN monetary <= p.monetary_pct[1] THEN 1
            WHEN monetary <= p.monetary_pct[2] THEN 2
            WHEN monetary <= p.monetary_pct[3] THEN 3
            WHEN monetary <= p.monetary_pct[4] THEN 4
            ELSE 5
        END AS m_score
    FROM rfm_metrics r
    CROSS JOIN percentiles p
)
SELECT
    user_id,
    r_score, f_score, m_score,
    (r_score || f_score || m_score) AS rfm_segment
FROM rfm_binned;

Recency инвертирован: меньше — лучше. Frequency и Monetary идут впрямую: больше — лучше. || — склеиваем в строку вида '543' — и получаем код сегмента.

Человеческие названия сегментов

А теперь немного маркетинга с человеческим лицом.

SELECT *,
    CASE
        WHEN r_score = 5 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
        WHEN r_score >= 4 AND f_score >= 3 THEN 'Loyal'
        WHEN r_score = 5 AND f_score = 1 THEN 'New customers'
        WHEN r_score <= 2 AND f_score >= 4 THEN 'At risk'
        WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost'
        ELSE 'Other'
    END AS rfm_group
FROM rfm_binned;

Теперь есть понятные ярлыки.

Что делать дальше?

Ты сделал RFM. И что теперь?

Вариантов много:

  1. Пуш‑уведомления по группам — «Champions» получают VIP‑скидку, «Lost» — призыв вернуться

  2. Анализ LTV по сегментам — сравнить, кто приносит деньги

  3. Фильтрация в витринах — не нужно плодить флажки, RFM даст понимание поведения

  4. A/B тесты на поведении — сравнить, как сегменты реагируют на разные фичи

Напоследок напомню о ближайших открытых уроках по аналитике в Otus:

  • 27 марта: PostgreSQL на стероидах: большие данные, высокие нагрузки и масштабирование без боли. Узнать подробнее

  • 31 марта: SQL: Учимся работать с индексами на примере PostgreSQL и MS SQL Server. Узнать подробнее

Посмотреть весь список открытых уроков по аналитике и не только можно в календаре.

© Habrahabr.ru