End-to-end Customer Churn Analysis in Tableau
Updated: Dec 15, 2022
For subscription-based businesses, reducing customer churn is a top priority. In this case study, I investigate a dataset from a fictitious Telecom company called JK-Telecom and analyze its churn rates. Analyzing churn does not simply mean calculating the churn rate. It is more about figuring out 1) why customers are churning at the rate they are, and 2) how to reduce churn.
The aim of this study is to answer these questions by creating calculated fields and various visualizations in Tableau, such as dual-axis graphs and scatter plots. I also present dynamic graphs by using filters and parameters, and combine everything into a story to share insights.
But first, what is churn exactly?
The churn rate, also known as the rate of attrition or customer churn, is the rate at which customers stop doing business with an entity. There are multiple ways to calculate churn
and it varies by industry and revenue model. The simplified formula for churn is to divide customers lost by total number of customers.
Churn rate = customers lost / total number of customers
Churn rate = 15 / 100
15 / 100 = 15%Step of this project
The project consists of the following sections:
Data check
Compute churn rates
Investigate churn reasons
Dig deeper in churn categories
Use maps to advantage: churn rates by state
Demographic analysis
Group discount contract
Unlimited data plan
International activity of customers
Contract type and payment method
Dashboard and Story
Summary
1. Data check
The first step in any analysis is doing a data check. I create two measures to check if the count of customer ids is equal to the count of unique customer ids. This check is particularly important, because in case there are duplicate rows we might double-count costs later.
I create two calculate fields called Number of Customers and Number of Unique Customers.
"" Creating calculate fields""
COUNT([Customer ID]) // Number of Customers
COUNTD([Customer ID]) // Number of Unique Customers The table shows that the count of unique customers match the count of customers with 6,687. Now, we know each record contains a unique customer id, it is time to get analyzing!
2. Calculating churn rates
It will be extremely useful to have a measure that calculates churn before deep diving into the analysis. We have a column called Churn Label that indicates Yes or No. Using an IF statement, I convert this column to a binary column that will contain 1 or 0, indicating if the customer churned or not, and use that to calculate the Churn Rate.
"" Create a calculate field called Churned""
IF [Churn Label] = "Yes" THEN 1 ELSE 0 END
"" Calculate the Churn Rate"
[Number of Churned Customers] / [Number of Customers]26.86% churn rate seems fairly high, so it is time to work so we can understand which type of users are churning.
3. Investigating churn reasons
The logical next step is to investigate the different reasons why customers churned. I create a column chart listing the different reasons why customers churn in descending order.
The top 5 churn reasons (except Don't know response) are as follows:
Competitor made better offer
Competitor had better devices
Attitude of support person
Competitor offered more data
Competitor offered higher download speeds
4. Digging dipper in churn categories
Churn Reasons are grouped together in the Churn Category column. The "Extra data charges", "Price too high" and other price related reasons are grouped together in the "Price" category.
It shows that 44.82%, almost half of all customers churning, are related to the competitor category.
5. Use maps to our advantage
Let's say that competitors launched aggressive promos in certain states, and JK-Telecom is wondering if it had impact on their customers. I create a map in Tableau to investigate the churn rate by state. For a informative map, I add Churn rate, Number of customers, and Number of churned customers to Tooltip. Also, I make use of Size or Color so it's easy to spot states with a higher churn rate.
In the map, we can see that California has the highest churn rate of 63.24%, as 43 out of 68 customers stopped subscription.
Â
Before moving on to the next part, let's take a look at findings that we discovered so far.
We know the average churn rate is 27%.
The main reason why customer churn is related to competitors. This could raise questions such as "Is the company services offer competitive enough?"
We discovered the churn rate in California is abnormally high with 63.24%
But it is a bit too early to make any general conclusions. We have no clear explanation yet for the relatively high churn rate of 26.86%, and there are still many columns to analyze. Now, we are a bit deeper in the analysis, we should make sure that we have an holistic analysis plan. Thus, in the following sections, I explore more diverse columns in the table below.
Â
6. Demographic analysis
First, I want to know the size of the different demographics group. Secondly, I want to see the percentage of people that are churning in each of those groups. Number of Customers (left axis) and Churn rate (right axis) are used as the two main measures.
It looks like the churn rate for Senior citizens is around 10% higher than the average 27%. The age groups of 70 and above have the highest churn rates, but they also contain the least amount of people.
7. Group discount contract
JK-Telecom offers group contracts to customers from the same household. The advantage for the customer is a discounted rate, while it is a great way for JK-Telecom to grow its customer base. So, the main goal of this section is to analyze if customers that are part of a group indeed have a lower phone bill, and if it has an impact on the churn rate.
It appears that monthly charges is significantly lower for people who are in a group of 2 or more. A group with 6 people has the lowest churn rate. The churn rate for people in groups is significantly lower (<10%), but over 75% of all customers are not part of a group.
8. Unlimited data plan
How does the Unlimited Data Plan influence churn rate? I set a hypothesis that people who are not on an unlimited data plan are more likely to churn. I create a quick text table that displays the churn rate for customers with and without the unlimited plan.
This is surprising. It actually appears that customers who are on an unlimited plan are more likely to churn. It would be good to have an idea of how much mobile data in gigabyte (GB) they are using on a monthly basis.
Let's create three buckets in a new calculated field Grouped Monthly GB Download: customers who consume less than 5 GB monthly, between 5 and 10 GB, or 10 or more GB. This is where an ELSEIF statement comes in handy.
"" Create a calculated field called Grouped Monthly GB Download""
IF [Avg Monthly GB Download] < 5 THEN "0-5"
ELSEIF [Avg Monthly GB Download] < 10 THEN "5-10"
ELSE ">10"The bar graph shows that customers who are on an unlimited plan but do not consume more than 5 GB per month tend to churn more.
9. International activity of customers
Let's say JK-Telecom request to analyze the international activity of customers and its relationship to churn. We are curious about the behavior of customers who call internationally, and if paying for an international plan influences their loyalty.
I create a simple text table shows the churn rate for the different international dimensions. The column Intl Plan shows if a customer has an international plan or not, and the row Intl Active shows if the customer is internationally active or not. Then, I convert my table into a highlight table so that the graph display a high churn rate with a red background, and a low churn rate with a green background. Also, I add relevant information to Tooltip.
The churn rate of customers who have an international plan but do not call internationally is skyhigh. The number of customer in this group is 177 with the average $33 of the monthly charge. The silver lining here is that although the churn rate is ridiculously high, there are luckily not that many customers part of this group. This group appears to have the highest average monthly charge of all four groups.
My advice to JK-Telecom is that the company can contact customers in this group to propose them to downgrade their plan. Proposing a cheaper and explaining the rationale will increase customer satisfaction and stop customers from churning.
10. Contract type and payment method
Before compiling the analysis into a dashboard, three important topics not explored yet are the payment method, the contract type, and how many months a person is a customer at JK-Telecom. I create a scatter plot using Account Length (in months) which is the average account length and Churn Rate. Afterwards, I add Contract Type and Payment Method to the scatter plot.
It appears that customers who are on a Month-to-Month contract are way more likely to churn. I put extra focus on this message by changing the size of the dots using an appropriate measure. 1,141 out of total 1,796 churners come from month-to-month contract and payment by Direct Debit.
11. Dashboard and Story
In this final section, I build 4 different dashboards and combine them together into a story.
I design the interactive dashboard in such a way to enable easy communication of insights.
* Click the arrow sign > in the image for the next page.
* Click here to see a full interactive version.
Interactivity makes a dashboard powerful.
Let's see what happens when the end user clicks on Month-to-Month. Month-to-month refers to the contract type, and explains the metrics for customers who are on a monthly contract. We notice the churn rate goes up to 46%, and that almost 1,600 out of 1,800 churners come from this group. What an insight! On the other hand, when we select the Two Year contract, the churn rate drops to an incredibly low 2.78%. These kind of interactions are powerful, and we can use them to our advantage.
12. Summary
Tableau is a playground for data analysis. We have applied various different skills throughout this case study. We started by data check and analyzing the dataset with a variety of visualizations such as dual axis graphs and scatter plots. Secondly, we created different calculated fields to make our analysis easier and more powerful, and manipulated the data with bins where applicable. Lastly, we leveraged the power of interactivity by using filters and even visualizations as filters. We also used parameters to make our graphs dynamic.
I will wrap up this post with one of my favorite quotes from the industry leader.
Visualizations give you answers to questions you didn't know you had. ã…¡Ben Schneiderman
Indeed, analysis with clear visualization deepens our understanding of the subject, and empowers us to ask meaningful and strategic questions. I hope you come up with your own interesting questions with my interactive dashboard.
Thanks for reading!
Â
This project is built on the course "Analyzing Customer Chrun" from Datacamp.