Analyzing customer churn in Excel

Caso di studio: analisi del churn dei clienti in Excel

Jess Ahmet

Content Developer, DataCamp

What is a case study?

  • Apply your skills
  • No new concepts
  • Solve a real-world problem

$$

Prerequisite courses:

  • Introduction to Excel
  • Data Preparation in Excel
  • Data Visualization in Excel
  • Data Analysis in Excel

Working lady

Caso di studio: analisi del churn dei clienti in Excel

Data analysis flow in Excel

Data analysis flow

Caso di studio: analisi del churn dei clienti in Excel

Data analysis flow in Excel

Data analysis flow

  • Check for duplicate or missing values
  • Do a sense check with other internal data sources
Caso di studio: analisi del churn dei clienti in Excel

Data analysis flow in Excel

Data analysis flow

  • Initial exploratory analysis to summarize data
  • Ask yourself the right questions
  • Build your first visualizations
Caso di studio: analisi del churn dei clienti in Excel

Data analysis flow in Excel

Data analysis flow

  • Choose the right visualization to convey a message
  • Perform more advanced analysis
Caso di studio: analisi del churn dei clienti in Excel

Data analysis flow in Excel

Data analysis flow

  • Combine visualizations in a dashboard format
Caso di studio: analisi del churn dei clienti in Excel

Data analysis flow in Excel

ata analysis flow

  • Communicate your insights to stakeholders
Caso di studio: analisi del churn dei clienti in Excel

The problem

Solving customer churn

  • A fictitious dataset about churn from a Telecom provider (Databel)
  • Your task: discover why customers are churning

$$ Customer attempting to leave

Caso di studio: analisi del churn dei clienti in Excel

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

  • Leaky bucket problem
  • Keeping customers is easier than getting new customers
  • Reducing churn is a priority for many companies

Leaky bucket problem

1 https://www.investopedia.com/terms/c/churnrate.asp
Caso di studio: analisi del churn dei clienti in Excel

Calculating churn

There are multiple ways to calculate churn:

  • Varies by industry and revenue model
  • An e-commerce platform could e.g. define a churner as someone who hasn't made a purchase in the last 12 months

$$

Simplified formula

Churn rate = customers' lost / total number of customers

Churn rate = 10 / 100

10 / 100 = 10%
Caso di studio: analisi del churn dei clienti in Excel

The data

Key characteristics

  • Databel, a fictitious Telecom provider
  • One big table containing 29 columns
  • One row per customer
  • Snapshot of the database at a specific moment in time

Antenna being used for communication

Caso di studio: analisi del churn dei clienti in Excel

The data

Dimensions

Column name Description
Customer_id The unique ID that identifies a customer
Churn Label Contains 'Yes' or 'No' to indicate if a customer churned.
Demographic fields Age, Gender, State, ...
Premium plans Unlimited Data, International Plan, ...
... ...
Caso di studio: analisi del churn dei clienti in Excel

The data

Measures

Column name Description
Total charges Sum of all monthly charges
Monthly charge The average of all monthly charges billed to the customer
Extra data charges Extra charges for data downloads above the specified customer plan
Extra international charges Extra charges for international calls for customers not on an international plan
Customer service calls Number of calls made to customer service
... ...

Metadata Sheet - Customer Churn

Caso di studio: analisi del churn dei clienti in Excel

Let's start analyzing!

Caso di studio: analisi del churn dei clienti in Excel

Preparing Video For Download...