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

Assume we have historical data about customer's responses to our direct mail offers as shown below. Today we want to send them new letters. We believe that customers who responded to our offerings recently are more likely to respond to our future offers. We can divide customers into 5 or 10 equal size buckets, called quintiles or deciles, based on how recently they responded. This practice is common.

        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 recently they responded to our offerings. The more recent the response, the higher the number for quintiles or deciles.

with temp_tbl as

(

select 

user_id, max(campaign_date) recent_response  from TBL_DIRECT_MAILING a where RESPONSE='Y'

group by user_id

),

temp_tbl2 as

(

select a.user_id, sysdate-recent_response as recency from 

(select distinct user_id from TBL_DIRECT_MAILING) a,  

temp_tbl b

where a.user_id=b.user_id(+)

)

select user_id,

round(recency) recency_days,

ceil(cume_dist() over(order by recency desc nulls first)*5) recency_quintile,

ceil(cume_dist() over(order by recency desc nulls first)*10) recency_decile

from temp_tbl2

temp_tbl2 order by user_id;

The output of the query looks like the following. Customers with higher quintile or decile numbers are more likely to respond to our offerings. Of course, the most accurate way to predict a customer's response is to build a predictive model that uses the recency as one of its input variables.

  USER_ID RECENCY_DAYS RECENCY_QUINTILE RECENCY_DECILE

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

     1001          151                3              5

     1002           30                5              9

     1003           72                2              4