Recency, Frequency, Monetary (RFM) Analysis: Part 4

Just like we do for recency variable, we can divide customers into 5 or 10 equal size buckets, called quintiles or deciles, based on how frequently they respond. 

        USER_ID CAT STATE_ID       CAMPAIGN_DATE CAMPAIGN_ID RESPONSE

---------- -- -------- --------- -------- --------

      1001 A  MA       01-MAY-12 A        N

      1001 A  MA       08-MAY-12 A        N

      1001 A  MA       15-MAY-12 A        Y

      1001 A  MA       22-MAY-12 A        N

      1001 A  MA       29-MAY-12 A        N

      1001 A  MA       06-JUN-12 A        N

      1001 B  CT       06-JUN-12 A        N

      1002 B  CT       01-MAY-12 A        N

      1002 B  CT       08-MAY-12 A        N

      1002 B  CT       15-MAY-12 A        Y

      1002 B  CT       22-MAY-12 A        N

      1002 B  CT       29-MAY-12 A        Y

The following query calculates quintiles and deciles for each customer based on how frequently they responded to our offerings. The more frequently the responses, the higher the number for quintiles or deciles.

with temp_tbl as

(

select 

user_id, count(1) num_of_response  from TBL_DIRECT_MAILING a where RESPONSE='Y'

group by user_id

),

temp_tbl2 as

(

select a.user_id, num_of_response  from 

(select distinct user_id from TBL_DIRECT_MAILING) a,  

temp_tbl b

where a.user_id=b.user_id(+)

)

select user_id,

num_of_response,  

ceil(cume_dist() over(order by num_of_response nulls first)*5) frequency_quintile,

ceil(cume_dist() over(order by num_of_response nulls first)*10) frequency_decile

from temp_tbl2

temp_tbl2 order by user_id;

The output of the query looks like the following. 

   USER_ID NUM_OF_RESPONSE FREQUENCY_QUINTILE FREQUENCY_DECILE

--------- --------------- ------------------ ----------------

     1001               1                  3                5

     1002               2                  5                9

     1003               1                  3                5

     1004               2                  5                9