RFM Analysis With Excel

Рет қаралды 44,813

Күн бұрын

This video is a complete tutorial on performing RFM analysis with Excel. Recency, frequency, monetary analysis is an established, simple, and powerful techniques traditionally used for customer segmentation.
I've personally used RFM analysis as the inspiration for all kinds of powerful analyses, including categorizing geographic areas of the US as part of my marketing analytics work.
⚠ NOTE - There was a bug in the original Customer Recency calculation. This has been fixed in the Excel workbook. ⚠
☕ If you found this content useful and would like to support the channel, you can buy me a coffee: www.buymeacoffee.com/DaveOnData
★★★ My online data analysis courses ▶ school.daveondata.com
-----------------------------------------------------------------------------------------------------------------------
Video Resources
-----------------------------------------------------------------------------------------------------------------------
💻 Get the Excel workbook for this video:
github.com/DaveOnData/DataAna...
📺 Want to learn more about using US Census Bureau data?
• A Real-World Data Clea...
-----------------------------------------------------------------------------------------------------------------------
Excel Analytics Training
-----------------------------------------------------------------------------------------------------------------------
👩‍🔬👨‍🔬 Learn to use your basic Excel skills to analyze the business like a Facebook data scientist:
📺 • Introduction to Busine...
Stay healthy and happy data sleuthing!
#RfmAnalysisWithExcel #RfmAnalysis #Rfm

Пікірлер: 49

@muhammadsulaiman4276 2 жыл бұрын

That’s simply GREAT! I had used your approach to rank our product portfolio as well (with some modifications) and it works great . Thanks a lot David,

@yordanosadigo Жыл бұрын

David ..you are always the best . I did my Maters in Data Science in 2017 with your Titanic dataset Data Analysis with R . I love how you simplify concepts

@Mike-jr7re 2 жыл бұрын

Thank David for the video. There is an error in recency. It is ranked from most recent as 0 to 9 as the worst score. So the filter should be 0 for recency , 9 for frequency and monetary. You should get 82 best records. cheers

@jonathannail9700 Жыл бұрын

Finally, someone explaining this, with the functions/calculations so us plebes can do it. :) Thanks!

@prathameshkoli2916 3 жыл бұрын

Great video Dave. I was looking for a video that will explain the RFM analysis in super simple manner. This video served the purpose. Thank you!

@DaveOnData 3 жыл бұрын

Thank you for the feedback, much appreciated!

@decaalv 3 ай бұрын

Great teacher. Thank you very much. I appreciate it. Great teachers make things simple enough to understand.

@DaveOnData 3 ай бұрын

Thank you so much for taking the time to provide this feedback! I'm happy to hear you've found my content useful.

@rashmihooda8566 Жыл бұрын

very well explained...super helpful

@hamidmghazi 2 жыл бұрын

As far as the explanation on EX the only thing you say is it means exclusive which is awesome! Brilliant

@kwazivilakazi9153 2 жыл бұрын

Very insightful.

@yemiakinwande7039 3 жыл бұрын

Super useful. Thanks David

@DaveOnData 3 жыл бұрын

Awesome! So glad you liked the video.

@DevdattaTV 3 жыл бұрын

Hey Dave, super simple tutorial. Helpful. I see that you have divided it into docile. I want to use the quintile format. Could you suggest the formula for the same?

@karan-aulakh96 3 жыл бұрын

Hey Dave. This is a good take on the RFM model and its application. I had a question though, shoildnt we reverse the current tiles for recency? I believe if one is a more recent customer, then one must be rated higher

@DaveOnData 3 жыл бұрын

Yes, this is a bug in the original video and Excel file - apologies! 🙄 I made notes of this in the video description and comments. The Excel workbook in the GitHub has been fixed.

@DaveOnData 3 жыл бұрын

⚠ NOTE - There was a bug in the original Customer Recency calculation. This has been fixed in the Excel workbook. ⚠ 💥 Learning R programming is easy for Excel users! 💥 📺 kzfaq.info/get/bejne/g7SgptybuNu2YmQ.html 👩‍🔬👨‍🔬 Learn to use your basic Excel skills to analyze the business like a Facebook data scientist: 📺 kzfaq.info/get/bejne/rq-IrNCSx9-qeYE.html

@thepratikjoshi44 Жыл бұрын

Nice video

@shyamss2338 2 жыл бұрын

Hi David, do companies use Excel or SQL or any of the other languages for performing RFM analysis? Moroever, how often are we supposed to perform RFM analysis (for instance the Telecom industry)?

@krishn3538 3 жыл бұрын

Thanks David. Amazing as always

@DaveOnData 3 жыл бұрын

Thank you for the feedback and glad you liked the video!

@krishn3538 3 жыл бұрын

@@DaveOnData Most welcome. How would I interpret 999 A score of 9 9 9 means the best customer ordered long ago ?

