Cohort Analysis on Customer Retention in Excel

🔽 Download the file from the video | mintyanalyst.com/excel-downlo...
📧 Subscribe to my newsletter | mintyanalyst.substack.com/
☕ Buy me a coffee | www.buymeacoffee.com/mintyana...
🧑‍🎓 6 hours of Free Financial Modeling Training | mintyanalyst.com/free-course-...
If you're enjoying this video, it'd be amazing if you could leave a comment to let me know your thoughts, give it a thumbs up to show your support, and if you haven't already, consider hitting that subscribe button. Your active involvement not only brightens my day but also plays a pivotal role in spreading my content to more people. Thank you for being a part of my journey! 🌟
[FREE Downloads]
🛒 Financial Modeling Excel Templates | store.mintyanalyst.com/
📊 Monte Carlo Simulation in Excel ebook | mintyanalyst.com/monte-carlo-...
📈 Excel Benchmark Analysis Template | mintyanalyst.com/free-excel-b...
🎓 Learn with me on Udemy:
Create a 3-Statement Budget Model | www.udemy.com/course/create-a...
Create a Sales Dashboard in Google Data Studio [FREE] | www.udemy.com/course/create-a...
Introduction to Excel for Finance and Accounting [FREE] | www.udemy.com/course/introduc...
📖 Read on Medium | / dobromir.dikov
🎵 Music | KZbin Audio Library

[@workywork924]

You made such a straight-forward explanation of how to do and why you did it. Very helpful! I've used cohort reports for years but never actually attempted my own.

[@MintyAnalyst]

Thanks for the positive feedback, it means a lot! 😊

[@naramarts1529]

Fantastic Tutorial - very clear and well explained. Many thanks

[@MintyAnalyst]

Thanks Nara, glad it was helpful :)

[@user-bc7xq8ep1f]

Noone has explained the cohort analysis so easily. Thanks a lot!

[@MintyAnalyst]

Glad you liked it! 😎

[@FindingyourKey]

This is mind blowing. Thank you so much!

[@MintyAnalyst]

Thanks, happy it was helpful 😊

[@qqqfranco4532]

What a amazing lesson for me, million thanks

[@MintyAnalyst]

Thank you for the comment, happy it was helpful :)

[@NitinSharma]

Man your content is just pure treasure which i do not wanna share it with anyone else 🤐 Hahaha, Just Kidding 😜 Already shared it with my friends 😎

[@MintyAnalyst]

Thanks Nitin, much appreciated! I will be posting something exciting about a FP&A product we are launching, make sure to subscribe so you can join the free beta version 😉

[@mariacarinaa.4216]

Men thanks for this video! I have a project on Cohort Analysis so this would help :)

[@MintyAnalyst]

thanks, happy it was helpful and good luck with the project

[@OneTwo-3]

A lot to follow, but very interesting and some interesting tricks revealed.

[@MintyAnalyst]

yeah, you're right, I was a bit all over the place 😅

[@ivanwendelltan-6523]

please make more videos about analytics involving customer service. Thank you!

[@MintyAnalyst]

Noted!

[@avanasro6765]

Thanks so much, this is fantastic! Could you set up a customer cohort analysis based on geography rather than based on start date? I am trying to look at retention across 120 fitness studios

[@MintyAnalyst]

Hi Ava, you could potentially set up a cohort analysis based on anything. I think geography would be the cohort, not the metric. So you can follow a similar approach as in the video and calculate Customer Retention per location. Or you can switch the metric with whatever else you are looking to track. Good luck :)

[@OOzd95]

Fantastic video! How would you go about calculating MRR for the cohort ?

[@OOzd95]

With different customer values and churn... its killing me that I cant do it

[@MintyAnalyst]

@@OOzd95 What I'd do in a situation where I have limited data is to calculate average MRR per cohort. I'd through the Monthly Subscription charge in a Pivot, and edit the Field Settings so it's Average and not Sum, and go from there. Of course, you might want to add some checks and make sure there are no outliers in the data (e.g. one customer paying 1000 when the average of the rest is 30), if there are outliers, I'd remove them. To make the analysis a bit more robust, for each cohort (no matter what parameter you use to split into cohorts) I'd calculate the average MRR, as well as the Max/Min MRR. Then, to make sure the analysis makes sense, I might look for such a split into cohorts that ensures deviation between average, max, and min is as low as possible. Hope this helps :)

