Guides

Case Study: Find the Best Customers of a Real Online Retailer with RFM in Excel

By Ravindra Bagale, Excel & Data Analytics trainer · LinkedIn · Published: 7 October 2026 · Last updated: 7 October 2026

A UK online shop that sells gift items, mostly to wholesalers, is planning December. The owner asks: "Who are our best customers, and who is slipping away?" We cleaned 473,903 invoice lines from January to November 2011 down to 14,587 invoices from 3,769 UK customers. RFM scoring shows that 820 Champions (21.8% of customers) brought 63.0% of revenue, and 318 customers who used to buy often have gone quiet. They spent £513,167 earlier in the year.

Download Online Retail II from the UCI repository, keep one country and one period, remove lines with no Customer ID, cancellations, non-product codes and zero or negative quantities, then add up the lines into one row per invoice. On a Customers sheet use MAXIFS for the last purchase date, COUNTIFS for the number of invoices and SUMIFS for spend. Score each with PERCENTILE.INC cut-offs and COUNTIF, combine the R and F scores into segments with a nested IF, and summarise with COUNTIF and SUMIFS.

Download the practice file (case-study-online-retail-rfm.xlsx)

Excel file, 1198 KB. It holds the trimmed real data and every formula used below. It has a raw sample (three days of original lines, with a Keep? formula), the cleaning log, every cleaned invoice, the Customers sheet with RFM formulas, the analysis and the dashboard.

The business question

December is the busiest month for a gift seller, and the marketing budget is small. Sending the same email to everyone wastes it. The owner needs three answers:

1. Which customers bring most of the money?
2. Which good customers have stopped buying and are worth a phone call?
3. Which customers are not worth an expensive campaign?

RFM answers all three with three numbers per customer: Recency (days since the last purchase), Frequency (how many invoices) and Monetary value (how much they spent). If RFM is new to you, read the RFM segmentation guide first; this page applies the same method to real data.

The data: source and licence

Step 1: Clean the lines

The sheet has one row per product line, not per order, and it has real-world mess. Open the Raw sample sheet of the practice file to see it: cancellations, blank Customer IDs, postage lines and customers from other countries, all mixed together.

1. Filter InvoiceDate to 1 January to 30 November 2011. December 2011 stops on the 9th, so leaving it out keeps eleven full months.
2. Filter Country to United Kingdom. UK customers are most of the lines, and mixing countries mixes very different buying patterns.
3. Remove lines where Customer ID is blank. You cannot score a customer you cannot identify.
4. Remove cancellations: filter Invoice with Text Filters > Begins With > C and delete those rows.
5. Remove codes that are not products: POST, DOT, M, m, C2, D, S, BANK CHARGES, AMAZONFEE, CRUK, PADS, B and gift vouchers (codes starting gift_).
6. Remove lines with zero or negative Quantity or Price.
7. Remove exact duplicate lines with Data > Remove Duplicates (tick all columns).
8. Add Value = =D2*F2 (Quantity x Price).

In the practice file the Keep? column does the same test in one formula, so you can see which rule removes each line:

=IF(OR(G2="",LEFT(A2,1)="C",D2<=0,F2<=0,ISNUMBER(MATCH(B2,NonProduct,0)),LEFT(B2,5)="gift_",H2<>"United Kingdom"),"Drop","Keep")

In the 4,759-line sample, 2,872 lines are kept. NonProduct is a named range with the code list on the Lists sheet.

Step 2: One row per invoice

RFM counts orders, not lines. Turn the cleaned lines into invoices:

1. Select the cleaned lines and choose Insert > PivotTable > New Worksheet.
2. Put Invoice in Rows, then add InvoiceDate and Customer ID to Rows as well, and set Report Layout > Show in Tabular Form and Do Not Show Subtotals.
3. Put Value and Quantity in Values (Sum).
4. Copy the PivotTable and Paste Values onto a new sheet, then press Ctrl+T and name the table Invoices.

The result is 14,587 invoices worth £6,277,357 in total, an average of £430.34 per invoice. The practice file keeps only the date part of each invoice time, because RFM works in days.

Step 3: Recency, Frequency and Monetary for each customer

Make a list of unique Customer IDs (Data > Remove Duplicates on a copy of the column, or =UNIQUE(Invoices[Customer ID])), turn it into a table called Customers, and add these formulas. The analysis date AsOfDate is 1 December 2011, the day after the last invoice.

Last Purchase  =MAXIFS(Invoices[Date],Invoices[Customer ID],A2)
Recency Days   =AsOfDate-C2
Frequency      =COUNTIFS(Invoices[Customer ID],A2)
Monetary       =SUMIFS(Invoices[Value],Invoices[Customer ID],A2)

Look at the spread before you score: 1,410 of the 3,769 customers bought only once in these eleven months, while the top 20% of customers (753) brought 71.9% of revenue.

Step 4: Score 1 to 5 and name the segments

Cut each measure into five groups of roughly equal size with PERCENTILE.INC, then count how many cut-offs a customer beats:

Cut-off (20%, 40%, 60%, 80%)  =PERCENTILE.INC(Customers[Recency Days],A6)
R Score  =5-COUNTIF(RCuts,"<"&D2)       (fewer days = higher score)
F Score  =1+COUNTIF(FCuts,"<"&E2)
M Score  =1+COUNTIF(MCuts,"<"&F2)
RFM Code =G2&H2&I2

The segment uses R and F (M follows F closely in this data):

=IF(AND(G2>=4,H2>=4),"Champions",IF(AND(G2>=3,H2>=3),"Loyal",IF(G2>=4,"New / promising",
  IF(H2>=3,"At risk",IF(G2<=2,"Lost / hibernating","Needs attention")))))

Step 5: Build the dashboard

1. On an Analysis sheet, list the six segments and use =COUNTIF(Customers[Segment],A13) and =SUMIFS(Customers[Monetary],Customers[Segment],A13).
2. Add a monthly table with =SUMIFS(Invoices[Value],Invoices[Date],">="&A24,Invoices[Date],"<"&EDATE(A24,1)).
3. On a Dashboard sheet make six KPI cards: customers, revenue, average invoice value, Champions' share of revenue, at-risk customers and their past spend.
4. Insert four charts: revenue by month (column), customers by segment (bar), share of revenue by segment (bar) and invoices per month (line).
5. Turn off gridlines and set the page to landscape, fit to one page.

The Dashboard sheet of the practice workbook (real data: Online Retail II, UCI, CC BY 4.0).

Sales build towards the holidays: September to November brought 40.9% of the eleven months' revenue, and November alone was £967,883 from 2,379 invoices.

Step 6: The recommendation

1. Look after the 820 Champions first. They are 21.8% of customers but 63.0% of revenue, and they bought on average 10.0 times in eleven months. Give them early access to the Christmas range and a named contact, not discounts.
2. Call the 318 At-risk customers this week. They bought 4.2 times on average but not for about 123 days, and together they spent £513,167. A personal reminder before the December rush is the cheapest revenue in this list.
3. Ask New / promising customers for a second order. 513 customers bought recently but only 1.5 times on average. A simple 'reorder' email with their last products can turn them into Loyal customers.
4. Keep Lost / hibernating customers on the cheap list. 1,173 customers (31.1%) brought 8.7% of revenue. A newsletter is enough; do not spend sales time here.

What this data cannot tell you

• Customers who bought before January 2011 look newer and less frequent than they are, because we only counted eleven months.
• Lines without a Customer ID (110,162 UK lines) are left out, so revenue here is not the shop's full revenue.
• The data is from 2011 and about one shop. Use it to learn the method, then run it on your own orders.