Analysing customer churn in Power BI

Problem Statement

The dataset under analysis is an fictitious dataset about churn from a Telecom provider. Need to perform data analysis and provide insightful details, action plans to reduce the rate of churning

Analysis

We will follow the below flow for our analysis :

Data Check

1. Check if all the customers in the dataset are unique, this can be done in multiple ways and one way is to create two new measures “Number of customers” and “Number of unique customers” . If the values of these two measure are equal then there are no duplicate entries in “customer_id “ column

   

   

   With the help of card visual, we can confirm that all entries in “customer_id “ column are unique

   

2. Out of these, need to find number of customers that are churned. The same can be obtained by creating new measure which filters and calculates number of people churned

   

   

Converting this number to percentage for better intuitive insights

26.86 % is a very huge number as it implies that for every 100 subscribers, about 27 customers are churning. Need to perform EDA on the reasons for such higher churning rate

Data Exploration and Visualisation

1. Next we will try to analyse the different reasons for why customers are churning, this can be visualised using bar chart as shown below:

The major contributing category is “Competitor” and top reasons being “Competitor made better offer” , “Competitor had better devices”

2. It would also be a good idea, to find out which states have the highest churning rate and based on this, telecom provider can introduce regional offers to attract its customers. Filled map could be a suitable visual to plot the states and their corresponding churning rate, but currently in the given data PowerBI was unable to recognise state column data category as “State”. Hence, identifying the correct data category of “State or Province” to this column will help in seamlessly building the map visual, the globe symbol highlighted below signifies that column has been recognised as a geographical location

   

   For intuitive understanding of visual, the filled map is gradient filled based on “Churn Percentage” measure calculated earlier

   

   

   From the above visual, we could clearly understand that California state has the highest churning rate of 63.24%

3. Next best thing to do would be to check the age group wise distribution of customers that are churning, but currently we have “Age” column as a continuous variable - we can convert this into a categorical variable by binning people with age less than 30 as “Under 30” , people with age greater than 65 as “Senior Citizen” and rest of the people as “Other” and storing this in column named as “Demographic”. This conditional statements can be achieved using an SWITCH statement in DAX as shown below:

   

   Building an column chart based on demographics, we can clearly see that even though the number of churned customers are more in the age group of 30-65, percentage wise we can see that senior citizens most often churn

   

While from above analysis, we can see broadly that senior citizens are most churning and lets deep dive to understand, exactly which age brackets are churning more, by dividing ages into bins of size 5

Building bar and line chart combo, with x-axis as the Age(bins) group, we can see that, generally as the age increases the churning percentage is also increasing and more specifically churning rate starts to shoot up after the age of 60, with peak reaching to 52% after 80 years

4. Currently the telecom provider has the “Group Contract” policy, where in if subscribed to this customers will get service at the cheaper price. So let’s explore the effect of this on churning

From the above visual, we could clearly see that customers who opted for Group policy have lower average monthly charges and lower churning rates. The churning rate is highest with 32.85% for customers of group size 0 and lowest of 5.60% for customers of group size 6

5. In the data given, there are three different contract types: "One Year", "Two Year", and "Month-to-Month". It would be a good idea to gather the values of yearly contracts into one. In this way, we can observe the difference between the customers who have only yearly contracts and those having monthly contracts

This multi row card shown below, clearly signifies that the churning rate is quite high in case of monthly subscriptions

From below clustered column chart, we could see that irrespective of gender, the trend remains the same

6. Telecom provider has the hypothesis that, customers with unlimited data plan generally tend to churn lower, so let us test this hypothesis by building a table visual

   

   It appears that customers who are on an unlimited plan are more likely to churn. To see if it is related to a certain amount of mobile data (GB) being used, create a new column Grouped Consumption that classifies the average monthly GB download in the following groups:

   • Less than 5 GB

   • Between 5 and 10 GB

   • 10 or more GB

   

   

   From the above clustered chart, we could clearly see that churning is higher in cases where customers opted for unlimited plan and their average monthly download size is less than 5 GB

7. Let us analyse the international activity of customers and its relationship to churn. We are curious about the behaviour of customers who call internationally, and if paying for an international plan influences their loyalty.

From the above matrix, we could see that the churning rate is higher in case where the customers have international plan but not active internationally.

We have already seen that the California state has higher churning rate, hence let us analyse the same data with filter of state as California

We could see that 72% of customers are using international calls service, without an active international plan

Dashboard

Findings and Suggestions