RFM Analysis for Small Business: Score Your Customers
Merchant Education & Growth

RFM Analysis: Segment Customers Using Your POS Sales Data

RFM analysis for small business, step by step: score customers from a POS export in a spreadsheet, then match each segment to the right campaign.

Hands typing on a laptop showing a blue bar chart, beside a calculator, tablet with stylus and binders on a wooden desk

Photo: Pixabay

On this page 6 sections
  1. How RFM Analysis Helps a Small Business Stop Blasting Everyone
  2. Export Transactions That Are Tied to a Named Customer
  3. Build the RFM Scoring Sheet in Four Steps
  4. Turn Scores Into Segments: Champions, At-Risk, New and Lapsed
  5. Match Each Segment to One Campaign
  6. Rerun Monthly or Quarterly and Track Who Moves

Key takeaways

  • Set score thresholds from how often your customers actually buy. Five equal fifths (quintiles) are a habit from large mailing lists, and a small customer list fills them with ties.
  • Use recency and frequency to decide a customer's segment. Use monetary value only to rank customers within a segment.
  • A regular who has stopped coming in can rank near the top of a plain sales report. RFM catches that customer, and a sales total doesn't.
  • Keep a holdout group (customers who get no message) on every campaign, and treat small-segment results as a rough signal until a few rounds agree.

RFM analysis lets a small business rank every customer on three numbers it already has in its sales history. Recency is the number of days since the last purchase, frequency is how many purchases the customer made, and monetary value is how much they spent in total. Each customer gets a score from 1 to 5 on each measure. The combined scores separate your best regulars from the ones who have quietly stopped coming in, so each group can get its own message. That costs less than sending one promotion to everyone, and it works better.

You don't need a data team. All you need is a year of transactions exported from your POS or online store and a spreadsheet, and the first pass fits in an afternoon.

How RFM Analysis Helps a Small Business Stop Blasting Everyone

Sending one promotion to your whole list costs you twice. A weekend discount texted to your best customers gives a markdown to people who were coming in anyway, and the markdown comes out of your margin. The same text means little to someone who last visited more than a year ago and has since found another salon. If you pay per message, you're paying to reach both of them.

Each RFM measure answers a different question. Recency tells you whether the relationship is still active: someone who bought last week is much easier to reach than someone you last saw a year ago. Frequency tells you whether a habit has formed. A long string of visits is a routine worth protecting, and a single visit means there's no routine yet. Monetary value tells you how much is at stake, which helps you decide where the owner's personal time is worth spending.

You need all three scores together. Only together do they tell a high-spending regular who has gone quiet apart from a one-time bargain hunter. The regular deserves a phone call, and the bargain hunter deserves one email at most.

Export Transactions That Are Tied to a Named Customer

RFM only works for customers you can identify, so first check how much of your sales carries a name, phone number or email. That share depends heavily on your industry. Online stores collect an email on nearly every order. Salons and spas book appointments by name and phone, so their records are almost as complete. Retail falls in the middle, because loyalty members and shoppers who ask for an emailed receipt can be identified but cash and anonymous card sales can't. Restaurants and bars usually have the least: online orders and reservations carry a name, but a walk-in who taps a card and leaves usually doesn't.

Thin coverage still gives you something to work with, because RFM works on whatever customers you can see. To grow that group, attach a customer to more sales at checkout. Many POS systems built for small businesses can prompt for a phone number or email at the register, and an emailed receipt or a loyalty program gives shoppers a reason to share one. Check what your system stores and whether the customer field shows up in its export.

Export transaction-level data for the past 12 months, with one row per order rather than daily totals. You need four fields: a customer identifier (ID, email or phone), an order or transaction ID, the transaction date, and the net sale amount.

Clean the file before you score anything:

  • Net each refund against its original sale, and delete voided transactions entirely. The difference between a void and a refund matters here because the two appear differently in your export.
  • Remove tips and sales tax, since neither is spending on what you sell.
  • Remove staff purchases, house accounts and any wholesale buyer whose order sizes would distort everyone else's scores.
  • Merge duplicate profiles for the same person, such as a client who gave her cell number once and her work email the next time.

Build the RFM Scoring Sheet in Four Steps

Set up the scoring tab with one row per customer so the formulas below line up:

