Analyzing Recency, Frequency and Monetary value to index your best customers
Recency-Frequency-Monetary (RFM) analysis is a indexing technique that uses past purchase behavior to segment customers.
Given a table with purchase transaction data, we calculate a score based on how recently the customer purchased, how often they make purchases and how much they spend in dollars on average on each purchase. Using these scores, we can segment our customer list to:
- identify our most loyal customers by segmenting one-time buyers from customers with repeat purchases
- increase customer retention and lifetime value
- increase average order size, etc.
1. Calculating RFM indices
To perform RFM analysis, we divide customers into four equal groups according to distribution of values for recency, frequency and monetary values. Four equal groups across three variables creates possible segments, which is manageable.
Given a table
customer_orders with the following schema:
we have the following calculations for each customer:
SELECT customer_id, MAX(order_date) AS last_order_date, COUNT(*) AS count_order, AVG(amount) AS avg_amount FROM customer_orders GROUP BY customer_id;
Next, we use percentiles to score the customers along each of these dimensions. If you're not familiar with the
ntile window function, our recipe on SQL Histograms describes it in more detail.
SELECT customer_id, ntile(4) OVER (ORDER BY last_order_date) AS rfm_recency, ntile(4) OVER (ORDER BY count_order) AS rfm_frequency, ntile(4) OVER (ORDER BY avg_amount) AS rfm_monetary FROM ( /* .. above query .. */ )
The final resulting table will assign a score between 1 and 4 for each of the quantiles (recency, frequency and monetary value.) These individual scores can be combined to create an aggregate score if needed.
SELECT customer_id, rfm_recency*100 + rfm_frequency*10 + rfm_monetary as rfm_combined FROM ( /* .. above query .. */ )
2. Understanding the RFM indices
By using the combined scores, you can categorize our customers as High value, Repeat, One-time, Lapsed, Lost, Lost cheap, etc. as the visual below:
|Monetary Value Quartiles|
You are reading a series of Advanced SQL tricks.
Read this next: Segmenting and Lead scoring your email list
Join 300+ data analysts who are using our weekly SQL tips to improve their skills. No spam, promise!