RFM Segmentation Spreadsheet: Build Five Usable Segments

An RFM segmentation spreadsheet can replace weeks of analytics work. Export 12 months of orders, calculate three scores, map every customer into five exclusive segments, then refresh monthly. That is enough for most teams to make better retention decisions without a warehouse or predictive model.

Do not optimize for analytical elegance. Optimize for a file one operator can refresh in under an hour and use without interpretation meetings. If a segment does not change treatment, delete it.

Build the RFM Segmentation Spreadsheet

Create an Orders sheet with customer ID in column A, order date in B, net revenue after refunds in C, and order status in D. Use completed orders only. Exclude tax, shipping, cancellations, gift-card purchases, and unidentifiable guest orders where possible.

Create a Customers sheet with one row per stable customer ID. Put a fixed scoring date in B1. Assuming the customer ID is in A2, calculate recency with =B$1-MAXIFS(Orders!B:B,Orders!A:A,A2,Orders!D:D,"Completed"). Calculate frequency with =COUNTIFS(Orders!A:A,A2,Orders!D:D,"Completed"). Calculate monetary value with =SUMIFS(Orders!C:C,Orders!A:A,A2,Orders!D:D,"Completed").

Keep the fixed scoring date instead of TODAY(). Otherwise, two people opening the same file on different dates can produce different segments. Use a 12-month order window when normal repurchase occurs within 90 days. Test 18 or 24 months only when the median interval between orders exceeds six months.

Data trap: one refunded wholesale order can create a false Champion. Duplicate profiles can turn a five-order buyer into three weak buyers. Before scoring, inspect at least 20 customer records: the five highest monetary values, five highest frequencies, five longest recencies, and five random rows.

Score Quintiles Without Splitting Ties

Store the 20th, 40th, 60th, and 80th percentiles for each metric in visible cells. For frequency in column C, the thresholds are =PERCENTILE.INC(C:C,0.2), then 0.4, 0.6, and 0.8. Repeat for monetary value and recency.

For frequency or monetary value, assign scores with =1+(C2>$H$2)+(C2>$H$3)+(C2>$H$4)+(C2>$H$5), where H2:H5 contains the four thresholds. For recency, where lower is better, use =5-(B2>$G$2)-(B2>$G$3)-(B2>$G$4)-(B2>$G$5).

The strict greater-than comparisons keep equal values together. That matters when thousands of customers have exactly one order. Quintiles may become uneven; accept that. Forcing exactly 20% into each bucket splits identical customers for no operational reason.

If you would rather not build the sheet, the RFM segmentation tool runs these same formulas on an orders CSV in your browser: the same PERCENTILE.INC quintiles, the same strict comparisons, and the same five segments. Nothing is uploaded.

Keep R, F, and M in separate columns. Do not sum them. A 155 customer bought recently but rarely; a 551 customer bought often and spent heavily but has gone quiet. Both total 11. They need opposite treatment.

Scoring trap: turning all 125 combinations into campaigns. Preserve the three-digit score for analysis, then collapse it for execution. Below roughly 500 identifiable customers, start with three score bands rather than five because small percentile shifts can move too many customers between buckets.

Map Every Score Into Five Exclusive Segments

Apply these rules in order: Champions first, Loyal Customers second, Promising Customers third, At-Risk Customers fourth, Hibernating Customers fifth. The order prevents overlaps and provides a fallback for every possible score.

In this sequence, Champions have R, F, and M of at least 4. Loyal Customers have R and F of at least 3 after Champions are removed. Promising Customers have R of at least 3 and F of 1 or 2. At-Risk Customers have R of 1 or 2 and F of at least 3. Hibernating Customers have R and F of 1 or 2.

If R, F, and M are in E2:G2, use =IF(AND(E2>=4,F2>=4,G2>=4),"Champions",IF(AND(E2>=3,F2>=3),"Loyal Customers",IF(AND(E2>=3,F2<=2),"Promising Customers",IF(AND(E2<=2,F2>=3),"At-Risk Customers","Hibernating Customers")))).

Test boundary rows before rollout. A 555 is Champion. A 431 is Loyal because frequency is 3. A 112 is Hibernating. A 353 is Loyal, not Champion. A 235 is At-Risk because inactivity overrides historical spend.

Mapping trap: assuming a segment deserves a campaign because it exists. Calculate reachable customers and expected outcomes first. If baseline conversion is 8%, a cell of 500 customers produces about 40 expected conversions; a 10% holdout contains 50 customers and only four expected conversions. That holdout is too thin for confident lift measurement. Pool several monthly cycles, enlarge the holdout, or combine treatments.

Assign One Job, Then Measure Migration

• Champions: protect 90-day repeat rate. Prioritize recognition, service recovery, and benefit use over blanket discounts.
• Loyal Customers: shorten median time between orders. Compare the new interval with their own prior 90-day baseline.
• Promising Customers: drive order two within 30–45 days, adjusted to the category’s observed reorder interval.
• At-Risk Customers: trigger treatment after 1.5 times the customer or category median reorder interval, not an arbitrary calendar date.
• Hibernating Customers: limit acquisition cost. Use low-cost channels; suppress chronic non-openers.

Treatment trap: sending every segment a renamed 15% discount. That changes copy, not strategy. Champions need reliability. Promising buyers need confidence. At-Risk buyers need a timely reason to return. Use 5–10% holdouts only when expected conversion counts support a useful comparison; otherwise rotate treatment by month.

Save one snapshot every 30 days. Use columns for customer ID, snapshot date, previous segment, current segment, previous RFM score, current RFM score, reachable status, treatment, holdout flag, next-90-day orders, and next-90-day net revenue. Report upward, flat, and downward migration alongside customer counts.

Judge the model over rolling 90-day windows. Clicks and redemptions diagnose campaign execution; they do not prove retention. A Promising customer moving to Loyal is success. An At-Risk customer making one discounted purchase but remaining At-Risk is weaker than the campaign dashboard claims.

Keep RFM simple until migration stops explaining revenue differences. Then add one field—category, margin, or predicted reorder date—and test whether it changes action. Ground that decision in the retention math behind LTV, churn, and repeat rate, not demand for a prettier dashboard.