Performing Customer Churn Rate Analysis in Excel

Summary

This tutorial video walks viewers through the process of performing a customer churn analysis using Excel. It emphasizes the importance of understanding churn rates for business success and offers a step-by-step guide on how to set up an analysis using a free dataset from Kaggle. The video demonstrates how to calculate tenure in years, create churn counters, and use pivot tables to analyze churn rates across different customer parameters such as gender, contract type, and paperless billing preferences. The presenter also provides insights on how to reduce churn and increase customer retention.

Takeaways

• 📊 Customer churn rate is a critical metric for businesses to track as it indicates the percentage of existing customers lost over a period.
• 💰 Retaining existing customers is more cost-effective than acquiring new ones, making churn analysis essential for business growth.
• 🔍 The tutorial uses a dataset from Kaggle to demonstrate how to perform churn analysis in Excel, emphasizing the importance of data granularity.
• 👥 The analysis includes examining churn rates across different customer segments such as gender, contract type, and paperless billing preferences.
• 📈 Excel's pivot table feature is highlighted as a powerful tool for calculating and visualizing churn rates and other metrics.
• 📋 The presenter suggests creating additional columns for tenure in years and churn counters to simplify the analysis process.
• 📊 A calculated field for churn rate is added to the pivot table to dynamically reflect changes in customer churn across different segments.
• 📊 The tutorial demonstrates how to create a combo chart in Excel to visually compare churn rates and average monthly charges.
• 🔑 The analysis reveals that month-to-month contract customers have the highest churn rate, suggesting the need for strategies to convert them to longer-term contracts.
• 📊 The tutorial concludes by emphasizing the importance of playing with different data parameters to gain insights and inform business decisions.

Q & A

What is the importance of customer churn analysis in business?

Customer churn analysis is crucial as it shows the percentage of existing customers lost over a given period. Understanding and reducing churn is vital because retaining an existing customer is cheaper than acquiring a new one.

What is the primary source of data used for the churn analysis in the video?

The primary source of data used for the churn analysis in the video is a free dataset from Kaggle, which is in CSV format.

What are the key variables included in the dataset used for the churn analysis?

The key variables in the dataset include customer ID, gender, senior citizen status, partner status, dependents, tenure, months, different services, contract type, paperless billing, payment method, monthly charges, total charges, and churn status.

How does the video suggest splitting the data for analysis?

The video suggests splitting the data into cohorts to see how many customers churn and if it correlates with different parameters such as gender, contract type, and paperless billing.

What is the purpose of calculating tenure in years during the churn analysis?

Calculating tenure in years simplifies the data, making it easier to analyze trends and patterns over time rather than looking at tenure in months.

Why is a churn counter column added during the analysis?

A churn counter column is added to simplify the calculation of churn rates by converting churn status (yes/no) into a numerical format (1/0), making it easier to count and calculate.

What is the significance of the total counter column added to the dataset?

The total counter column ensures that each customer is unique and that there are no duplicates in the data set, which is important for accurate churn rate calculations.

How does the video demonstrate the calculation of churn rate using a pivot table?

The video demonstrates creating a pivot table with a calculated field for churn rate, which is the churn counter divided by the total counter, providing a dynamic way to calculate churn rates for different cohorts.

What insights can be gained from analyzing churn rates based on contract types?

Analyzing churn rates based on contract types can reveal that month-to-month customers have higher churn rates compared to those on one-year or two-year contracts, suggesting that longer-term contracts may be more effective in retaining customers.

How does the video suggest using the churn analysis results to improve business strategies?

The video suggests using churn analysis results to identify areas for improvement, such as incentivizing customers to switch to longer-term contracts, and targeting marketing campaigns to reduce churn.

What additional parameters can be analyzed to gain further insights into customer churn?

Additional parameters that can be analyzed include gender, partner status, paperless billing preference, and tenure, which can provide insights into customer behavior and help refine marketing strategies.

Outlines

00:00

📊 Introduction to Customer Churn Analysis in Excel

