Расчет RFM-метрик для каждого клиента

RFM-анализ начинается с расчета трех ключевых метрик для каждого клиента в базе данных. Эти метрики позволяют количественно оценить поведение клиента и являются основой для последующей сегментации.

Три столпа RFM-анализа

Recency (Давность) — показывает, как давно клиент совершил последнюю покупку. Измеряется либо как дата последнего заказа, либо как количество дней с момента последней покупки до текущей даты (или даты анализа). Клиенты с меньшим значением Recency (покупавшие недавно) более вовлечены и с большей вероятностью откликнутся на маркетинговые предложения.

Frequency (Частота) — количество транзакций или заказов клиента за анализируемый период. Высокая частота указывает на лояльность и регулярность взаимодействия с брендом. Клиенты с высокой частотой обычно более предсказуемы в своем поведении.

Monetary (Денежная ценность) — суммарная выручка от клиента за весь период или средний чек. Показывает, сколько денег клиент принес бизнесу. Клиенты с высоким Monetary являются наиболее ценными с точки зрения дохода.

Практический расчет метрик

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

WITH rfm_metrics AS (
    SELECT 
        customer_id,
        -- Recency: дата последней покупки
        MAX(order_date) AS last_purchase_date,
        -- Количество дней с последней покупки (пример для фиксированной даты)
        DATEDIFF(day, MAX(order_date), '2024-12-31') AS recency_days,

        -- Frequency: количество заказов
        COUNT(DISTINCT order_id) AS frequency,

        -- Monetary: общая сумма покупок
        SUM(amount) AS monetary_value
    FROM orders
    WHERE order_date >= '2023-01-01'  -- анализируем год данных
    GROUP BY customer_id
)
SELECT * FROM rfm_metrics
ORDER BY monetary_value DESC
LIMIT 10;

В этом запросе мы группируем все заказы по customer_id и для каждого клиента вычисляем: - MAX(order_date) — дата последней покупки - DATEDIFF() или вычитание дат — разница в днях между последней покупкой и датой анализа - COUNT(DISTINCT order_id) — количество уникальных заказов - SUM(amount) — суммарная выручка от клиента

Работа с датами

Расчет Recency требует вычисления разницы между датами. Синтаксис может различаться в зависимости от СУБД:

PostgreSQL:

CURRENT_DATE - MAX(order_date) AS recency_days
-- или
DATE_PART('day', CURRENT_DATE - MAX(order_date)) AS recency_days

ClickHouse:

dateDiff('day', MAX(order_date), today()) AS recency_days

SQLite:

julianday('2024-12-31') - julianday(MAX(order_date)) AS recency_days

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

Обработка граничных случаев

При расчете метрик следует учитывать особые случаи:

• Клиенты с одной покупкой: Frequency = 1, что нормально для новых клиентов
• Возвраты и отмены: Решите, включать ли отмененные заказы в расчет Frequency и как обрабатывать возвраты в Monetary (вычитать или исключать)
• Нулевые суммы: Заказы с amount = 0 (например, подарочные или промо) могут искажать метрики

-- Пример с фильтрацией
SELECT 
    customer_id,
    MAX(order_date) AS last_purchase_date,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(amount) AS monetary_value
FROM orders
WHERE 
    status = 'completed'  -- только завершенные заказы
    AND amount > 0        -- исключаем нулевые суммы
GROUP BY customer_id
HAVING COUNT(DISTINCT order_id) >= 1;  -- минимум одна покупка

Ключевой вывод: Корректный расчет RFM-метрик требует понимания бизнес-логики и структуры данных. Используйте агрегатные функции (MAX, COUNT, SUM) в сочетании с GROUP BY, а для Recency применяйте функции работы с датами. Полученные метрики станут основой для присвоения скоров и создания сегментов на следующем этапе.