Databel’s Customer Churn Using Excel
Introduction
Databel is a telecom company that provides various subscription-based services, including calls and data services for both local and international customers.
This documentation outlines my step-by-step data analysis process of understanding and reducing customer churn, essential for maintaining and growing Databel’s customer base.
Understanding Customer Churn
Customer churn is the rate at which customers cancel their subscriptions within a certain time frame. It’s crucial for subscription-based businesses like Databel because high churn rates can seriously affect revenue and growth.
By analyzing why customers leave, companies can spot trends and factors that lead to customers leaving and develop strategies to enhance customer retention.
Objective
The main aim is to analyze the customer churn rate for Databel.
By identifying the key factors that contribute to churn and understanding customer behavior, we can develop strategies to enhance customer retention.
Data Collection
The dataset for this analysis was sourced from a DataCamp case study — Analyzing Customer Churn in Excel and the link to the dataset can be found here.
The dataset contains 29 columns and 6,688 rows, with each row representing a unique customer. it includes the following range of characteristics:
- Customer Demographics: Gender, Age, phone number, and location.
- Account Information: Account length, type of subscription, and contract duration
- Usage Data: Frequency and patterns of service usage (local and international calls, data consumption).
- Transaction Data: Payment history, and billing information.
- Customer Interactions: Support tickets, and call logs.
- Churn Information: Churn label, churn category, churn reason, and churned.
- Measures: Total charge, average charge, monthly charge, extra data charge, etc.
Domain Research
Before diving into the data analysis, I love getting a good understanding of industry trends and patterns. That means not only understanding the dataset specifics but also exploring broader industry shifts and common occurrences. By doing this, I’m better equipped to spot significant insights and anomalies in the data, leading to more effective and strategic decision-making.
I started my research by focusing on several key questions:
- What are the main reasons customers leave telecom services?
- How do demographic factors like age and location influence customer retention?
- What usage behaviors, such as service frequency and type, are linked to higher churn rates?
- Do specific customer interactions, like frequent support inquiries or complaints, signal potential churn?
- How do payment habits and billing issues relate to customer turnover?
These questions guided my research, laying a solid foundation for understanding the dataset and the factors impacting customer churn.
Here is an overview of the data:
Data Preprocessing in Excel
Introduction
Data preprocessing is a crucial step in any data analysis project. It involves cleaning and preparing raw data to ensure it is accurate, complete, and suitable for analysis.
For Databel, this step was essential to ensure that the insights derived from the data would be reliable and actionable. The preprocessing phase involved removing duplicates to transform data into a usable format.
Data Cleaning
To start the data-cleaning process, I converted the data range into a table for easier manipulation and analysis. I did this by selecting the range, navigating to the Styles group in the Home tab, and selecting Format as Table. I renamed the data table ‘Customer’s Table’.
This action not only organized the data into a structured format but also made it simpler to apply filters and manage the dataset efficiently.
Removing Duplicates
To ensure the dataset was free of repeated entries, I utilized Excel’s “Remove Duplicates” feature. I navigated to the Data Tools group within the Data tab and selected Remove Duplicates. No Duplicates were found.
This step was crucial for maintaining the dataset’s accuracy and reliability by ensuring that each entry was unique. By removing all repeated entries, I preserved the integrity of the data, which prevented skewed results and ensured reliable insights throughout the analysis process.
I also checked the dataset for spelling errors, blank rows, blank cells, and whitespaces. With the dataset cleaned, it was now time to analyze the dataset.
Exploratory Data Analysis (EDA)
Overview
Exploratory Data Analysis (EDA) is a crucial initial step in the data analysis process, involving the calculation, investigation, and deeper understanding of churn. This process involves summarizing essential data characteristics using visual methods and summary statistics.
EDA plays a pivotal for Databel in identifying complex patterns in churn rates, identifying anomalies, and confirming assumptions through thorough analysis and graphical representations.
Calculating Churn
To start, I added a new column named ‘churned’ to our customer's table using an IF( ) function. This column assigns a value of ‘1’ to customers marked as ‘Yes’ in the churn label, and ‘0’ to customers marked as ‘No’.
Next, I created a PivotTable of the customer's table and placed it in a new worksheet named ‘Customers Pivots’. In the PivotTable, I displayed the total count of customers and the number of churned customers. I then renamed the columns into more user-friendly names: ‘Total Customers’ and ‘Churned Customers’.
To determine our churn rate, I added a new calculation labeled ‘Churn Rate’ - which divides the number of churned customers by the total number of customers.
The calculated churn rate for Databel stands at 26.86%.
Investigating Churn
The logical next step is to investigate why customers churned. I started by creating a blank PivotTable from the customer's table and placing it in the ‘Customers Pivots’ worksheet.
Next, I analyzed the total number of churned customers by their churn reasons. To make the data more interpretable, I renamed the row header to ‘Churn Reasons’ and the column header to ‘Churned Customers’.
I then ordered the churn reasons in descending order, with the most common reason at the top, and displayed the churned customers as a % of grand total.
To visualize my findings, I created a 2D bar chart titled ‘Churn Reasons’ and made further edits to enhance its clarity and visual appeal.
From this analysis, I identified the top three reasons for customer churn, namely:
Competitors made better offer
Competitors had better devices
Attitude of support person
Digging Deeper into Churn Categories
Continuing in the ‘Customers Pivots’ worksheet, I created a blank PivotTable from our customer data and conducted a deeper analysis of churn (as a % of grand total) by category and reason, focusing on identifying the main causes of churn.
This analysis revealed that the ‘Competitor’ category had the highest percentage of churned customers, particularly due to competitive offers. To emphasize this, I filtered the PivotTable to display only the ‘Competitor’ churn category
To visualize my findings, I created a Doughnut chart titled ‘Competitor Churn Analysis’ to present this information clearly. I refined the chart by removing unnecessary elements and enhancing the overall style for better clarity and impact.
The analysis showed that 37.64% of customers left because competitors made better offers.
Advanced Analysis
Overview
After conducting exploratory data analysis, I discovered that there were still many unanswered questions in the data, especially concerning Databel’s competitiveness given the 26.86% churn rate. I decided to conduct a more thorough analysis which included taking a holistic approach by examining demographics, age groups, call plans, and international call behaviors to uncover deeper, more detailed insights.
Analyzing Demographics
To gain deeper insights from the data, I analyzed various demographic factors, particularly age categories. I started by working on the ‘databel — aggregate’ worksheet, where I added a new ‘demographics’ column. This involved using a nested IF() formula to classify customers into groups like ‘under 30’, ‘Senior’, and ‘Other’.
Afterward, I created a fresh ‘churn analysis’ worksheet and set up a PivotTable based on the databel — aggregate data. Within this PivotTable, I introduced a calculated field called ‘sum of churn rate %’ to measure churn by dividing the number of churned customers by the total customer count, formatted as a percentage.
These steps allowed me to explore the data more deeply, especially in understanding churn patterns across different demographic segments.
Interestingly, upon closer examination, the demographic group with the highest churn rate turned out to be ‘Senior’.
To visualize my findings, I created a Doughnut chart titled ‘Churn by Demographics’ to present this information clearly. I refined the chart by removing unnecessary elements, enhancing its overall style for better clarity and impact.
Analyzing Age Group
After discovering that senior citizens churn more frequently, I decided to analyze customer age comprehensively. I created different age groups and visualized the distribution of customers and their corresponding churn rates.
To achieve this, I duplicated the demographics PivotTable, replaced ‘demographics’ with ‘age’, and added a ‘total customer’ field segmented by age groups of ten.
I visualized my findings using a combination chart of a line and clustered-column chart. This chart effectively displayed customer numbers and churn rates across each age bracket, and I formatted it to make the information more visually appealing.
Through this analysis, I found that the age group “79–88,” despite having the smallest number of customers, had the highest churn rate.
Analyzing Unlimited Plan
I have a hypothesis that people who aren’t on an unlimited data plan are more likely to churn, so I decided to analyze how having an unlimited data plan influences churn rates.
I started by creating a PivotTable in the churn analysis worksheet using the aggregate data. This table analyzed the number of customers with an unlimited data plan and their respective churn rates.
The initial results showed that customers with unlimited plans are more likely to churn. To see if this was related to the amount of mobile data they use, I added a new column in the aggregate worksheet called ‘Grouped consumption’. This column classified average monthly data usage into three groups: ‘less than 5GB’, ‘between 5GB & 10GB’, and ‘10 or more GB’. I then refreshed my PivotTable to include this new column and rearranged it to analyze churn rates by unlimited data plans and data usage groups.
To visualize my findings, I used a stacked column chart showing churn rates for unlimited data plans, broken down by average consumption levels. I also formatted the chart to make it more visually appealing.
From this analysis, I found that the churn rate for people on an unlimited plan who consume less than 5GB of data is 34.69%.
Analyzing International Calls
During my analysis, I noticed the international activity information and decided to explore whether there’s a relationship between customers’ international activity and churn. I wanted to understand the behavior of customers who make international calls and whether paying for an international plan affects their loyalty.
To analyze this, I created a PivotTable in the churn analysis worksheet using the aggregate data. I filtered the PivotTable to show only customers on an international plan and then sorted the churn rates from highest to lowest. Finally, I applied a value filter to display the top 25 states with the highest churn rates.
To visually highlight the churn rates, I used conditional formatting with a Red-Yellow-Green color scale on the values within the PivotTable.
This showed that California (CA) has a particularly high percentage of churners among customers on an international plan.
Data Visualization
After completing my analysis, I translated the complex data into a visually pleasing and easily understandable dashboard. This dashboard illustrates the factors influencing customer churn, such as demographic characteristics, service usage patterns, and competitor’s actions.
The Customer Churn Dashboard provides a clear and comprehensive view of the underlying causes of customer churn. This visual approach not only makes the data easier to interpret but also helps in making informed decisions to improve customer retention.
Summary Insights
- Overall Churn Rate: Databel’s overall churn rate stands at 26.86%.
- Top Reasons for Churn: The primary reasons for customer churn include - better offers from competitors, superior devices from competitors, and poor attitudes from support personnel.
- Demographic Insights: Senior citizens have the highest churn rate among different age groups.
- Age Group Analysis: The age group 79–88, despite having the fewest customers, shows the highest churn rate.
- Unlimited Plan Usage: Customers on unlimited data plans tend to have a higher churn rate, especially those who use less than 5GB of data monthly.
- International Plan Analysis: Customers on international plans show higher churn rates, with California having a particularly high percentage of churners.
Recommendations to Reduce Customer Churn
- Competitive Offers: Introduce competitive offers and device upgrades that match or exceed those of competitors. Establish loyalty programs offering discounts, special offers, and exclusive services to reward long-term customers, enhancing satisfaction and retention.
- Customer Support Training: Invest in comprehensive training programs for support staff to improve customer interaction and satisfaction.
- Senior Citizen Retention Programs: Develop targeted retention programs and incentives for senior citizens to reduce their churn rates. Foster a sense of community and belonging through events, clubs, or personalized communications to enhance engagement among senior customers.
- Age-Specific Plans: Develop customized plans and services that cater to the unique needs of various age groups, with a special focus on individuals aged 79–88. Implement personalized marketing strategies to effectively communicate the benefits and value of these tailored plans to each demographic.
- Unlimited Plan Optimization: Re-evaluate the unlimited data plans to offer better value for customers who consume less data by introducing flexible options while analyzing data usage patterns to align with customer needs and preferences.
- International Call Plans: Improve the appeal of international call plans through competitive pricing, better features, or added benefits. Offer loyalty rewards or discounts to long-term users of these plans to increase satisfaction and retention.
- Advanced Customer Engagement: Utilize data analytics to personalize engagement strategies, ensuring communications meet individual customer needs and preferences. Implement proactive support measures to reach out to customers showing early signs of dissatisfaction or potential churn, strengthening retention efforts.
You can check out the analysis on my GitHub profile.
