Video 1
So, we have discussed till now about the excel part of customer lifetime value how excel modeling can be done with customer lifetime value. But sometimes these problems are much simpler sometimes these problems are not cumbersome or lots of assumptions that you can generate from the data. That kind of assumptions you do not have in your hand sometimes because not always marketing manager will have an access of data in a minute level. Now, if you do not have the access of a data in the minute level the question comes up is that how I can have few basic information from which I can calculate customer lifetime value of a customer. Now, if I can calculate that, if I have the ability to calculate that ─ individual level or a group level then I can take multiple marketing decisions based on that. For example, I can find out that who are my more profitable customers, who are my less profitable customers, how what are the patterns that these more profitable customers are showing, what kind of purchase ─ products or services that they like, what kind of prices that they like and then I can make my product or service design according to that. And at the same time, I can also find out that who are my not so profitable customers; and if I can find out who are my not so profitable customers, I can also find out that how to tackle them, how to make sure that they do not clog my resources because if they can clog my resources then there is a problem. Otherwise what I can do is I can attract them, I can give coupons, I can give certain kind of incentives.
So, that they can come in such kind of positions they ask for service or they come for product purchasing some kind of time when my system my resources are less I would say less engaged. For an example, if you know that there are various retail companies.Let’s say grocery companyBig Bazaar for example, gives a Wednesday where it is a it is a everyday like Walmart goes for everyday low cost Big bazaar low gives certain kind of discounts on Wednesdays. Now why Wednesday? So, first of all they wanted to pick up a date day where it will be known for that particular day that, in that this particular day Big Bazaar is giving certain kind of discounts that is number 1. Number 2 is for Big Bazaar, they know the people who are coming in the
probably weekdays and in the afternoons of the weekdays are generally those people who don’t work, because in weekdays if you have work you will go for job. So, those who don’t work probably are not so profitable customers for them. Now, if these groups of people come on the weekends, then my shop floors will be very I would say congested. And if my shop floors are congested I my actual profitable customers might not prefer to come to me they might go to somewhere else, because shopping is also an experience.
Now, if that is the situation then the Big Bazaar has to do something so, that they can attract this group of customer, who are not as profitable as the actual weekend buyers. Also they don’t want to lose this particular customer. So, they don’t want to push the price high such that they lose these customers who are not working. But they also want to keep them but at the same time they don’t want them to clog in a certain days or certain dates when other kind of profitable customers’ foot fall is expected. So, they give this kind of discounts in the weekdays. So, everybody will come on the, on Wednesday, then not Thursday not Tuesday, Monday or Friday, Wednesday, which is absolutely the middle of the week. So, you come in the middle of the week and you purchase and you get discounts. So, these kind of things comes from customer lifetime value unless and until you can calculate customer lifetime value at a personal individual level this becomes a problem. So, in this particular video we will talk about the CLV formula, how the CLV formula was created. So, let me come to this. So, CLV formula comes from the lifetime. Now, here I am assuming that the lifetime is basically infinite. So, it’s a as long as possible lifetime and let’s say the revenue that you make is R, the cost of goods sold is a cost of service ─ cost of goods sold or service is C let’s say. Let’s say, along with that the customer also gives you, the company also gives you certain marketing and that marketing cost. So, some kind of advertisement cost, and when we I
calculate that marketing cost, at a per customer basis individual customer’s marketing cost I can calculate, that comes up to be let’s say, let’s say something I will write that m or small m okay. And the margin from each purchase then is basically revenue minus cost or this gives me basically: M is equal to R minus C [M = R - C.] Now, these are some simple values that you can get from the, from my past data very easily like per customer revenue, I if I have 5 customer. Let’s say in my database I have 5 customers; customer 1, customer 2, customer 3, customer 4, customer 5. I then take the average of these 5 customers to say that, this is my average revenue which is capital R. Similarly let’s say each customer has certain kind of cost like some customer in a, in case of cost of goods sold the cost is fixed, but if it is a service for some customer the cost will be a little bit higher. For some customer the cost will be a little bit lower. So, I can take an average of that also and that becomes my C.So, then average margin minus average cost becomes my basically sorry average revenue minus average cost becomes my margin. So, that is M is equal to R minus C. And also let’s say if I have 100 customers in total and my total marketing cost is something that by 100, that divided by 100 will be my marketing cost per customer. Each customer gets that
marketing cost that is his share. So, that is small m and this is the recurring cost every year I do this marketing cost. So, I send certain emails I arrange certain kind of I certain kind of events for the customers. I do some marketing promotion activities. Now, whatever I do it’s a bulk cost that bulk capital cost is actually shared by multiple customers. So, that is something that I have right now. So, then, for each time period; each time period how much is my total net profit per customer? Net profit per customer is capital M by small m [M/m], which is also, can be written as R minus C minus m [R-C-m]. So, we will use this capital M minus small m, the margin that you generate minus the marketing cost the per unit marketing cost that you
generate. So, this is the money that I gain if the customer stays for 1 year. So, let’s calculate this year 0, year 1, year 2, year 3, year 4 and so on ok. So, if I if the customer stays with me in year 0, how much is the money that I generate? This is capital M minus small m [M-m]. I will just delete this, capital M minus small m, fair enough. Now, if he stays in the next year also then what is the amount that I gain?So, let’s say, the retention rate is r. Retention rate is r means that this is the number of people if 1 customer stays, 1 customer stays with me in year 0. Then in year 1 basically r customers will stay, in year 2 r square [r2]customers will stay, in year 3 r cube [r3] customer will stay. So, the probability the net money that I can generate here is basically M minus m into r for year 1[r(M-m)]. Let’s make it a little bit, for year 1 this is like this. For year 2 out of this r another r will stay. So, M minus m r square [r2(M-m)]. In year 3 r cube into M minus m [r3(M-m)], in year 4 r to the power 4 into M minus m [r4(M-m)], and so on. So, that is how the money that I will I that I will gain will be calculated. So, if let’s say 100
customer stays in the first, then r 100 into r that many customers will stay in year 1, 100 into r into r further another multiplication of r will stay in year 2.
And r cube into M minus m will stay in year 3 and r to the power 4 into M minus m will stay in year 4 and. So, on now this is the money that I will gain over time. So, the summation of all of these things will be the money that I gain over time.But what happens is there is something called discount rate an interest rate or a discount rate these two things are generally which is which is also called sometimes the time value of money. How much is the value of money 100 rupees, which you can gain 1 year later what is the
today’s value of that? So, let’s say it is like if you put 100 rupees in the bank and the interest rate is let’s say a 10% then in the 1 year later you will get 110 rupees right. Then that means, what that if you whatever you get after 1 year, 110 rupees the current days value of that money is 100 rupees. Because if I, if you forego 100 rupees now you will get a 110 rupees later. So, then current
day is value is 100, 100 rupees then. So, that 10 rupees that you are getting in percentage is basically the time value of money. So, then what then if you get 110 how I will convert that to 100? You will say that the time value of money will be today’s value, today’s value into 1 plus i, where i is the interest rate let’s say today’s value into 1 plus i is equal to tomorrows value or 1 year later value not tomorrow basically 1 year. Because i is always defined in a so value 1 year later, where i is the yearly interest rate where i is the yearly rate. So, this is I can say today’s value will be, then what will be that today’s value of something that I get later today’s value of something that I get later: value
1 year later divided by 1 plus i, that will be today’s value. Similarly today’s value you can do a little bit of calculation. And you can say that today’s value is equal to value let’s say 5 years later divided by 1 plus i to the power 5 [1 + i5] or in other words if it is t years later, then 1 plus i to the power t [1 + it]. So, that is what I get. So, if I use this particular formula; if I use this particular
formula, then how much is today’s value? Today’s value for this 1 is by 1 plus i to the power 0 because that is something that I am getting in this year only. This is by 1 plus i to the power 1, this is 1 plus i square, this is 1 plus i cube and this is 1 plus i to the power 4 and so on. That is what I am getting here. Just check carefully that the 1st year it is 1 plus i to the power 0, in the 2nd year it is 1 plus i to the power of 1, 3rd year.It is 1 plus i to i square 4th year means corresponding value that of in terms of today’s money is this much. So, if I get r squared M minus m in year 2; if I get r square M minus m
in year 2 corresponding thing’s value in today’s market will be this much the denominator divided by 1 plus i square. So, this is what I have got till now, see. Now what will be the CLV then, what will be the customer lifetime value, then? Customer lifetime value is M minus m plus M minus m to the power r by 1 plus i plus M minus m to the power r square by 1 plus, basically the summation of all of those values plus M minus m r cube by 1 plus i cube and so on. And if that is the case I can take M minus m as common 1 by r plus 1 by i plus r square by 1 plus i squared plus r cube by 1 plus i cube and so on. Now, just think r by 1 plus i, what is this value? Is this smaller than 1? See r is the retention rate, the maximum value retention rate can take is 1 everybody is retained right. So, that numerator can take the maximum value of 1, the minimum value denominator can take is also 1, the minimum value 1 plus i, when the interest rate is 0 the denominator becomes 1. When the interest rest is anything positive, anything positive the denominator becomes higher 1. So, r by 1 plus i that is why this ratio, r divided by 1 plus i, this ratio will
be always smaller than 1.And if it is a infinite series ─ infinite GP series. With the value of the GP the multiplication factor is smaller than 1, then we know the formula. So, if it is 1 plus a plus a square plus a cube plus a to the power 4 plus dot-dot-dot upto infinite times. The formula is basically 1 by 1 minus a, that is the formula for this one if you remember that is from GP series basic
GP series. So, I just write that particular thing as my formula. So, I will just increase. o, correspondingly if you just check the maths the formula that I got is CLV is equal to capital M minus m divided 1 minus r by 1 plus i. So, if we simplify it we get. So, this is the basic formula that I get now that is that is the basic formula that you will get in any book the i can be written as d discount factor i write interest rate you can write discount factor. But the other values probably the margin will be named by the notations can be different the nomenclatures can be different, but ultimately this will be the formula.
Video 2
So, where this applies? Let’s say Netflix I am giving you an example let’s say there is a company called Netflix. Let’s say Netflix, says that our average. So, Netflix has a fixed amount that they charge every month. So, let’s say their yearly revenue is around 20 dollars, no they charge how much 500 rupees per month, if I am not wrong. So that means 10 dollars. So, 120 dollars let’s say Netflix is 120 dollars that is their revenue. And they say that our margin basically is 30% or a little bit higher 60% . So, 60% of 120; that means, around72 dollars is their margin and they do some marketing costs and at this moment of marketing cost is probably higher. So, they say that our marketing expenditure per customer is 5 dollars; this is what they have told us this information. Now, these are very basic information you can get now they are us, they are trying to find out that, what is their customer lifetime value? Now, the customer lifetime value i they are getting at this moment market is not good. So, let’s say i is equal to 5% and the retention rate is around let’s say 60% . So, then their CLV calculation will be CLV will be basically, 72 minus 5 into 1 plus 0.05 divided by 1 plus 0.05 minus 0.6. So, this comes 67 into 1.05 and this is 0.54 whatever is the value that you get. So, that is the calculation of CLV the basic calculation. Now, where it applies? This is not that simple that it will be applied nowhere? So, where it will apply further let’s say. So, the values in general remain same that is a they are thinking about giving a small discount. They are thinking that if I change this 10 dollars per month to 8 dollars per month, two things happens; one is my customer base goes up; that means, the marketing cost comes down, but, per customer comes down, but at the same time I am not making revenue. So, that is something we have to take into account. So, 8 dollars per month; that means, my monthly revenue is 19, the yearly revenue is 96 dollars; revenue is 96 dollars. The margin is now a little bit lower because 30% if I say, see the cost has not changed. So, 72 minus 120 the cost was, basically how much? The cost was 8 ─ 48 dollars was the cost. So, the cost did not change I am giving a price discount, but the cost did not change. So, if I give a price discount of 2 dollar per month the cost does not change. So, the cost still remains 48 dollars per year. So, the cost is 48 dollars per year and that is
why the margin is also 48 dollars. This is what they have generated right now. But what else, their marketing cost has come down because the customer base goes up and they do a little bit of maths. And they find out their marketing cost per customer is now 4 dollars because my customer
base went up, because I have given a discount more customer came in customer is going up. So, whatever was 5 dollars previously now it has become 4 dollars. What happens with the customers? But they have also seen here that not only the customer base went up. The retention rate also went up the more customers stayed back. So, what is the new retention rate now? They find out that around 75% people stays back. Now, see in the blind eye, in the blind eye if I see in the first year they are making loss you see here they are making a gain of as 172 dollars were there margin minus 5.So, 67 dollar was they are making in the 1st year, in the previous condition 67 dollar was the profit, in the 1st year in this condition 44 dollars is the profit in the 1st year. So, ideally in common sense it is getting dropped. So, I should not do this because it will increase my price probably I do not increase my customer base probably, but the net money that I am generating per customer is going down. So, you can suggest me that you will only do this if previously it was how much, previously it was as I told 67 dollars 67 dollars with demand at 67 dollars. If this is higher than 48 dollars into demand at demand at the or d1 or d2, just I will write d1 and d2, d1, d2; obviously, here the margin came down the amount of profit that I am making came down,
but d1 and d 2, d2 went up than d1. Now, common ─ in normal common sense what I will do that if my net income is going up in comparison to the previous income then I will go ahead and do this. That is a myopic view. A myopic view is that if the 1st year’s total income is higher than the 1st year’s total income in the situation 2, I will go ahead and do this. But what we have to also take into account that not always it is only the 1st years decision;
that whatever you take decision the retention rate also gets affected. And if retention rate gets affected the overall CLV for 1st one customer changes how let’s say i is still 5 %. So, here CLV if you remember: it is 48 minus 4 into 1.05 divided by how much? 1 minus 0.06which is 0.04, in this case, 0.25 1 plus, 0.05 minus 0.75. So, just do the calculation 44 into 1.05 divided by 0.3. And check this value whatever it is coming and then you will not compare 67 into d1, you will compare the CLV1 into d1 means the CLV at situation 1 into demand at situation 1, whether that is greater than or smaller equal to CLV2 into d2. So, this is CLV2 the previous case was CLV1, you will calculate these.
You will not consider 1 years, whatever sales is happening and etcetera whatever profit you are generating, but you will check for in the lifetime how much profit is generating. That will give you a choice that whether you will go for this discount or this promotion strategy or this strategy or that strategy.
So, when we compare multiple strategies also this comes to, comes in handy.
Video 3
So, if you remember in the last in this particular class we have discussed about the CLV formula basic CLV formula. I have done the math’s part, how to calculate CLV, how that formula came ─ the derivation of the formula basically. And then how that can be used in two simple problems are whether you should give a discount or not a simple basic problem. Now, what happens is, you have to think here there that there are two things that effects the CLV, one is that retention rate which is r and another is the discount rate which is i.Now often times customers or the managers thinks that customers will not stay for lifetime. We are doing the calculation for infinite time period. Customers will not stay for lifetime and they will stay for a let’s say 5 year, 6 year, 10 years time. So, should we do this calculation for infinite time period or should we do ─ be practical and do it for next 5 years. Now 5 year is a very important point often times because this is not too long not too short, 5 years is a timeframe often time you will see that when you
go to a interview the interview person in the front ask you that, where do you want to see yourself in 5 years? So, that is a question basically that they are asking that. Be realistic don’t be too much dreamy that what I will become something xyz in 10 years and don’t be very short very myopic that, I will just join this job in 1 year be realistic and at the same time not myopic. And that is why you tell me that up to 5 years you can probably be able to visualize yourself. So, visualize and tell me where what will what do you want to be in 5 years same thing applies here that I will do this calculation not being
very dreamy about. What happens in this 10, 20 years 5 years is a realistic assumption. And now when 5 years is a realistic assumption the question comes is that I want to make maximum amount of money in this 5 years. Now here if you see in this particular table that I am showing here. There are discount rates and there are retention rates. Now discount rates are basically
the 2 %, 4 %, 6 %, 8 % these are basically your how much is the interest rate that this particular market has, while retention rate is given. Now, you cannot handle the discount rate, you have no control on the discount rate, but you have pretty good control on the retention rate. You can do various things, various kinds of marketing strategies you can take up, to ensure that your retention rate goes up. Now if discount rate is, if I come down discount
rate is like this and here this is basically a very I would say volatile market. So, when the discount rate is high ─ the interest rate is high then anything can happen. It is it is very fluctuating very volatile. And in that kind of a market even a small retention rate can make maximum amount of money in the 5 years because you do not expect much of the money at the later point of time. Whatever happens now, whatever money get generated, gets generated now because later nobody stays with you or-or even if they don’t stay with you that does not matter because of most of all the money generate now, at this moment. The money that you generate later point of time they might have ah, I would say, if the volatility is very high then you want to make money as quickly as possible. So, this is something that we
can see that you will see that even with a very…..When the retention it is 40% and if I increase from discount rate to 2% to 20%; so, 99 is the case in this particular situation. On the other hand, if the retention rate is ─ if your market is very stable: so, this is a volatile market and this is a, stable market. In a volatile market even with 40% retention rate you make lots of money.
When the retention rate is 90% most of the people stays back, you make less amount of money in 5 years. So, in a volatile market if the retention rate is high anywhere) with in general the retention rate is high this drops. Basically from this side to this side the 5 year interest rate drops, but in a volatile market within a 5 year, even with a smaller retention rate you can make lots of money. Now I will focus on this 47% if the retention rate is high.
And the market is stable, 2% is the discount rate market is stable market is not moving much. So, whatever money you make today tomorrow and today they have similar value to you then you want to make your customers stay. You want to get the money slowlyover time. So, at that time the retention rate higher is basically better because you make more money through retention rate. If the retention rate is smaller the most amount of money you make in the 1st year itself later point of time you don’t make money. So, in this kind of a situation you might want to focus on: when your discount rate is
high - low you want, might want to focus on your retention rate much higher much, much more importance you will give on the retention rate. But when it is discount rate is high, you might say that I will not give so much importance on the retention rate. Because you see 40% retention rate you get 100% , 60% retention rate event if you increase the retention rate by 20% still most of the money is getting generated by 1st years, 1 st 5 years only. So, I don’t think that you have to focus on the retention rate much when the volatility is very high. So, this is a basic time horizon kind of a discussion that you have to keep in mind when you discuss about customer lifetime value. Now, I have given a simple formula for Netflix. I have given you some problems. The
formula for Netflix and it is formula for credit cards. Credit cards also, you, the more you use credit card the more value you generate for the customer right. So, the credit cards will also calculate the customer life time value and based on that they will say that.You have a preapproved offerr of let’s say a credit card with 1, 00,000 or 50,000 or 2, 00,000 of this thing depending on how much is your credit-worthiness. And they will say that you have a preapproved card why don’t you sir come and pay the membership fee or whatever and then get the card for you. So, these kinds these kind of offers
discounts blah blah blah are also there. So, you will get ─ you get that kind of calls from banks. Now they are also focusing on your ─ they give multiple offers in the 1 st year, why will you, why will they give multiple offers in the 1st year? You will make lots of loss in the 1st year if they give multiple offers, because they are not making money in the 1st year. They are making money over your usage as you go on using for multiple times they get a little bit of I would say commission from where? Whenever you use the credit card that
is number 1 and by chance if you forget to pay you pay back then they will make money from that. So, your un-credit worthy behavior and your multiple purchases this is the two basic streams of pay and also tie ups and etcetera.
So, these are the streams of revenue. That revenue does not get generated in one transaction over a lifetime it gets us profitable. Now; that means, that they also have to calculate customer lifetime value. But the calculation will be different for them and for Netflix and that is what I am going to show you now. So, if you remember in the previous one I told that customer lifetime value is M minus m 1 plus i divided by 1 plus i minus r. This is the basic formula. Now, what happens in case of credit cards, how credit cards make money? So, credit card in the 1st year they don’t make any margin.
They make, start making margin from the 2nd year the 1st year basically they do all the… So, it is a credit card right. So, you pay later. You purchase initially and you pay later. So, basically you, we are focus and it is a monthly, in this condition it is a monthly purchase. So, if you make monthly purchases in the, at the end of the month you pay for the last months purchases. So, if I do this calculation for the monthly perspective, then customer lifetime value calculation will be, that M minus m 1 plus i by 1 plus i minus r minus M, because for 1 month, for exactly 1 month the Customer Lifetime Value will be generated later. The margin will be generated later. For the 1st month you will make a loss and then you will start gaining. For on the 1st month you will do lots of marketing expenditure which will be a loss and the 2nd month onward you will…. Or I will make a purchase 1st and then I will pay you back. So, in the 1st month you pay for my purchase. So, that margin or whatever we are denoting that margin will be not there in the 1st month. That is the simple difference between a Netflix. This is Netflix and this is basically a credit card company, any credit card company their calculation is like this. And if that is the case you can check, what is the formula then? So, M minus m just 1 minute. I think this will be the formula. So, M minus m by i plus i minus 1 plus i minus r divided by 1 plus i minus r; yes and that gives me ─ that gives me CLV is equal to if I just check this formula this is M minus
m, just check this formula r by 1 plus i minus r. So, this is what I get as the net formula r into M minus m divided by 1 plus i minus r. So, instead of instead of 1 plus i here which is the general case I am getting r, that is the
difference between these two cases. So, it applies to which kind of cases? In any come, any condition where you make the purchase 1st and you make the payments later ─ 1 month later, you make the payments of the last month in this month is something what you will do. It applies for other cases also for example, room rentals. Room rentals you do all the services first and you pay later. So, anywhere in any cases where the payment is later and the services the, so let’s say, let’s say, you have gone to a serviced apartment and sometimes you pay in the serviced apartment at the end of the month in the 1st full month they give you service. And then in the end of the month you are paying. if that kind of a condition is there then the 2nd formula will apply, not the 1st formula will apply. So, if you have to pre pay or then or then and there whenever you are consuming you have to pay then the 1st formula will
apply.But if the consumption and the payment happens after a after a time period there is a time gap, then the 2nd formula will apply because the marketing cost will be initiallygiven. And in the 2nd time period onwards the money that will be getting generated in the year 0 there will be no money getting generated. So, that is a basic difference between the Netflix’s case and a credit card company’s case.
Video 4
Now CLV for Cohort or Contract: now if you have to, if you check carefully that we have assumed one single thing. We have assumed that the retention rate is r. What is the retention rate r means Retention rate r means that in the 1st year r number of customer stays back in the 2nd year r squared, 3 rd year r3 and so on 4th year r4 and so on. So, if I just try to plot that, okay this is time and this is retention how many customers stayed back in the 1st year 1? Because everybody stayed back in year 0, in year 1 this many customers stayed back. In year 2 this is r let’s say year 2 r squared customers stayed back in year 3 r3 customer stayed back and so on. And r cube, r4 will be slowly coming down because you know that these are r is smaller than 1. So, r2 and r3will be slowly coming down. Now if I plot it if I plot it will look like
a curve like this. So, this is a basically a retention curve.
Log in to save your progress and obtain a certificate in Alison’s free Understanding the Economics in Customer Relationship Management online course
Sign up to save your progress and obtain a certificate in Alison’s free Understanding the Economics in Customer Relationship Management online course
Please enter you email address and we will mail you a link to reset your password.