RFM Score Calculation: Segmenting Customers by Recency, Frequency & Monetary Value Using NTILE()

Business Context

NovaMart is a growing retail chain with 85 stores across six regions and a registered loyalty base of 50,000 active customers. The CRM and marketing team runs a quarterly customer segmentation exercise to allocate campaign budgets, personalise communications, and identify customers at risk of churning. The framework they use is RFM — Recency, Frequency, Monetary — a proven direct-marketing model that scores each customer on three dimensions: how recently they purchased, how often they purchase, and how much they spend. For the current campaign planning cycle covering purchases made in 2024, the Head of CRM needs every active customer scored on each RFM dimension from 1 (worst) to 5 (best) and assigned to a named segment.

The SQL Challenge

The core technique is NTILE(5) — a window function that divides customers into five equal buckets based on a sort order, assigning scores 1–5 without needing manual threshold definitions. Each RFM dimension requires its own NTILE(5) OVER (ORDER BY ...) call with a carefully chosen sort direction: Recency uses ORDER BY last_purchase_date DESC (more recent = higher score), Frequency uses ORDER BY total_orders DESC (more visits = higher score), and Monetary uses ORDER BY total_spend DESC (higher spend = higher score). The three scores are then combined into a composite rfm_score and mapped to named segments using a CASE expression. The solution uses multiple CTEs to cleanly separate the aggregation, scoring, and labelling steps.

Your Role and Deliverable

You are the data analyst supporting the CRM team at NovaMart. Your task is to write a SQL query that produces one row per customer containing their RFM metrics, individual dimension scores (1–5 each), a composite RFM score, and a named segment label. The output feeds directly into the campaign planning tool used by the marketing team to build audience lists for the next quarterly campaign cycle.