Recency, Frequency, Monetary (RFM) Analysis: Part 5. RFM Code

To calculate monetary variable, we can use analytic function sum (amount) over(). Thus, we can combine the quintiles for recency, frequency and monetary to create a RFM code for each customer. This can be done using the following query. RFM code 555 is the best group of customers.


   USER_ID    CAT   STATE_ID CAMPAIGN_DATE CAMPAIGN_ID  RESPONSE     AMOUNT
---------- -- -------- --------- -------- -------- ----------
      1001 A  MA       29-MAY-12 A        N
      1001 A  MA       22-MAY-12 A        N
      1001 A  MA       15-MAY-12 A        Y                50
      1001 A  MA       08-MAY-12 A        N
      1001 A  MA       01-MAY-12 A        N
      1001 B  CT       06-JUN-12 A        N
      1001 A  MA       06-JUN-12 A        N
      1002 B  CT       08-MAY-12 A        N
      1002 B  CT       15-MAY-12 A        Y                50
      1002 B  CT       22-MAY-12 A        N
      1002 B  CT       29-MAY-12 A        Y                50
      1002 B  CT       01-MAY-12 A        N
with temp_tbl as
(
select 
user_id, 
min(sysdate-campaign_date) as recency,
count(1) frequency, 
sum(amount) as sum_amount
  from TBL_DIRECT_MAILING a where RESPONSE='Y'
group by user_id
),
temp_tbl2 as
(
select 
a.user_id,  
recency,
frequency, 
sum_amount from 
(select distinct user_id from TBL_DIRECT_MAILING) a,  
temp_tbl b
where a.user_id=b.user_id(+)
),
temp_tbl3 as
(
select user_id,
ceil(cume_dist() over(order by recency desc nulls first)*5) recency_quintile,
ceil(cume_dist() over(order by frequency nulls first)*5) frequency_quintile,
ceil(cume_dist() over(order by sum_amount  nulls first)*5) amt_quintile
from temp_tbl2)
select user_id, recency_quintile*100+frequency_quintile*10+amt_quintile as RFM_Code from
temp_tbl3 order by user_id;

The result may look like the following.

   USER_ID   RFM_CODE
---------- ----------
      1001        333
      1002        555
      1003        233