RFM Analysis

[Vonschwartz]

Hello all,

i'm newbe and i'll really appreciate your help i'm trying to perform an RFM Analysis using set Analysis since it's difficult to to that within the load Script.  i'need help for Customer Segmentation i use nested set Analysis for my scoring but it'isnt work for the Segmentation.

=if(DaysSinceLastPurchase <= Fractile(TOTAL DaysSinceLastPurchase, 0.2),1,
if(DaysSinceLastPurchase <= Fractile(TOTAL DaysSinceLastPurchase, 0.4),2,
if(DaysSinceLastPurchase <= Fractile(TOTAL DaysSinceLastPurchase, 0.6),3,
if(DaysSinceLastPurchase <= Fractile(TOTAL DaysSinceLastPurchase, 0.8),4,5)))) this is for the recency and it works similar for Frequency and Monetary but the set Expression is not working for segments

you'll find attached a sample Data in Excel sheet. Thanks in Advance for any help.

Vonschwartz

=if(Recency >=4 & Frequency >=4 & Monetary>=4,'Lost Customers',
if(Recency >=5 & Frequency <=2 & Monetary <=2,'Cannot Lose Them',
if(Recency =4 & Frequency <=3 & Monetary <=3, 'At Risk',
if(Recency =3 or 4 & Frequency =3 or 4 & Monetary =3 or 4, 'Need Attention',
if(Recency =2 or 3 & Frequency >=5 & Monetary >=5,'Promising',
if(Recency =1 or 2 & Frequency >=5 & Monetary >=5, 'New Customers',
if(Recency <=3 & Frequency >=4 & Monetary >=4 , 'Potential Loyalist',
if(Recency <=4 & Frequency <=3 & Monetary <=3, 'Loyal Customers',
if(Recency <=2 & Frequency <=2 & Monetary <=2, 'Champions',0)))))))))

[marcus_sommer]

The following might be a way:

m1: mapping load 1, fractile(DaysSinceLastPurchase, 0.2) & '|' & fractile(DaysSinceLastPurchase, 0.4) ...
from Source;

t: load CustomerNumber,
    if(DaysSinceLastPurchase <= subfield(applymap('m1', 1, null()), '|', 1), 1,
    if(DaysSinceLastPurchase <= subfield(applymap('m1', 1, null()), '|', 2), 2,
    … as R
from Source;

- Marcus

[Vonschwartz]

Thank you  Marcus,

I'm sorry your example solution is not clear i don't understand how to implement it in the load script.

[marcus_sommer · Accepted solution]

It means to calculate the R, F, M separate in beforehand created mapping-tables and to fetch then the values per applymap to perform the comparing - afterwards R, F, M could be merged to RFM and a further mapping could assign your buckets.

My example here is a bit shortened just to 2 R states and combined/merged all fractile-values into a single one to avoid multiple mapping-tables and which are later splitted with the subfield() - just to keep the example readable - you need only to add the further states and the logic for F and M.

The above logic will in general also work if your data are in the reality different to what you showed here - I guess again that "DaysSinceLastPurchase" and the other measures in your excel aren't fields else also the result of expressions and that there are further context like periods, channels, categories or similar. Of course it makes it more complex and you will need more working-steps to implement it.

I think it looked more complicated as it is - the essential point is to do this step by step to comprehend each step - if it worked it could be merged to a more complex logic ...

- Marcus

[Vonschwartz · Accepted solution]

good morning Marcus and thank you for help,

here is the way a did it in load script

m1:
mapping load
1, fractile(DaySinceLastPurchase, 0.2) & '|' & fractile(DaySinceLastPurchase, 0.4)& '|' & fractile(DaySinceLastPurchase, 0.6) & '|' & fractile(DaySinceLastPurchase, 0.8)& '|' & fractile(DaySinceLastPurchase, 1)
resident CustomerPurchaseFrequency;

m2:
mapping load
1, fractile(OrderByCustomer, 0.2) & '|' & fractile(OrderByCustomer, 0.4)& '|' & fractile(OrderByCustomer, 0.6) & '|' & fractile(OrderByCustomer, 0.8)& '|' & fractile(OrderByCustomer, 1)
resident CustomerPurchaseFrequency;

m3:
mapping load
1, fractile(Sales, 0.2) & '|' & fractile(Sales, 0.4)& '|' & fractile(Sales, 0.6) & '|' & fractile(Sales, 0.8)& '|' & fractile(Sales, 1)
resident CustomerPurchaseFrequency;

Temp:
load m_org_customer_id,
if(DaySinceLastPurchase <= subfield(applymap('m1', 1, null()), '|', 1), 1,
if(DaySinceLastPurchase <= subfield(applymap('m1', 1, null()), '|', 2), 2,
if(DaySinceLastPurchase <= subfield(applymap('m1', 1, null()), '|', 3), 3,
if(DaySinceLastPurchase <= subfield(applymap('m1', 1, null()), '|', 4), 4,5)))) as Recency,

if(OrderByCustomer <= subfield(applymap('m2', 1, null()), '|', 1), 5,
if(OrderByCustomer <= subfield(applymap('m2', 1, null()), '|', 2), 4,
if(OrderByCustomer <= subfield(applymap('m2', 1, null()), '|', 3), 3,
if(OrderByCustomer <= subfield(applymap('m2', 1, null()), '|', 4), 2,1)))) as Frequency,

if(Sales <= subfield(applymap('m3', 1, null()), '|', 1), 5,
if(Sales <= subfield(applymap('m3', 1, null()), '|', 2), 4,
if(Sales <= subfield(applymap('m3', 1, null()), '|', 3), 3,
if(Sales <= subfield(applymap('m3', 1, null()), '|', 4), 2,1)))) as Monetary

resident CustomerPurchaseFrequency;

it is working but to be honest i don't understand the Syntax it doesnt look  like mapping load i had till now.

Many thanks