Build an RFM segmentation table with SQL

RFM describes customers using recency, frequency and monetary value. Recency measures time since the latest qualifying purchase; frequency counts qualifying purchases within a defined observation window; monetary value sums a specified financial measure in that window. These definitions matter more than the labels attached to the resulting segments.

A customer who bought yesterday is recent. That does not establish loyalty, profitability or willingness to accept a marketing offer. RFM is a descriptive starting point, and its usefulness depends on the decision you test with it.

Set the observation contract

Use the synthetic advanced SQL dataset. It contains eleven completed purchases from January through April 2026, with five customers and 135,000 paise of purchase value. The snapshot date is May 1. Dates are already normalized, and each row represents one purchase.

The exercise uses January 1 inclusive through May 1 exclusive. Frequency is purchase count, not purchased-item count. Monetary value is the fixture's purchase amount; refunds, margin and acquisition cost are unavailable. Consequently, this example cannot identify the most profitable customers.

Customers with no purchases in this window are absent. In a real customer database, decide whether to retain them with a separate nonbuyer status. An undefined last-purchase date should not become zero recency.

Calculate raw RFM before scoring it

SELECT customer_id,
       CAST(julianday('2026-05-01') - julianday(MAX(purchased_at)) AS INTEGER)
           AS recency_days,
       COUNT(*) AS purchase_frequency,
       SUM(amount_paise) AS monetary_paise
FROM purchases
WHERE purchased_at >= '2026-01-01' AND purchased_at < '2026-05-01'
GROUP BY customer_id
ORDER BY customer_id;

The expected results are:

Frequency totals eleven and monetary value totals 135,000. Those controls should reconcile to the selected transaction population. Recency uses calendar-day distance, not the number of full 24-hour periods since a timestamp. SQLite documents the date arithmetic used here in its date-function reference.

Avoid a misleading score on a tiny sample

Applying NTILE(5) to five customers creates five buckets even when the underlying differences are commercially unimportant. Ties may be split across buckets, and scores can change when another customer enters the population without anyone changing their own behavior.

Keep the raw values visible. For this teaching example, create an explicit operational rule instead of pretending that a five-level statistical segmentation is robust:

WITH rfm AS (
    SELECT customer_id,
           CAST(julianday('2026-05-01') - julianday(MAX(purchased_at)) AS INTEGER) AS r,
           COUNT(*) AS f, SUM(amount_paise) AS m
    FROM purchases
    WHERE purchased_at >= '2026-01-01' AND purchased_at < '2026-05-01'
    GROUP BY customer_id
)
SELECT customer_id, r, f, m,
       CASE WHEN f = 1 THEN 'one observed purchase'
            WHEN r > 45 THEN 'repeat buyer; investigate inactivity'
            ELSE 'recent repeat buyer' END AS review_group
FROM rfm ORDER BY customer_id;

The 45-day threshold is an illustrative policy, not an industry benchmark. A and D are recent repeat buyers; B and C warrant investigation under that policy; E has one observed purchase. E's low frequency partly reflects its late first appearance. Comparing it directly with January customers ignores different exposure time.

Turn a segment into a testable decision

Before sending offers, inspect buying cycles, consent, returns and existing contact rules. A 52-day gap may be normal for one product and unusual for another. Exclude customers whose unresolved service problems require support rather than promotion.

Evaluate an intervention against an eligible comparison group using a predefined outcome and observation period. Measure incremental value after offer costs and returns. A high response rate among recent customers does not prove that an offer caused additional purchases.

Save the snapshot date, window, monetary definition and rule version with each output. Re-running a query next month should produce a new dated snapshot, not silently overwrite the meaning of an old campaign report.

Exercise: change the window to March and April. Explain why A's frequency falls even though its customer history has not changed. Then add a refund policy and describe whether monetary value should represent gross purchases, net purchases or contribution margin.

NeuraPath's Data Analytics with Generative AI course provides a route from SQL aggregation to business analysis. A strong segmentation portfolio includes the customer table, reconciliation checks and an evaluation plan for the action those segments might support.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Calculate rolling averages without hiding missing dates.
• Continue with SQL funnel conversion when events arrive out of order.