How does the rfm segmentation query work?

For each customer, recency is days since the last order, frequency is the order count and monetary is the total spend. NTILE(5) splits customers into five equal groups on each measure; for recency the order is reversed, so a score of 5 means most recent. Combine the three scores (for example 5-5-5 for the best customers) to define segments such as champions, at risk or lapsed.

What commonly goes wrong with rfm segmentation in SQL?

Quintile scores are relative: the best customers of a weak business still score 5.

RFM Segmentation in SQL

RFM segmentation scores each customer on recency, frequency and monetary value, so you can tell your best, lapsing and one-off customers apart.

Example query

This example assumes: orders(id, customer_id bigint, order_date date, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

WITH rfm AS (
  SELECT customer_id,
         CURRENT_DATE - MAX(order_date) AS recency_days,
         COUNT(*) AS frequency,
         SUM(total_amount) AS monetary
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id,
       recency_days,
       frequency,
       monetary,
       NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
       NTILE(5) OVER (ORDER BY frequency)         AS f_score,
       NTILE(5) OVER (ORDER BY monetary)          AS m_score
FROM rfm
ORDER BY monetary DESC;

How it works

For each customer, recency is days since the last order, frequency is the order count and monetary is the total spend.
NTILE(5) splits customers into five equal groups on each measure; for recency the order is reversed, so a score of 5 means most recent.
Combine the three scores (for example 5-5-5 for the best customers) to define segments such as champions, at risk or lapsed.

MySQL, BigQuery, SQL Server and SQLite differences

NTILE works in PostgreSQL, MySQL 8+, SQL Server, BigQuery and SQLite 3.25+. Date subtraction differs: DATEDIFF(CURDATE(), MAX(order_date)) in MySQL, DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) in BigQuery.

Common mistakes

Quintile scores are relative: the best customers of a weak business still score 5.
Many customers with a single order make the frequency groups lopsided; consider fixed thresholds instead of NTILE for frequency.
Refunds and cancelled orders must be excluded, or monetary value is overstated.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

Score every customer on recency, frequency and spend.
Which customers were high spenders but haven't ordered in 6 months?