ColumnContents
ACustomer ID
BLast purchase date
CNumber of orders
DTotal net spend
EDays since last purchase
FRecency score
GFrequency score
HMonetary score
ICombined label
  1. Summarize by customer. Build a pivot table with customer ID as the rows and three values: Max of transaction date, Count of order ID and Sum of net amount. Paste the result into a new tab as plain values so it fills columns A through D.
  2. Calculate recency in days. Enter your analysis date in J1, outside the table. In E2, enter =$J$1-B2 and fill down. Format column E as a number, not a date.
  3. Score each measure. Using the salon thresholds from the example below, the recency score in F2 is =LOOKUP(E2,{0,46,91,181,271},{5,4,3,2,1}). That gives a 5 to anyone seen within 45 days and a 1 to anyone gone 271 days or more. Frequency in G2 is =LOOKUP(C2,{1,2,3,5,9},{1,2,3,4,5}) and monetary in H2 is =LOOKUP(D2,{0,150,400,800,1500},{1,2,3,4,5}). On those two, higher values earn higher scores.
  4. Build the label. In I2, =F2&"-"&G2&"-"&H2 produces a label such as "5-4-4". Keep the three scores in their own columns too, because you'll filter on them.

Two details trip people up. First, LOOKUP searches a sorted list and returns the matching position from the result list, as Microsoft's documentation for the function describes, so always enter the thresholds in ascending order. Second, if a customer's refunds bring their net spend below zero, the monetary formula has no threshold to match and returns an error. Remove those customers, since their spending history won't tell you anything useful.

Score Against Your Own Buying Cycle, Not Fixed Fifths

Many RFM tutorials split each measure into fifths, called quintiles: the top fifth of customers scores 5, the next fifth scores 4, and so on. That's a convention, not a rule. A U.S. patent on RFM scoring (No. 9,760,900) describes five bins per measure as typical and says other numbers of bins can be effective.

Quintiles often don't work for a small business. In many shops, a large share of customers bought exactly once, so the lower frequency fifths are full of ties. The spreadsheet then either gives different scores to identical customers or piles most of the list into one bucket.

Set the thresholds from how your business actually runs. For a salon whose clients rebook every month or two, six weeks without a visit is normal and half a year is a warning sign. A coffee shop measures its cycle in days. An online store selling a monthly supply of something should base recency on that reorder window. A furniture store may see good customers only once every few years, so there RFM is better for finding past buyers to invite to an event than for measuring habits.

If you have thousands of customers and order counts that spread out well, quintiles are fine. =MAX(1,ROUNDUP(PERCENTRANK.INC($C$2:$C$2000,C2)*5,0)) scores frequency by quintile. Adjust the range to your row count, and point it at column D to score spend. PERCENTRANK.INC returns a value's rank in a data set as a fraction between 0 and 1, and Microsoft's documentation notes it uses three digits unless you set a different precision. For recency, subtract the result from 6, because fewer days earns a better score.

Worked Example: Six Salon Clients

Illustrative numbers only. Assume a salon with an analysis date of November 1, a 12-month lookback and these thresholds:

  • Recency (days since last visit): 0–45 scores 5, 46–90 scores 4, 91–180 scores 3, 181–270 scores 2, 271 or more scores 1
  • Frequency (visits): 1 scores 1, 2 scores 2, 3–4 scores 3, 5–8 scores 4, 9 or more scores 5
  • Monetary (12-month spend): under $150 scores 1, $150–$399 scores 2, $400–$799 scores 3, $800–$1,499 scores 4, $1,500 or more scores 5
ClientDays Since Last VisitVisitsSpendR-F-M
A129$1,6205-5-5
B1407$1,1803-4-4
C201$955-1-1
D3001$601-1-1
E604$5204-3-3
F2106$8902-4-4

A plain sales report ranks Client B second, just behind A, and nothing looks wrong. RFM shows the problem. B came in seven times during the year, a steady regular by any measure, but her last visit was 140 days before November 1, in mid-June. That's more than four months without coming in.

Turn Scores Into Segments: Champions, At-Risk, New and Lapsed

Three 1-to-5 scores make 125 possible combinations. LatentView, an analytics firm, notes that in practice these collapse into about 10 or 11 working segments. A small business needs fewer than that, because every segment needs its own campaign and someone to run it. Apply these five rules in order, and put each customer in the first segment whose rule they match:

SegmentRuleClients in Example
ChampionsR of 4 or 5, F of 4 or 5A
NewR of 4 or 5, F of 1C
At-RiskR of 1 to 3, F of 3 to 5B, F
LapsedR of 1 or 2, F of 1 or 2D
DevelopingEveryone elseE

The monetary score doesn't define any segment, and that's deliberate. It sets priority within each one: an at-risk client with an M of 4 or 5 gets a personal call from the owner, while one with an M of 2 gets the standard email. Monetary also flags new customers with a high spend score, such as an online shopper whose first order is far above your average. They've spent a lot once but haven't formed a habit yet, so watch them closely.

