hey everyone eric here in this video
i'm going to teach you the real way we
look at customer retention and customer
lifetime value in the startup ecosystem
which is using cohort analysis this
analysis is one of the first things a
venture capitalist would ask for when
thinking about investing in a business
okay let's get started so i'm going to
give you a little background on customer
cohorts first to understand what they
are
and the first thing i want to say is
that the only way to understand
customer retention is using cohorts do
not
waste your time doing some other type of
analysis
because this is how we actually do it so
the great thing about cohorts is that
they show customer attention for both
subscription
and non-subscription businesses and
non-subscription businesses
a lot of people really struggle when
trying to analyze the retention of their
customers so
this works for you so how do cohort
analysis tables work
so a cohort analysis table organizes the
data
by the initial purchase month of
customers
and then looks at the stream of
subsequent purchases
through time based on that initial
purchase month
so let me show you here so this is a
customer cohort table
and so the cohort is january this cohort
is march this cohort is june
and so how it works is that let's go to
april
the cohort is april because the initial
purchase month was april so all the
customers that purchased for the very
first time in april
we look at in this row so 21 people
purchased and we call this month zero
because this is really day
zero of our relationship with them it's
the first time they purchased
so one month later those 21 people
purchased again then
two months later those initial 21
purchase people
20 of them purchased again so one person
decided not to purchase
three months later 18 of the initial 21
purchased again
four months later so but we're always
referencing back
the initial 21 people in the april
cohort
so it's looking at the stream of
subsequent purchases through time based
on the
initial purchase month also
i want to just help you understand
conceptually something about how this
table works
how many purchases did we have total in
april
21 no we have the people who purchased
for the first time in april
but then we have the march people they
purchased
in the second month of their
relationship with our customer of our
company
is april march april the february people
the third month is april february march
april january fourth month so
the real total number of customers we
had that purchased from us in april was
85 you can see right here so to
understand one month in time you
actually have to go diagonally through
the table but we're more focused on the
cohort
which is the initial purchase group and
how much did they purchase afterwards
so with this organization of the data we
can see retention
and customer lifetime value and we're
going to analyze
all of that right now and so our setup
is that we're a sas
business with a 50 dollar monthly
subscription
and people are billed month to month so
each month
any number of people can cancel that no
longer want to continue
uh buying our product so now that i
explained to you the sort of initial
customer cohort table
and by the way this type of table it's
possible to do it in excel
typically with startups you have a data
scientist who can write sql or
pull the information out of your
database and they can organize it in
these tables
so this is pretty much industry standard
and your data needs to get organized
like this some
some way sometimes there's like plugins
and applications google analytics even
has some customer
um cohorts if you look around them a
bunch there's there's an area of google
analytics that has it so
you can figure out how to get your data
into this format
so let's look at the actual customer
retention so
we're going to write a formula 30 we're
going to take that initial
cohort and we're going to divide it by
the same month but we're going to lock
the column so dollar sign in front of
the d i tapped f4
to do that so what is this going to do
let's just multiply this over so first
off that formula allows us to just
continue to
look and reference the initial purchase
month and
take the current month and divide it by
the initial cohort because we're talking
about the january cohort
so what this basically shows us is that
um a hundred percent of customers made
it
to month month 2
with us and then only 80 percent
sorry 86 percent of the initial 35
customers
got to to month 2 with the business and
so over time you see that the
the customers some percentage of them
starts to drop off
but 12 months into their relationship
with the cust
with the company you still have 26
percent of your customers
purchasing still so that gives you a
sense of sort of what
is the retention of that january cohort
but the reason why we break it down each
month individually
is because the quality of the customers
that you acquire
is probably going to vary over time so
let me just clean this
up so all of these areas with zeros
it's zeros because this is some time
period in the future
so there's no information so if you
think about this
um let me just clean this up and then
i'll explain to you
let's say december is the is the most
current month we have data for
so what about january well january
hasn't happened yet
so we can't calculate that so december
for the december cohort is here but
december for the november cohort is here
and so all these different cells
represent december
for all these different cohorts but we
don't have january yet
so these tables always are going to look
diagonal
if you have the data that is from the
most recent month
so this data shows us how each
individual cohort
has sort of different um retention
metrics
so in general it seems like they're
pretty similar but
like this july cohort we still had 91
percent of people purchasing
whereas this january court we had 74
percent of people purchasing
and it could be that um you know maybe
in july
we did some sort of event or we did
different marketing and we acquired a
different
type of profile or a different type of
person that's
stickier so you want to be able to see
that to try to understand your data
so this is a customer this is a customer
retention table this is what
a lot of the vcs are after and people
running businesses are monitoring
closely
but what about financial data and first
off before i move on
i just want to say that if you're
finding this content valuable please
like this video and subscribe to my
channel right now so that you don't miss
any of my new videos
so this is a table called
i'm calling it net revenue by cohort and
it's the exact same information
as this 35 customers but this just
represents
the value of the purchases that they
made so these 35 customers purchased
generated 17.50 in revenue in month zero
in month one 1750 month two
et cetera et cetera et cetera so you see
that you have your initial cohort
and then you have your stream of
subsequent purchases over time but now
we're actually looking at the revenue
not just the individual people
because the revenue is more relevant to
our business
than individual people because people
don't always purchase
the the same amount of something a lot
of times they purchase less a lot of
times they purchase more
so let's do the exact same and we can
actually just copy this formula
because it's just going to reference
this table and it'll give us the same
recalculation of this data
so you can see here that it's doing the
same thing
and so this is called net dollar
retention or
net revenue retention and this is a
extremely extremely important metric
a lot of times they call net dollar
retention ndr and you'll see this
referenced a lot in
in startups so first thing i want to
highlight is you'll see
that the ndr here actually goes over 100
so how could it be that march where you
had
22 people them in their in their first
and second month
of their relationship with the customer
with the company you have the same
amount of people purchasing
but the revenue is more so the revenue
is actually
more than the initial month well it
could be that we offered those
you know 21 or 22 people some sort of
deal so we said hey you know if you buy
this additional product we'll give you a
discount whatever
and we actually expanded the amount of
money that they're spending with the
company
and that's called um yeah that's called
uh expansion revenue and
most sas companies are trying to get
expansion revenue in the best sas
companies
some sometimes see um their sort of
net dollar retention exceeding 150
percent because they're able to grow
their relationship with the customers
because the customers are having such a
good experience
they want to buy more of the product so
if you see this
it's not an error it's actually a really
positive thing
but other than this this is a a
sort of a net revenue retention table
and it shows you
something similar to the customer
retention table
but now what i really want to try to do
is figure out
what is the cumulative lifetime revenue
for each cohort for each customer and
then what is the
customer lifetime value so
let's walk through this so cumulative
lifetime revenue
is pretty easy if you have the revenue
like this so what we're going to do is
we're going to take that first month
and then we're going to take the initial
month and we're going to add the second
month
so those initial 35 customers
if you look at their first two months of
purchasing cumulatively
they've generated three thousand five
hundred dollars
and by month eleven fourteen thousand
even though in their first month they
only purchased seventeen
fifty they've purchased uh fourteen
thousand by uh month 11.
so again let's just copy this down
and then delete the months which don't
make sense
which are months in the future which
don't exist yet
and then we can just delete some of this
i bet someone's going to tell me to
comment some faster way to do this
please do
okay so what can we see
now we can see that cumulatively
these original 36 people actually
spent fourteen thousand dollars with us
and we can see this by each
cohort this is our cumulative lifetime
revenue by cohort and when you can start
to understand this
you can understand the stickiness of a
customer and
you can understand the dynamics of your
occurring revenue so that when you
acquire customers today you know how
much revenue you can expect from them a
year from now
and that gives you a lot of visibility
into your profitability
so this is important but
now let's look at this on a per customer
basis that is a little more helpful
so we're going to take our cumulative
lifetime revenue and divide it by
your january cohort which is 35
customers
and lock the column so lock
um dollar sign in front of the d i did
that by tapping f4
so what does this tell us
okay so again clean this up
so we can delete this
delete the stuff here delete some of
this extra stuff
okay so this tells us that of those
original 30
35 customers
this shows us the total
cumulative revenue divided by
those 35 customers so this gives us an
average it says on average for each
customer that we acquired we made
406 dollars now obviously some of those
35 customers
dropped off and some of them stuck on so
some portion of those customers spent a
lot more than 406 and some portion of
those customers
spent a lot less but the average is 406
and that is really really interesting
and really important for us to know
because then we know
when we acquire a new customer today in
let's say january
we know that we might only make fifty
dollars from them in the first month
but within a year on average we're going
to make four hundred dollars from each
customer
but revenue is is important but what we
really need is the
profit how much profit are we going to
make on the on the relationship with
each customer
and the total lifetime gross profit that
you make from one customer
is your customer lifetime value
and so that's what we're going to
calculate right here and in this case
all we have to do is take your
customer lifetime revenue and multiply
it by your gross margin and your gross
margin
is how much profit you make at the
product level
so excludes your marketing it excludes
sort of your overhead cost but it's just
your revenue minus the direct cost of
your product
so let's lock the gross margin and i put
an assumption here saying our gross
margin is 65
so we can see that of those 50 33
are gross margin so it means 33
come back to the company as profit
so same as always let's just
copy this formula and then i will
explain to you
why this is one of the most important
probably the most important metric for
any business
which is the customer lifetime value
and i'll explain sort of through an
example exactly what we do
with this metric so
let's say right now our customer
acquisition cost is 115
and by the way your customer acquisition
cost is the total
advertising spend that you spend
to get one new customer to buy for the
very first time
so you're spending a hundred and fifteen
dollars to get
one customer to buy for their first
purchase
but after you spend that 115 dollars
that customer comes
on average and spends fifty dollars with
the cut with the company
and only thirty three of those fifty
dollars are profit
so we spent a hundred and fifteen
dollars and we made thirty three dollars
so should we be spending 115 dollars
on marketing can we afford that well if
you just look at
their first purchase it's like no way we
only made 33 dollars
but if you can see that over time okay
but by their second purchase
we've actually made 65 on average off a
customer
by their third 95 by their fourth 123
and 123
in profit is more than the marketing
cost
so at this point we actually become
profitable on
our relationship with each customer
and so in this month we're actually
making a profit
more profit more profit more profit so
by their 12th month
we only spent 115 dollars to get them as
a customer but we've made 264 dollars of
profit
so we've um made back our money more
than 2x
uh in the first year of our relationship
with the customer so 115
cac is no problem and this is really
what
businesses are trying to understand how
much what's the maximum we can spend on
marketing
and still be profitable eventually on
our relationship with the customer
which is your customer lifetime value so
the cac
ltv ratio is is absolutely critical
and and you're obviously trying to make
a multiple of your cac in the customer
lifetime value
so i hope you now have a really good
understanding of how to calculate
customer retention
net revenue retention and customer
lifetime value
using cohort analysis tables by the way
in the description below you can
download this excel file if you want to
use it for yourself
i've also linked down below to some of
my other financial modeling videos check
those out and if you found this content
valuable please
subscribe and click the little bell icon
like this video and leave me a comment
below
also check out my online programs if you
want to do more finance training with me
that's all for today thanks for watching
and i'll see you in the next video