Most businesses have more customer data than they know what to do with. They can tell you total revenue, average order value, month-over-month growth. What they struggle to answer is a simpler and more useful question: which customers actually matter, and which ones are slipping away?
RFM segmentation answers that question using data you almost certainly already have — transaction records, customer IDs, order dates and order values. No machine learning required. No external tools. Just SQL and a framework that has been used in direct marketing since the 1990s because it works.
This guide walks through building RFM segmentation from scratch in SQL, interpreting the results and turning them into actions your marketing team can use tomorrow.
What RFM stands for
Recency — how recently did this customer last buy from you?
Frequency — how many times have they bought in a given period?
Monetary — how much have they spent in total?
The premise is straightforward. A customer who bought last week, has placed six orders this year and has spent $800 total is a very different customer from someone who bought once, two years ago, for $30. They require different communication, different offers and different levels of investment in retention. RFM gives you a consistent, reproducible way to make those distinctions across your entire customer base.
What you need to get started
A transactions table with at minimum these columns:
customer_id -- unique identifier for each customer
order_date -- date of purchase (DATE or DATETIME)
order_value -- value of each order (DECIMAL or FLOAT)That is it. If you have these three columns, you can build RFM segmentation. If your data is in BigQuery, PostgreSQL, MySQL, Redshift or Snowflake, the SQL in this guide will work with minor syntax adjustments noted where relevant.
Step 1 — Calculate the raw RFM metrics
Start by building a base table with one row per customer. You need three numbers per customer: days since their last order (recency), total number of orders (frequency), and total spend (monetary).
-- Set a reference date — either today or the last date in your dataset
-- Using a fixed date makes your analysis reproducible
WITH reference_date AS (
SELECT DATE('2024-01-01') AS ref_date
-- Replace with CURRENT_DATE if you want today's date
),
rfm_base AS (
SELECT
t.customer_id,
-- Recency: days between last order and reference date
DATEDIFF(r.ref_date, MAX(t.order_date)) AS recency_days,
-- Frequency: total number of orders
COUNT(DISTINCT t.order_id) AS frequency,
-- Monetary: total spend
SUM(t.order_value) AS monetary
FROM transactions t
CROSS JOIN reference_date r
GROUP BY t.customer_id, r.ref_date
)
SELECT * FROM rfm_base
ORDER BY monetary DESC;A note on DATEDIFF syntax:
- MySQL / BigQuery:
DATEDIFF(ref_date, order_date)— returns (later date − earlier date) - PostgreSQL / Redshift / Snowflake:
(ref_date - MAX(order_date))orDATE_PART('day', ref_date - MAX(order_date))
Run this first and look at the output before moving on. Check that your recency values make sense — a customer who bought yesterday should have recency_days close to zero. Check your monetary values against known totals. Finding data quality issues here is much easier than finding them three steps later.
Step 2 — Score each metric on a 1–5 scale
Raw numbers are hard to act on. A customer with 4 orders is different from one with 40, but how different? Scoring converts the raw numbers into consistent 1–5 scales that can be combined and compared.
The scoring works like this:
- For recency, a lower number is better (bought more recently = better customer). Score 5 goes to the most recent buyers, score 1 to the most lapsed.
- For frequency and monetary, a higher number is better. Score 5 goes to the highest values.
I use NTILE to split customers into five equal groups:
WITH reference_date AS (
SELECT DATE('2024-01-01') AS ref_date
),
rfm_base AS (
SELECT
t.customer_id,
DATEDIFF(r.ref_date, MAX(t.order_date)) AS recency_days,
COUNT(DISTINCT t.order_id) AS frequency,
SUM(t.order_value) AS monetary
FROM transactions t
CROSS JOIN reference_date r
GROUP BY t.customer_id, r.ref_date
),
rfm_scored AS (
SELECT
customer_id,
recency_days,
frequency,
monetary,
-- Recency: lower days = better = higher score
-- NTILE splits into 5 equal groups; we reverse so recent = 5
6 - NTILE(5) OVER (ORDER BY recency_days ASC) AS r_score,
-- Frequency: higher orders = higher score
NTILE(5) OVER (ORDER BY frequency ASC) AS f_score,
-- Monetary: higher spend = higher score
NTILE(5) OVER (ORDER BY monetary ASC) AS m_score
FROM rfm_base
)
SELECT
customer_id,
recency_days,
frequency,
ROUND(monetary, 2) AS monetary,
r_score,
f_score,
m_score,
-- Combined RFM score as a string (useful for segment lookup)
CONCAT(r_score, f_score, m_score) AS rfm_score,
-- Average score as a single number
ROUND((r_score + f_score + m_score) / 3.0, 2) AS rfm_avg_score
FROM rfm_scored
ORDER BY rfm_avg_score DESC;After running this, every customer has an R score, F score and M score between 1 and 5. A customer scored 5-5-5 is your best customer. A customer scored 1-1-1 is either churned or was never really a customer in the first place.
Step 3 — Assign customer segments
Scores are useful. Labels are actionable. This step maps score combinations to named segments that your marketing team can use directly.
-- Add this as a final SELECT on top of the rfm_scored CTE above
SELECT
customer_id,
recency_days,
frequency,
ROUND(monetary, 2) AS monetary,
r_score,
f_score,
m_score,
CONCAT(r_score, f_score, m_score) AS rfm_score,
CASE
-- Champions: bought recently, buy often, spend the most
WHEN r_score = 5 AND f_score >= 4 AND m_score >= 4
THEN 'Champions'
-- Loyal Customers: buy regularly, good monetary value
WHEN f_score >= 4 AND m_score >= 3
THEN 'Loyal Customers'
-- Potential Loyalists: recent customers with some frequency
WHEN r_score >= 4 AND f_score >= 2
THEN 'Potential Loyalists'
-- Recent Customers: bought recently but only once or twice
WHEN r_score >= 4 AND f_score <= 2
THEN 'Recent Customers'
-- At Risk: used to buy often but have not returned recently
WHEN r_score <= 2 AND f_score >= 3
THEN 'At Risk'
-- Hibernating: low recency, low frequency, low monetary
WHEN r_score <= 2 AND f_score <= 2
THEN 'Hibernating'
-- About to Lapse: average scores, declining recency
WHEN r_score = 3 AND f_score >= 2
THEN 'About to Lapse'
-- Need Attention: above average but not recent
WHEN r_score = 3 AND m_score >= 3
THEN 'Need Attention'
ELSE 'Others'
END AS customer_segment
FROM rfm_scored
ORDER BY rfm_avg_score DESC;A note on the CASE statement: these thresholds are a starting point, not a rule. Adjust them based on your actual data distribution. If your business has a very long purchase cycle (quarterly or annual), your recency thresholds should be much wider. If most customers buy weekly, they should be narrower. Run the base query first, look at your recency_days distribution and set thresholds that reflect your actual customer behaviour.
Step 4 — Summarise the segments
Once every customer has a segment, build the summary table that tells you what you are working with:
-- Segment summary
SELECT
customer_segment,
COUNT(*) AS customer_count,
ROUND(AVG(recency_days), 0) AS avg_recency_days,
ROUND(AVG(frequency), 1) AS avg_orders,
ROUND(AVG(monetary), 2) AS avg_lifetime_value,
ROUND(SUM(monetary), 2) AS total_revenue_contribution,
ROUND(SUM(monetary) /
SUM(SUM(monetary)) OVER() * 100, 1) AS pct_of_revenue
FROM rfm_final -- replace with your final query name or CTE
GROUP BY customer_segment
ORDER BY total_revenue_contribution DESC;This output will typically show you something that surprises people the first time they run it: a small number of segments generate a disproportionate share of revenue. Champions and Loyal Customers are usually 10–20% of your customer base but 50–70% of your revenue. At Risk customers are often a similarly small percentage of customers but represent significant revenue that is actively in danger of being lost.
What to do with each segment
The point of RFM is not the scores. It is the actions. Here is how to use each segment:
Champions (high R, high F, high M) These customers are already doing what you want. Do not over-communicate with them — they do not need heavy discounting and aggressive email sequences. Focus on loyalty rewards, early access to new products and referral programmes. They are your best candidates for word-of-mouth growth.
Loyal Customers (high F, decent M, moderate R) Ask them for reviews. Make them feel recognised. A personalised thank-you, a birthday offer or early access to a sale costs very little and reinforces the behaviour you want to continue. Avoid training them to wait for discounts.
Potential Loyalists (recent, some frequency) This is your highest-leverage segment for intervention. These customers have shown enough interest to come back at least once. A targeted email sequence — product education, community, social proof — can convert them from occasional buyers to loyal customers.
At Risk (used to buy regularly, have not returned recently) These customers need a win-back campaign with a time-limited reason to return. The offer should reflect their historical spend level — a customer who spent $400 with you warrants a more generous offer than one who spent $40. Keep the message honest: "We noticed you haven't been back in a while and wanted to check in" outperforms a generic discount blast.
Hibernating (low everything) Low-cost, automated communication only. A reactivation email sequence is worth running, but do not invest significant manual effort here. If they respond, great. If not, suppress them from future campaigns — continuing to email unengaged customers hurts your sender reputation and skews your engagement metrics.
Common mistakes when building RFM
Using calendar year as the analysis window without thinking about seasonality
If you pull data from January to December for a retail business, December customers will look more recent than they should relative to October customers who bought during peak season. Either use a rolling window (last 12 months from today) or explicitly account for seasonal patterns.
Not deduplicating on order level before aggregating
If your transactions table can have multiple rows per order (one per line item, for example), your frequency count will overcount orders and your monetary sum may double-count. Always group by order_id before aggregating to the customer level.
Treating all 1-1-1 customers the same
A 1-1-1 customer who made one small purchase two years ago is different from a 1-1-1 customer who made one large purchase last week and has not returned yet. Filter your segmentation outputs before acting on them rather than treating every low-score customer as the same problem.
Setting thresholds once and never revisiting them
Customer behaviour changes. A recency threshold of 90 days that made sense in 2022 might need to be 60 days in 2024 if your purchase cycle has shortened. Re-run the distribution analysis and check whether your thresholds still reflect reality every quarter.
Taking it further
RFM is a starting point. Once you have the segments built and understood, there are natural extensions:
Add a churn probability layer. The At Risk and Hibernating segments are your highest churn risk, but not all At Risk customers are equally likely to return. A simple logistic regression model trained on which At Risk customers in previous cohorts went on to reactivate can help you prioritise intervention spend.
Track segment migration over time. Run the RFM analysis monthly and build a migration matrix showing how many customers moved from Potential Loyalists to Loyal Customers, or from Loyal to At Risk. Segment movement tells you whether your retention efforts are working in ways that aggregate revenue numbers sometimes mask.
Use the monetary score to set offer levels. Your campaign budget should reflect expected return. Champions and Loyal Customers with high M scores warrant higher-value offers. Hibernating customers with low M scores should receive low-cost automated touchpoints only.
Full query — copy and run
Here is the complete query in one block, ready to run against a transactions table:
WITH reference_date AS (
SELECT DATE('2024-01-01') AS ref_date
),
rfm_base AS (
SELECT
t.customer_id,
DATEDIFF(r.ref_date, MAX(t.order_date)) AS recency_days,
COUNT(DISTINCT t.order_id) AS frequency,
SUM(t.order_value) AS monetary
FROM transactions t
CROSS JOIN reference_date r
GROUP BY t.customer_id, r.ref_date
),
rfm_scored AS (
SELECT
customer_id,
recency_days,
frequency,
monetary,
6 - NTILE(5) OVER (ORDER BY recency_days ASC) AS r_score,
NTILE(5) OVER (ORDER BY frequency ASC) AS f_score,
NTILE(5) OVER (ORDER BY monetary ASC) AS m_score
FROM rfm_base
),
rfm_segmented AS (
SELECT
customer_id,
recency_days,
frequency,
ROUND(monetary, 2) AS monetary,
r_score,
f_score,
m_score,
CONCAT(r_score, f_score, m_score) AS rfm_score,
ROUND((r_score + f_score + m_score) / 3.0, 2) AS rfm_avg_score,
CASE
WHEN r_score = 5 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
WHEN f_score >= 4 AND m_score >= 3 THEN 'Loyal Customers'
WHEN r_score >= 4 AND f_score >= 2 THEN 'Potential Loyalists'
WHEN r_score >= 4 AND f_score <= 2 THEN 'Recent Customers'
WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
WHEN r_score <= 2 AND f_score <= 2 THEN 'Hibernating'
WHEN r_score = 3 AND f_score >= 2 THEN 'About to Lapse'
WHEN r_score = 3 AND m_score >= 3 THEN 'Need Attention'
ELSE 'Others'
END AS customer_segment
FROM rfm_scored
)
SELECT * FROM rfm_segmented
ORDER BY rfm_avg_score DESC;Key takeaways
- RFM uses three metrics — Recency, Frequency, Monetary — available in any transactions table
- NTILE(5) splits customers into five equal scoring bands per metric — adjust thresholds for your business
- Segments are only useful if they lead to different actions — Champions need different treatment from At Risk customers
- Run the base query first and check the data distribution before building the scoring layer
- Re-run monthly and track segment migration to measure whether your retention strategy is working
- RFM is a foundation, not a ceiling — layering churn probability models on top makes it significantly more powerful
Building RFM segmentation for your business and want a second pair of eyes on the query or the segment strategy? Feel free to get in touch.
Patience Anono · PA Data Analytics · [padataanalytics.com](https://padataanalytics.com) · hello@padataanalytics.com