RFM analysis: validate segments against business actions

RFM summarizes how recently a customer purchased, how often they purchased and how much they spent within a specified history. It can organize customer analysis, but a segment name such as “at risk” is an interpretation that needs evidence. A low recency score alone does not establish that a customer is about to leave.

The useful output is a reproducible customer table connected to an appropriate business action, with subsequent evaluation of whether that action helps.

Fix the snapshot and purchase window

Use the original synthetic purchase dataset. The snapshot date is May 1, 2026. Include purchases from January 1 inclusive through May 1 exclusive. Each purchase ID represents one purchase, and amounts are integer paise.

Recency is days since the latest included purchase. Frequency is the number of included purchases. Monetary value is the sum of included amounts. This exercise has no returns or cancellations; a real contract must decide how those affect each measure.

SELECT customer_id,
 CAST(julianday('2026-05-01')-julianday(MAX(purchased_at)) AS INTEGER) AS recency_days,
 COUNT(DISTINCT purchase_id) AS 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;

Expected results are:

E is the most recent customer but has only one observed purchase. Calling E a loyal customer would require information that this table does not provide.

Verify the aggregation independently

From the product analytics lab directory, this example reads the sibling purchase fixture and checks the full result:

import csv
from collections import defaultdict
from datetime import date
from pathlib import Path

groups = defaultdict(list)
with Path('../advanced-sql/purchases.csv').open(newline='', encoding='utf-8') as handle:
    for row in csv.DictReader(handle):
        if '2026-01-01' <= row['purchased_at'] < '2026-05-01':
            groups[row['customer_id']].append(row)
actual = {}
for customer, rows in groups.items():
    latest = max(date.fromisoformat(r['purchased_at']) for r in rows)
    actual[customer] = ((date(2026,5,1)-latest).days,
                        len({r['purchase_id'] for r in rows}),
                        sum(int(r['amount_paise']) for r in rows))
assert actual == {'A':(30,3,35000), 'B':(52,3,25000),
                  'C':(60,2,20000), 'D':(28,2,40000), 'E':(27,1,15000)}
print(actual)

This fixture has unique purchase IDs. Deduplicating frequency while summing replayed amounts would otherwise produce inconsistent measures. Validate the source grain before calculating either.

Choose scores without concealing ties

IBM's RFM analysis documentation describes the three component scores and their combination. A combined label is a convenient summary, not a universal probability of response.

With only five customers and repeated frequencies, five equal-sized frequency bins would create arbitrary distinctions. NTILE can place equal values into different bins depending on ordering. Keep raw measures visible and either use explicit business thresholds or adopt a documented tie policy.

For an illustrative follow-up queue, define “previous repeat purchaser, no purchase for more than 45 days” as frequency at least two and recency above 45. B and C qualify. That rule is transparent, but its usefulness still depends on the product's normal buying cycle.

Validate the proposed action

Before contacting that queue, check eligibility and whether a service issue should be resolved first. Do not automatically discount customers who would return without an incentive.

For a permitted campaign, compare an assigned treatment with a suitable control and evaluate incremental contribution over a fixed horizon. Response rate alone can reward discounts that reduce profit. Freeze the segment before observing the campaign outcome to avoid selecting customers with hindsight.

Also test stability: do customers switch segments because of meaningful behavior or because a quantile boundary moved when new customers arrived? Store the snapshot date, purchase window, threshold version and customer count with every export.

Exercise: extend the history window by a month and add an older purchase for E. Explain why frequency and monetary value change while recency does not. Keep both snapshots reproducible.

NeuraPath's Data Analytics with Generative AI course connects customer-level SQL with business evaluation. An RFM project is stronger when it tests a decision than when it only colors a segmentation chart.

Continue learning

This article is part of the Customer and product analytics sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Churn rate: choose the population at risk.
• Continue with Market-basket analysis without confusing popularity with affinity.