Quick answer:
RFM scores customers from 1 to 5 on recency (last order date), frequency (order count) and monetary value (total spend), usually with ntile. It joins scores into a cell such as 543, then maps cells to named segments with CASE.
Shopify already assigns RFM groups in customer segments. SQL lets you use your own cut-offs and segment names or include data from outside Shopify.
When you’re in the business of selling a product or service, any customer is a good customer, right?
Not exactly.
The reality is that while all your customers absolutely deserve tender loving care, they’re not all equally valuable.
Thankfully, RFM analysis enables you to use a simple rubric to discover who your best customers really are and to make them even more of an asset. In this post, we’ll explain what RFM analysis is, show you how to create RFM cells, and explain how that translates into RFM customer segmentation.
This tutorial features SQL queries written in Postgres-style SQL, which also runs on Amazon Redshift, Snowflake and DuckDB. If you’re on a Google BigQuery data warehouse, you’ll need small adjustments, such as CAST(... AS STRING) instead of CAST(... AS VARCHAR) and a dataset name in front of the view name.
What is RFM analysis?
An RFM report is a way of segmenting customers using three key metrics: recency (how long ago their last purchase was), frequency (how often they purchase), and monetary value (how much they spent).
RFM analysis is based on the assumption that customers who purchased from you recently are more likely to purchase again. That principle holds true across a variety of industries, as does evidence that frequent buyers and big spenders are more likely to respond to promotions than customers who buy less often or who spend less.
Created in the ‘90s as a way to make direct mail campaigns more effective, RFM leverages the Pareto principle to answer critical questions like:
- Who are my best or most loyal customers?
- Which customers am I in danger of losing?
- Which customers should I focus my marketing efforts on?
- Which customers can I entice to spend more?
This kind of customer analysis can make a huge impact on your business. Whether you’re looking to improve your marketing ROI, build loyalty, reduce churn, or increase CLV, RFM analysis is a step in the right direction.
Does Shopify Have Built-in RFM Segments?
Yes. Shopify assigns RFM groups based on recency, frequency and monetary spend, including Champions, Loyal, Needs attention and At risk. The customer segment editor offers the rfm_group filter alongside number_of_orders, amount_spent and last_order_date. ShopifyQL’s sales schema also includes an rfm_group dimension for splitting sales reports by group, giving you a starting point without SQL.
Shopify defines those groups, so you use its labels rather than your own cut-offs and segment names. Its scores only see Shopify activity; a warehouse version lets you control how to exclude test and cancelled orders and count refunds against spend. You can also join outside data, such as ad costs or support tickets. This chapter builds that version; the Shopify analytics guide covers how Shopify data gets into a warehouse.
How to Create an RFM Report for Shopify Data
Because it focuses on just three metrics, RFM reporting is deceptively simple. For example, when doing RFM analysis for Shopify, all you really need is three data points:
- Recency: last order date
- Frequency: count of total orders
- Monetary value: total spend
Together, these metrics reveal who your repeat purchasers are, which customers are the most loyal over time, and which customers spend the most in your shop. An RFM report provides critical information about your customers and while RFM is strongly associated with B2C and retail, it’s an equally valid way for B2B, services, or SaaS businesses to better understand their customers.
To get that information, sync your Shopify data to a data warehouse with a connector such as Fivetran or Airbyte, then run the following query in your SQL editor. It saves the results as a view called rfm_values, so the later queries can build on it:
/* RFM VALUES: one row per customer, saved as a view */
CREATE VIEW rfm_values AS
WITH refunds_by_order AS (
/* REFUNDS: value and tax returned per order */
SELECT r.order_id,
sum(rli.subtotal) AS refunds,
sum(rli.total_tax) AS tax_returned
FROM refunds r
JOIN order_line_refunds rli ON rli.refund_id = r.id
GROUP BY 1
),
shipping_by_order AS (
/* SHIPPING LINES: charges after shipping discounts */
SELECT order_id,
sum(discounted_price) AS shipping_charge
FROM order_shipping_lines
GROUP BY 1
)
SELECT lower(o.email) AS email, -- CUSTOMER
max(o.created_at) AS recent_order, -- RECENCY
count(o.id) AS total_orders, -- FREQUENCY
sum(o.total_line_items_price) AS gross_sales, -- MONETARY (AS GROSS SALES)
sum(o.total_line_items_price
- coalesce(o.total_discounts, 0)
- coalesce(r.refunds, 0)
+ coalesce(o.total_tax, 0)
- coalesce(r.tax_returned, 0)
+ coalesce(s.shipping_charge, 0)) AS total_sales -- MONETARY (AS TOTAL SALES)
FROM orders o
LEFT JOIN refunds_by_order r ON r.order_id = o.id
LEFT JOIN shipping_by_order s ON s.order_id = o.id
WHERE NOT o.test
AND o.cancelled_at IS NULL
AND o.email IS NOT NULL
GROUP BY 1;
SELECT * FROM rfm_values ORDER BY email;
The query uses generic table names: orders, refunds, order_line_refunds and order_shipping_lines. Each connector names its tables its own way, so map them to yours; the Shopify data model chapter shows how they relate. Test and cancelled orders are filtered out so they don’t count as purchases. total_sales subtracts discounts and refunds and adds tax and shipping, while gross_sales is the line item total before any of that. Emails are lowercased so one customer isn’t split in two.
The query above provides the values you’ll need to carry out an RFM analysis. The resulting data will look like this:
| recent_order | total_orders | gross_sales | total_sales | |
|---|---|---|---|---|
| sarah@example.com | 2026-01-03 10:00:00 | 2 | 3030.00 | 2725.29 |
| eli@example.com | 2026-09-22 09:00:00 | 1 | 1250.00 | 1337.50 |
The table above tells us that while Sarah has greater frequency (2 orders) and monetary value ($3,030 in gross sales; $2,725.29 in total sales), Eli may actually be a better target for an end-of-year promotion because he’s purchased more recently.
How to go from raw data to actionable RFM analysis
Once you have a table with data on each of your customers’ recency, frequency, and monetary value, it’s time to start segmenting. While you can slice the data as finely as you’d like, using quintiles mirrors the Pareto principle by indicating which of your customers are in the top 20% for all three metrics.
To create the quintiles, we’ll use an ntile window function that needs just a few lines of SQL on top of the RFM Values query. We’ll continue to use Shopify as an example, but the same query can be used for any business with just a few adjustments to the table and column names.
/* RFM QUINTILES */
SELECT email,
ntile(5) OVER (ORDER BY recent_order, email) AS rfm_recency,
ntile(5) OVER (ORDER BY total_orders, email) AS rfm_frequency,
ntile(5) OVER (ORDER BY total_sales, email) AS rfm_monetary
FROM rfm_values
ORDER BY 2 DESC, 3 DESC, 4 DESC;
This query returns a table with customer emails alongside the quintile that each customer falls into for recency, frequency, and monetary value. Looking at recency, a 5 means the customer’s purchase was within the top 20% most recent, while a 1 means the customer is in the bottom 20%.
| rfm_recency | rfm_frequency | rfm_monetary | |
|---|---|---|---|
| david@example.com | 5 | 4 | 3 |
| amelia@example.com | 5 | 2 | 5 |
| matthew@example.com | 5 | 2 | 4 |
| preethi@example.com | 5 | 1 | 5 |
| hanna@example.com | 5 | 1 | 4 |
| racquel@example.com | 4 | 5 | 3 |
| jordan@example.com | 4 | 5 | 2 |
| juan@example.com | 3 | 5 | 5 |
| nadim@example.com | 3 | 2 | 5 |
| alys@example.com | 2 | 5 | 5 |
In the table above, we can see that while Hanna is in the top 20% of customers for recency, she’s in the bottom 20% for purchase frequency. Meanwhile, Jordan hasn’t purchased as recently, but he’s bought multiple small orders from you.
When tied order counts skew the frequency score
ntile creates five nearly equal groups, even with tied values. If one-time buyers make up 40% of your customer list, some land in frequency group 1 and others in group 2 despite identical order counts.
The email after each ORDER BY above is a tie-breaker that keeps those splits consistent between runs with unchanged data, but it doesn’t make them meaningful. For frequency, fixed order-count thresholds often read better than quintiles. To use them, swap this expression in for the rfm_frequency line:
/* FREQUENCY BY FIXED ORDER COUNTS */
CASE WHEN total_orders >= 6 THEN 5
WHEN total_orders >= 4 THEN 4
WHEN total_orders = 3 THEN 3
WHEN total_orders = 2 THEN 2
ELSE 1 END AS rfm_frequency
The thresholds shown fit the sample store, so set yours from your own order-count distribution. Recency and monetary value rarely tie, so quintiles usually work for them, and the rest of this chapter keeps quintiles for all three scores.
Creating RFM cells
Now that you know where your customers fall generally, let’s make the quintile data easier to work with. The most common approach is to concatenate the quintile rank for each metric to create a 3-digit number also known as a “cell.”
When concatenating the data this way, your best customers would have a cell value of 555 because they’re in the top 20% for all three metrics, while a cell value of 111 would be given to customers in the bottom 20% for all three.
/* RFM CELL */
WITH quintiles AS (
SELECT email,
ntile(5) OVER (ORDER BY recent_order, email) AS rfm_recency,
ntile(5) OVER (ORDER BY total_orders, email) AS rfm_frequency,
ntile(5) OVER (ORDER BY total_sales, email) AS rfm_monetary
FROM rfm_values
)
SELECT email,
rfm_recency,
rfm_frequency,
rfm_monetary,
CAST(rfm_recency AS VARCHAR) || CAST(rfm_frequency AS VARCHAR)
|| CAST(rfm_monetary AS VARCHAR) AS rfm_cell
FROM quintiles
ORDER BY 5 DESC;
CAST converts scores to text because Postgres won’t join integers with ||; the later CASE step also needs rfm_cell as text.
| rfm_recency | rfm_frequency | rfm_monetary | rfm_cell | |
|---|---|---|---|---|
| david@example.com | 5 | 4 | 3 | 543 |
| amelia@example.com | 5 | 2 | 5 | 525 |
| matthew@example.com | 5 | 2 | 4 | 524 |
| preethi@example.com | 5 | 1 | 5 | 515 |
| hanna@example.com | 5 | 1 | 4 | 514 |
| racquel@example.com | 4 | 5 | 3 | 453 |
| jordan@example.com | 4 | 5 | 2 | 452 |
| juan@example.com | 3 | 5 | 5 | 355 |
| nadim@example.com | 3 | 2 | 5 | 325 |
| alys@example.com | 2 | 5 | 5 | 255 |
While this approach is popular, reading the data requires some mental gymnastics. It’s natural to look at the rfm_cell column and treat it as a 3-digit number. Looking at the data that way would mean customers in “higher” RFM cells are the more valuable ones.
But because this way of segmenting by RFM is just a concatenation of three numbers, that may not be the case. Looking at the example above, Alys might seem like one of your best customers because she’s in the top 20% in two categories: frequency and monetary value. However, she hasn’t bought from you in a while so Racquel could actually be the better target for a marketing campaign.
Creating actionable RFM segmentation
Now that you have clear RFM cells, you need to create meaningful groupings that can help turn the data into actionable insights. With three metrics and five tiers for each, you can create up to 125 (5x5x5) customer segments. If the idea of 125 segments is giving you a headache, consider using quartiles (which would give you 4x4x4 = 64 segments) or even thirds (3x3x3 = 27 segments) to simplify things.
How you draw the lines between segments depends on the nature of your business. If you’re a hardcore Pareto fan who wants to reward your best purchasers, you may want to focus on customers with an RFM cell of 555. However, if you want to open things up a bit to include recent and frequent customers who may have spent a little less, you could create a segment that includes everyone with an RFM cell value of 555 and 554.
The key when creating customer segments is to make the buckets large enough to be actionable (Marketing will lose their minds if they think they need to create 125 separate customer campaigns) but small enough that you can address them as a group. As an example, we’ll outline just four potential segments, but if you’re interested in getting more specific, check out this resource for an even more robust version.
The key when creating customer segments is to make the buckets large enough to be actionable but small enough that you can address them as a group.
Here’s an example of how you might segment RFM cell data:
| Cannot lose | Active fans | Promising newbies | Potential churners | |
|---|---|---|---|---|
| RFM cell values | 355, 255 | 543, 542, 453, 452 | 525, 524, 515, 514 | 335, 334, 325, 324 |
| Conditions for inclusion | Low R High F&M | High R&F Low M | High R&M Low F (so far) | Mid R Low F High M |
| Description | Big spenders who haven’t purchased lately | Customers who buy often & recently, but at low price points | New customers with large orders | High spending customers who haven’t purchased often or lately |
Once you’ve identified the segments you want to address and the conditions for inclusion, meaning the RFM cell values associated with each, you can create a SQL query that puts customers into the right bucket. To do that, use CASE statements to return the segment name based on the RFM cell value:
/* RFM SEGMENTS */
WITH quintiles AS (
SELECT email,
ntile(5) OVER (ORDER BY recent_order, email) AS rfm_recency,
ntile(5) OVER (ORDER BY total_orders, email) AS rfm_frequency,
ntile(5) OVER (ORDER BY total_sales, email) AS rfm_monetary
FROM rfm_values
),
cells AS (
SELECT *,
CAST(rfm_recency AS VARCHAR) || CAST(rfm_frequency AS VARCHAR)
|| CAST(rfm_monetary AS VARCHAR) AS rfm_cell
FROM quintiles
)
SELECT email,
rfm_recency,
rfm_frequency,
rfm_monetary,
rfm_cell,
CASE WHEN rfm_cell IN ('355', '255') THEN 'Cannot lose'
WHEN rfm_cell IN ('543', '542', '453', '452') THEN 'Active fans'
WHEN rfm_cell IN ('525', '524', '515', '514') THEN 'Promising newbies'
WHEN rfm_cell IN ('335', '334', '325', '324') THEN 'Potential churners'
ELSE 'Other' END AS rfm_segment
FROM cells
ORDER BY 6 DESC, 5 DESC;
| rfm_recency | rfm_frequency | rfm_monetary | rfm_cell | rfm_segment | |
|---|---|---|---|---|---|
| hanna@example.com | 5 | 1 | 4 | 514 | Promising newbies |
| preethi@example.com | 5 | 1 | 5 | 515 | Promising newbies |
| amelia@example.com | 5 | 2 | 5 | 525 | Promising newbies |
| matthew@example.com | 5 | 2 | 4 | 524 | Promising newbies |
| nadim@example.com | 3 | 2 | 5 | 325 | Potential churners |
| alys@example.com | 2 | 5 | 5 | 255 | Cannot lose |
| juan@example.com | 3 | 5 | 5 | 355 | Cannot lose |
| racquel@example.com | 4 | 5 | 3 | 453 | Active fans |
| jordan@example.com | 4 | 5 | 2 | 452 | Active fans |
| david@example.com | 5 | 4 | 3 | 543 | Active fans |
This query returns a table with each customer and their associated rfm_segment. In the table above, there’s a lot of good news: You have a mix of active fans and high value customers, and you’re attracting plenty of promising newbies. However, you do have a churn risk (Nadim) that may need attention.
In the full sample, 15 of 25 customers land in Other, because these four segments cover only 14 of the 125 possible cells. That’s normal for a first pass. Before handing the list to Marketing, it’s worth looking at the largest cells inside Other and adding them to an existing segment or giving them one of their own.
What you do next depends on how Marketing prefers to work with the data: You can upload the tags straight into Shopify or marketing automation platform so they can easily create email lists with personalized offers at scale. For example, those “can’t lose” customers might get an invitation to become brand ambassadors, while those “promising newbies” get follow-up emails with recommended products that complement their previous purchases.
Getting segments back into Shopify
There are three ways to send these segments to Shopify as customer tags:
- A customer CSV import, with a
Tagscolumn, using the link above. - A scheduled script, using the GraphQL Admin API’s
tagsAddmutation. - A reverse ETL tool, such as Hightouch, syncing warehouse results to Shopify and other marketing tools.
Use one prefix for every RFM tag, such as rfm-cannot-lose, and remove the old RFM tag when a customer’s segment changes, so nobody carries two. Once the tags are in Shopify, a customer segment can filter on them. This one keeps Cannot lose customers who accept email marketing:
customer_tags CONTAINS 'rfm-cannot-lose' AND email_subscription_status = 'SUBSCRIBED'
A Shopify segment is a saved filter, not a fixed list, so membership follows synced RFM tag changes without rebuilding the segment.
What comes next: Taking RFM to the next level
You may not be launching winback email campaigns, but that doesn’t mean your work here is done. Once you have a solid RFM framework in place, you can make it even more valuable by working with stakeholders to create a dashboard that would make it easy for them to view customers by segment on demand.
BI tools such as Metabase or Looker Studio can read the segment query straight from the warehouse, so the dashboard updates whenever the query reruns.
You could also consider layering in additional metrics. For example, you could add relationship length to the mix to find LRFM, which adds in the delicious complexity of identifying customers with long term loyalty. Alternatively, you could layer in data around acquisition costs. While those 555s might be unicorns, if they were acquired through expensive ad campaigns, they’ll have to stick around for a while to really be worth it.
Alternatively, you could move away from simplistic cells and toward more sophisticated RFM clustering using a K-means model. With clustering, you’d input values for recency, frequency, and monetary value and the algorithm would identify the optimum number of segments as well as which customers fall in each grouping. It’s a more complex (and, arguably, time-intensive) approach, but it’s also far more sensitive than crudely assigning RFM cells to segments.
And, of course, you should be working to measure the success of the campaigns launched for each segment. That could be as simple as looking at something like engagement, purchase rate, or average order value, but you might also consider measuring RFM migration, which is how customers move from one cell to another over time.
To measure migration, you need history. Save the segment query’s output to a table on a schedule, for example once a month, with a snapshot date column, then compare each customer’s cell between two snapshots. A scheduled dbt model or your warehouse’s scheduled queries can run the refresh; dbt snapshots also record when a row changed.
No matter how you slice, er, segment it, using data to understand your customers is always a good idea. By running an RFM analysis, you can easily identify top customers as well as purchasers who could become even more valuable…with the right segmentation, of course.
Common questions
What is a good RFM score?
555 means a customer ranks in the top 20% for all three metrics with quintiles. A cell contains three separate scores, not an overall ranking: a 255 customer can matter more than a 453 customer, depending on the segment and campaign purpose.
Should I use quintiles, quartiles or fixed thresholds?
Quintiles mirror the Pareto principle and create 125 possible cells; quartiles give 64, and thirds give 27. Fixed thresholds suit frequency when customers share the same order count, avoiding splits across quintiles. Your order-count distribution determines which thresholds fit your store.
Can I do RFM analysis without a data warehouse?
Yes. Shopify’s rfm_group filter in customer segments and dimension in ShopifyQL sales reporting provide built-in groups without a warehouse. A warehouse helps when you want your own cut-offs and segment names, control over refund handling or data from outside Shopify.
Why do customers change RFM cells between runs?
Quintiles are relative: other customers’ new orders can shift boundaries without a customer purchasing again. Equal values can also swap groups without a tie-breaker. Dated snapshots track RFM migration, while a consistent tie-breaker helps distinguish arbitrary splits from changes in buying behavior.
Where to next
RFM shows who is valuable now; the customer lifetime value chapter estimates value over time. The customer analytics chapter covers other ways to understand your customers. When you’re mapping these queries to your warehouse, the Shopify data model chapter explains the tables behind them.
Connectors, warehouses and BI tools that speak Shopify, with pricing and free tiers.
Browse the tools