RFM Segmentation in SFMC: SQL Queries, Scores and Segments

You have purchase history in Salesforce Marketing Cloud, and right now your best customers get the same email as someone who bought once two years ago. RFM segmentation in SFMC fixes that, and it takes two SQL queries rather than a data science project.

How do you do RFM segmentation in SFMC? Use a SQL query activity to score every customer on recency (days since the last order), frequency (number of orders) and monetary value (total spend), then map those scores to named segments such as Champions or At risk. Write the result to a data extension and use it in Email Studio or Journey Builder.

‍

This guide gives you both queries, the target data extension setup, a checklist you can copy, and the pitfalls that usually break RFM audiences. If you would rather skip the typing, you can also generate SFMC SQL from plain English and review the result before it runs.

‍

What RFM segmentation means in SFMC

‍

RFM is a scoring model from direct marketing. It ranks customers by how recently they bought, how often they buy and how much they spend. The idea is simple: people who bought recently, often and for a lot of money deserve a different message than people who drifted away.

‍

The three signals

Recency is the number of days since the last order. Frequency is the count of orders in a fixed window, for example the last 24 months. Monetary is the sum of order values in that same window.

Each signal gets a score from 1 to 5, where 5 is best. Three scores combine into a segment. You decide where the thresholds sit, because a luxury retailer and a grocery brand have very different purchase cycles.

‍

Why SQL fits better than a filter

Filtered Data Extensions filter rows on fields that already exist. RFM needs a count and a sum across several order rows per customer, so the data has to be aggregated first. A SQL query activity in Automation Studio does exactly that with GROUP BY, COUNT(), SUM() and MAX().

‍

Prepare the data: orders and customer data extensions

‍

Before you write any SQL, you need four data extensions in place: a source of customers, a source of orders, and two targets for the results.

‍

Source data extensions

The examples use generic names. Replace them with your own.

• Customers_Master: one row per customer, with SubscriberKey and EmailAddress.
• Orders: one row per order, with OrderID, SubscriberKey, OrderDate (date) and OrderTotal (number or decimal).

If your orders carry refunds, cancellations or test orders, decide now how to exclude them. RFM on dirty order data produces confident but wrong segments.

‍

Target data extensions

Create both targets before you build the query activities, because the target cannot be a data extension used inside the query itself, as Salesforce documents for the SQL query activity.

• RFM_Scores: SubscriberKey (primary key), EmailAddress, RecencyDays, Frequency, Monetary, RScore, FScore, MScore.
• RFM_Segments: SubscriberKey (primary key), EmailAddress, RScore, FScore, MScore, Segment.

Both use Overwrite as the update type, because scores change every time the model runs and you want a clean snapshot, not stale rows. If you want a refresher on the three options, read Overwrite vs Update vs Append in SFMC query activities.

‍

RFM segmentation SQL, step 1: calculate and score R, F and M

‍

The first query aggregates orders per customer in an inner query, then scores each metric with CASE in the outer query. SFMC supports nested queries and CASE statements, but not common table expressions, so the inner query sits in the FROM clause.

‍

The query

SELECT
  s.SubscriberKey,
  s.EmailAddress,
  s.RecencyDays,
  s.Frequency,
  s.Monetary,
  CASE
    WHEN s.RecencyDays <= 30 THEN 5
    WHEN s.RecencyDays <= 90 THEN 4
    WHEN s.RecencyDays <= 180 THEN 3
    WHEN s.RecencyDays <= 365 THEN 2
    ELSE 1
  END AS RScore,
  CASE
    WHEN s.Frequency >= 10 THEN 5
    WHEN s.Frequency >= 6 THEN 4
    WHEN s.Frequency >= 3 THEN 3
    WHEN s.Frequency = 2 THEN 2
    ELSE 1
  END AS FScore,
  CASE
    WHEN s.Monetary >= 1000 THEN 5
    WHEN s.Monetary >= 500 THEN 4
    WHEN s.Monetary >= 250 THEN 3
    WHEN s.Monetary >= 100 THEN 2
    ELSE 1
  END AS MScore
FROM (
  SELECT
    c.SubscriberKey,
    c.EmailAddress,
    DATEDIFF(day, MAX(o.OrderDate), GETDATE()) AS RecencyDays,
    COUNT(o.OrderID) AS Frequency,
    SUM(o.OrderTotal) AS Monetary
  FROM Customers_Master c
  INNER JOIN Orders o ON o.SubscriberKey = c.SubscriberKey
  WHERE o.OrderDate >= DATEADD(month, -24, GETDATE())
  GROUP BY c.SubscriberKey, c.EmailAddress
) s

Target data extension: RFM_Scores. Primary key: SubscriberKey. Update type: Overwrite.

‍

How the query works

