Customer Churn: Power BI Project
In this project, I would be assessing the churn data of a fictitious telecom company, Databel. My goal is to delve into what factors affect our churn rate, to what degree and observe avenues for reducing churn rate.
I start off by conducting a data check. Is the count of customer id the same as the distinct count of customer id ? To find this out, I create a calculations table to house the calculated measures I will make in this project. Visualizing the count and distinct count values using cards enables me to see the resulting values.
This lets me know that each record in the dataset is unique so our aggregates will not be affected.
The column Churn Label contains either “Yes” or “No” values letting me know which customers have churned. To make my work easier, I create a new binomial column assigning 1 for all “yes’ and 0 for all “no”. This column will be called “Churned”.
Summing my ‘Churned’ column gives me the total customers who have churned during the period. I can now calculate the churn rate using the formula [churn rate = number of churned / number of customers ], making sure to format the result as a percentage. Visualizing this measure in a card results displays a churn rate of 26.86% which is fairly high. To gain a better understanding of why Databel has such a high churn rate, I will delve into who is churning and why.
Visualizing the churn categories using a tree map shows that Competitor presence(umbrella term for the various competitor related reasons) has the most influence on customers churning.
With 2 of the top reasons coming from that category.
Let’s see how churn rate changes from state to state.
California has the highest churn rate with a whooping 63.24%.
Now, I want to see the demographics involved in churning. I create a “Demographics” column based on age.
Seniors are churning above the group average. To delve deeper into the age distribution of churners, I create age buckets to visualize the juxtaposition of the volume of each age bucket against each bucket’s churn rate. This helps to see who Databel should focus their attrition reduction efforts on.
Moving on from the age demographic, I now want to assess how group contracts affect churn rate.
The monthly charge is significantly lower for people who are in a group of 2 or more people.
Overall, churn rate drastically reduces where 2 or more people get on a group plan.
The database groups contracts under “One Year”, “Two Year” and “Month-To-Month”. I want to categorize them only based on whether the contract is monthly or yearly so I use a switch function to create a new column “Contract Category”.
The churn rate decreases drastically for subscribers on a yearly plan.
I have a theory that just as people on a yearly plan are unlikely to churn, the same is true for people on an unlimited plan. Let us test out this theory.
Shockingly, subscribers on an unlimited plan are more likely to churn. I suspect this may be related to the average GB consumption of the group. To test this out, I categorize the average GB consumption into “Less than 5 GB”, “Less than 10 GB” and “10 GB or more”.
Naturally, subscribers on an unlimited plan who consume less than 5 GB of data are the most likely to churn — as being on said plan does not actually benefit them — and their counterparts who are not on an unlimited plan are the least likely to churn.
Now, I want to see how subscribers who purchase an international plan churn.
As expected, subscribers on an international plan who are not active(internationally) have the highest attrition. Conversely subscribers who are not on an international plan but actively make international calls churn the fastest among non-active plan subscribers. This poses an opportunity for Databel to increase marketing efforts to those folks. Also proposing a downgraded plan to subscribers on an international plan who do not make international calls will increase customer satisfaction and subsequently reduce churning.
For instance, in California, the state with the highest churn rate (rate of 63.24%), the churn rate for international call makers who are not on an international plan is a whooping 72%.
Could the perception that our competitors offer better plans (a major reason for churning) be because subscribers are unaware of our international plans ?
Let’s assess Databel’s performance over time.
We see a downward trend in churn rate over number of months a customer has been with us. This is good news. The longer a subscriber has been with us, the less likely they are to churn.
Satisfied with my analysis so far, I move on to creating the dashboard. In creating the dashboard, I keep my audience in mind. In this specific case, my audience is the Head of Sales and Marketing at Databel. Keeping her in mind while creating the dashboard will help tailor the dashboard to give her the most value — making sure to use terminology she understands. Another goal is to limit the number of visualizations. In my excitement, I created many visualizations to understand the data better and conduct a thorough analysis. However, the audience does not need all those visualizations cramped into one dashboard for them. That may cause sensory overload and take away from the effectiveness of my work. Only visualizations most relevant to her current enquiry are needed for now. Finally, I will make the dashboard interactive so that she can engage with it on her own in case any new pertinent curiosities pop up she quickly wants more insight on.
Thank you for taking the time to go through this project. To access the dataset, visit my Github. You can see other projects by me here. Let’s connect on LinkedIn.
