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

RFM-анализ — классический метод сегментации клиентской базы. Он работает везде: e-commerce, SaaS, банки, рестораны. Реализуется одним SQL-запросом — никакого Python и Excel.

Что такое RFM

Три метрики на каждого клиента:

Каждому клиенту присваивается оценка от 1 до 5 по каждой метрике. Затем комбинация оценок даёт сегмент.

Шаг 1: Исходные метрики

Предположим, есть таблица orders:

-- orders: order_id, customer_id, created_at, total_amount

Считаем R, F, M для каждого клиента:

WITH rfm_raw AS (
    SELECT
        customer_id,
        -- Recency: дней с последней покупки (меньше = лучше)
        EXTRACT(DAY FROM NOW() - MAX(created_at))::int AS recency_days,
        -- Frequency: количество заказов
        COUNT(DISTINCT order_id)                        AS frequency,
        -- Monetary: сумма покупок
        SUM(total_amount)                               AS monetary
    FROM orders
    WHERE status = 'completed'
      AND created_at >= NOW() - INTERVAL '1 year'  -- анализируем последний год
    GROUP BY customer_id
)
SELECT * FROM rfm_raw LIMIT 10;

Пример результата:

Шаг 2: Скоринг через NTILE

NTILE(5) делит клиентов на 5 равных групп. Важный нюанс: для Recency меньше = лучше, поэтому инвертируем порядок сортировки.

WITH rfm_raw AS (
    SELECT
        customer_id,
        EXTRACT(DAY FROM NOW() - MAX(created_at))::int AS recency_days,
        COUNT(DISTINCT order_id)                        AS frequency,
        SUM(total_amount)                               AS monetary
    FROM orders
    WHERE status = 'completed'
      AND created_at >= NOW() - INTERVAL '1 year'
    GROUP BY customer_id
),
rfm_scores AS (
    SELECT
        customer_id,
        recency_days,
        frequency,
        monetary,
        -- R: 5 = покупал недавно, 1 = давно
        NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
        -- F: 5 = много покупок, 1 = мало
        NTILE(5) OVER (ORDER BY frequency ASC)     AS f_score,
        -- M: 5 = большие суммы, 1 = маленькие
        NTILE(5) OVER (ORDER BY monetary ASC)      AS m_score
    FROM rfm_raw
)
SELECT * FROM rfm_scores LIMIT 10;

Почему NTILE, а не пороговые значения? Пороги (например, «больше 10 заказов = 5») зависят от бизнеса и устаревают. NTILE делит относительно текущей базы — это универсально и не требует ручной настройки.

Шаг 3: Итоговый скор и сегменты

Суммируем три оценки и присваиваем сегмент:

WITH rfm_raw AS (
    SELECT
        customer_id,
        EXTRACT(DAY FROM NOW() - MAX(created_at))::int AS recency_days,
        COUNT(DISTINCT order_id)                        AS frequency,
        SUM(total_amount)                               AS monetary
    FROM orders
    WHERE status = 'completed'
      AND created_at >= NOW() - INTERVAL '1 year'
    GROUP BY customer_id
),
rfm_scores AS (
    SELECT
        customer_id,
        recency_days,
        frequency,
        monetary,
        NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
        NTILE(5) OVER (ORDER BY frequency ASC)     AS f_score,
        NTILE(5) OVER (ORDER BY monetary ASC)      AS m_score
    FROM rfm_raw
),
rfm_segments AS (
    SELECT
        customer_id,
        recency_days,
        frequency,
        monetary,
        r_score,
        f_score,
        m_score,
        (r_score + f_score + m_score) AS total_score,
        CASE
            WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4
                THEN 'Чемпионы'
            WHEN r_score >= 3 AND f_score >= 3
                THEN 'Лояльные'
            WHEN r_score >= 4 AND f_score <= 2
                THEN 'Новые клиенты'
            WHEN r_score >= 3 AND f_score >= 1 AND m_score >= 3
                THEN 'Потенциально лояльные'
            WHEN r_score <= 2 AND f_score >= 4
                THEN 'Под угрозой оттока'
            WHEN r_score <= 2 AND f_score >= 2
                THEN 'Засыпающие'
            WHEN r_score = 1 AND f_score <= 2
                THEN 'Потерянные'
            ELSE 'Требуют внимания'
        END AS segment
    FROM rfm_scores
)
SELECT
    segment,
    COUNT(*)                           AS customers,
    ROUND(AVG(recency_days))           AS avg_recency_days,
    ROUND(AVG(frequency), 1)           AS avg_orders,
    ROUND(AVG(monetary))               AS avg_revenue,
    ROUND(SUM(monetary))               AS total_revenue
FROM rfm_segments
GROUP BY segment
ORDER BY total_revenue DESC;

Типичные результаты и что с ними делать

Автоматизация: VIEW для актуальных данных

Создайте материализованное представление и обновляйте его ежедневно:

CREATE MATERIALIZED VIEW rfm_customer_segments AS
-- ... полный запрос выше ...
;

-- Обновление (например, через cron или pg_cron)
REFRESH MATERIALIZED VIEW rfm_customer_segments;

Теперь маркетинговые инструменты могут читать сегменты напрямую из базы.

Хотите отработать CTE и аналитические функции на практических задачах? Попробуйте тренажёр SQLlab.