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