Churn Rate in Excel

Project

📖 Background

Are you ready to apply your Excel skills? For subscription-based businesses, reducing customer churn is a top priority. In this Excel competition, you’ll investigate a dataset from telecom company Databel and analyze their churn rates.

Analyzing churn doesn’t just mean knowing the churn rate: it’s also about figuring out why customers are churning at the rate they are and how to reduce churn. Answer these questions by creating calculated columns, building PivotTables, and creating an eye-catching dashboard.

Use this Datalab workbook to download the data and import it in Excel on your machine. When you’re finished, share your work by attaching your .xlsx file to this workbook. To do so, click File > Show workbook files, and then upload the file, or by dragging and dropping the file on the editor.

💾 The data

The data consists of four parts:

• Customer status: the status and reason why customers churned
• Demographics: demographic data on each customer
• Contract information: information on the type of contract
• Subscription types & charges: numerical data on how the customer uses his subscription

More information about the data can be found here.

➡️ The data can be downloaded from the Files section: click File > Show workbook files.

💪 Challenge

Using Excel, analyze the data and create visuals to answer the following questions:

1. What is the average churn rate?

2. What are the main reasons customers churn?

3. Are there differences in churn rate between customers that consume less than 3 GB of data and more than that? Do you see differences between customers with an unlimited subscription?

   Don’t feel limited by the questions above, you’re encouraged to use your skills and creativity to make your worksheet your own. Create a screenshot of your (main) Excel worksheet, and paste it into the designated field hereunder. Summarize your findings in an executive summary.

Solution

📷 Dashboard

Key Findings:

Average Churn Rate: 27% Primary Churn Reasons: Competitor offers and devices (45%) Support attitude High-Risk Groups: Senior citizens: ~40% churn California residents: ~63% churn Monthly contract holders: ~46% churn Unlimited plan users consuming <3GB data: ~39% churn Customers not in groups: ~33% churn

Recommendations to Reduce Churn:

1. Competitive Analysis: Investigate competitor offers and devices to develop more attractive packages.
2. Customer Support Training: Enhance support staff training to improve customer interactions.
3. Targeted Retention Strategies:
   • Develop specific retention plans for senior citizens.
   • Investigate and address the high churn rate in California.
4. Contract Incentives: Offer incentives for customers to switch from monthly to yearly contracts or to join a group contract which offers advantages and is generally cheaper.
5. Data Usage Optimization: Create tailored plans for unlimited users consuming less than 3GB to better meet their needs.

By addressing these areas, Databel can strategically reduce churn and improve customer retention, therefore avoiding a total loss of up to $1,373,770.