RFM scores customers on recency, frequency and monetary value, and was built for consumer mail-order. This page shows how to compute RFM in Excel from an order export, with the exact formulas for the three measures and the 1 to 5 scores, then explains why the consumer version misleads in B2B, where customers order on very different cadences, and gives the three changes that fix it: recency against the customer's own cadence, frequency as a trend against the customer's own history, and monetary value read against a norm for similar customers.
RFM is three columns and three scores. It was designed for catalogue retailers in the 1990s and is still the most searched customer segmentation method. It works in B2B after three changes. This page gives the plain version in Excel, then the changes.
Sheet Orders, one row per order line: customer in A, order date in B, amount in C. On Calc: as-of date in B1; period start in B2, twelve months before the as-of date; prior period start in B3, twenty-four months before.
Customer list in A6 down, from UNIQUE or Remove Duplicates.
Recency in days, B6:
=$B$1-MAXIFS(Orders!B:B,Orders!A:A,A6)
Frequency, distinct order dates in the last twelve months, C6, Microsoft 365:
=IFERROR(ROWS(UNIQUE(FILTER(Orders!B$2:B$50000,(Orders!A$2:A$50000=A6)*(Orders!B$2:B$50000>=$B$2)))),0)
Monetary, D6:
=SUMIFS(Orders!C:C,Orders!A:A,A6,Orders!B:B,">="&$B$2)
Scores from 1 to 5 by quintile. Recency is reversed, because fewer days is better. R score, E6:
=6-MIN(5,INT(PERCENTRANK.INC($B$6:$B$5000,B6)*5)+1)
F score, F6:
=MIN(5,INT(PERCENTRANK.INC($C$6:$C$5000,C6)*5)+1)
M score, G6: the same on column D. The RFM cell is the three digits together:
=E6&F6&G6
A customer at 555 bought recently, often, and a lot. One at 155 used to and has stopped.
| Customer | Pattern | Plain RFM | What is really happening |
|---|---|---|---|
| A | Orders weekly; last order 5 weeks ago | R3 F5 M5 | Has missed five orders. The most urgent account in the book, scored as fine |
| B | Orders every six months; last order 4 months ago | R1 F1 M3 | Completely normal. Scored as nearly lost |
| C | Largest account; revenue down 30 percent on last year | R5 F5 M5 | Declining fast. Scored as perfect |
| D | Small account, buys every category at full share | R4 F3 M1 | Nothing more to win. Scored as weak |
| E | Mid-size, buys one category of six | R4 F4 M3 | Largest growth opportunity in the book. Scored as average |
Every row is scored wrongly for the same reason: RFM compares each customer with all the others, and B2B customers are not comparable on raw recency, frequency or size.
Count of distinct order dates over twenty-four months in I6. Typical gap between orders, H6, from first and last order and that count:
=IF(I6>=3,(MAXIFS(Orders!B:B,Orders!A:A,A6)-MINIFS(Orders!B:B,Orders!A:A,A6,Orders!B:B,">="&$B$3))/(I6-1),NA())
Recency ratio, J6:
=B6/H6
A ratio of 1 means the next order is due about now. Above 2.5 is dormant. Customer A is at 5; customer B is at 0.7. Score the ratio, not the days. The dormant customers guide builds this column in full.
Revenue in the prior twelve months, K6:
=SUMIFS(Orders!C:C,Orders!A:A,A6,Orders!B:B,">="&$B$3,Orders!B:B,"<"&$B$2)
Trend, L6:
=IF(K6>0,D6/K6-1,"new")
Customer C shows minus 30 percent here, whatever its size. Do the same for order count if frequency matters on the desk.
What the customer spends means little without what a customer like them could spend. Group customers by segment, such as trade, size band or sector, and compare each customer with the median of the well-developed customers in its group, by category. That is share of wallet, and it is what separates customer D, fully developed, from customer E, one category of six. The share of wallet guide and the norm guide cover the method; it needs a segment column and a category column in the export, and is the step where a spreadsheet starts to strain.
| Customer | Recency ratio | Trend | Share against norm | Action |
|---|---|---|---|---|
| A | 5.0 | −8% | 85% | Call today: missed five orders |
| B | 0.7 | +2% | 60% | Nothing due; cross-sell at next order |
| C | 0.9 | −30% | 70% | Review: largest account, declining |
| D | 0.8 | +1% | 98% | Protect at low cost |
| E | 1.0 | +5% | 25% | Grow: five categories unbought |
Three columns, as before. Each now compares the customer with itself or with its peers, and each row has an action.
| Segment | Rule | Action |
|---|---|---|
| At risk | Recency ratio above 2, or trend below −20 percent, on a customer of meaningful value | A call this week, from the owner |
| Grow | Recency and trend healthy; share against norm under 50 percent | Category conversation at next contact |
| Protect | Healthy on all three; high value | Keep cadence; senior contact |
| Maintain | Low value, low gap | Low-cost service |
Plain scores used as they come. The weekly buyer who stopped is R3; nobody calls.
Quintiles on a skewed base. Monetary scores put the top fifth together when the top 2 percent are a different kind of customer.
Eleven segments with names. Champions, hibernating, about to sleep: borrowed from consumer retail, with no owner and no action.
Run once. RFM is a snapshot. The adjusted version is useful weekly, because recency ratio moves weekly.
Plain RFM is an afternoon in Excel. The adjusted version, with per-customer cadence, trend and a norm by segment and category, refreshed weekly with owners attached, is a coverage intelligence system by another name; see the four signs a spreadsheet is no longer enough and the B2B segmentation guide. Covirage computes the three adjusted columns for every account from the order export, and turns the at-risk and grow segments into ranked lists with owners.
As a first pass, yes: it takes an hour in a spreadsheet and gives every customer a position. The adjusted version is what makes it useful. The plain scores mostly rediscover which customers are big and which order often, which the business already knew.
Keep it to a handful with an action each. High on all three: protect. High monetary, recency slipping against own cadence: call this week. High frequency trend, low value against norm: grow. Low on everything: serve at low cost. Eleven named segments with clever labels are harder to act on than four.
Twenty-four months of orders, so that customers with long cadences have enough history for a typical gap, with monetary value and frequency measured on the most recent twelve and compared with the twelve before.