Как посчитать RFM-сегментацию в SQL

Зачем RFM

RFM-сегментация — простой и эффективный способ разделить customers на группы для marketing. «Champions» (high R+F+M) ≠ «At Risk» (low R, был F+M). Разные actions для каждого segment.

Формула

Recency  = дней с последней покупки (меньше = лучше)
Frequency = кол-во покупок за период
Monetary  = revenue за период

R/F/M score = NTILE(5) каждого
RFM_score   = R*100 + F*10 + M (например, 555 — best)

Базовый расчёт

WITH user_rfm AS (
    SELECT
        user_id,
        EXTRACT(DAY FROM CURRENT_DATE - MAX(created_at))::INT AS recency_days,
        COUNT(*) AS frequency,
        SUM(total) AS monetary
    FROM orders
    WHERE status = 'paid'
      AND created_at >= CURRENT_DATE - INTERVAL '365 days'
    GROUP BY user_id
),
rfm_scored AS (
    SELECT
        user_id,
        recency_days,
        frequency,
        monetary,
        NTILE(5) OVER (ORDER BY recency_days)         AS r_score,
        NTILE(5) OVER (ORDER BY frequency DESC)       AS f_score,
        NTILE(5) OVER (ORDER BY monetary DESC)        AS m_score
    FROM user_rfm
)
SELECT
    user_id,
    r_score,
    f_score,
    m_score,
    r_score * 100 + f_score * 10 + m_score AS rfm_score
FROM rfm_scored
ORDER BY rfm_score DESC;

NTILE: 1 — best 20%, 5 — worst 20%. Note: для recency меньше = лучше, поэтому ORDER BY recency_days ASC.

Сегменты

Стандартные RFM-сегменты:

WITH scored AS (
    -- ... (RFM scoring выше)
)
SELECT
    user_id,
    CASE
        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
        WHEN r_score >= 4 AND f_score >= 3 THEN 'Loyal'
        WHEN r_score >= 3 AND f_score <= 3 THEN 'Potential'
        WHEN r_score <= 3 AND f_score >= 4 THEN 'At Risk'
        WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost'
        ELSE 'Other'
    END AS segment
FROM scored;

Применение

• Champions: VIP-программа, exclusive deals.
• Loyal: referral incentives.
• Potential: cross-sell, increase frequency.
• At Risk: win-back campaigns, discounts.
• Lost: aggressive re-engagement или write off.

Частые ошибки

Ошибка 1. NTILE на equal values. Если много users с same monetary, NTILE assignment может быть suboptimal.

Ошибка 2. RFM score без context. 255 vs 552 — same numbers, different segments. Use named segments, not raw score.

Ошибка 3. Window mismatch. Recency считается от today. Если данные «застывшие» — recency меняется без real change.

Ошибка 4. Period for F+M. 30, 90, 365 days? Период должен соответствовать продукту.

Ошибка 5. Применять без personalization. Champions получают spam discounts → их LTV падает. Champions нужны exclusive offers, не commodity.

Связанные темы

• RFM-анализ
• SQL для RFM-анализа
• Как посчитать frequency в SQL
• Как посчитать LTV в SQL

FAQ

Why NTILE(5) and not NTILE(10)?

NTILE(5) проще для interpretation: 5 buckets. NTILE(10) — granularity, но 1000 categories total.

RFM для B2B?

Работает, но adjust: F и M по аккаунтам, не users.

RFM static или dynamic?

Динамичный. Re-calculate weekly / monthly.

How to update segments?

Standard: weekly. High-frequency продукт: daily.

RFM vs ML segmentation?

RFM — простой и interpretable. ML — точнее, но black-box.