Присвоение RFM-скоров и создание сегментов

После расчета сырых RFM-метрик необходимо преобразовать их в стандартизированные оценки (скоры), которые позволят сравнивать клиентов и группировать их в осмысленные сегменты. Этот процесс включает разбиение клиентов на квантили и формирование RFM-кодов.

Квантильное разбиение с помощью NTILE

Функция NTILE(n) — это оконная функция, которая разбивает упорядоченный набор данных на n примерно равных групп (квантилей) и присваивает каждой строке номер группы от 1 до n. Для RFM-анализа обычно используют NTILE(5) (квинтили) или NTILE(3) (терцили).

Важная особенность: для Recency логика обратная — меньшее значение (недавняя покупка) должно получить более высокий скор. Поэтому при сортировке для Recency используется ORDER BY recency_days ASC, а для Frequency и Monetary — ORDER BY ... DESC.

WITH rfm_raw AS (
    SELECT 
        customer_id,
        DATEDIFF(day, MAX(order_date), '2024-12-31') AS recency_days,
        COUNT(DISTINCT order_id) AS frequency,
        SUM(amount) AS monetary_value
    FROM orders
    WHERE order_date >= '2023-01-01'
    GROUP BY customer_id
),
rfm_scores AS (
    SELECT 
        customer_id,
        recency_days,
        frequency,
        monetary_value,
        -- Recency: чем меньше дней, тем выше скор
        NTILE(5) OVER (ORDER BY recency_days ASC) AS r_score,
        -- Frequency: чем больше покупок, тем выше скор
        NTILE(5) OVER (ORDER BY frequency DESC) AS f_score,
        -- Monetary: чем больше выручка, тем выше скор
        NTILE(5) OVER (ORDER BY monetary_value DESC) AS m_score
    FROM rfm_raw
)
SELECT * FROM rfm_scores
ORDER BY r_score DESC, f_score DESC, m_score DESC;

Обратите внимание на направление сортировки: - recency_days ASC — клиенты с меньшим количеством дней (недавние покупки) попадают в группу с более высоким скором - frequency DESC и monetary_value DESC — клиенты с большими значениями получают более высокий скор

Формирование RFM-кода

RFM-код — это строковое представление трех скоров, объединенных вместе. Например, клиент со скорами R=5, F=5, M=5 получает код "555" (лучший сегмент), а клиент с R=1, F=1, M=1 — код "111" (наименее ценный).

WITH rfm_scores AS (
    -- предыдущий CTE с расчетом скоров
    ...
),
rfm_segments AS (
    SELECT 
        customer_id,
        r_score,
        f_score,
        m_score,
        -- Конкатенация скоров в строку
        CONCAT(r_score, f_score, m_score) AS rfm_code,
        -- или через приведение типов
        CAST(r_score AS VARCHAR) || CAST(f_score AS VARCHAR) || CAST(m_score AS VARCHAR) AS rfm_code
    FROM rfm_scores
)
SELECT 
    rfm_code,
    COUNT(*) AS customer_count
FROM rfm_segments
GROUP BY rfm_code
ORDER BY customer_count DESC;

Этот запрос покажет распределение клиентов по RFM-кодам. При использовании 5 квинтилей получается 125 возможных комбинаций (5×5×5), но не все из них обязательно будут представлены в данных.

Классификация на бизнес-сегменты с CASE WHEN

Работать со множеством микросегментов неудобно, поэтому RFM-коды группируют в более крупные бизнес-сегменты с понятными названиями. Для этого используется конструкция CASE WHEN:

WITH rfm_scores AS (
    -- расчет скоров
    ...
),
rfm_classified AS (
    SELECT 
        customer_id,
        r_score,
        f_score,
        m_score,
        CASE 
            -- VIP-клиенты: высокие показатели по всем метрикам
            WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 
                THEN 'VIP / Champions'

            -- Лояльные клиенты: часто покупают, недавно активны
            WHEN r_score >= 3 AND f_score >= 3 
                THEN 'Loyal Customers'

            -- Потенциальные лояльные: недавние покупатели с хорошей частотой
            WHEN r_score >= 4 AND f_score >= 2 
                THEN 'Potential Loyalists'

            -- Новые клиенты: недавняя покупка, но низкая частота
            WHEN r_score >= 4 AND f_score <= 2 
                THEN 'New Customers'

            -- Спящие: давно не покупали, но раньше были активны
            WHEN r_score <= 2 AND f_score >= 3 
                THEN 'At Risk / Hibernating'

            -- Потерянные: давно не покупали и низкая частота
            WHEN r_score <= 2 AND f_score <= 2 
                THEN 'Lost / Cannot Lose'

            -- Остальные
            ELSE 'Other'
        END AS customer_segment
    FROM rfm_scores
)
SELECT 
    customer_segment,
    COUNT(*) AS customer_count,
    ROUND(AVG(r_score), 2) AS avg_r,
    ROUND(AVG(f_score), 2) AS avg_f,
    ROUND(AVG(m_score), 2) AS avg_m
FROM rfm_classified
GROUP BY customer_segment
ORDER BY customer_count DESC;

Выбор количества квантилей

Выбор между NTILE(3), NTILE(5) или NTILE(10) зависит от размера клиентской базы и бизнес-задач:

Для большинства задач NTILE(5) является оптимальным выбором.

Ключевой вывод: Оконная функция NTILE позволяет преобразовать сырые метрики в стандартизированные скоры. Объединение скоров в RFM-код и последующая классификация через CASE WHEN создают понятные бизнес-сегменты, с которыми может работать маркетинговая команда. Правильная настройка условий сегментации требует понимания специфики бизнеса и итеративной доработки.