Match Each Segment to One Campaign

Champions: Recognition, Not Discounts

These customers already come in, and rewarding them with markdowns trains them to wait for sales. Give them access and recognition instead, such as a first look at new inventory or early holiday booking. Champions are also the right people to ask for reviews and referrals. If you want a formal structure, design a loyalty program that pays for itself so it doesn't give away margin these regulars would have spent anyway.

New: Win the Second Visit

Many relationships never get past the first purchase, so time the follow-up to your buying cycle. A salon can send a rebooking reminder a few weeks after a first cut. An online store can send a how-to email or a reorder prompt before the product runs out. A small incentive with an expiration date fits here.

At-Risk: Find Out What Changed

This segment has the most value left to recover, so put most of your effort here. Look for a cause before you send anything: maybe the client's stylist left, or a product she bought regularly went out of stock. A personal note that names the gap, such as "We haven't seen you since June," lands better than a generic coupon. If you include an offer, make it worth something and give it a deadline.

Lapsed: Two Tries, Then Stop

Send one or two low-cost win-back messages for lapsed customers. If nothing comes back, move them to a slower list or drop them, especially from paid SMS. Texting any segment is regulated by federal law. Under the FCC's rule at 47 CFR 64.1200(f)(9), marketing texts sent with an autodialer generally require prior express written consent: a signed agreement (an electronic signature counts) that authorizes marketing messages to a specific number and states that agreeing isn't a condition of purchase.

You must honor an opt-out within ten business days at most. Before you launch, review the SMS marketing rules under the TCPA. Some states have their own telemarketing laws as well. This is general information, not legal advice, so confirm your program with a qualified attorney.

Developing: Build Frequency

These customers come in but haven't settled into a routine. Rewards based on visits give them a reason to return sooner, and bundles work well for retail and online stores.

Prove It With a Holdout Group

For every campaign, randomly pick some customers in the segment and send them nothing. Then compare how many people in each group come back. Illustrative numbers: say your at-risk segment has 80 customers and you hold out one in five. You message 64 and hold back 16. If 16 of the 64 return within 30 days (25%) and 2 of the 16 holdouts return (12.5%), the campaign produced about 8 visits that wouldn't have happened otherwise, not 16.

Small groups are noisy, though. With 16 holdouts, a single customer moves that group's rate by more than six percentage points. Treat one round as a hint and trust the result only once several rounds agree. For more on reading these results, see using POS customer data to market on a small budget.

Rerun Monthly or Quarterly and Track Who Moves

An RFM score is a snapshot, and customers move between segments all the time. Rerun it monthly if customers buy often, as at a café or an online consumables store. Quarterly is enough when visits are weeks apart. Keep the thresholds fixed between runs. If you change them, segment counts shift for reasons that have nothing to do with customer behavior, and you can no longer compare one run to the next.

Save each run on its own tab with the date in the name, then watch two things. The first is segment size: a shrinking champion count is an early warning that can appear before revenue drops. The second is movement. Use a lookup to put each customer's previous segment next to their current one, then count the changes. How many at-risk customers became champions again after your outreach?

How many new customers moved up to developing? That table tells you more about your marketing than total sales does, because it follows what individual customers did. The same customer summary also gives you most of what you need to calculate customer lifetime value from your sales data, which tells you how much keeping a champion is worth.

This week, export 12 months of transactions, build the scoring tab and run one at-risk campaign with a holdout group. If your system can't tie sales to customers, fix that first. You can request a free quote from MannyPay to talk through POS options, and whichever provider you talk to, ask them to show you which customer fields the system captures and whether they appear in its exports.

Sources

  1. Trend-Factored RFM Scores to Improve Campaign Performance, U.S. Patent 9,760,900 (USPTO)
  2. RFM Analysis: How to Calculate Scores and Segment Customers (LatentView Analytics)
  3. PERCENTRANK.INC Function (Microsoft Support)
  4. WorksheetFunction.Lookup Method (Microsoft Learn)
  5. TCPA Text Message Marketing Compliance (Clark Meyers)
  6. TCPA Consent Basics (America's Credit Unions)

About the author

Isabella Cruz

Isabella Cruz is Business Growth & Commerce Editor for Beyond the Swipe, writing for small businesses, restaurants, retailers, entertainment venues and e-commerce sellers.

This article is for general educational purposes and is not legal, tax or financial advice. Card network rules and state laws change; confirm current requirements for your business.

Related reading