The script begins with an introduction to a tutorial on customer churn analysis using Excel. The speaker emphasizes the importance of tracking customer churn rates for businesses that serve individual customers, as it indicates the percentage of existing customers lost over a period. The tutorial aims to show how to set up a churn analysis in Excel using a free dataset from Kaggle. The dataset includes various customer attributes such as ID, gender, contract type, payment method, and churn status. The analysis will focus on understanding the relationship between different parameters and customer churn.

05:01

🔢 Setting Up the Churn Analysis

The speaker proceeds to guide through setting up the churn analysis in Excel. They explain how to calculate tenure in years from months, create a churn counter to facilitate the calculation of churn rates, and ensure each customer is counted uniquely using the COUNTIF function. A pivot table is then created to calculate the churn rate as a ratio of churned customers to the total number of customers. The analysis reveals different churn rates for various contract types, with month-to-month contracts having the highest churn rate.

10:01

📈 Analyzing Churn Rates and Monthly Charges

The analysis continues with the creation of a new sheet for detailed analysis. The speaker copies contract types and churn rates into the new sheet and adds a pivot table to visualize churn rates and average monthly charges. They discuss the potential impact of increasing monthly charges for month-to-month customers to incentivize longer contract commitments. The speaker also suggests that analyzing these metrics can provide insights into customer behavior and help businesses make data-driven decisions.

15:04

📊 Further Analysis and Conclusion

The final part of the script involves further analysis of the dataset, including looking at churn rates by gender, partnership status, paperless billing preference, and tenure. The speaker notes that while churn rates are similar for male and female customers, partners have lower churn rates despite higher average charges. They also observe that customers without paperless billing are less likely to churn, suggesting a preference for traditional billing methods among older or less tech-savvy customers. The analysis concludes with the speaker summarizing the churn analysis process and encouraging viewers to explore different parameters in their datasets to gain insights.

Keywords

💡Customer Turn Analysis

Customer Turn Analysis refers to the process of examining the rate at which customers end their relationship with a business. In the context of the video, it is crucial for businesses to understand this metric to improve customer retention strategies. The script discusses how to perform this analysis in Excel, highlighting its importance for business success.

💡Excel

Excel is a widely used spreadsheet program by Microsoft that allows for data organization, analysis, and visualization. The video script uses Excel as a tool to demonstrate how to conduct a customer return rate analysis, emphasizing its utility for business data processing and decision-making.

💡Churn Rate

Churn Rate is a metric that measures the percentage of customers who discontinue their relationship with a business over a given period. The video script explains how to calculate churn rate using Excel, which is essential for identifying customer retention issues and developing strategies to reduce customer loss.

💡Data Set

A Data Set in the video refers to a collection of data points used for analysis. The script mentions using a free dataset from Kaggle for the analysis, which includes various customer attributes such as gender, contract type, and churn status. This dataset is crucial for conducting the customer turn analysis.

💡Cohort

In the context of the video, a Cohort refers to a group of customers who share a common characteristic and are analyzed together. The script discusses splitting data into cohorts based on different parameters like contract type to understand churn behavior among different customer segments.

💡Pivot Table

A Pivot Table is an interactive table in Excel that allows summarizing and analyzing data. The video script demonstrates creating a pivot table to calculate the churn rate and analyze it across different customer groups, showcasing its role in simplifying complex data analysis.

💡Retention

Retention in the video refers to the practice of keeping existing customers. It is mentioned that retaining customers is cheaper than acquiring new ones, emphasizing the economic benefits of focusing on customer retention strategies.

💡CSV Format

CSV (Comma-Separated Values) Format is a type of computer data file used to store tabular data. The script instructs viewers to download a dataset in CSV format for analysis in Excel, indicating its common use for data exchange and analysis.

💡Monthly Charges

Monthly Charges refer to the recurring fees customers pay for a service. The video script analyzes the average monthly charges and their correlation with churn rate, suggesting that pricing strategies might influence customer retention.

💡Contract Type

Contract Type in the video refers to the duration of the agreement between the customer and the business, such as month-to-month, one-year, or two-year contracts. The analysis explores how different contract types impact churn rates, indicating that contract length might be a factor in customer retention.

💡Paperless Billing

Paperless Billing is a billing method that does not involve physical paper. The script analyzes the churn rates of customers who use paperless billing versus those who do not, suggesting that preference for digital services might be linked to customer loyalty.

Highlights

Introduction to customer churn analysis in Excel

Importance of tracking customer churn for business success

Explanation of how customer churn represents lost customers over a period

Cost-effectiveness of retaining existing customers over acquiring new ones

Using a free dataset from Kaggle for the analysis

Description of the data set's variables like customer ID, gender, contract type, and churn status

Method to split customers into cohorts for analysis

Analyzing churn rates in relation to gender

Examining churn rates based on contract types like month-to-month, one-year, and two-year contracts

Hypothesis on the impact of paperless billing on customer churn

Adding columns for tenure in years and churn counter for easier calculations

Using the ROUNDUP function to calculate tenure in years

Creating a churn counter column to facilitate churn rate calculations

Ensuring unique customer entries with a total counter column

Creating a pivot table to calculate churn rates

Analyzing churn rates across different contract types

Observing average monthly charges and their relation to churn rates

Suggestion to incentivize longer contract terms to reduce churn

Creating a new sheet for detailed analysis and visualization

Using charts to visualize churn rates and average monthly charges

Analyzing churn rates in relation to gender and finding no significant difference

Observing lower churn rates among partnered customers

Hypothesis on the relationship between paperless billing and customer loyalty

Trend of decreasing churn rates as customer tenure increases

Encouragement to explore all parameters in the dataset for insights

Conclusion and summary of the churn analysis process

Transcripts

hey guys welcome to another episode so

today we're taking

a look at a customer turn analysis and

how to do that in

excel

[Music]

if you're working in a business or you

have your own business that serves

primarily individual customers

you're probably aware that customer

return is one of the

most crucial metrics that you should pay

close attention to

if you want to succeed it basically

shows you what percentage of your

already established customers you're

losing um

over a given period and it's really

important to understand it

track it and find ways to reduce it

because as you know it's much

cheaper to retain an existing customer

than to

find and convert a new prospect

so let's just go ahead and take a look

how we can easily set up

a quick customer return rate analysis in

excel that would greatly benefit your

business

for this uh analysis we're going to use

a free

data set from kaggle i'll leave a link

to this in the description

just go ahead download okay this is

our data set keep in mind that this is

in a csv format

let's name it analysis of turn

and make sure to select an excel file

now that we have this saved tag here's

what we have so we have the customer id

the gender

if it's a senior citizen if it's a

partner

yes no if there are some defendants

their tenure and months

different services that they have

the type of contract if it's a

month-to-month one year

and i believe you have two years if they

use paperless billing

the payment method the monthly charges

the total charges

and the churn which shows us if this

customer has

churned or not yet for the purpose of

our analysis we want to

split those into cohort so meaning that

let's say we want to see

how many of our customers churn

and if this has anything to do with

different parameters that we have here

so for instance the easiest thing to do

is to

let's say look at female and male as

gender

and try to figure out if let's say

female

users tend to turn less because they're

more loyal to our brand

or maybe male users are more loyal

because we have a more

like male-centric um marketing campaigns

things like that we can also take a look

at let's say if month-to-month contracts

turn

more than one year or two year contracts

that require

a bigger commitment and i would expect

that actually

that we're going to see the highest

churn rate on month-to-month

contracts we can also look at paperless

billing because

usually people who don't use paperless

billing are

older less technical people that are

less likely to go ahead and

change their um their provider

and things like that for the purpose of

this i'm gonna completely disregard

the different types of services because

i want to show you that

even if you have no particular industry

knowledge

about the business you can still draw

some

at least basic conclusions based

just on on regular uh data that's uh

that's available in pretty much

any company to start our analysis we're

gonna add

three more columns here to the side the

first thing you want to do is because

the 10 year here is in months

and that's just too granular you can see

that we have like 58

71 so i want to calculate the tenure in

years call this column then you're in

ears and uh let's just

do some colors here i always like to do

like

my data is in blue and

my added columns will be in

orange so my tenure in years

use the roundup function so what we're

going to do

is we're going to take the number of

months

divided by 12 to calculate the number of

years

and round it to zero but round it

up so anything between one and 12 months

will be rounded up to one year so it's

within

one year okay this is one

let's ctrl g to copy down let's see

34 months is in their third year 45 is

in their fourth

one one the second year the first one so

it seems to be working

i'm gonna copy that down all the way

that's that's our tenure in years the

next column we're gonna add

is i want to do a churn counter and

um this will pretty much just allow me

let me copy the formatting here

this would pretty much make it easier to

calculate the turn rate because right

now the churn is

uh just yes or no and

it will be harder to count the yeses or

the nose

so just gonna do if

the turn equals to yes

and give me one otherwise give me zero

so

that way when we copy that down you can

see that we get

zero uh we get one next to each customer

that

churned and that way if we just take the

sum

of all those

1869 those are the customers that

turned out of the total 7 000 much

easier to calculate

and i also want to have a counter for

all my customers

and this would serve two purposes

at the same time let's name it total

counter one uh

it would allow me to have the same way

as with the turn counter just an easy

way to to get

the number of customers but it would

also help me ensure that each of those

customers

is unique and that there are no like um

in some data sets you might have like

different plans for the same customer so

they may turn one but start another

and i want to make sure that this is

continuous

uh in time so if one customer turned and

then came back

they won't have two lines if that makes

sense and

in order to do that i'm just going to do

a countdiff function

and i want everything within

column a to be counted

but when wherever it's equals to

a2 which is the current uh number

okay this gives me one and

if my assumptions are correct we should

have

only once here so the sum is

7043 and our last row is 7044

so it's the same number this means that

each customer each of those customer ids

only appears once next step

let's create a private table i'm gonna

select

the whole table and gonna go to insert

private table first thing we want to do

here

is right now we have no calculation in

terms of

our churn rate create a calculated field

so i'm going to go to the pivot table

analyze tab fields items and sets

calculated field

and let's name this churn rate

and i want it to be equal to my

churn counter divided over my

total counter so now no matter what

cohort and no matter what grouping

we use this would always take

the sum of the turn counter

for our respective view and

divide it over the total counter

okay at now we have our turn rate here

okay and you can see that it

automatically populates and the

average turn rate that we have is 26 and

a half percent

that's already a good basis like

our starting basis for our analysis but

let's go ahead and look at uh what can

we start already told you that i'm

pretty sure

that um that the contract

will have the contract type will have

different uh turn rate so let's drop

that

into our rows and yeah you can see that

we have

just convert those into percentages that

we have our month-to-month

users churned at about 43 percent

are one year at 11 and our two-year at

three percent

something else that we can add here is

just add the monthly charges

okay obviously we don't want those as

numbers but we also don't want the sum

we can change the value field settings

to average

and let's just get the average monthly

charges

per dollar so one thing that we can see

is that there's not much of an incentive

to go into a one-year or two-year

contract

you don't get a lot of benefits so maybe

one way to reduce monthly turn

is to make more people switch to one

year or two year

and if we add our total counter

this will show us the number of

customers that we have

in each cohort so you can see that here

we have like

more than twice the other categories

half of our customers that ever signed

on

were month-to-month customers so if

let's say we raise

the average monthly charge for those and

incentivize them to switch to a one year

or two year contract

then this will already have a huge

impact on our revenue

okay what what other things can we look

at but

actually before we do that let's go

ahead

and create a new sheet

name it analysis i'm just gonna

apply my formatting here

zoom it in a bit what we can start

building out here

is we can start copying those

their contract type so that whenever we

do like a new view in the pivot table

that shows us something

uh important we can always go ahead and

place it here so we can

later reference in our analysis gonna go

here

grab those

and i'm gonna paste them as values

so those will be numbers and those

will be percentages

and those will be numbers as well the

monthly charges are pretty much

our mrr our monthly recurring

revenue and this is our contract

type go ahead

make this look a bit better

okay and something else that we can do

here is select those go to

insert and add a table

and this table doesn't show us a lot

right now but if we select the title and

here you can link it here's a pro tip

for you so that

whenever we change this here the name of

the table will

also change and let's go ahead

and change the chart type we want to

combo

we want our turn rate to be our line

and beyond the secondary axis and our

average

monthly charges to be like that

this is actually a bit misleading i

think because

the difference is from just like 61

to 66 but it appears so huge

so let's go ahead and uh maybe

if we switch those it would be

a bit better if we select

the axis here and format it

let's select this go to the

to here to the axis options and um

let's just make the minimum be 0 and the

maximum

be 100

okay now we get a much better

representation

and uh for this just for the sake of

making it

appear a bit better let's do it to 50

what i'd like to do next is grab this

and i just changed the fill color i

don't really like this one

and this here i'm gonna make it in my

orange okay we can add

the data labels here select them

go here place them above

and bolt them with ctrl b

and then maybe select this one

make it white and do the same for this

one

make it white and i'm also gonna add

the numbers here i want those numbers to

be

on the inside of the

end here i want them all to be white

that's both them and i also want

those to be a number and

no decimals so this would show me

our monthly charges here

and our churn rate over here and uh

this is not sum this is just churn rate

okay so this is what we can do

for each of the analysis that we perform

uh

we're not going to do that here but

let's just go ahead and see what

else can we learn from our data set

what what i usually do is have all those

listed here

and here maybe i'll add like a huge text

box and write down some analysis or

comment so this is then ready to be

pdf or just sent out what else can we

look at already mentioned that we might

look at gender so let me just close that

let's go ahead here remove our contract

and

add our gender you can see that

they're pretty much the same number of

female and

male users and the churn rate

is also quite similar

so this um this can either mean that

we have a really well-rounded marketing

campaign

that doesn't um appeal more to

to men or women

[Music]

yeah you can see that our partners have

much lower churn rates of less than 20

compared to 33 for non-partners

something that's peculiar here is that

we charge our partners more

on average which may as well have a

reasonable explanation but

with with the amount of data that we

have there's no way to

to analyze that but you can see that

just by walking in the door in

any business grabbing some data on sales

you can already start to get a pretty

good picture

and and start gaining insight that would

help

the business something else that we can

look at

is let's remove partners i already

mentioned

the paperless billing yeah just

as i expected someone that has not

opted for paperless billing is much less

likely

to churn and my expectation would be

that those are probably um older people

or more traditional companies that

that prefer to do things on paper and

they're not so tech savvy so it's much

less likely for them to let's say go

online

research competitors of our business and

uh and ultimately switch

over here it's also uh

noticeable that the charge the average

charge is much higher for people that

opted for paperless billing

so if those are individuals that are

customers

and i think this is a telecommunication

data set then i would fully expect

that that here we have the younger

people

and uh here we have older people that

opted for

less features and that's why their

monthly plans are cheaper

but um but at the same time they turn

much less

and last let's look at um our

tenure the the thing that we prepared so

it's at the tenure in years and

we can see so we have some trend rate of

zero here

and zero years so pretty much we have 11

customers that just signed

but let's look at the rest and you can

see that

as as is normal and expected as our

customers stay

longer the turn rate decreases

and and this is normal because

the longer they stay the more of them

already churned in previous periods

and also they're more loyal so let's say

someone that's

stuck with us for six years or between

five and six years

it's uh it's really less likely that

they're gonna churn

compared to someone that's been with us

less than one year

remember we round it up so this means

between zero and one

this means from one to two years and so

on

you can go ahead and try that with every

single parameter that that you have in

your data set

and what i'd usually do is just go ahead

play around like that and start copying

all those that make sense or give me

some

insight start copying them here maybe

add a chart

maybe not depends on on what you plan to

do with the data

that's pretty much my whole process when

i'm trying to

to perform a churn analysis and when i

do that it's usually a brief overview of

a client so

i won't go into too much detail i'll end

up

with an excel spreadsheet that would

have like five

six of those and some comments here and

that'll be the extent

of my analysis that's all i had for you

today i really hope you enjoyed this

video

on setting your own turn rate analysis

in excel if you enjoyed the video give

it a thumbs up

also don't forget to subscribe if you're

not already and maybe even punch the

bell icon to receive notifications every

time i

upload a new video till then thanks for

watching and i'll catch you in the next

one

you