Calculating the scores of RFM

Dear all,

I have attached an excel sheet that's calculating the scores of the RFM but I'm facing some problems.

What I'm trying to reach is that, for example the scores of R (Recency) it will be calculated by taking all the values in the recency and put them in the ascending order and then divide them into 5 group by taking the lower 20% and give it score 5 and then the next 40% give it 4 and then 60% and give it 3, 80% give it 2 and 100% and give it 1. I did this by using this formula =PERCENTILE.INC(B3:B16,0.2) the bold is the percentages. But still I'm having some cells which are N/A and some cell are not matching. I thing the problem is something related to the order of the values from lower to higher so in some points it's not reading the values.

Any help in that please?

Thanks in advance for your kind support!

RFM Sample.xlsx

Workflow

Output

Parse

Help

Accepted answers

atcodedog05

Hi @Feras95p

As @DavidSta percentile is different from quartile. 20% percentile is 20%th rank 20% quartile is all before 20%th rank.

And here is how you can implement percentile in Alteryx

In Summarize tool in numeric section you have a option for percentile.

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂 And sorry for the late response was away.

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

RFM.yxmd

All comments

DavidSta

Hi @Feras95p,

this problem is caused by the VLOOKUP in Excel.

Your percentile scores are correct calculated. The VLOOKUP with the TRUE option is an approximate match looking for the lowest value in your column.

As 276,2 is your 20th percentile this means all values lower or equal than this should be within the 1st group.

Source: https://www.statisticshowto.com/

Instead of using the 20% of the data you are using the 80% (greater or equal) of your data for the lookup causing the N/A values.

To implement a conditional join like this in Alteryx you can take a look e.g. on this post or you take a look to the "Advanced Join" Macro from the Gallery (find more details on this here).