Cohort Analysis on Customer Retention in Excel

Подписаться 7 тыс.

Просмотров 55 тыс.

50% 1

🔽 Download the file from the video | mintyanalyst.c...
🍃 Check out Minty Tools | mintytools.com/
📧 Subscribe to my newsletter | mintyanalyst.s...
☕ Buy me a coffee | www.buymeacoff...
🏷️Don't forget to use promo code "MINTY50" for a 50% discount during checkout!
🧑‍🎓 6 hours of Free Financial Modeling Training | mintyanalyst.c...
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.mintyana...
📊 Monte Carlo Simulation in Excel ebook | mintyanalyst.c...
📈 Excel Benchmark Analysis Template | mintyanalyst.c...
🎓 Learn with me on Udemy:
Create a 3-Statement Budget Model | www.udemy.com/...
Create a Sales Dashboard in Google Data Studio [FREE] | www.udemy.com/...
Introduction to Excel for Finance and Accounting [FREE] | www.udemy.com/...
📖 Read on Medium | / dobromir.dikov
🎵 Music | RU-vid Audio Library

Опубликовано:

1 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 80

@imranchoudhry 2 года назад

@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 2 года назад

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 2 года назад

@@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 2 года назад

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 2 года назад

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

@NitinSharma 2 года назад

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 2 года назад

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 😉

@gollumarx 2 месяца назад

Very nice video, but the music is actually a bit distracting, it comes just as noise to me

@MintyAnalyst 2 месяца назад

Sorry about that, trying to be better at audio design in my newer videos :)

@lishadsouza3349 7 месяцев назад

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

@MintyAnalyst 7 месяцев назад

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.

@sammychervitz 2 года назад

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

@MintyAnalyst 2 года назад

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.

@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 ;)

@junaidqureshi1675 6 месяцев назад

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

@MintyAnalyst 6 месяцев назад

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

@junaidqureshi1675 6 месяцев назад

@@MintyAnalyst Thank you so much!

@shanaazbanks 11 месяцев назад

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 10 месяцев назад

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 ✨

@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

@ankuragarwal8464 2 года назад

Where do we find the Plugin you have used. meginmetrices_dev

@MintyAnalyst 2 года назад

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

@ivanwendelltan-6523 3 года назад

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

@MintyAnalyst 3 года назад

Noted!

@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 10 месяцев назад

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 ✨

@workywork924 7 месяцев назад

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 7 месяцев назад

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

@qqqfranco4532 Год назад

What a amazing lesson for me, million thanks

@MintyAnalyst Год назад

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

@eubrunomiau 2 месяца назад

Amazing, the best example I've seen in YT!!

@MintyAnalyst 2 месяца назад

Wow, thanks! 😊

@barutistudio1397 3 года назад

Thanks

@govindasakpal9484 4 месяца назад

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

@MintyAnalyst 4 месяца назад

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.

@adamrosegarcia211 2 года назад

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

@MintyAnalyst 2 года назад

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.

@iffatm1 2 года назад

Great help thanks

@MintyAnalyst 2 года назад

thanks, glad it was helpful :)

@maclagor2465 3 года назад

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 3 года назад

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 3 года назад

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

@MintyAnalyst 3 года назад

@@maclagor2465 Thanks 😊

@lorenahp1 2 года назад

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 2 года назад

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.

@nama.930 6 месяцев назад

Hey how can i sign up for magnimetrics

@MintyAnalyst 6 месяцев назад

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

@OOzd95 3 года назад

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

@OOzd95 3 года назад

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

@MintyAnalyst 3 года назад

@@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 3 года назад

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

@FindingyourKey 2 года назад

This is mind blowing. Thank you so much!

@MintyAnalyst 2 года назад

Thanks, happy it was helpful 😊

@akashdegala4032 2 года назад

Can you share this data set

@MintyAnalyst 2 года назад

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

@naramarts1529 3 года назад

Fantastic Tutorial - very clear and well explained. Many thanks

@MintyAnalyst 3 года назад

Thanks Nara, glad it was helpful :)

@ЭльмираГазизова-ч7и Год назад

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

@MintyAnalyst Год назад

Glad you liked it! 😎

@yomioye1994 3 года назад

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

@MintyAnalyst 3 года назад

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 :)

@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.)

@tanyataneja5901 2 года назад

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 2 года назад

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.

@OOzd95 3 года назад

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

@MintyAnalyst 3 года назад

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.

@SalvadorGerardoFloresSaiz Год назад

Great content. Thanks for sharing

@MintyAnalyst Год назад

Thanks for watching!

@avanasro6765 2 года назад

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 2 года назад

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 :)