
Dan Patiño
AI Strategy & Innovation at Coderhouse
Data
RFM analysis is a simple, proven way to segment customers by how valuable and engaged they are, using only three behaviors from your transaction data: Recency (how recently a customer bought), Frequency (how often they buy), and Monetary value (how much they spend). Each customer gets a score on each dimension, and the combination of those scores places them in a segment such as “champions,” “at risk,” or “hibernating.”
The method comes from direct marketing — catalog companies used it long before e-commerce existed, as described in the overview of RFM in market research. It is still everywhere today because it is cheap to build, easy to explain to marketing and sales teams, and surprisingly good at predicting who will buy again. For a data analyst, it is often the first customer segmentation project that turns directly into campaigns and revenue.
The three RFM dimensions
Recency
Days since the customer’s last purchase. Recency is usually the strongest predictor of the next purchase: someone who bought last week is far more likely to buy again than someone whose last order was a year ago. Note that a lower number of days is better, so the score is inverted.
Frequency
Number of purchases (or orders) in the analysis period. Frequent buyers have built a habit with your brand. In subscription businesses, frequency can be replaced by number of renewals or active months.
Monetary value
Total (or average) revenue per customer in the period. It separates big spenders from occasional small orders. Many teams use net revenue after refunds to avoid rewarding customers who return most of what they buy.
How to build an RFM model step by step
1. Choose the period and the customer unit
Pick a window that matches your buying cycle — often the last 12 months for retail, longer for durable goods. Decide whether you score individual customers, households, or B2B accounts, and make sure guest checkouts are deduplicated by email or customer ID.
2. Calculate the raw values
From the orders table, compute for each customer the last order date, the count of orders, and the sum of revenue. In SQL this is a single GROUP BY customer_id with MAX(order_date), COUNT(DISTINCT order_id), and SUM(revenue), followed by a date difference against a fixed reference date.
3. Turn values into scores
The classic approach splits each dimension into quintiles and assigns scores from 1 to 5. In a warehouse you can use a ranking function such as NTILE(5), documented among the BigQuery numbering functions; if you are new to them, start with SQL window functions. Remember to invert recency so that the most recent buyers get a 5. In spreadsheets, PERCENTRANK or fixed thresholds work too.
4. Combine scores into segments
You can concatenate the three scores into a code (for example 545) or group combinations into named segments. A common, practical set looks like this:
Champions: high recency, frequency, and monetary scores. Reward them and ask for reviews or referrals.
Loyal customers: frequent buyers with good recency. Good targets for upsell and early access.
Potential loyalists / new customers: recent buyers with low frequency. Focus on a strong second purchase.
At risk: used to buy often and spend well, but have not bought in a while. Prioritize win-back campaigns.
Hibernating / lost: low scores everywhere. Reach them cheaply or not at all.
5. Activate and measure
Segments only matter when they change actions: different email flows, ad audiences, discount levels, or sales follow-ups. Measure results against a holdout group so you know whether the campaign caused the extra purchases, and refresh scores on a schedule (weekly or monthly) because customers move between segments.
RFM vs other segmentation methods
RFM is rule-based and transparent, which is its biggest strength. More advanced approaches build on it: k-means clustering can find segments automatically from the same R, F, and M values, and cohort analysis shows how groups acquired at the same time retain. RFM scores also make excellent input features for churn prediction models and customer lifetime value estimates.
Common mistakes
Ignoring the business model. Quintiles assume a spread of behavior. If most customers buy only once, frequency scores bunch together; use fixed thresholds (1, 2, 3–5, 6+ orders) instead.
Letting outliers distort monetary scores. A few wholesale buyers can dominate the top bucket. Cap extreme values or analyze B2B and B2C separately.
Treating segments as permanent labels. RFM is a snapshot. A champion today can be at risk in three months, which is exactly the movement you want to catch.
Where AI fits in
AI assistants can now write the RFM SQL, suggest segment names, and draft campaign ideas per segment in minutes. Machine learning models go further by predicting the probability of the next purchase instead of scoring the past. The analyst’s job is still to validate the data, choose sensible thresholds, and prove with experiments that acting on segments actually drives revenue.
FAQ
What does RFM stand for?
Recency, Frequency, and Monetary value: how recently, how often, and how much a customer buys.
Can I do RFM analysis in Excel?
Yes. With an orders export, a pivot table gives you last date, order count, and total revenue per customer, and percentile formulas turn them into scores. SQL or Python become more convenient as data grows.
Does RFM work for subscription businesses?
It works with adjustments: recency can be days since last login or activity, frequency can be active months or renewals, and monetary value can be monthly recurring revenue.
Keep learning
RFM is one of the fastest ways to show business impact with data, and AI is making segmentation and prediction more powerful. If you want to go deeper:
Data Analytics Course — learn SQL, Excel, and dashboards to build customer segments that teams actually use.
AI Engineering Course — learn to build AI applications and models that predict customer behavior and automate decisions.
I'm Dan Patiño, head of AI Strategy & Innovation at Coderhouse. My day-to-day work involves merging the tactical management of e-commerce (CRO, Email Marketing and SEO) with the development of disruptive solutions. I specialize in building internal AI-powered apps to automate tasks and boost innovation within the team. I firmly believe that technology is strategy's best ally. To dive deeper into my professional journey, I'll be waiting for you on my LinkedIn profile.