RFM Analysis With Excel

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. ⚠

-----------------------------------------------------------------------------------------------------------------------
Video Resources
-----------------------------------------------------------------------------------------------------------------------
💻 Get the Excel workbook for this video:

📺 Want to learn more about using US Census Bureau data?

-----------------------------------------------------------------------------------------------------------------------
Excel Analytics Training
-----------------------------------------------------------------------------------------------------------------------
👩‍🔬👨‍🔬 Learn to use your basic Excel skills to analyze the business like a Facebook data scientist:

Stay healthy and happy data sleuthing!

#RfmAnalysisWithExcel #RfmAnalysis #Rfm

[decaalv]

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

[DaveOnData]

⚠ 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! 💥

👩‍🔬👨‍🔬 Learn to use your basic Excel skills to analyze the business like a Facebook data scientist:

[Mike-jrre]

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

[prathameshkoli]

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!

[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

[jonathannail]

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

[karan-aulakh]

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

[hamidmghazi]

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

[muhammadsulaiman]

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,

[bringer-of-fire]

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?

[empaulstube]

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

[johnbenzon]

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.

[NikitaShilyaev]

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)

[cleopatra]

Can you pls make a video on ABC analysis?

[jourdango]

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)

[psanders]

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.

[yunjiechen]

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

[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!

[manasa]

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

[DevdattaTV]

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?