Analyzing-Customer-Churn-In-Power-BI

Overview

Understanding the meta data gives a good grip over the data you are going to handle.
Churn Rate Analysis: Calculated the churn rate and found it to be 26.86%. You also identified the top reasons for customer churn, such as competitors offering better deals, device quality, and support issues.
Geographical Analysis: explored the geographical distribution of churned customers, focusing on states like California and Pennsylvania with high churn rates. This information can help target areas for improvement.
Demographics Analysis: categorized customers by age groups and found that seniors and those under 30 had varying churn rates. This information can inform marketing and service strategies tailored to different age groups.
Group Contracts: Customers with group contracts had a lower churn rate, indicating that offering group contracts might be a successful strategy for reducing churn.
Contract Type Analysis: Monthly contracts had a higher churn rate compared to yearly contracts, and there was a slight difference between male and female customers. Understanding contract preferences and their impact on churn is valuable.
Data Plans and Churn: Contrary to the hypothesis, customers with unlimited data plans had a higher churn rate. examined the impact of data consumption on churn rates.
International Calls: found that the combination of International Plan and International Calls influenced churn rates significantly. This can help in refining international call plans and pricing.
Customer Service Calls: Analyzing the relationship between payment methods, contract types, and customer service calls revealed insights about the connection between customer service interactions and churn.
Extra Charges: Customers using more data faced higher extra data charges. Understanding these charges can help in designing more competitive data plans.
Geographical Variation in Service Calls: You observed variations in the number of customer service calls across different states. This can be used to allocate resources and improve service in regions with higher call volumes.
Dashboard: created a dashboard to present your insights, making it easier to share the findings with others in a visually appealing way.

Meta data Information

Exploratory Analysis:

Step 1 : To create measures and columns

we start by creating 2 measures Count of Customers and count of unique customers both are same.
Next step we calculate the churn rate with the column churn label having parameters "yes" or "no"
In order to calculate churn rate we have to calculate churned customers and apply this formula.
churn rate=churned customers/Total no of customers
it shows the churn rate is 26.86%.

Step 2: Investigating Churn Reasons:

Created a column chart to of why customers churned.
Top 20 Reasons on Why customers are churning more.

By seeing this column chart we can say that 16.87% of our customers leaved because competitor made better offer it means that we are not competitive enough and also 16.54% have left they have better devices both reasong having the same percentages tells that we need to work on our devices, 11% have left they didn't got the support from the support person telling we need to work on support persons too
Other factors accounting to 5%.
- This tree map shows main categories of churn :

Step 3: Using Maps to find Competitors:

From the above map churn rate w.r.t no of customers tells that majority of churned customers are located in california and has churn rate of 63.24% , out of 68 customers 43 customers are churned which company needs on focus on why it is happening in that california area.
Next major state is Pennsylvania. churn rate of 33% which is half the churn rate of california.

Investigating Churn Patterns - Analysis

Analyzing Demographics:

In the demographics in metadata we have 2 columns named:
- Under 30
- Senior
Using this we create a measure named demographics with this formula.

Demographics = if('Databel - Data'[Senior]="Yes","Senior",if('Databel - Data'[Under 30]="Yes","Under 30","Other"))

It tells that seniors having the churn rate 38% and under 30 people accounting for 23% and others between age groups of abover 30 and below 65 accounting for 24%.
By creating we can analyze this more deeper with the different age groups having bin size of 5.
From the above line and stacked column chart we can that churn rate is increasing when the age is increasing. we can see a significant amount of churned customers when the age is 60.
It also tells ours majority customers lies in the age 45 of customers 694 and age 25, count of customers 674.
Few customers lies in the Age group 80-85.

Inspecting Groups

Databel offers group contracts to customers of same household.
The advantage of customers is discounted rate. great way to grow the customer base.
To analyze customers have lower phone bill it has impact on the churn rate

The above line and stacked column chart tells the churn rate is very less for the groups 2-6 means customers who are having group contracts are less likely to churn.
we can also there is significant price differences between the customers who are having group contract and no group contract.
For no group contract people there is 32% churn rate and avg monthly charge is 33$.
For group contract people the avg monthly charge is 22-23$ and churn rate is around 5-6%.

Multiple fields to investigate

Databel has 3 contract types :
- One Year
- Two Year
- Month To Month -- we will see the count of cutomers in each contract and churn rate in the contracts, is gender affecting the churn rate wrt contract.

From the multi row card we can see that 46% churn rate is from monthly contracts the people who have taken monthly contract are more likely to churn than the people who taken yearly contract.
We can see that our customers prefer taking montly contracts 3411 more than 50% are in those monthly contracts followed by Two year and 1 year.
while considering gender there is much differnce between the male and female having churn rates of 45% and 47%. Females is slightly higher than the male one.

Unlimited Plans

Databel Has hypothesis that:

people who are not on unlimited plan are most likely to churn.

To investigate how data plans affects the churn rate.

We will create 3 groups based on data consumption:
- Less than 5GB
- Between 5 and 10GB
- 10 or more GB

Grouped Consumption = if('Databel - Data'[Avg Monthly GB Download]<5,"Less than 5 GB",if('Databel - Data'[Avg Monthly GB Download]<10,"Between 5 and 10 GB","10 or more GB"))

