Skip to content
Dataset walkthrough

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
  1. Target outcome
  2. Grain contract
  3. Definitions
  4. Build sequence
  5. Expected result
  6. Limitations
  7. Dataset and exercise
Target outcome

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.

Grain contract

Know what one row means.

orders
Key · order_id
One row per placed order, including cancelled ones.
payments
Key · payment_id
One row per payment attempt; a retried order has a failed row and a succeeded row.
refunds
Key · refund_id
One row per refund; an order can have several partial refunds.
customer_rfm
Key · customer_id
One row per customer with at least one order that was not cancelled.
customer_segments
Key · customer_id
One row per scored customer, with one segment each.
Definitions

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.
Build sequence

Work from source grain to tested output.

  1. 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.sql
    Recency, frequency, and monetary value per customer

    Create a temporary view with the three raw measures. A temporary view lasts for the connection and does not change the downloaded file.

    36 lines
    CREATE 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.sql
    Check the base table

    Confirm the row count and the range of each measure before scoring.

    7 lines
    SELECT
      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.
  2. 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.sql
    Recency, frequency, and monetary scores

    Convert each measure to a 1 to 5 score. Recency is ordered from oldest to newest so the most recent buyers score 5.

    40 lines
    CREATE 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.sql
    Look at the frequency score boundaries

    Show which order counts fall into each frequency score.

    8 lines
    SELECT
      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.
  3. 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.sql
    One segment per customer

    Assign 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 lines
    CREATE 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.sql
    Segment summary

    Produce the table a marketing or retention team would read.

    11 lines
    SELECT
      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.
  4. 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.

    sql
    One row per customer

    Return zero rows when no customer appears twice.

    6 lines
    SELECT
      customer_id,
      COUNT(*) AS row_count
    FROM customer_segments
    GROUP BY customer_id
    HAVING COUNT(*) > 1;
    sql
    Tied customers share a score

    Return zero rows when every order count maps to a single frequency score. Run this against an NTILE version and it fails.

    6 lines
    SELECT
      frequency,
      COUNT(DISTINCT f_score) AS distinct_scores
    FROM customer_rfm_scores
    GROUP BY frequency
    HAVING COUNT(DISTINCT f_score) > 1;
    sql
    Segment value reconciles to the source tables

    Return zero rows when the segments add up to succeeded payments minus refunds on orders that were not cancelled.

    29 lines
    WITH 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.
Expected result

Release-backed output, not demo numbers.

v1.0.0 · verified output
walkthroughs.rfm-customer-segmentation-sql.segment-summary

Six segments covering 19,850 customers and 60,070,660.02 of net payment value.

Columns
segmentcustomerspct_of_customersavg_recency_daysavg_ordersnet_valuepct_of_net_value

Checked 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.

Limitations

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.
Continue with the data

Open the matching dataset and exercise.