Performing Customer Churn Rate Analysis in Excel

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

[@OOzd95]

Amazing video once again!

[@MintyAnalyst]

thanks, glad it's helpful :)

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