RFM Analysis: How to Segment Customers in a Spreadsheet

RFM analysis is a way to segment customers by their buying behaviour. Each customer gets a score for Recency (how long since the last purchase), Frequency (how many purchases) and Monetary value (how much they spent). Customers with similar scores form segments, such as champions, new customers or former regulars at risk, and each segment gets a different action. All you need is an order export and a spreadsheet; a first version takes an afternoon.

The method is old and simple, and that is its strength: it uses data every shop or service business already has, and it answers a practical question: who should we contact this month, and with what? This guide shows the formulas, a scoring scheme for small customer bases, a worked example with ten customers and the action for each segment.

What R, F and M measure

Dimension	Measured as	Question it answers
Recency (R)	Days since the last purchase	Is this customer still active?
Frequency (F)	Number of purchases in the analysis window	Is buying from us a habit?
Monetary (M)	Total spend in the analysis window	How much is the relationship worth?

Recency is usually the strongest signal of whether someone will buy again. Frequency shows habit. Monetary value helps you decide how much personal effort a customer deserves. Read together, they separate a loyal regular who has gone quiet (high F, low R) from a new customer who has just started (high R, low F), two people who need very different messages.

When RFM is worth doing

RFM fits businesses where customers buy repeatedly at irregular intervals: shops, online stores, restaurants with ordering accounts, salons, wholesalers, service firms with recurring jobs. It is less useful when:

• almost every customer buys once (there is little frequency to score),
• customers pay a fixed subscription (frequency and spend are the same for everyone),
• you have only a few dozen customers (you can review them by hand).

Step 1: Export the order data

You need one row per order with three columns: customer ID, order date and order amount. Choose an analysis window that covers at least one full buying cycle, often 12 or 24 months.

Before scoring, clean the data:

• Remove cancelled and refunded orders, test orders and internal accounts.
• Merge duplicate customers (the same person with two email addresses or a guest checkout). Our guide on what data to store in a CRM covers the hygiene rules that prevent duplicates in the first place.
• If your margins differ a lot between products, consider using gross profit instead of revenue for M.

Step 2: Calculate R, F and M for each customer

Put the orders in a sheet called Orders (A: customer ID, B: date, C: amount) and create a second sheet with one row per customer. In Google Sheets or Excel 365:

Column	Formula (row 2)
A: Customer ID	=UNIQUE(Orders!A2:A5000)
B: Recency (days)	=DATE(2026,9,30)-MAXIFS(Orders!B:B,Orders!A:A,A2)
C: Frequency (orders)	=COUNTIF(Orders!A:A,A2)
D: Monetary (total spend)	=SUMIF(Orders!A:A,A2,Orders!C:C)

Adjust the range in column A to the number of orders you have. Use a fixed reference date, here 30 September 2026, rather than TODAY(). Otherwise every number changes each day and you cannot reproduce last month's segments.

Step 3: Score each dimension

There are two common ways to turn raw values into scores.

Option A: Quintiles (1–5)

Each score covers 20% of customers. The most recent fifth gets R = 5, the most frequent fifth F = 5, the highest-spending fifth M = 5. With customers in rows 2 to 1001:

• F score: =MIN(5,1+INT(PERCENTRANK.INC(C$2:C$1001,C2)*5))
• M score: same formula on column D
• R score: =6-MIN(5,1+INT(PERCENTRANK.INC(B$2:B$1001,B2)*5)) (reversed, because fewer days is better)

Quintiles adapt automatically to your data and work well from several hundred customers upwards. Their weakness shows when many customers have the same value: if 60% of customers bought exactly once, they cannot be split into neat fifths, and the scores become lumpy.

Option B: Three levels with fixed thresholds

For smaller customer bases, three levels per dimension are easier to understand and to explain to your team. Set the thresholds from your buying cycle, in the same way you would choose a lapse window for calculating churn. An example for a shop whose customers buy every few months:

Score	Recency	Frequency (24 months)	Monetary (24 months)
3	0–90 days	5 or more orders	$800 or more
2	91–270 days	2–4 orders	$200–$799
1	more than 270 days	1 order	under $200

As formulas: =IF(B2<=90,3,IF(B2<=270,2,1)) for R, =IF(C2>=5,3,IF(C2>=2,2,1)) for F and =IF(D2>=800,3,IF(D2>=200,2,1)) for M. The rest of this guide uses this three-level scheme.

