RFM Customer Segmentation in SQL: A Worked Example
Score ecommerce customers on recency, frequency, and monetary value in SQL, then group them into six segments. Runs on a free dataset, with totals reconciled.
On this page · 7 sections
- Release
- v1.0.0
- Tool
- SQLite SQL
- Time
- 35 minutes
- Deliverables
- 4 build steps, 5 modeled assets
What this build produces.
A customer table with recency, frequency, and monetary scores from 1 to 5, and a six-row summary showing how many customers and how much net payment value sit in each segment.
Know what one row means.
- ordersKey · order_id
- One row per placed order, including cancelled ones.
- paymentsKey · payment_id
- One row per payment attempt; a retried order has a failed row and a succeeded row.
- refundsKey · refund_id
- One row per refund; an order can have several partial refunds.
- customer_rfmKey · customer_id
- One row per customer with at least one order that was not cancelled.
- customer_segmentsKey · customer_id
- One row per scored customer, with one segment each.
Fix the meaning before the code.
- Snapshot date
- 2026-01-01, the day after the last order in the release. Recency is measured back from this fixed date so the result does not change with the day you run the query.
- Recency
- Whole days between the customer's most recent order that was not cancelled and the snapshot date. Lower is better.
- Frequency
- The number of the customer's orders that were not cancelled.
- Monetary value
- Net payment value: succeeded payments on the customer's orders minus refunds on those orders. It includes tax and shipping because customers paid them.
- Score
- A number from 1 to 5 for each measure, where 5 is best. A customer's score is set by the share of customers at or below their value, in steps of 20%. Customers with the same value always get the same score.
- Segment
- One of six groups from the recency and frequency scores. Champions: recency 4 to 5 and frequency 4 to 5. Recent: recency 4 to 5, frequency 1 to 3. Loyal: recency 3, frequency 4 to 5. Needs attention: recency 3, frequency 1 to 3. At risk: recency 1 to 2, frequency 4 to 5. Lapsed: recency 1 to 2, frequency 1 to 3.
Work from source grain to tested output.
- 01
Build one row per customer
Payments and refunds are both more detailed than an order. Sum each to the order first, then roll orders up to the customer, so no payment is counted once per refund.
sql · rfm.sqlRecency, frequency, and monetary value per customerCreate a temporary view with the three raw measures. A temporary view lasts for the connection and does not change the downloaded file.
36 linesCREATE TEMP VIEW customer_rfm AS WITH paid_by_order AS ( SELECT order_id, SUM(amount) AS paid_amount FROM payments WHERE payment_status = 'succeeded' GROUP BY order_id ), refunded_by_order AS ( SELECT order_id, SUM(amount) AS refunded_amount FROM refunds GROUP BY order_id ) SELECT o.customer_id, CAST( julianday('2026-01-01') - julianday(substr(MAX(o.ordered_at), 1, 10)) AS INTEGER ) AS recency_days, COUNT(*) AS frequency, ROUND( SUM(COALESCE(p.paid_amount, 0) - COALESCE(r.refunded_amount, 0)), 2 ) AS monetary FROM orders AS o LEFT JOIN paid_by_order AS p ON o.order_id = p.order_id LEFT JOIN refunded_by_order AS r ON o.order_id = r.order_id WHERE o.order_status <> 'cancelled' GROUP BY o.customer_id;sql · rfm.sqlCheck the base tableConfirm the row count and the range of each measure before scoring.
7 linesSELECT COUNT(*) AS customers, MIN(recency_days) AS min_recency_days, MAX(recency_days) AS max_recency_days, MAX(frequency) AS max_frequency, ROUND(SUM(monetary), 2) AS total_monetary FROM customer_rfm;Verification- On v1.0.0 the view has 19,850 rows. The release has 20,000 customers; 150 of them have no order that was not cancelled, so they have nothing to score.
- Recency runs from 1 to 1,096 days and the largest frequency is 16 orders.
- Total monetary value is 60,070,660.02, which equals succeeded payments on orders that were not cancelled minus all refunds.
- 02
Score each measure from 1 to 5
The usual shortcut is NTILE(5), which deals customers into five equal groups. Frequency has only 16 distinct values across 19,850 customers, so NTILE puts customers with the same number of orders into different scores. CUME_DIST keeps tied customers together.
sql · rfm.sqlRecency, frequency, and monetary scoresConvert each measure to a 1 to 5 score. Recency is ordered from oldest to newest so the most recent buyers score 5.
40 linesCREATE TEMP VIEW customer_rfm_scores AS WITH ranked AS ( SELECT customer_id, recency_days, frequency, monetary, CUME_DIST() OVER (ORDER BY recency_days DESC) AS recency_position, CUME_DIST() OVER (ORDER BY frequency) AS frequency_position, CUME_DIST() OVER (ORDER BY monetary) AS monetary_position FROM customer_rfm ) SELECT customer_id, recency_days, frequency, monetary, CASE WHEN recency_position <= 0.2 THEN 1 WHEN recency_position <= 0.4 THEN 2 WHEN recency_position <= 0.6 THEN 3 WHEN recency_position <= 0.8 THEN 4 ELSE 5 END AS r_score, CASE WHEN frequency_position <= 0.2 THEN 1 WHEN frequency_position <= 0.4 THEN 2 WHEN frequency_position <= 0.6 THEN 3 WHEN frequency_position <= 0.8 THEN 4 ELSE 5 END AS f_score, CASE WHEN monetary_position <= 0.2 THEN 1 WHEN monetary_position <= 0.4 THEN 2 WHEN monetary_position <= 0.6 THEN 3 WHEN monetary_position <= 0.8 THEN 4 ELSE 5 END AS m_score FROM ranked;sql · rfm.sqlLook at the frequency score boundariesShow which order counts fall into each frequency score.
8 linesSELECT f_score, COUNT(*) AS customers, MIN(frequency) AS min_orders, MAX(frequency) AS max_orders FROM customer_rfm_scores GROUP BY f_score ORDER BY f_score;Verification- The frequency ranges do not overlap: score 1 is 1 to 2 orders, score 2 is 3 orders, score 3 is 4 orders, score 4 is 5 to 6 orders, and score 5 is 7 to 16 orders.
- The groups are uneven, from 2,768 customers in score 1 to 6,218 in score 4, because customers with the same order count stay together.
- NTILE(5) on the same column gives 3,970 customers in every group, but splits customers with 3 orders between scores 1 and 2.
- Recency score 5 covers customers who ordered 1 to 50 days before the snapshot date; score 1 covers 352 days or more.
- 03
Name the segments and summarize them
Recency and frequency describe behavior; monetary value describes its result. Group customers on the first two, then report value for each group.
sql · rfm.sqlOne segment per customerAssign each customer to exactly one of six segments. The CASE branches are evaluated in order, so each later branch can rely on the earlier ones having failed.
18 linesCREATE TEMP VIEW customer_segments AS SELECT customer_id, recency_days, frequency, monetary, r_score, f_score, m_score, CASE WHEN r_score >= 4 AND f_score >= 4 THEN 'Champions' WHEN r_score >= 4 THEN 'Recent' WHEN r_score = 3 AND f_score >= 4 THEN 'Loyal' WHEN r_score = 3 THEN 'Needs attention' WHEN f_score >= 4 THEN 'At risk' ELSE 'Lapsed' END AS segment FROM customer_rfm_scores;sql · rfm.sqlSegment summaryProduce the table a marketing or retention team would read.
11 linesSELECT segment, COUNT(*) AS customers, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_customers, ROUND(AVG(recency_days), 0) AS avg_recency_days, ROUND(AVG(frequency), 1) AS avg_orders, ROUND(SUM(monetary), 2) AS net_value, ROUND(100.0 * SUM(monetary) / SUM(SUM(monetary)) OVER (), 1) AS pct_of_net_value FROM customer_segments GROUP BY segment ORDER BY net_value DESC;Verification- The summary has six rows and the customer counts add up to 19,850.
- Champions are 5,382 customers, 27.1% of the base, and account for 37.5% of net payment value.
- At risk is the second-largest segment by value: 2,884 customers who averaged 6.2 orders, last ordered 332 days ago on average, and account for 18.4% of net payment value.
- Lapsed is 25.3% of customers and 14.5% of net payment value.
- 04
Test the grain, the scores, and the total
Each query returns zero rows when the model is right. Keep them next to the model and run them again whenever the data or the rules change.
sqlOne row per customerReturn zero rows when no customer appears twice.
6 linesSELECT customer_id, COUNT(*) AS row_count FROM customer_segments GROUP BY customer_id HAVING COUNT(*) > 1;sqlTied customers share a scoreReturn zero rows when every order count maps to a single frequency score. Run this against an NTILE version and it fails.
6 linesSELECT frequency, COUNT(DISTINCT f_score) AS distinct_scores FROM customer_rfm_scores GROUP BY frequency HAVING COUNT(DISTINCT f_score) > 1;sqlSegment value reconciles to the source tablesReturn zero rows when the segments add up to succeeded payments minus refunds on orders that were not cancelled.
29 linesWITH paid AS ( SELECT SUM(p.amount) AS amount FROM payments AS p INNER JOIN orders AS o ON p.order_id = o.order_id WHERE p.payment_status = 'succeeded' AND o.order_status <> 'cancelled' ), refunded AS ( SELECT SUM(r.amount) AS amount FROM refunds AS r INNER JOIN orders AS o ON r.order_id = o.order_id WHERE o.order_status <> 'cancelled' ), model AS ( SELECT SUM(monetary) AS amount FROM customer_segments ) SELECT paid.amount - refunded.amount AS source_net_value, model.amount AS model_net_value FROM paid CROSS JOIN refunded CROSS JOIN model WHERE ABS(paid.amount - refunded.amount - model.amount) > 0.01;Verification- All three queries return zero rows on v1.0.0.
- The reconciliation test would catch a join that repeats payments, which is the most common way a customer value table goes wrong.
Release-backed output, not demo numbers.
walkthroughs.rfm-customer-segmentation-sql.segment-summarySix segments covering 19,850 customers and 60,070,660.02 of net payment value.
segmentcustomerspct_of_customersavg_recency_daysavg_ordersnet_valuepct_of_net_valueChecked on 2026-11-21 against ecommerce-sales-v1.0.0.sqlite as published. By net value: Champions 22,519,248.79, At risk 11,052,057.42, Loyal 9,301,810.11, Lapsed 8,698,494.20, Recent 5,276,308.17, Needs attention 3,222,741.33.
What this result does not claim.
- The data is synthetic. The segment sizes describe how the generator spreads orders across customers, not how real shoppers behave, and they are not a benchmark.
- Scores are relative. A 5 means the top fifth of this customer base on this snapshot date; move the date and customers change score without changing behavior.
- The six segments use recency and frequency only. The monetary score is in the table for a second cut, such as separating high-value from low-value customers inside At risk.
- Monetary value is payments minus refunds, so it includes tax and shipping. It is not merchandise sales or recognized revenue.
- The 150 customers with no order that was not cancelled are left out. A real program would treat them as their own group.
- The temporary views need a SQLite connection that allows CREATE, such as the sqlite3 shell or DB Browser for SQLite on the downloaded file. The browser sandbox on this site runs SELECT statements only.
