Performing Customer Churn Rate Analysis in Excel

🍃 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!
Dataset used in the video:
www.kaggle.com...
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...
Cloud FP&A Platform for SMEs and Solopreneurs | magnimetrics.com
Sign Up for Magnimetrics for FREE | app.magnimetri...
Excel Add-in for FP&A professionals | magnimetrics.c...
Learn with me on Udemy:
Create a Dynamic 5-Year Financial Model in Excel | www.udemy.com/...
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
AI-Powered Business Social Media Posts Generator | mintyposts.com
AI-Powered IFRS/GAAP assistant | gaaplify.com
Music | KZbin Audio Library

[@afolabiabiodun76]

Thanks for this video, please can you explain the total counter colum again?

[@MintyAnalyst]

It's a simple COUNTIF function that counts how many times the value in the current row of column A appears in the entire column A. You can do this to check if each row is unique. Hope this clears things out 😊

[@afolabiabiodun76]

@MintyAnalyst Yes, thank you sir

[@LaraTraveling]

Thanks a lot! Easy to understand and really helpfull tutorial!

[@MintyAnalyst]

Thank you Dilara, happy it was helpful :)

[@SamuelObayagbona]

Hi Dobromir, Thanks for the video, I have a question about the total counter, In the video, your dataset had unique customer IDs, What would you have done if the customer ID column had duplicates, For instance, the customer cancels a plan and subscribes to another.

[@MintyAnalyst]

depends on how many such cases there are... if they are rare, I wouldn't worry about them as they won't have a big impact, but if there are many such customers, then you can either try to combine the duplicates in a continuous subscription or you can leave the data set as is and make sure you note somewhere that this was your approach... at the end of the day, as long as you know the premise of your analysis you can consider such specifics when analyzing the results. in short, there's no 'right' way to do it 😉

[@srisainathreddy6894]

such a great video really helped me but i have a question like this where i need to find the new customers and the churn rate is already given and also the revenue for every month is also given.

[@MintyAnalyst]

Glad it was helpful. I am not sure I understand your question, but if you follow the same logic in reverse, it should work. Good luck :)

[@srisainathreddy6894]

@@MintyAnalyst A company has decided to launch a monthly magazine, which costs $3 per month. We expect the initial churn rate to be 17%. We also expect the churn rate to stabilize over time, decreasing by 5% every month. (In other words, the churn rate after 1 month is 17%, the churn rate after 2 months will be 16.2%, and so on.) Assuming that the magazine will launch on Jan 1, 2021, what is the ​minimum number of new subscribers​ we will need every month to reach $1,000,000 in total revenue by Dec 31, 2021? This is the problem and its really tricky can you please help me how to reach you dikov..

[@MintyAnalyst]

@@srisainathreddy6894 Hi there, it's kinda hard to reply to this one in a comment, so I prepared a new video outlining the solution. If you are subscribed to the channel, it should pop up in your dashboard tomorrow :)

[@srisainathreddy6894]

@@MintyAnalyst I am already subscribed dobomir please upload it and thanks

[@razza1234]

When I get to the pivot table stage, it won’t let me click Calculation Field. Anyone know what I should do

[@MintyAnalyst]

Hi Razza, a bit late, but I hope you fixed it! I tried reproducing your issues but couldn't, calculated fields are available in my case...

[@shyamss2338]

This dataset has 7043 records. What do we do when there are large datasets from telecom companies for instance in real life. Wouldn’t excel have a limit?

[@MintyAnalyst]

It has, it's about 1milion rows/records. Once excel falls short, you can look into using power query in excel, or switch to a BI solution like Qlik or Tableu. You can also keep working locally and look into performing similar analysis with python.

[@shyamss2338]

@@MintyAnalyst Thank you for the response. I understand that excel has a limit of 1 million records but suppose a telecommunication provider has 6.5 millions records, then what can be done? If excel has this limitation, then why do companies still do churn analysis using excel? Can you do a video where large datasets can also be done like you mentioned using power query or tableau? Would be helpful to a lot of us.

[@shyamss2338]

@@MintyAnalyst how would we do churn analysis in tableau? Here the source data would be our database for tableau? Do you have any relevant video. Thank you for the response. 😀

[@MintyAnalyst]

@@shyamss2338 companies still use Excel as they rarely have more than 1m records. Also, with Excel's PowerQuery, you can link external data sources (e.g., a database in SQL) and operate with it within Excel by aggregating the data.

[@MintyAnalyst]

@@shyamss2338 I still believe it can be done in a BI tool (I have no videos on that), but if it's a large company and has millions of records, I wouldn't do it in Excel or try to pull it off in a BI app. I would expect a large company to have a pretty powerful CRM system, and as far as I know, most large CRM's calculate churn for you. It may make sense to research CRM systems and see if this is a better option. Good luck :)

[@OOzd95]

Amazing video once again!

[@MintyAnalyst]

thanks, glad it's helpful :)