Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Applying RFM Module to My case

Feras95p
8 - Asteroid

Dear all,

 

I have an excel sheet data for a restaurant customers. 

 

I have a data for 35,000 customers, the data include that:-

 

1- The date of the first order.

2- The date of the last order.

3- The average days between the orders.

4- The total number of the orders.

5- The total dishes of the orders.

6- The total cost  of the order.

 

 

I want to apply the RFM module to identify the customer segment  

 

 

Note that all the records are unique by their phone number but I have delete their number due to privacy. 

 

Any help in that please? I

 

Thanks in advance for your kind support.

6 REPLIES 6
atcodedog05
22 - Nova
22 - Nova

Hi @Feras95p ,

 

Here is an approach for your data.

 

Here is my consideration to support RFM, below fields are assigned to RFM

Recency = Last order was before (days)

Frequency = The average in days between the orders

Monetary = The total cost of the dishes

 

Customer with no Frequency might need to be handled separately. 

atcodedog05_0-1587381920879.png

This workflow is attached.

Creating a bucket for Recency, Frequency, Monetary is suggestible. 

 

And later creating customer groups would help you identify patterns. Refer below example from a case study.

atcodedog05_0-1587382160643.png

 

You need to create groups that supports your business use case.

 

Here is the link of a similar use-case.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Customer-segmentation-in-Alteryx/td-p/...

 

I hope this helps. Let me know what you think about it.

 

Thank you for sharing this scenario. It was a great exposure for me too.

Cheers and Happy Analyzing : )

Feras95p
8 - Asteroid

Hi @atcodedog05 ,

 

Thanks for your kind support ! It seems that you did such an amazing work! 

 

Unfortunately, I couldn't open the workflow due to different version. Is there anyway to convert the versions?

 

k.PNG

 

 

 

Thanks again for your support! 

 

atcodedog05
22 - Nova
22 - Nova

Hi @Feras95p 

 

Here is a handy post on how downgrade version. 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-downgrade-packaged-workflow-Fil...

 

Please try and let me know. If you still face issues i will help you out.

Feras95p
8 - Asteroid

Hi @atcodedog05 ,

 

Thanks, it's opening with me now. 

 

I was wondering what do you mean by RFM (bins) and what's the used of them?. Also, I didn't understand how do you calculate the RFM bins. Could you please explain it for me?

 

Now by the support of your workflow I have all the data which are

 

CustomerID= mobile phone of the customer

Recency= last order was before (days).

Frequency= The average in days between the orders.

Monetary= The total cost of the dishes.

 

Now in order to group them like the link you shared with me I have to give a score in scale of 1-5  to each of RFM values . Is there any technique I have to follow to give a score to each RFM for each customer?

 

 

Thanks in advance for your kind support.

atcodedog05
22 - Nova
22 - Nova

Hi @Feras95p 

 

RFM model is purely business specific is varies from business to business.

 

Recency : gives customer who was last active

Frequency : gives customer who is more active

These 2 are more importance/weightage 

 

Monetary : gives sales got by the customer. There might also be scenario where a guy bulk ordered but he didnt use the service later.

 

Recency and Frequency can have buckets like 

1-5 days

5-10  days

(linear)

 

or

1-3 days

4-7 days

14 days

(kind of exponential)

 

This is totally dependent on your business.

 

Also another method is quartiling like in box plot. You create bins like Q1(Top 25%), Q2(Top 26%-50%), Q3, Q4

https://www.youtube.com/watch?v=HwiBD8MB-14 

https://www.optimove.com/resources/learning-center/rfm-segmentation

 

The decision for all these are dependent on the business knowledge and nature.

 

You or your peers needs to make this decision it would be hard to for someone outside the business.

 

Hope this helps 🙂

Feras95p
8 - Asteroid

Hi @atcodedog05 ,

 

 

How are you? I hope you're doing well.

 

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 them divide them into 5 group by taking the lower 20% and give it score 5 and then the next 40% and then 60%, 80% and 100%. 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.

 

Kindly have a look into the excel file and let me know if you get the where is the problem.

 

 

Thanks in advance for your kind support!

Labels