RFM Analysis Implementation.

// 01Why RFM still works

40+ year old method. Despite modern data science, still core to e-commerce segmentation — because:

• Explainable. Every segment's reason for being there is transparent.
• Low data needs. An order table suffices; no ML pipeline required.
• Action-tied. Each segment has a defined what-to-do.
• Manual-trackable. Monthly refresh becomes routine.

Definition: RFM Analysis glossary.

// 02Data prep

RFM only needs an order table. Columns:

• customer_id
• order_date
• order_value (with or without tax/shipping — be consistent)

Shopify, WooCommerce, BigCommerce, all e-com platforms can export these three. Last 12-24 months is enough; under 6 months and the recency band is too narrow.

Cleanup

1. Exclude refunded orders (or net them in order_value).
2. Filter test orders (admin / dev emails).
3. Merge duplicate accounts (same person, multiple emails).

// 03RFM scoring in Excel

Step 1: customer aggregation

Pivot table:

• Rows: customer_id.
• Values:
  • Max(order_date) — last order.
  • Count(order_date) — order count.
  • Sum(order_value) — total spend.

Step 2: derive R, F, M raw values

• R_raw = TODAY() − last_order_date (days).
• F_raw = order_count.
• M_raw = total_spend.

Step 3: quintile scoring

Use Excel PERCENTILE for 5 quintile cutoffs per dimension.

• R: closest 20% → R=5, farthest 20% → R=1.
• F: most frequent 20% → F=5, rarest → F=1.
• M: highest spend 20% → M=5, lowest → M=1.

Concatenate: =R&F&M → "555" format.

// 048 actionable segments

Reduce 125 possible combos (5×5×5) to 8 meaningful segments:

Segment	RFM Code	Typical %	Meaning
Champions (VIP)	555, 554, 545, 455	4-8%	Most valuable: recent, frequent, high-spend.
Loyal Customers	5XX, X5X (high F)	8-15%	Buying steadily, not VIP-tier.
New Customers	5X1, 5X2 (high R, low F)	10-20%	Recently first purchased.
At Risk	X5X-X4X with low R	5-10%	Frequent past, recent silence.
Hibernating	2XX-3XX	15-25%	Quiet for a while, not yet lost.
Lost	11X-12X	10-20%	Long absent, low frequency.
New Low-Spend	551, 541	5-10%	New but low AOV; upsell target.
One-Big-Purchase	515, 514	3-7%	High-value but one-time.

// 05Segment-level campaign strategies

Champions

• Early access (new launches, exclusives).
• Personalized content (founder note, behind-the-scenes).
• Auto-tier in loyalty.
• Frequency: 1-2/week max; don't fatigue.

Loyal Customers

• Cross-sell into adjacent categories.
• Referral program invite.
• Subscription / auto-replenish offer.

New Customers

• Onboarding email series (3-5 messages, days 0-30).
• Second-order recommendation (from first-purchase category).
• Brand story + value content.

At Risk

• Proactive winback: "We miss you" + personalized offer.
• Survey: why did they stop? open feedback.
• Personal coupon (universal, 10-15%).

Hibernating

• Content-led reactivation: blog, education.
• New-collection announcement.
• Seasonal campaign push.

Lost

• One-shot aggressive winback (25-40% off).
• If silent: archive, stop spending budget.

New Low-Spend

• Premium-category recommendation (upsell).
• Bundle offers (raise basket).

One-Big-Purchase

• Satisfaction survey.
• Adjacent-category recommendation.
• Subscription convert (turn one-time into recurring).

// 06Modern RFM (RFM+) extensions

Additional dimensions on classic RFM:

• RFML — Length (relationship duration). 5-year customer behaves differently than 1-year.
• RFMP — Profit (margin, not just revenue). Prioritize high-margin segments.
• RFM-Channel — acquisition channel dimension. Meta-acquired ≠ Google-acquired LTV.
• RFM-Predictive — adds future-purchase probability (BG/NBD model).

Multi-dimensional grows complex; most brands start classic and extend on demand.

// 07Monthly refresh + segment movement

RFM isn't one-time; refresh monthly. Segment movement is the most valuable insight:

• Champions → At Risk: critical alert; proactive campaign immediately.
• New → Loyal: onboarding works; replicate for similar cohorts.
• Hibernating → Active: winback worked; generalize the campaign.

Maintain a Last-Month × This-Month matrix; 30 minutes monthly turns into year-end profit.

// 08RFM pitfalls

• Insufficient for new businesses: under 6 months data, quintile cutoffs are noise.
• Seasonal blindness: Black Friday-missing customers fall into At Risk; need season-aware filters.
• Static segments. Without monthly refresh it doesn't drive decisions.
• One-list email. Doing RFM then sending the same message to everyone is wasted work.
• Ignoring margin. If high-M segment buys low-margin categories, "VIP" misleads.