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() - (web_last_purchase[1])::timestamp) as value,
ntile(10) over (order by date_part('days', now() - (web_last_purchase[1])::timestamp) desc) as ntiles
from customer_attributes
where web_last_purchase 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
web_num_purchases[1]::numeric as value,
ntile(10) over (order by web_num_purchases[1]::numeric) as ntiles
from customer_attributes
where web_num_purchases[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
web_sum_spend[1]::numeric as value,
ntile(10) over (order by web_sum_spend[1]::numeric) as ntiles
from customer_attributes
where web_sum_spend 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 in CDP
Recency Score attribute
select GREATEST(
(value::jsonb ->> (max(bucket) filter (where num_days_since_last_transaction >= bucket))::text)::numeric
, 1) as value
from context,
(select
bucket,
date_part('days', now() - max(event_time)) num_days_since_last_transaction
from base, (select key, (jsonb_object_keys(value::jsonb))::numeric as bucket from context) c
where c.key = 'recency'
and source_id = 'me_web'
and type = 'purchase_made'
group by c.bucket) z
where key = 'recency'
group by context.value
Frequency Score attribute
select GREATEST(
(value::jsonb ->> (min(bucket) filter (where num_transactions <= bucket))::text)::numeric
,1) as value
from context,
(select
bucket,
count(1) num_transactions
from base, (select key, (jsonb_object_keys(value::jsonb))::numeric as bucket from context) c
where c.key = 'frequency'
and source_id = 'me_web'
and type = 'purchase_made'
group by bucket) z
where key = 'frequency'
group by context.value
Monetary Score attribute
select GREATEST(
(value::jsonb ->> (min(bucket) filter (where total_spend <= bucket))::text)::numeric
,1) as value
from context,
(select
bucket,
sum((payload -> 'payload' ->> 'transactionTotal')::numeric) total_spend
from base, (select key, (jsonb_object_keys(value::jsonb))::numeric as bucket from context) c
where c.key = 'monetary'
and source_id = 'me_web'
and type = 'purchase_made'
group by c.bucket) z
where key = 'monetary'
group by context.value
4. Classify the customer population into RFM segments
This is where you explore the data and get creative!
5. Set up RFM segment attribute
Customer Segment |
Recency |
Frequency |
Monetary |
Champion |
70-100 |
90-100 |
70-90 |
Potential Loyalist |
50-100 |
80-100 |
0-60 |
Big spenders |
50-100 |
60-80 |
80-100 |
Promising |
40-80 |
60-70 |
40-70 |
New |
70-100 |
0-70 |
0-30 |
At risk |
0-40 |
80-100 |
60-90 |
Lost |
0-40 |
0-80 |
0-50 |
Assuming this is how you segmented your customers based on these scores, this is how you can set up the RFM Segment attribute.
with rfm as (
select
((max(c.value) filter (where c.key = 'recency'))::jsonb ->> (max(recency_bucket) filter (where num_days_since_last_transaction >= recency_bucket))::text)::numeric r,
((max(c.value) filter (where c.key = 'frequency'))::jsonb ->> (min(frequency_bucket) filter (where num_transactions <= frequency_bucket))::text)::numeric f,
((max(c.value) filter (where c.key = 'monetary'))::jsonb ->> (min(monetary_bucket) filter (where total_spend <= monetary_bucket))::text)::numeric m
from context c, (
select
recency_bucket::numeric recency_bucket,
frequency_bucket::numeric frequency_bucket,
monetary_bucket::numeric monetary_bucket,
date_part('days', now() - max(event_time)) num_days_since_last_transaction,
count(1) num_transactions,
sum((payload -> 'payload' ->> 'transactionTotal')::numeric) total_spend
from base, (SELECT
jsonb_object_keys(recency::jsonb) as recency_bucket,
jsonb_object_keys(frequency::jsonb) as frequency_bucket,
jsonb_object_keys(monetary::jsonb) as monetary_bucket
FROM
(select max(c.value) filter (where c.key = 'recency') recency,
max(c.value) filter (where c.key = 'frequency') frequency,
max(c.value) filter (where c.key = 'monetary') monetary
from context c
where c.key in ('recency','frequency','monetary'))st) sub_context
where source_id = 'me_web' and type = 'purchase_made'
group by sub_context.monetary_bucket, sub_context.frequency_bucket, sub_context.recency_bucket
) z
)
select
case
when r >= 70 and r <= 100 and f >= 90 and f <= 100 and m >= 70 and m <= 90 then 'Champion'
when r >= 50 and r <= 100 and f >= 80 and f <= 100 and m >= 0 and m <= 60 then 'Potential Loyalist'
when r >= 50 and r <= 100 and f >= 60 and f <= 80 and m >= 80 and m <= 100 then 'Big Spender'
when r >= 40 and r <= 80 and f >= 60 and f <= 70 and m >= 40 and m <= 70 then 'Promising'
when r >= 70 and r <= 100 and f >= 0 and f <= 70 and m >= 0 and m <= 30 then 'New'
when r >= 0 and r <= 40 and f >= 80 and f <= 100 and m >= 60 and m <= 90 then 'At Risk'
when r >= 0 and r <= 40 and f >= 0 and f <= 80 and m >= 0 and m <= 50 then 'Lost'
end as value
from rfm
No Comments