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]

If I understand it right you want to create calculated dimensions. If they contain aggregation functions they must be wrapped with an aggr(), like:

aggr(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)))), Dim1, Dim2)

Dim1 and Dim2 are here placeholder for the dimensions in which context this calculation should be happens.

For you second buckets it's probably quite the same whereby multiple conditions needs to be concatenated with AND or OR and not with &. If the values for Recency and Frequency and Monetary aren't unique on the above already mentoned calculation context you need to apply further aggregations on them like min/max/avg because a simple field-referencing returned always NULL if there is more as a single value available.

- Marcus

[Vonschwartz]

Hello Marcus and thanks for your Reply,

i'm trying to perform a RFM Analysis based on Recency, Frequency and Monetary value i used the function fractile to Group customers in 5 Groups relatively to their R ,F and M Value as you have seen in the Excel sheet Data each customer is with the formulas is assign a value from 1 - 5 for each Metric R, F and M, joinning the Metric together result in a Score "RFM",

il will like for now to Segment my Customer based on the Score the combination of R, F and M value like in the sheet Segmentation is giving me the Segment to which the customer belong. may there is a way to perform it in the load script i'm a litle bit lost. 

[marcus_sommer]

If I now look into your excel it's not really clear for me if this is already the source for your segmentation or it's an aggregated view on the real data behind them?

If it's the source you need just CustomerNumber as dimension for the above mentioned aggr() for the recency-segmentation. But your RFM shouldn't be done within the UI else within the script - and I wouldn't use a large nested if-loop for it else just a mapping for RFM because in your data it are one 84 different values and they could be easily assigned in a manual way.

- Marcus

[Brett_Bleess]

Patrice, did Marcus' last post help at all, or are you still trying to sort things out?  If it did, be sure to use the Accept as Solution button on the post to mark things as the solution and give Marcus credit for the help.  If you did something different, consider posting what you did and then mark that, and if you still need help, leave an update for us.

The only thing I have that may be of some further help is the following:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Grabbed this one based upon what you and Marcus were discussing.  There are links to some other posts in that post as well.

Regards,
Brett

[Vonschwartz]

good morning,

i'm still Looking for a solution  Marcus post is bringing me to the same Level i was already   the classification in Recency , Frequency and Monetary is working good. But i'm Looking for a solution for the next Level how to Segment my customers using the Output of the preceeding classification, i have tryed this :

Aggr(if(R <=2 and F <=2 and M <=2, 'Champion',
        if(R <=4 and F <=3 and M <=3, 'Loyal Customer',
        if(R <=3 and F >=3 and M >=3, 'Potential Loyalist',
        if(R <=2 and F >=5 and M >=5, 'New Customer',
        if(R =3 or 4 and F =3 or 4 and M=3 or 4, 'Need Attention',
        if(R =3 or 4 and F >=4 and M >=4, 'About To Sleep',
        if(R >=4 and F <=4 and M <=4, 'At Risk',
        if(R >=4 and F >=4 and M >=4,'Lost Customers', 'Hibernating')))))))),CustomerNumber)

it is giving me back "Need Attention" for all Customers I don't know why actually customer with 

R=1 and F= 1 and M= 1 should be "Champion". but the Overall Output is "Need Attention"

Maybe you have an idea on how to achieve that.

Thank you

[marcus_sommer]

Your CustomerNumber might not be the right dimension for this calculation. It could be that your data aren't properly associated within the datamodel and/or that you need different and more dimensions - for example any period-dimension. Beside this there are syntax-issues within the expression because you could not declare:

R =3 or 4

else it must be

R =3 or R = 4

and if there are multiple conditions you might need to use brackets to ensure that they are evaluated in the right order, like:

(R =3 or R = 4) or alternativ: match(R, 3, 4)

Like above mentioned I would try to do these kind of matching within the script - it's usually much easier.

- Marcus

[Vonschwartz]

thank you Marcus for your Suggestion but it's not working  i wanted to do all in the load script as you suggested but qlikview is giving me back an error "nested Expression not allowed) in my data i have three fields (DaysSinceLastpurchase, OrderByCustomer and SalesAmount) i wanted to classify my customer in 5 Groups using fractile function of qlikview for 

e.g:  load distinct CustomerNumber

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)))) as R // Recency  and the same for OrderByCustomer and SalesAmount. but qlikview ist not accepting my if condition. that why I'm doing it using set Analysis and it works for the three fields that i have named R, F and M. but the Problem now is how to use the value (Output) of R, F, and M from set Analysis to Segment my customers this part it not working at all.

thanks for your help

[marcus_sommer]

I guessed already that R, F, M are no native fields else the result of calculations. In principle you could use these calculations within your huge nested if-loop if they are all properly wrapped with the appropriate aggr() functions - but the complexity of such an expression will be enormous and by larger datasets it will be definitely very slow.

I assume that you got a script-error by calculating it during the load because you missed a proper group by statement for this aggregation - fractile() is an aggregation like sum() or max() and needs therefore a group by for each not in the aggregation included field - and I think you couldn't do it within a single load else you will need at least one extra load because you couldn't compare:

DaysSinceLastPurchase <= Fractile(TOTAL DaysSinceLastPurchase, 0.2)

in one load because DaysSinceLastPurchase would also needed to be specified in the group by and therefore your fractile wouldn't return your expected value. Without knowing how your data look like it's difficult to say how you could match the aggregation-results with your detail data - but I think I would join or map them together.

- Marcus

[Vonschwartz]

thanks a lot for your Reply,

my mistake i forgot the Group by Statement my data sample is attached.

thank you