[@OOzd95]

@@MintyAnalyst ❤️❤️❤️ Thanks so much!

[@earlycareevolution]

Thank you for this video. I have a ecommerce business so I do not track a cancel date. What would I put in the place of cancel date and months to track returning customers?

[@MintyAnalyst]

I don't think the exact same approach would work well for an ecom business where each sale is individual. You will he better off building cohorts based on other propertiea (e.g., number of repeat orders, cart value, etc.)

[@SalvadorGerardoFloresSaiz]

Great content. Thanks for sharing

[@MintyAnalyst]

Thanks for watching!

[@lorenahp1]

In your customer retention tab, what would you say is your average 6 month customer retention? Would it be a straight average of the 6 month retention percentages or would it be a weighted average based on size of cohort?

[@MintyAnalyst]

The retention %'s are not averages for the month. They represent what % was retained based of off the time the customers got subscribed. The average 6-month retention would be the average of the column for 6 months.

[@jkf1192]

What changes would you need to make if you were doing the cohort analysis for for one-off e-commerce orders?

[@MintyAnalyst]

Well, if it's one-off purchases, then there's no retention to analyze, so the whole example here wouldn't make much sense. But you can always split your customers into cohorts based on various criteria (education, occupation, location, etc.) and try to identify trends. Do 60+ years old people purchase mostly on the weekends? Then you can re-focus your marketing campaigns for them to be on the weekends. Do lawyers purchase mostly large orders with multiple items? Then offer bundles and market them to lawyers... The possibilities are endless ;)

[@iffatm1]

Great help thanks

[@MintyAnalyst]

thanks, glad it was helpful :)

[@shanaazbanks]

Do you have an easy way to drill down into the drops? For example, from your example, when there was a dip in June, how could we easily drill down the details of the cancelled accounts. Am I making sense? Otherwise there are a few filters that need to be added.

[@MintyAnalyst]

The way I'd do it is go to the raw data, and filter the month where I saw the drop. I might leave another month as well, for reference. It really depends on the data, but generally, researching drops is harder than researching spikes, as you need to identify something that's not there as opposed to identifying something additional. In short, no specific way that I'd do it, but even going back to the raw data and limiting it to a single month is already way easier in my opinion. Hope this helps ✨

[@sammychervitz]

What is control + D? When exactly do you apply it?

[@MintyAnalyst]

Hi Sammy, thank you for the comment. If you have a total in D5 that sums A5 through C5 and you want to recreate this total for the next 5 rows, you can select from D5 to D15, press Ctrl+D and it will copy down (hence the D) the formula/value in the first cell of the selection (D5) all the way down to D15. You can do that with formulas and values. Same is for Ctrl+R, but it goes to the right within your selection.

[@barutistudio1397]

Thanks

[@magdaposluszny3567]

basic qs - around 7:02 you mention that "a customers signed up in august, at the third month, x customers churned" - doesn't this show count of customers?

[@MintyAnalyst]

Not sure I get the question, the number 1 that I am talking about in the 3rd month is count of customers exactly, yes. I am interested in the count based on the number of months their lifetime lasted. Again, not sure I am getting the question, let me know if you meant something else ✨

[@imranchoudhry]

@10:42 When you say "I am going to copy the whole thing once more" notice all the values in the table change. Please explain what happened there? I am following you step by step to apply the same with my data but am stuck there.

[@imranchoudhry]

Figured it out. Thanks a lot for the great instructional video. I would like to suggest though, instead of using shortcut keys, take us through mouse led actions while explaining the shortcut verbally. It will be far more convenient for novice users to follow.

[@MintyAnalyst]

@@imranchoudhry hi there, happy you figured it out and it was useful! yep, I sometimes forget to narrate shortcuts, but if I were to do everything with the mouse, this video would've been 30 minutes 😅

[@faraazkhan7825]

Same question. I was following it step by step but now stuck at 10:42. I Can't seem to figure out how did the values get changed.

[@MintyAnalyst]

