Customer segmentation Excel template
RFM segmentation groups customers by three numbers: days since the last purchase (R), number of orders (F) and total revenue (M), each scored from 1 to 5. This free Excel template calculates the scores with formulas, places every customer in one of nine segments, from Champions to Lost, and summarises the customer count and revenue share of each segment.
Excel file (.xlsx), five sheets, formulas ready for 2,000 customers.
We confirm your email address with a code. The file downloads and a copy arrives in your inbox.
Paste the list, the segment appears next to it
The table comes from the sample data in the file. Paste your own customers into the same four columns and the scores and segments recalculate.
| Customer | Last purchase date | Orders | Total revenue | Days | R | F | M | RFM | Segment |
|---|---|---|---|---|---|---|---|---|---|
| Sample customer 01 | 10/27/2026 | 24 | 486,000 | 3 | 5 | 5 | 5 | 555 | Champions |
| Sample customer 02 | 10/21/2026 | 18 | 352,500 | 9 | 5 | 5 | 5 | 555 | Champions |
| Sample customer 03 | 10/15/2026 | 3 | 41,800 | 15 | 4 | 2 | 3 | 423 | Potential loyalists |
| Sample customer 04 | 10/28/2026 | 1 | 12,400 | 2 | 5 | 1 | 1 | 511 | New customers |
| Sample customer 05 | 09/02/2026 | 15 | 298,000 | 58 | 3 | 4 | 5 | 345 | Loyal |
| Sample customer 06 | 06/11/2026 | 21 | 512,300 | 141 | 2 | 5 | 5 | 255 | Can't lose |
| Sample customer 07 | 05/19/2026 | 9 | 96,700 | 164 | 2 | 4 | 4 | 244 | Can't lose |
| Sample customer 08 | 08/24/2026 | 2 | 18,900 | 67 | 3 | 1 | 2 | 312 | Need attention |
| Sample customer 09 | 03/08/2026 | 1 | 7,600 | 236 | 1 | 1 | 1 | 111 | Lost |
| Sample customer 10 | 10/09/2026 | 11 | 154,200 | 21 | 4 | 4 | 4 | 444 | Champions |
Three numbers, each scored 1 to 5
Each customer is compared with the rest of the list and placed in one of five equal slices. The best slice scores 5, the weakest scores 1.
- R: recency
- Days from the last purchase to the analysis date. Fewer days, higher score.
- F: frequency
- Number of orders in the period you choose. Customers with the same number of orders get the same score.
- M: monetary
- Total revenue from the customer in the same period. Any currency works, as long as the whole list uses one.
Nine segments and what to do with each
R and F decide the segment. M only separates large customers who stopped ordering. Rules are read top to bottom and the first match sets the segment.
| Segment | Rule | What to do |
|---|---|---|
| Champions | R 4–5 and F 4–5 | Show them new products first. Ask for references and referrals. |
| Can't lose | R 1–2, F 4–5 and M 4–5 | Have a sales manager call. Find out why they stopped ordering and prepare a tailored offer. |
| At risk | R 1–2 and F 3–5 | Send an email that recalls their last order, then follow up with a call from their sales rep. |
| Loyal | R 3 and F 4–5 | Suggest complementary products. Offer an annual agreement or a maintenance contract. |
| Potential loyalists | R 4–5 and F 2–3 | Make the next order easy: a reorder reminder and a related product suggestion. |
| New customers | R 4–5 and F 1 | Call after the first delivery, send usage tips and invite a second order. |
| Need attention | R 3 and F 1–3 | Make a short call to ask about their needs, or send a personal suggestion. |
| Hibernating | R 2 and F 1–2 | Send a low-cost reminder email. Keep sales time for other segments. |
| Lost | R 1 and F 1–2 | Try one win-back email. If they do not return, note why they left. |
Use the template in four steps
- 1
Export your data
From your accounting or CRM software, take each customer's last purchase date, order count and total revenue. The last 12 months is a good starting period.
- 2
Paste your own list
Delete the sample data on the Customers sheet and paste your list into columns A–D. The formulas in the other columns are ready for 2,000 rows.
- 3
Check the analysis date
On the Settings sheet, leave the date empty or enter the day you exported the report.
- 4
Start from the summary
The Summary sheet shows the customer count and revenue share of each segment. Give the first calls to Can't lose and At risk.
Excel file (.xlsx), five sheets, formulas ready for 2,000 customers.
We confirm your email address with a code. The file downloads and a copy arrives in your inbox.
What is in the file
- How to use
- Four steps and how the scores are calculated.
- Customers
- Where you enter customer name, last purchase date, order count and revenue. Days, R, F, M scores and the segment come from formulas.
- Settings
- The analysis date. Leave it empty to use the day after the latest purchase in the list.
- Segments
- The rule and the action for each of the nine segments. Rename a segment here and it changes across the file.
- Summary
- Customer count, revenue, revenue share and average revenue per segment.
From segment to campaign
In PureField Marketing your team builds segments from CRM records with rules and sends each segment its own email campaign. PureField Marketing
Frequently asked questions
What is RFM analysis?
Which programs open the template?
How many customers does it handle?
Why does the analysis date matter?
Can I change the segment rules?
Why do you ask for an email address?
Move customer tracking from Excel to a CRM.
No credit card needed during the trial.