@DaveOnData 3 жыл бұрын

Doh! That's a bug. 🙄 The recency score should be higher for more recent purchases. I shall fix the Excel workbook in the GitHub and make notes on the video.

@DaveOnData 3 жыл бұрын

The bug has been fixed in the Excel file in the GitHub repository.

@bringer-of-fire 3 ай бұрын

Hi, I have a question about the customer selection for Recency. At 12:55, you chose segment 9, which identified customers with DaysSinceLastOrder greater than 300 days. However, if we select segment 0, it would capture customers with a lower DaysSinceLastOrder threshold, potentially including those who ordered more recently. So we might want to consider segment 0 instead of segment 9?

@manasa5639 2 жыл бұрын

Hi David wanted to know if you can help having into range of 1 to 5 (with interpretation into segmentation of Champions , Potential Loyalists, New Customers ,At Risk, Can’t Lose Them ).Will be helpful

@psanders4754 Жыл бұрын

Hi Dave, I tried this method using different data and my receny score seemed to produce an opposite result: more recent buyers received a lower score.

@jourdango2615 Жыл бұрын

Can you create a separate video on how to use RFM analysis in a business setting? for example, what are different customer sergmentations based on the RFM scores, and how would the business act on them? E.g what would you do after you identify power users based on scores X, Y Z? what would you do with people almost at churn (e.g used to be power users, but haven't used them in a long while)

@leodhasach... Жыл бұрын

I'm a bit weak on pivot tables (must look for a tutorial). Meantime could I get a similar result by sorting excel columns by a...then b... then c... ? Thanks!

@johnbenzon6546 2 жыл бұрын

The regency score needs to be inverted if you are using 9 as a good score and 0 as unfavorable (quickly done with a vlookup up table). In other words, fewer days returns a low score using the percentage.exc calculation….so the 0 should really be a 9 to be consistent with high frequency and monetary numbers as being favorable.

@bennylin4736 Жыл бұрын

Nice pick up.

@yunjiechen2043 2 жыл бұрын

Hey Dave. Could u plz tell me how to find the max of the older date? TYSM

@shahramsohail9459 2 жыл бұрын

Beginner here! How did you make that kinda pivot table?

@empaulstube6947 Ай бұрын

I think under the Recency column, the numbers with the lowest number of "Days Since Last Order" should receive the highest i.e. 9.

@user-dh6wx3fe6y 3 ай бұрын

great, but your recency score should be inverted - 9 should be for the most recent order (least number of days since last order) so I would add: 10 - (your formula)

@cleopatra3933 7 ай бұрын

Can you pls make a video on ABC analysis?

@shahramsohail9459 2 жыл бұрын

How max order date is calculated?

@ohanalee1538 2 жыл бұрын

Hello Dave. I like your tutorial, it helps me so much doing my homework. But there's still homework that I can't do. So I have a question, can the results of the RFM rank/score be followed by making a cluster of k means? Thank you in advance.

@DaveOnData 2 жыл бұрын

If I understand your question correctly, you can think of RFM as a clustering technique. For example, after performing RFM you then use exploratory data analysis (EDA) on the customers with 999 scores. You then perform EDA on the 111 customers to see if there are any interesting/insightful differences between the two groups (e.g., age, gender, education, etc.). This is a classic customer segmentation exercise. You could also perform a k-means clustering on your customer data to arrive at some clusters (e.g., k = 5 clusters). You would then perform EDA on each of the 5 clusters, again looking for interesting/insightful differences between the groups. This is also a classic customer segmentation exercise.

@ohanalee1538 2 жыл бұрын

Thank you for answering! :D

@stutichugh6702 2 жыл бұрын

Hi! helpful- but you customer sales are in ascending order but then also call/rank the top bucket to be - top10% of users and rank 9...tad confusing

@DaveOnData 2 жыл бұрын

Yes, unfortunately there was a bug found after the fact. The Excel file in the GitHub has been fixed.

@remo9909 2 жыл бұрын

Nice video but if the score should be only b/n 1-5 then what changes to make ? i guess it becomes *5 and for R it will be 5- Formula . Am i right?

@harrybaker7912 2 жыл бұрын

I was wondering the same thing I think that is correct.

@hiteshjoshi3148 2 жыл бұрын

Hi Dave i have a request please make a video on decision tree on excel.

@DaveOnData 2 жыл бұрын

While it is certainly possible to implement the CART algorithm in Excel without VBA code, it would be very error-prone and complex. I would suggest learning to use R (which is very easy for Excel users) instead. Check out this video on my channel that demonstrates how Excel skills makes learning R easy: kzfaq.info/get/bejne/g7SgptybuNu2YmQ.html

@hiteshjoshi3148 2 жыл бұрын

@@DaveOnData sure sir thanks but my skillset is power bi excel and sql i will definitely learn it once hone the former