@@faraazkhan7825 I am selecting all rows, copying those and inserting the copied rows above everything. The values change as the references are now to the wrong parts (for the newly copied section I have inserted above everything. But the values don't matter as we later change them, here we are only copying the whole thing to have the same structure to work with. Hope this helps!

[@OOzd95]

Is it possible to calculate MRR if all customers have different values?

[@MintyAnalyst]

What do you mean by different values? MRR is the sum of the recurring revenues each month. In simple terms, f you have 3 customers that pay 10, 100, and 350 per month, MRR is 500.

[@adamrosegarcia211]

Can i use cohort in terms how the payment behavior of our client?

[@MintyAnalyst]

Cohort analysis relates to splitting your data set into groups based on one or more characteristics. If you have information of the payment behavior, you can split your customers that way.

[@lishadsouza3349]

If my dataset already has churn score, how can I segment the scores into low and high churn rate?

[@MintyAnalyst]

Well, I think you can add a column that is equal to either High or Low based on the churn score, and then you can segment based on this categorical column.

[@tanyataneja5901]

i hope, i can use cohort analysis in non subscription business model. In that case, can I take first order date and last order date?

[@MintyAnalyst]

hi Tanya, thank you for your comment. I am not sure I understand the question, you can use any metric that is meaningful to you business and analyze it in cohorts the same way you would analyze it in aggregate.

[@yomioye1994]

How would you perform cohort analysis when all your customers are active?

[@MintyAnalyst]

If all customers are active, you are changing the whole premise of the analysis in the video, so you will need to change it. For example, you can split customers into cohorts and analyze how their average MRR (monthly recurring revenue) develops over time. Then you can show the growth/decline of MRR in each subsequent period in a similar fashion. IT really depends on what you want to analyze, you don't have to keep the exact same format, cohort analysis is all about splitting your customer base in cohorts with similar characteristics. Good luck :)

[@nama.930]

Hey how can i sign up for magnimetrics

[@MintyAnalyst]

hey there, unfortunately, we had to close down Magnimetrics, so there's no way to sign up anymore, we're winding the company down 😞

[@govindasakpal9484]

If we dont have cancel date then how should we calculate??? Please gelp.

[@MintyAnalyst]

I don't think there is an easy way to follow this approach if you don't have a cancel date. You must find a way to at least estimate how long each customer stayed with you, otherwise there's no way to analyze retention, because you don't really know what your retention is.

[@tuancuong37]

how can i download these file in your video ?

[@MintyAnalyst]

you can find the file at the bottom of this article magnimetrics.com/cohort-analysis-explained-with-an-excel-example/

[@junaidqureshi1675]

How do I download the data file? Can you provide a fresh link?

[@MintyAnalyst]

Hey, you can find it here: mintyanalyst.com/excel-downloads/

[@junaidqureshi1675]

@@MintyAnalyst Thank you so much!

[@akashdegala4032]

Can you share this data set

[@MintyAnalyst]

Hi Akash, you can download the data set here: magnimetrics.com/excel-downloads/

[@maclagor2465]

Why you delete the "active" column? You seem to be removing important data. The reason why I bring this up is because your "Subscribed"column (i.e. the totals) does not tie to the sum of the respective row. My gut tells me this is wrong... EDIT: Great material otherwise - thanks for sharing!

[@MintyAnalyst]

Hi Mac, thank you for the comment! I think you misunderstood the way the columns work. The 'Subscribed' column is not a total column. It is a starting reference point that shows 100% as all subscribers are 'subscribed' in the beginning. The next columns show how these fall off as time progresses, it is not a breakdown. It shows what percentage of the subscribers remain with us in the next period. You can read more and download an excel example here: magnimetrics.com/cohort-analysis-explained-with-an-excel-example/

[@maclagor2465]

@@MintyAnalyst Hey Dobromir - really appreciate the insight - this makes sense! Great work, keep up the valuable content!

[@MintyAnalyst]

@@maclagor2465 Thanks 😊

[@ankuragarwal8464]

Where do we find the Plugin you have used. meginmetrices_dev

[@MintyAnalyst]

Hi Ankur, it's a plugin I am currently developing. It's now has a bunch more features and is called Minty Tools. Not yet released, but you can sign up to be notified here: mintyanalyst.com