Customer Churn Analysis Using Excel.

Sandeep Singh
4 min readOct 29, 2023

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:

  1. Data Source: Customer Churn CSV File.
  2. Aircel, a fictitious Telecom provider,
  3. One big table containing 29 columns.
  4. 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.

Overall Customer Churn

In this stage of data analysis, a primary factor that leads to customer churn becomes apparent.

  • Competitor Churn Reasons
Table-01 Overall All Churn Reason
  • Age Group Churn Analysis
Churn by Age Group
  • Churn by Demographics:
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 by Age Group

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 due to Competitor

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.
Churn by Data Usage

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?

  1. 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.

Final Dashboard

--

--