RFM analysis: what it is and how to segment customers in a spreadsheet

Аналитик сегментирует клиентов в таблице на мониторе в офисе в Ташкенте
Content

RFM analysis splits customers into groups by three factors: how recently they bought, how often, and how much they spent. We walk you through running it in Excel or Google Sheets step by step and what to do with each group.

KelyanMedia editorial team · Updated November 13, 2026

Аналитик сегментирует клиентов в таблице на мониторе в офисе в Ташкенте

RFM stands for Recency (how recently the last purchase was made), Frequency, and Monetary (amount spent). The method doesn't require special software: an export of orders and a spreadsheet are enough.

The result of the analysis is a clear list: which customers to message about new products, whom to win back with a discount, and whom not to spend budget on.

In short: what you need to know

  • DataYou need three columns from your CRM or POS system: customer ID, order date, order amount.
  • ScoresEach customer gets a score from 1 to 3 for recency, frequency, and monetary value, which gives a code like 333 or 112.
  • ValueCustomers are split into segments by their code, and each segment gets its own message and its own offer.

What RFM shows and what data you need

RFM works where customers buy repeatedly: shops, salons, clinics, delivery, subscription services. For one-off large deals it is almost useless.

ParameterWhat we calculateWhere to get itWhat it shows
R, recencyDays from the last purchase to the analysis dateThe customer's most recent order dateHow "warm" the customer is right now
F, frequencyNumber of orders for the periodNumber of order rows per customerThe habit of buying from you
M, monetaryTotal of all orders for the periodTotal per customerThe customer's value for revenue

← Swipe to scroll the table →

Choose a period long enough to cover several purchase cycles: for groceries a few months is enough, for furniture you need a year or more. If you still keep orders in a notebook or in chats, put your records in order first; see the article CRM in Tashkent: why your business needs one and how to choose it.

How to run an RFM analysis in a spreadsheet: 6 steps

All the work is done with formulas and a pivot table, with no manual tagging. The function names below are for the English versions of Excel and Google Sheets.

  1. Export orders for the period: customer ID, date, amount. Remove canceled and returned orders.
  2. Build a pivot table by customer: last order date (MAX), number of orders (COUNT), amount (SUM).
  3. Calculate recency: =DAYS(analysis_date; last_order).
  4. Set score boundaries from 1 to 3 for each factor. It's convenient to split customers into three roughly equal groups or to rely on your own purchase cycle.
  5. Assign scores with the IF formula and build the code: =R&F&M.
  6. Name the segments by their codes and count how many customers and how much revenue each one has.

An illustrative example for a cosmetics store, analysis date November 1. Boundaries: recency up to 30 days = 3, 31 to 90 = 2, over 90 = 1; frequency of 4 or more orders = 3, 2 or 3 = 2, one = 1; amount is split into three groups by customer revenue.

ClientDays since purchaseOrdersAmountCode
А126high333
Б1405high133
IN201low311
Г602medium222
Д2001low111

← Swipe to scroll the table →

RFM segments and what to do with them

In practice, the 27 possible codes are grouped into 5 or 6 segments. A segment exists for the sake of an action: if a group has no separate message, you can merge it with a neighboring one.

SegmentCodesWhat to doWhat not to do
Best333, 332, 323Early access to new products, a loyalty program, a request for a reviewGiving discounts for no reason
At risk of leaving133, 132, 123A personal message or call to find out why, an offer for the next orderSending a generic mailing
New311, 312A sequence after the first purchase: instructions, a review, a reason to come backPushing discounts right away
Middle222, 221, 212Curated selections, cross-selling related productsIgnoring them: this is the largest group
Lost111, 112, 121One attempt to win them back, then exclude them from paid adsSpending ad budget on them all the time

← Swipe to scroll the table →

You can export segments as phone lists for mailings in Telegram or SMS and upload them to ad accounts as audiences, if the customer has consented to such data processing.

Learn about full-service digital marketing →

RFM limitations and common mistakes

RFM sees only money and dates. It knows nothing about returns, margins, or how much effort it takes to serve a customer.

  • the same score boundaries for all products: normal purchase recency differs for consumables and for appliances;
  • running the analysis once: customers move between segments, so recalculate monthly or quarterly depending on how often they buy;
  • wholesale and retail customers in one table: wholesalers take all the high monetary scores;
  • segments without actions: if no one received a separate message after the analysis, the work was wasted;
  • no control group: leave part of a segment without the mailing to see whether it worked.

Important

The example in this article is illustrative; score boundaries are chosen to fit your purchase cycle. Using customers' phone numbers for mailings and ads requires their consent: check the rules for processing personal data with a lawyer.

FAQ

How many customers do you need for RFM analysis?
You can apply the method to any customer base, but with a few dozen customers the segments end up with 2 or 3 people each, and it's easier to review them manually. It starts to make sense when each group has enough people for a separate mailing.
Can you use scores from 1 to 5?
Yes, the segments become more precise, but there will be 125 of them instead of 27. For a small business, a scale from 1 to 3 is usually more convenient.
How is RFM different from ABC analysis?
ABC analysis ranks customers or products by their contribution to revenue. RFM adds recency and frequency, so it shows not only who is valuable but also who is starting to leave.

Next steps

Need to set up work with your customer base and segment-based mailings: see full-service digital marketing.

Need to automate order tracking and reports: learn about business IT optimization.

Still have questions: call +998 33 339 33 34.

Discuss working with your customer base →

6 useful guides:

increasing the number of customers and brand awareness
Download guides and implement new tools to grow your company.

Website for your business:

Fill out the brief and get a quote for your website
You receive a project implemented by experts with international experience.

How to choose an advertising agency in Tashkent without wasting your budget

A business owner in a meeting room in Tashkent compares advertising agency proposals using a printed table