• The inner query joins Customers_Master to Orders on SubscriberKey and keeps only the last 24 months of orders.
• MAX of OrderDate gives the latest order, and DATEDIFF turns it into days since that order. That is recency.
• COUNT of OrderID is frequency, and SUM of OrderTotal is monetary value.
• GROUP BY collapses all orders of one customer into a single row.
• The outer query wraps that result as s and scores each metric from 1 to 5 with CASE. Each CASE stops at the first match, so keep the conditions ordered from best to worst.

The thresholds are examples. Look at your own distribution first, for instance by running the inner query alone, and move the cut-offs so each score holds a meaningful share of customers. For more on DATEADD() and DATEDIFF(), see SFMC SQL date functions.

‍

Step 2: turn RFM scores into named segments

‍

Scores are useful for analysis, but marketers need audiences with names. The second query reads RFM_Scores, which is a different data extension from its target, and applies a segment label.

‍

The segment query

SELECT
  r.SubscriberKey,
  r.EmailAddress,
  r.RScore,
  r.FScore,
  r.MScore,
  CASE
    WHEN r.RScore >= 4 AND r.FScore >= 4 AND r.MScore >= 4 THEN 'Champions'
    WHEN r.RScore >= 3 AND r.FScore >= 3 THEN 'Loyal'
    WHEN r.RScore <= 2 AND r.FScore >= 4 THEN 'At risk'
    WHEN r.RScore >= 4 AND r.FScore <= 2 THEN 'New or promising'
    WHEN r.RScore <= 2 AND r.FScore <= 2 THEN 'Lapsed'
    ELSE 'Needs attention'
  END AS Segment
FROM RFM_Scores r

Target data extension: RFM_Segments. Primary key: SubscriberKey. Update type: Overwrite.

‍

How to read the segments

• Champions: recent, frequent, high spend. Reward them, ask for reviews, give early access.
• Loyal: solid on recency and frequency. Keep them engaged with relevant content.
• At risk: they bought often but not recently. This is your win-back priority.
• New or promising: bought recently but only once or twice. Welcome and onboarding content fits here.
• Lapsed: low recency and low frequency. Try one reactivation attempt, then reduce pressure.
• Needs attention: everyone in between.

The order of the WHEN lines matters. A customer who matches Champions is never tested against the later rules. For the win-back side, read how to build a reactivation audience in SFMC.

‍

Quick answer: RFM segmentation checklist

‍

Copy this list when you set the model up for the first time.

1. Create Customers_Master and Orders, or confirm they already exist with SubscriberKey on both.
2. Create RFM_Scores and RFM_Segments with SubscriberKey as primary key.
3. Build query activity 1 with the scoring query, target RFM_Scores, Overwrite.
4. Build query activity 2 with the segment query, target RFM_Segments, Overwrite.
5. Put both in one automation, in that order, and schedule it.
6. Use RFM_Segments as the entry source or as a sendable audience.

‍

Pitfalls that break RFM audiences

‍

Most RFM problems come from the data, not the SQL. These are the ones worth checking before the first send.

‍

Data and logic issues

• Customers with no orders disappear because of the INNER JOIN. Use a LEFT JOIN if you want a never-purchased segment.
• Returns and cancelled orders inflate frequency and monetary value unless you filter them out in the WHERE clause.
• Multiple currencies make monetary scores meaningless. Convert to one currency first.
• SubscriberKey and email are not the same thing. Join on the key that your orders actually carry.

‍

Performance and output issues

Queries in SFMC have a 30-minute timeout, so filter the order window instead of scanning all history. If a run still fails, see SFMC SQL query timeout: causes and fixes.

Salesforce also notes that decimal fields used in a query do not return decimal output, so test the Monetary column and consider working in whole currency units. Read the SQL reference for the current list of limits.

‍

FAQ

‍

How do you do RFM segmentation in SFMC?

Aggregate orders per customer with a SQL query activity to get recency, frequency and monetary value. Score each from 1 to 5 with CASE, write the scores to a data extension, then run a second query that maps score combinations to named segments.

‍

Can I use NTILE for RFM scoring in SFMC SQL?

The Salesforce SQL reference does not list NTILE, and it states that SQL support does not precisely correspond to SQL Server. Fixed CASE thresholds are the safe choice. If you want percentile scoring, test it with Validate Syntax before you rely on it.

‍

How often should I refresh RFM scores?

Match your purchase cycle. Weekly works for most retailers, daily for high-volume stores. Because both queries use Overwrite, each run replaces the previous snapshot.

‍

Can a filtered data extension do RFM?

Not on its own. A filtered data extension filters existing rows and does not count or sum across several order rows per customer. That is why the aggregation has to happen in SQL first.

‍

Build this audience in seconds with QAiry

‍

Writing and debugging RFM queries is a good exercise, but it is slow when marketers need a new segment every week. QAiry turns a plain-language request into production-ready SFMC SQL and a Data Extension inside your own account, so you can review the query before it runs.

You can try QAiry for free or watch a product demo to see how a request like customers who bought three times in the last year but not in the last 60 days becomes an audience.