A Simple Customer Segmentation Model You Can Build in a Spreadsheet

20 September 2026·4 min read

Quick answer: You don't need a fancy CRM to segment your customers properly — a simple recency, frequency and value model built in a spreadsheet will get you most of the way there. Score each customer on how recently they bought or booked, how often, and how much they've spent, then group them into a handful of segments like VIPs, Regulars, At Risk and Lapsed. It works from data most businesses already have sitting in a POS, booking system or invoicing tool. It's a starting model, not a permanent one, but it's genuinely useful long before you're ready to invest in dedicated software. ✨

A lot of businesses think proper segmentation needs an expensive CRM or marketing platform, so it never happens at all. 💖 Meanwhile they send the exact same email, with the exact same offer, to a customer who bought once three years ago and a customer who's ordered every month for two years — then wonder why the results are lukewarm. A genuinely useful first version of segmentation fits in a spreadsheet most businesses already have open.

What most businesses get wrong

Treating every customer as one audience, so first-timers and long-term regulars get identical messages.
Waiting for the "right" software before segmenting at all, so years go by with nothing done.
Building elaborate models with ten or more segments that are impossible to act on consistently, so the whole thing collapses within a month.

The RFM-Lite Spreadsheet Model

Set up these columns in Sheets or Excel, using the last twelve months of data:

Customer name and contact details
Last purchase/booking date
Days since last purchase (Recency)
Number of purchases/bookings, last 12 months (Frequency)
Total spend, last 12 months (Monetary)
R score, F score, M score (1-3 each)
Total score and Segment

Scoring guide (adjust the thresholds to suit your own business):

Recency: 3 = within 60 days · 2 = 61-180 days · 1 = 180+ days
Frequency: 3 = 4+ purchases/bookings · 2 = 2-3 · 1 = 1
Monetary: 3 = top third of spend · 2 = middle third · 1 = bottom third

Segments: Total 8-9 = VIP · Total 6-7 = Regulars · Mid score with low Recency = At Risk · Frequency of 1 and recent = New · Low score and old Recency = Lapsed.

An ecommerce store: Applied straight to an order export, flagging VIPs for early access to new stock and At Risk customers for a small win-back discount. Simple, and it worked — though it needed someone to actually own re-running the export each quarter, or the segments quietly went stale.

A B2B firm with a small number of large clients: Monetary became contract value, Frequency became meaningful touchpoints or renewals, and Recency became last real contact. With only a handful of clients, raw scores needed a layer of judgement on top — a quiet client might still be a VIP relationship, just not an active one that week.

A booking-based service business: Added an extra column flagging no-show rate alongside booking frequency, since a customer who books often but no-shows constantly scores high on Frequency without actually being valuable. A useful reminder that the raw numbers still need a sense check before you act on them.

How to actually build it

Export your data from your POS, booking system, invoicing tool or ecommerce platform — at least the last 12 months.
Set up the columns above in a spreadsheet.
Calculate Recency, Frequency and Monetary using simple formulas — dates, COUNTIF, SUMIF.
Score each on 1-3 using thresholds that reflect your own customer base, not someone else's benchmark.
Assign segments using the score combinations.
Decide one action per segment — an email, a call, a small perk — before building any further segments.
Refresh the sheet quarterly, not once and forget it.

💡 This is a starting model, not a forever one. A spreadsheet is manual, and manual breaks down once you're past a few hundred customers or updating it starts eating more than an hour. That's your cue to look at a CRM or email platform with built-in segmentation — not a reason to avoid starting with a spreadsheet now.

Mistakes to avoid

Building segments you never actually send anything to.
Using arbitrary thresholds that don't reflect your real customer base.
Letting the sheet go stale after the first build.
Creating far more segments than you can realistically act on.
Ignoring context, like a no-show pattern or a complaint, that raw numbers alone won't show.

Frequently asked questions

Do I need special software to start?

No — a spreadsheet and the data you already have in your POS, booking system or invoices is enough to build a genuinely useful first version.

How many customers do I need before this is worth doing?

Even a hundred or so customers with reasonable purchase or booking history is enough to see meaningful, actionable groups form.

Can this replace a proper CRM eventually?

Not indefinitely, and it's worth being upfront about that. Past a certain size, a spreadsheet becomes a liability — manual errors creep in, nothing updates automatically, and it's easy for no one to truly own keeping it current. Plan to graduate to dedicated software once the model is proving its worth, not before.

What do I actually do once I have segments?

Pick one simple action per segment first — a win-back email for Lapsed, early access for VIPs — and get that working reliably before adding more complexity on top.

Keep reading 🤍

Written by

Kate, founder of Chronically Online

I help Gold Coast and Brisbane businesses grow with branding, websites and marketing that actually works.

Work with me ✦