Customer Churn Analysis Using Excel.
The analysis follows the 6 steps of Data Analysis Ask, Prepare, Process, Analyze, Share, and Act.
Phase 1: Ask
In this stage, we define the problem we want to address through data analysis.
1.0 Background
Aircel is a Telecom Provider. It operates in various parts of the United States. It provides customers with various data planes, along with telecommunication services. Recently the company has been suffering from a higher rate of churn.
Defining Churn: 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.
1.2 Business Task:
Analyze the Aircel Data to gain insights into how consumers are using the Services and discover the trends for reducing the churn.
1.3 Business Objectives:
- Reduce Customer Churn.
- Improve Customer Retention.
- Enhance Customer Satisfaction.
- Staying Competitive:
1.4 Deliverables:
- A clear summary of the business task.
- A description of all data sources used.
- Documentation of any cleaning or manipulation of data.
- A summary of the analysis.
- Supporting visualizations and key findings.
- High-level content recommendations based on the analysis.
Phase 2: Prepare
In the Prepare phase, we identify the data being used and its limitations.
2.1 Information on Data Source:
- Data Source: Customer Churn CSV File.
- Aircel, a fictitious Telecom provider,
- One big table containing 29 columns.
- Snapshot of the dataset at a specific moment in time.
2.2 Limitation of Data Set:
- As data is fictitious, we are unable to ascertain its integrity or accuracy.
2.3 Tool
We are using Excel for data cleaning, transformation and visualization.
Phase 3: Process
In this process phase, we check for missing or inconsistent data and address any issues to ensure the data is ready for analysis.
- Explore and observe data.
- Check for and treat missing or null values.
- Transform data — format data type.
- Perform preliminary statistical analysis.
Phase 4: Analyze
4.1 Perform calculations Using the Pivot table
Using the pivot table we explore the overall churn Rate is 26.86% which is significantly higher than the average churn rate.
In this stage of data analysis, a primary factor that leads to customer churn becomes apparent.
- Competitor Churn Reasons
- Age Group Churn Analysis
- Churn by Demographics:
Phase 5 Share:
In this step, we are creating visualizations and communicating our findings based on our analysis.
5.1 Data Visualization and Findings
Churn Analysis by Age Group
In this Cluster Column Chart, we are looking at the churn percentage usage in terms of age group.
- We discover that the Churn percentage is higher for seniors between the age of 69–88 years.
Churn Analysis by Competitors
In this pie chart, we provide a more detailed breakdown of churn attributed to competition. In Table 01 we find out competitors are the major cause of churn.
We discover that:
- “Competitors made better offers” is the primary reason for churn and “Competitors had better devices” is the second main reason for churn.
Data Usage Impact on Churn:
In the Above Stacked Bar Chart, we are analyzing customer churn rates in relation to data usage.
- The “Between 5 and 10 GB” category also has a high churn rate of 33.37%. It’s important to understand why customers in this range are leaving.
Phase 6: Act
In the final step, we will be delivering our insights and providing recommendations based on our analysis.
Here, we revisit our business questions and share with you our high-level business recommendations.
What are the trends identified?
- The primary reasons for customer churn
- Competitors making better offers (17.13%),
- having superior devices (16.79%), and
- offering more data (6.22%)
- To tackle these challenges, it’s essential to regularly review and adjust pricing, enhance device options, and provide competitive data plans and bonus data incentives to retain and satisfy customers.
2. Attitude of support person (11.48%)
- Poor customer service experiences can lead to churn. Focus on training and equipping your support personnel to provide exceptional service.
3. Customer Age Group
- The “Senior” age group has the highest churn rate, which may be influenced by reasons such as pricing and device offerings. Tailor the offerings and customer service to address the specific needs and concerns of this age group.
- It’s important to continually collect customer feedback, conduct surveys, and stay aware of market trends to refine your strategies for reducing churn and retaining customers effectively.
4. Data Usage Impact on Churn
- By tailoring offerings, implementing retention strategies, and educating customers, you can work to reduce churn and improve customer satisfaction in various data usage categories.