The hypothesis is incorrect based on the above table that the people who have taken unlimited plan has more churn rate than who didn't taken it. it also tells that we have 4494 customers in the unlimited data plan and 2193 customers are with no data plan combining both we have 6687 customers.
We will also see whether data consumption has any effect on it.

From this above clustered bar chart we can see that group 1 i.e. who use less than 5GB has 34% greater churn rate than people who dont use it has less churn rate 12%.
Group 2 and Group 3 are same wrt to the usage of unlimited data plan having churn rates of 29-33%.

International Calls:

To analyze international activity of customers and its relationship to churn rate.
we create a matrix between this 2 column Intrplan and Intractive.

From the above matrix we can see when International plan is there and it doesn't have International calls active means there is more churn rate for those customers having high churn rate of 71.19%.
And viceversa International plan is not there and it have International calls active means there is more churn rate churn rate of 40.34%.
If they dont have both means there is churn rate of 20%. lowest churn is possible when both International plan and International calls active is yes having churn rate of 7.59%.

If we consider the california state it has "International plan is yes and International calls not active means" - 100% churn rate in this category which we need to focus.

Contract Type:

Databel want to improve it's customer support since there are reported issues there lets analyze payment method, contract type and how many months a person is a customer
lets see churn rate decreases over time or not. yes it decreases over time.
Most of the customers who use payments is by
- Debit card - 55.36%.,
- Credit Card - 39.09%,
- Paper check - 5%

we can see at the starting years of customer journey we have more churn rate with month to month 64% churn rate and it occured after 49 months of churn rate 63%.
1 year contract has churn rate 33% after 19 months and after 74 months.

Sharing Your Analysis:

Created a Dashboard by collating all the information from the best insights possible into databel Key concerns.
How this different churn categories, types of contracts, Reasons, States can effect the churn.
We can see that california has high churn in month-to-month Contract churn rate of 79%.
303 customers had churned because competitor made better offer and 1579 people churned are from month to month contract type.
203 people have churned because of the attitude of our support person.

Payment Method and Contract Category With Customer Service Calls:

For this we create a measure Avg Customer Service Calls Avg Customer Service Calls = (sum('Databel - Data'[Customer Service Calls])/COUNT('Databel - Data'[Customer Service Calls]))

we have 6123 customer service calls from that 3112 calls we get from payment method of direct debit and having monthly contract which makes half of our calls. having average customer service calls of 1.47 and those people having churn rate of 53.90% and they have been customer of us i.e. account length of 18 months.
After the Direct Debit we have paper check which has the avg customer service calls of 1.36 and has a churn rate of 57.33% and the account length of 8 months.
For the people who are yearly contract also facing some issue with the direct debit payment having around 837 customer service calls which is greater when comapred to credit and paper check.
The churn rate is high for those customers. now it is clear the people who uses customer service often are the people who churn more than average. In this case customers who are using monthly contract are using direct debit as payment method.

International and Data Plan:

It is good to observe extra charges based on the data consumptions.
For the people who use 10 or more GB having Avg Extra Data Charges of 31.19 and Average Extra International Charges of 46.76.
The people who use less than 5GB has churn rate of 34.71% and having Avg international charge of 34.
The people who are using data between 5GB and 10GB having same extra data charges of 31-32 but there is a significant difference with the international plan.
We can say the people have to pay extra if they are not on unlimited data plan and they are charged more for Extra Intenrational Charges.

More Insights:

Even though we have higher churn rate for california we have the less no of customer calls. 44 customer calls.
GA Has the 109 service call which is highest than all other states.

Recommendations:

Competitive Pricing and Offers:

To address the churn reasons related to competitors making better offers, consider revising your pricing and promotional strategies to stay competitive. Explore loyalty programs or special offers to retain existing customers.

Device Quality and Support:

Improve the quality of devices and provide better support to address the reasons for churn related to device quality and support issues.

Geographic Focus:

Pay special attention to regions with high churn rates, such as California. Investigate the specific issues in those areas and tailor your services and offers to meet local needs.

Demographics-Based Marketing:

Customize marketing and services based on different age groups. For instance, create age-specific promotions or services for seniors, those under 30, and the 30-65 age group.

Group Contracts:

Promote group contracts to encourage customer retention. Consider offering special discounts or incentives for customers who sign up for group contracts.

Contract Type Analysis:

Analyze why monthly contracts have a higher churn rate compared to yearly contracts. Consider offering more attractive monthly plans or incentives for longer-term commitments.

Customer Service:

Improve customer service quality, particularly for customers using monthly contracts and direct debit payment methods, as they tend to have higher churn rates.

Competitor Analysis:

Continuously monitor the strategies and offers of competitors to stay competitive and adapt as necessary

https://app.powerbi.com/view?r=eyJrIjoiZjQ4ZGVmNGQtZWRlNC00ZDE3LWI0ZTMtNmFkY2Q1NTkwNzU1IiwidCI6IjQxNDhkYTRmLTc3NmQtNDhlOC04YmRjLTlhYWJjNDc5NDYzNiIsImMiOjEwfQ%3D%3D

tiru18324/Analyzing-Customer-Churn-In-Power-BI