ePrivacy and GPDR Cookie Consent by Cookie Consent Skip to main content

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[web_last_purchase[1])::timestamp) as value,
    		  ntile(10) over (order by date_part('days', now() - (mx_last_transaction_datetime[web_last_purchase[1])::timestamp) desc) as ntiles
          from customer_attributes
          where mx_last_transaction_datetimeweb_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 
              mx_total_trans[web_num_purchases[1]::numeric as value, 
              ntile(10) over (order by mx_total_trans[web_num_purchases[1]::numeric) as ntiles
          from customer_attributes
          where mx_total_trans[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 
              mx_total_amount[web_sum_spend[1]::numeric as value, 
              ntile(10) over (order by mx_total_amount[web_sum_spend[1]::numeric) as ntiles
          from customer_attributes
          where mx_total_amountweb_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

 

 

Back to top