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.

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

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