Customer Segmentation with RFM Analysis in SQL

In the world of data analytics, understanding your customers is key to driving growth. One of the most effective and time-tested methods for customer segmentation is RFM Analysis. But you don't need expensive CRM software to do it—you can build a powerful RFM model right in your database using standard SQL.

In this guide, we'll walk through how to calculate Recency, Frequency, and Monetary scores and segment your customer base into actionable groups like "Champions", "At Risk", and "New Customers".

What is RFM Analysis?

RFM stands for three key metrics that describe customer behavior:

1. Recency (R): How recently did the customer make a purchase?
2. Frequency (F): How often do they purchase?
3. Monetary (M): How much do they spend?

By scoring customers on these three dimensions (usually from 1 to 5), you can group them into segments. For example, a customer with high scores in all three categories is a "Champion," while someone with high Monetary value but low Recency might be "At Risk" of churning.

Step 1: Preparing the Data

To perform RFM analysis, you typically need a transaction table. Let's assume we have an orders_rfm_demo table with customer_id, order_date, and total_amount.

The first step is to aggregate this data to the customer level. We need:

• Last Order Date: MAX(order_date)
• Count of Orders: COUNT(order_id)
• Total Spend: SUM(total_amount)

SELECT
    customer_id,
    MAX(order_date) as last_order_date,
    COUNT(order_id) as frequency,
    SUM(total_amount) as monetary
FROM orders_rfm_demo
GROUP BY customer_id;

Step 2: Calculating RFM Values

Now, let's turn these raw numbers into the "R", "F", and "M" values.

• For Recency, we need the number of days since the last purchase. We'll compare the last_order_date to a reference date (usually "today" or the date of analysis).

SELECT
    customer_id,
    julian_day('2024-01-01') - julian_day(MAX(order_date)) as recency_days,
    COUNT(order_id) as frequency,
    SUM(total_amount) as monetary
FROM orders_rfm_demo
GROUP BY customer_id;

(Note: In most SQL dialects like PostgreSQL you might use CURRENT_DATE - MAX(order_date), but here we use a fixed date for reproducibility.)

Step 3: Determining RFM Scores with NTILE

This is where the magic happens. We can't just look at raw dollars because spending ranges vary wildly between businesses. Instead, we rank customers relative to each other using the logic: "Top 20% get a score of 5, bottom 20% get a score of 1".

The SQL window function NTILE(5) is perfect for this.

• Recency Score: Lower days is better (5 points), higher days is worse (1 point). Wait, NTILE assigns 1 to the lowest values. So for Recency (where low is good), a low rectency_days gets NTILE 1. We want the opposite. We can order by recency_days DESC for the NTILE function so the largest days (worst) get 1 and smallest (best) get 5. Or simply order DESC.
• Frequency Score: Higher is better. Order by frequency ASC.
• Monetary Score: Higher is better. Order by monetary ASC.

Let's see it in action in our playground.

Interactive RFM Playground

Try modifying the queries below to see how different customers are scored.

Step 4: Creating Human-Readable Segments

Having a score like "555" or "121" is useful for machines, but humans prefer labels. We can use a CASE statement to translate these scores into named segments.

Common segment definitions:

• Champions: R=4-5, F=4-5, M=4-5
• Loyal Customers: F=3-5, R=3-5 (regardless of M)
• Potential Loyalists: Recent customers with average frequency
• Needing Attention: High R, Low F/M (About to churn)

SELECT
    customer_id,
    r_score, f_score, m_score,
    CASE
        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
        WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal Customers'
        WHEN r_score >= 4 AND f_score = 1 THEN 'New Customers'
        WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
        ELSE 'Standard'
    END as customer_segment
FROM scored_data;

Why RFM Works So Well

RFM is powerful because it compresses messy customer history into three business-readable signals:

• Recency: are they still engaged?
• Frequency: do they come back often?
• Monetary: are they economically meaningful?

That makes it much easier to move from raw transaction tables into segmentation decisions that marketing, CRM, and lifecycle teams can actually use.

Instead of one giant "average customer," you get groups with different behaviors:

• high-value loyal customers
• promising but still new customers
• big spenders drifting away
• one-time low-value buyers

Those are much more actionable than a single blended retention or revenue number.

RFM Is Only as Good as the Business Definitions

The scoring logic may be technically correct but still strategically weak if the definitions are sloppy.

Questions to settle first:

• Are refunds excluded?
• Are test or internal orders excluded?
• Is frequency counting orders, sessions, or paid invoices?
• Over what time window are scores calculated?
• Are all customers compared globally, or within region / channel / plan type?

Those choices affect the meaning of every segment. SQL makes the calculation reproducible, but it does not pick the right business definition for you.

Percentiles vs Custom Score Buckets

The article uses NTILE(5) because it is a strong general starting point. But percentile-style scoring is not always the best production choice.

Percentile scoring is good when:

• the customer base is large
• you want relative ranking
• the distributions are reasonably smooth

Custom score buckets are better when:

• the distributions are extremely skewed
• the business already has clear thresholds
• you need stable definitions over time

For example, "Frequency >= 10 orders gets a 5" may be more useful than a moving percentile if the marketing team needs consistent segment rules quarter after quarter.

A Practical Workflow for RFM SQL

Use this sequence when building or reviewing RFM segmentation:

1. Clean the transaction dataset.
2. Aggregate to one row per customer.
3. Validate raw Recency, Frequency, and Monetary metrics.
4. Apply score logic.
5. Translate score combinations into business labels.
6. Inspect the size and value profile of each segment before shipping the report.

That last step matters. A beautiful segmentation model is not useful if the resulting groups are too small, too noisy, or strategically meaningless.

Best Practices for RFM in SQL

1. Filter Anomalies: Exclude returns (negative values) or test transactions before calculating.
2. Timeframe Matters: RFM is usually calculated over a sliding window (e.g., last 12 months). Older data might skew Recency.
3. Binning Distributions: NTILE forces equal-sized groups. If your data is skewed (e.g., 90% of customers bought once), NTILE might arbitrarily split them. In such cases, using custom CASE ranges for scores (e.g., "Frequency 1 = 1 point", "Freq 2-5 = 3 points") might be more accurate than strict percentiles.

Related Articles

• Calculating Customer Lifetime Value (CLV) in SQL for connecting segments to customer economics instead of treating all value as one average.
• Cohort Analysis with SQL for measuring how grouped customer behavior changes over time after acquisition.
• Building Conversion Funnels in SQL for analyzing the earlier journey stages that often feed your segmented customer base.

Conclusion

RFM analysis is a low-hanging fruit in data science that delivers immediate value. By implementing it directly in SQL, you can create dynamic dashboard reports that update automatically as new orders come in. No need to export CSVs or run Python scripts—your database can handle the heavy lifting!