RFM - самый простой и эффективный способ сегментировать клиентскую базу. 3 метрики → 11 сегментов → персонализированные действия для каждого.
#intermediate #block-09
Что такое RFM
| Метрика | Вопрос | Что измеряет |
|---|---|---|
| Recency | Когда последняя покупка? | Свежесть (чем недавнее, тем лучше) |
| Frequency | Как часто покупает? | Лояльность |
| Monetary | Сколько тратит? | Ценность |
Расчёт
Для каждого клиента:
- R = дней с последней покупки
- F = количество покупок за период
- M = сумма покупок за период
Каждому присваивается score 1-5 (quintile):
| Score | Recency | Frequency | Monetary |
|---|---|---|---|
| 5 | Последняя покупка 1-7 дней назад | 10+ покупок | Top 20% по сумме |
| 4 | 8-30 дней | 6-9 покупок | 20-40% |
| 3 | 31-60 дней | 3-5 покупок | 40-60% |
| 2 | 61-120 дней | 2 покупки | 60-80% |
| 1 | 120+ дней | 1 покупка | Bottom 20% |
RFM-сегменты
| Сегмент | R | F | M | Действие |
|---|---|---|---|---|
| Champions | 5 | 5 | 5 | Exclusive offers, loyalty program, referral |
| Loyal | 4-5 | 4-5 | 3-5 | Upsell, cross-sell, early access |
| Potential Loyalists | 4-5 | 2-3 | 2-3 | Engagement programs, loyalty incentives |
| New Customers | 5 | 1 | 1-2 | Onboarding, welcome series, first purchase offer |
| Promising | 3-4 | 1-2 | 1-2 | Nurture, education, brand building |
| Need Attention | 3 | 3-4 | 3-4 | Personalized offers, remind of value |
| About to Sleep | 2-3 | 2-3 | 2-3 | Win-back campaign, special discount |
| At Risk | 2 | 4-5 | 4-5 | ⚠️ Urgent: personal outreach, big incentive |
| Can't Lose | 1 | 4-5 | 4-5 | 🚨 Most valuable churners: CEO email, phone call |
| Hibernating | 1-2 | 1-2 | 1-2 | Last-chance offer, then archive |
| Lost | 1 | 1 | 1 | Remove or minimal effort |
SQL для RFM
WITH rfm AS (
SELECT
customer_id,
DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) AS recency,
COUNT(*) AS frequency,
SUM(revenue) AS monetary,
NTILE(5) OVER (ORDER BY DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) ASC) AS r,
NTILE(5) OVER (ORDER BY COUNT(*) ASC) AS f,
NTILE(5) OVER (ORDER BY SUM(revenue) ASC) AS m
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
)
SELECT
customer_id, recency, frequency, monetary, r, f, m,
CONCAT(CAST(r AS STRING), CAST(f AS STRING), CAST(m AS STRING)) AS rfm_score,
CASE
WHEN r >= 4 AND f >= 4 AND m >= 4 THEN 'Champions'
WHEN r >= 4 AND f >= 3 THEN 'Loyal'
WHEN r >= 4 AND f <= 2 THEN 'New Customers'
WHEN r = 3 AND f >= 3 THEN 'Need Attention'
WHEN r <= 2 AND f >= 4 THEN 'At Risk'
WHEN r <= 2 AND f <= 2 THEN 'Hibernating'
ELSE 'Other'
END AS segment
FROM rfm;
RFM → Marketing Actions
| Сегмент | Push | Ad Audience | Offer | |
|---|---|---|---|---|
| Champions | VIP newsletter | Early access | Exclude from acquisition | Referral program |
| At Risk | Win-back series | Re-engagement | Retargeting | Personal discount 20% |
| New Customers | Welcome series | Onboarding tips | Lookalike (seed) | Second purchase incentive |
| Hibernating | Last-chance email | - | Suppress (save budget) | Deep discount or remove |
🔧 Практика
- Запросите данные из CRM/базы заказов (customer_id, order_date, revenue)
- Рассчитайте RFM scores (SQL или Google Sheets)
- Создайте 5 сегментов
- Для каждого: определите email sequence + ad audience + offer