Tutorial: How to set up RFM attributes (WIP)

Here, we will describe the implementation process to set up attributes for the RFM (Recency, Fequency, Monetary) model. To learn more about the model, please refer to this article.

1. Set up base attributes

For each part of the model, we set up their base attribute. These base attributes are required in order to calculate their respective scores.

	What the score means	Base attribute
Recency	How recently a customer made an order.  A higher recency score means customer's last order was made more recently.	Date of Last Order
Frequency	How many orders the customer made. A customer with more orders would have a higher frequency score.	Number of Orders
Monetary	How much money has the customer spent. A customer who spends a lot would have a higher monetary score.	Total Spend ($)

2. Set up context queries

Because a customer's RFM scores is based on the spending behavior of all customers, a context query is required. The output of the context query will be used in the calculation of RFM scores.

Here is sample code for the context queries of your RFM attribute. Note that the Recency query uses min and orders the number of days in the ntiles in descending order.

Recency

with tmp as (
	select min(value) as value, ntiles * 10 as percentile
    from (select 
              date_part('days', now() - (mx_last_transaction_datetime[1])::timestamp) as value,
              ntile(10) over (order by date_part('days', now() - (mx_last_transaction_datetime[1])::timestamp) desc) as ntiles
          from customer_attributes
          where mx_last_transaction_datetime is not null) as buckets
    group by 2
    order by 2
)
select jsonb_object(
		array_agg(value)::text [],
		array_agg(percentile)::text []
	)::text as value
from tmp

Frequency

with tmp as (
	select max(value) as value, ntiles * 10 as percentile
    from (select 
              mx_total_trans[1]::numeric as value, 
              ntile(10) over (order by mx_total_trans[1]::numeric) as ntiles
          from customer_attributes
          where mx_total_trans[1]::numeric >0) as buckets
    group by 2
    order by 2
)
select jsonb_object(
		array_agg(value)::text [],
		array_agg(percentile)::text []
	)::text as value
from tmp

Monetary

with tmp as (
	select max(value) as value, ntiles * 10 as percentile
    from (select 
          		mx_total_amount[1]::numeric as value, 
          		ntile(10) over (order by mx_total_amount[1]::numeric) as ntiles
          from customer_attributes
          where mx_total_amount is not null) as buckets
    group by 2
    order by 2
)
select jsonb_object(
		array_agg(value)::text [],
		array_agg(percentile)::text []
	)::text as value
from tmp

3. Set up RFM scores attributes

4. Classify the customer population into RFM segments

This is where you explore the data and get creative! 

5. Set up RFM segment attribute