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.
Gelöst! Gehe zu Lösung.
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.
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.
You need to create groups that supports your business use case.
Here is the link of a similar use-case.
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 : )
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?
Thanks again for your support!
Hi @Feras95p
Here is a handy post on how downgrade version.
Please try and let me know. If you still face issues i will help you out.
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.
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 🙂
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!