Databel is a fictional data provider, and this project focuses on analyzing the reasons behind customer churn.
The dataset contains 29 variables (columns) and 6,687 observations (rows).
- Customer ID: Unique identifier for each customer.
- Churn Label: Indicates whether a customer churned ("Yes" or "No").
- Churn Category: Groups multiple churn reasons for analysis.
- Churn Reason: The specific reason why a customer ended their contract.
- Gender: The gender of the customer ("Male", "Female", or "Prefer not to say").
- Age: The age of the customer.
- Under 30: Indicates if the customer is under 30 ("Yes" or "No").
- Senior: Indicates if the customer is above 65 ("Yes" or "No").
- Contract Type: Type of contract ("Month to Month", "One Year", or "Two Year").
- Payment Method: Preferred payment method ("Credit Card", "Direct Debit", or "Paper Check").
- State: The state code where the customer resides.
- Phone Number: The customer's phone number.
- Group: Indicates if the customer is part of a group contract ("Yes" or "No").
- Number of Customers in a Group: The number of customers in the group.
- Account Length (in months): The number of months the customer has been with Databel.
- Local Calls: The amount of local calls (within the US) made by the customer.
- Local Mins: The number of minutes spent on local calls.
- Intl Calls: The amount of international calls made by the customer.
- Intl Mins: The number of minutes spent on international calls.
- Intl Active: Indicates if the customer made international calls ("Yes" or "No").
- Intl Plan: Indicates if the customer has a premium international calling plan ("Yes" or "No").
- Extra International Charges: Additional charges for international calls for customers not on a plan.
- Customer Service Calls: The number of calls made to customer service.
- Avg Monthly GB Download: Average monthly download volume in gigabytes.
- Unlimited Data Plan: Indicates if the customer has an unlimited data plan ("Yes" or "No").
- Extra Data Charges: Additional charges for data downloads for customers without an unlimited plan.
- Device Protection & Online Backup: Indicates if the customer has paid for device protection and backup ("Yes" or "No").
- Monthly Charges: The average monthly charges for the customer.
- Total Charges: The total sum of all monthly charges.
- All variables were checked for correct data types and duplicates. No duplicates were found.
- The main dataset was copied to a new worksheet named "Aggregate" for further analysis.
- A new column named "Churned" was created based on the "Churn Label" column, where "Yes" was converted to 1 and "No" to 0 using the
IFfunction in Excel. - Churn rate was calculated by dividing the sum of churned customers by the total number of customers, resulting in a churn rate of 26.86%.
- A pivot table was created from the "Customer" worksheet to analyze churn reasons by summing the "Churned" values for each reason.
- A competitor analysis was conducted to identify the top 4 reasons customers preferred other companies: better devices, better offers, higher download data, and more data offered.
- Data consumption patterns were examined based on the data plan using a pivot table.
- A new column categorizing customers into "Under 30", "Senior", and "Other" based on their age was created using the
IFfunction. - A pivot table indicated that most churn occurs among "Senior" customers.
- Further analysis grouped customers by age ranges of 10 years, revealing the highest churn rate in the 79-88 years old range.
- Churn rates by state were analyzed based on the international plan, with California showing the highest churn.
I decided to conduct an in-depth analysis at Databel, starting with identifying the commercial age (Tenure) of our customers. Initially, I calculated customer tenure by subtracting the monthly fee from the annual fee to estimate how long they had been using the service. This calculation was performed on both monthly and yearly bases. If a customer had not completed one year, their Tenure was set to 0; otherwise, the total duration was divided by 12 to compute their Tenure in years.
According to the annual Tenure calculations, nearly half of the customers who had not completed one year ended up churning. However, after the second year, churn rates gradually decreased and continued to decline until the seventh year.
When analyzing the distribution, we observed that 34% of customers left before completing their first year. This group is followed by those who completed their 3rd year, 6th year, 2.5 years, and 4th year. The primary reasons for customer churn were competitor offers, negative customer experiences (attitudes), and dissatisfaction.
Most of the churned customers were non-citizens over the age of 30. This suggests that communication strategies and customer experience touchpoints may need to be re-evaluated. Among those who didn’t complete a full year, the majority were again non-citizens and over 30 years old. The data also shows that 73% of those who didn’t complete one year and 62% of those who completed their 6th year churned.
The main reasons non-citizens over 30 left the service were better offers from competitors, dissatisfaction, and negative experiences with staff. Within this group, the leading complaint was that competitors provided better devices. Most of these customers used debit cards for payments and contacted customer service more frequently than the average. This might indicate that they experienced more problems and didn’t receive adequate support, particularly related to billing or technical issues.
This group also had the highest churn rate among those on monthly contracts. Their usage data shows that they often lacked a dedicated support team to solve their issues, and their primary needs were related to internet data packages.
Two-Year Tenure and Churn Reasons
For customers with two years of tenure, the top reason for churn was competitors, followed by negative attitudes and dissatisfaction. Based on these insights, if Databel addresses internal personnel issues, offers competitive internet packages, and provides new devices, it could significantly improve satisfaction among non-citizens and customers over 30.
Among senior customers (citizens), the churn rate was 38%. Interestingly, churned customers in this segment spent approximately 20% more than those who remained. The top churn reason was again competitors, followed by the company's attitude. Most complaints in this group were related to better offers from other providers.
A deeper analysis revealed that the core problems stemmed from the behavior of customer service and sales representatives, as well as the company's pricing policies. The most churned customers were those with monthly contracts. Moreover, customers who were involved in more than one group or plan were less likely to churn.
In this segment, churned customers had higher average local and international call minutes than other demographics. This group also had the highest churn rate overall.
The segment with the lowest churn rate consisted of customers under 30 years old. Within this group, the average age of churned users was around 25. Those who left the service in their second year had the highest average extra data usage, while those who left in their sixth year had the highest spending costs.
Compared to other segments, this group downloaded approximately three times more data. It also ranked second, after the senior segment, in terms of average local call duration.
- A comprehensive dashboard was created in Excel summarizing the findings from the analysis.
This project aimed to provide insights into customer churn patterns at Databel. The results can help guide strategies to reduce churn and improve customer retention.
Based on these insights, the company can reduce the overall churn rate by offering 2-year contract agreements instead of monthly plans. Another potential strategy is to introduce group contracts, targeting families of three or more members, which could also help lower churn rates.
Additionally, the company could organize training programs for its personnel to improve customer interaction and address behavioral issues. Offering newer and higher-quality devices may also contribute to customer retention.
From a demographic perspective:
For customers over the age of 30 and non-citizens, the company can offer modern devices bundled with 2-year contracts and ensure they have access to more competent sales and customer service teams.
For senior customers (citizens), the company could provide affordable domestic and international call packages, tailored to their usage patterns.
For customers under the age of 30, the focus could be on offering cost-effective internet and domestic call packages, which align with their typical usage behavior.