Build an RFM‑based segmentation model by scoring each customer on Recency, Frequency, and Monetary, then clustering the scores into actionable segments.
Step‑by‑step implementation (R)
1. Extract raw events – pull the last 12 months of transaction data from your warehouse.
```sql
SELECT customer_id, order_date, order_amount
FROM sales.orders
WHERE order_date >= CURRENT_DATE - INTERVAL '12 months';
```
2. Compute RFM metrics – use dplyr to aggregate.
```r
library(dplyr)
rfm <- orders %>%
group_by(customer_id) %>%
summarise(
recency = as.numeric(difftime(max(order_date), Sys.Date(), units = "days")),
frequency = n(),
monetary = sum(order_amount)
)
```
3. Score each dimension – quintile ranking (1 = best, 5 = worst) for Recency (inverse), Frequency, Monetary.
```r
rfm_scored <- rfm %>%
mutate(
r_score = ntile(-recency, 5),
f_score = ntile(frequency, 5),
m_score = ntile(monetary, 5),
rfm_score = paste0(r_score, f_score, m_score)
)
```
4. Choose segmentation logic – either pre‑defined RFM buckets or unsupervised clustering. Example bucket table:
| RFM Score | Segment |
|-----------|--------------------|
| 111‑122 | Champions |
| 131‑222 | Loyal Customers |
| 311‑422 | At‑Risk |
| 511‑555 | Lost |
5. Cluster with K‑means (optional) – if you prefer data‑driven groups, scale scores and run K‑means.
```r
library(cluster)
set.seed(42)
kmeans_res <- kmeans(rfm_scored %>% select(r_score, f_score, m_score), centers = 4)
rfm_scored$cluster <- kmeans_res$cluster
```
6. Validate segments – compute lift on response rate for a recent campaign.
```r
lift <- rfm_scored %>%
group_by(cluster) %>%
summarise(response_rate = mean(campaign_response))
```
7. Deploy – export customer_id, segment (or cluster) to your CDP via API.
```bash
curl -X POST https://api.cdp.example/v1/segments \
-H "Authorization: Bearer $TOKEN" \
-H "Content-Type: application/json" \
-d @segments.json
```
Checklist before launch
- [ ] Data freshness ≤ 24 h.
- [ ] Recency threshold aligns with purchase cycle (e.g., 30 days for fast‑moving goods).
- [ ] Minimum frequency of 2 purchases to avoid noise.
- [ ] Monetary outliers trimmed at 99th percentile.
- [ ] Segment names match downstream automation rules.