Step 4: Turn scores into segments

Three levels for three dimensions give 27 combinations, too many to act on. A practical shortcut: build the segments from R and F, and use M to decide who gets personal attention within each segment.

	F1 (1 order)	F2 (2–4 orders)	F3 (5+ orders)
R3 (0–90 days)	New	Promising	Champions
R2 (91–270 days)	Cooling one-timers	Needs attention	Slipping regulars
R1 (270+ days)	Lost	Hibernating	At risk

In the spreadsheet, combine the two scores into a code with =E2&F2 (for example "31") and look up the segment name in a small table with VLOOKUP.

Worked example: ten customers

A homeware shop runs the analysis on 30 September 2026. The figures are hypothetical.

Customer	Recency (days)	Orders	Spend	R-F-M	Segment
A	12	9	$1,450	3-3-3	Champions
B	45	6	$620	3-3-2	Champions
C	20	1	$85	3-1-1	New
D	130	4	$540	2-2-2	Needs attention
E	310	7	$1,120	1-3-3	At risk
F	400	1	$60	1-1-1	Lost
G	75	2	$310	3-2-2	Promising
H	200	1	$950	2-1-3	Cooling one-timers
I	290	3	$260	1-2-2	Hibernating
J	8	5	$180	3-3-1	Champions

Three customers stand out. E was a regular who spent more than almost anyone and has not ordered for ten months: the most valuable person to call this week. H bought once, but for $950; a personal thank-you and a relevant suggestion are worth more here than an automated email. C is new and needs a reason for a second order soon, because the step from one purchase to two is where most customers are lost.

Step 5: Decide what to do with each segment

Segment	Goal	Action
Champions	Keep and appreciate	Thank them, early access, ask for a review or referral. Avoid discounts they do not need.
Promising	Build the habit	Useful tips, a reminder at the typical repurchase time, a small loyalty reward
New	Win the second purchase	Follow-up after delivery, help with first use, see the customer onboarding checklist
Slipping regulars	Find out early	Personal check-in: anything we could do better?
Needs attention	Re-engage	Reminder of what is new or relevant to what they bought
Cooling one-timers	Learn and nudge	One short question about their first experience; a suggestion based on their purchase
At risk	Win back personally	Personal contact from the owner or account manager, see the customer churn warning signs playbook
Hibernating	Low-cost reactivation	A short win-back email campaign
Lost	Decide whether to try	One final automated message, then stop emailing

Within each segment, sort by M and give the highest spenders personal attention first. That is where the monetary score earns its place.

Step 6: Store the segment and repeat

Add a field "RFM segment" and a field "segment date" to your CRM or customer list, and refresh both monthly or quarterly with the same thresholds. Then watch the movements, not just the snapshot:

• How many "New" customers became "Promising" within 90 days?
• How many "Champions" slipped to "Slipping regulars"?
• Did customers contacted in "At risk" return?

These movements show whether your retention work is effective, similar to a cohort retention analysis but grouped by behaviour instead of start date. For customers who bought only once, the repeat purchase rate gives you the matching headline figure.

What RFM does not tell you

• Why customers behave as they do. RFM shows that a regular has gone quiet, not whether they moved, found a cheaper supplier or had a bad experience. You still have to ask.
• Future value. Monetary value looks back. To estimate what a segment is worth going forward, take its average order value, purchase frequency and margin and enter them in the customer lifetime value calculator.
• Profit. High-spending customers who only buy discounted items may be worth less than they look. Use gross profit for M if that matters in your business.
• B2B buying centres. If several people order for one company, score the company, not the individuals.

Common mistakes

• Scoring dirty data. Duplicate customers turn one regular into several one-time buyers and scatter them across the wrong segments.
• Too many segments. If you cannot name a different action for a segment, merge it with its neighbour.
• A window shorter than the buying cycle. Customers who buy twice a year look lost in a three-month window.
• Changing thresholds every time. Movements between segments are only meaningful with stable rules.
• Using RFM only for discounts. Champions need recognition, new customers need help, at-risk customers need a conversation. Most segments do not need a coupon.

Your next step

Export 24 months of orders, clean out refunds and duplicates, and calculate recency, frequency and spend for every customer with the formulas above. Score them with three levels, build the R-F grid and pick the two segments with the clearest action, usually "At risk" and "New". Contact those customers this month, note the segment in your CRM, and run the analysis again in three months to see who moved.