1. SQL Recipes
2. Ranking Customers
3. Analyzing Recency, Frequency and Monetary value to index your best customers

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: