Performing Customer Churn Rate Analysis in Excel
Summary
TLDRThis 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
π 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.
π’ 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.
π 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.
π 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.
Mindmap
Keywords
π‘Customer Turn Analysis
π‘Excel
π‘Churn Rate
π‘Data Set
π‘Cohort
π‘Pivot Table
π‘Retention
π‘CSV Format
π‘Monthly Charges
π‘Contract Type
π‘Paperless Billing
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
Browse More Related Video
"My business has stopped growing..what should I do?"
7 Ways to Get People to Buy More Times
3 Best Ai Services To Provide If Your a Beginner (starting with $0)
Build a Slicer Panel in Power BI Like a PRO
Machine Learning Projects You NEVER Knew Existed
Pivot Tables in Excel | Excel Tutorials for Beginners
5.0 / 5 (0 votes)