Loading
Notes
Study Reminders
Support
Text Version

Customer Lifetime Value Calculation

Set your study reminders

We will email you at these times to remind you to study.
  • Monday

    -

    7am

    +

    Tuesday

    -

    7am

    +

    Wednesday

    -

    7am

    +

    Thursday

    -

    7am

    +

    Friday

    -

    7am

    +

    Saturday

    -

    7am

    +

    Sunday

    -

    7am

    +

Video 1

we are discussing about customer lifetime value and how to calculate that. So, basically we are discussing about the economic part, the mathematical part the maths ─ the maths and money part of customer relationship management. So, here I will solve some small hands on and under the domain of this is also is common between customer marketing analytics program and also customer relationship program. Some of the aspects are common and, but still it is important. So many people who are doing this particular course alone will need this particular understanding which is very
easy and, but very important as well. So, there is a company called Buford Electronics a company that sales around 350 million electronic products in USA. So, this company is a B2B kind of company and in the B2B company there are two types of customer base that they generally target. One issmall companies and one is large companies. So, electronics they are selling let’s say, they are selling some projector some CPUs, some this and that. So, even the small companies might need that and the bigger organizations will also need that. So, how do you how do they define small companies and large companies? According to them: all those companies which has revenue less than 3000 dollars per year smaller companies and companies which has 25000 to 100000 dollar per year are largerm company. So, this is something that they themselves have created the division. Now they are not sure, the case is like this that they are not sure that which of these two types of companies they should target. So, when there is a targeting problem that they are facing that whom they should focus on. So, what they did is, they went back and talked with their sales team and they found out some basic figures to based on which they want to take a call. So, one of the basic
figures that they got is called Conversion Ratio. What is conversion ratio? That means how many leads, that what percentage of your leads that you generate ultimately gets converted. So, it has been seen that small companies has higher conversion plan 15% conversion ratio, but the large companies have lower conversion plan only 5% percent conversion ratio and small companies ─ the cost per sales visit is also low only 20 dollars. So, sales visit is let’s say, if you want to if you go for a small company or if you go for a B2B sales you sometimes have to go to that particular company’s premises and give presentations stay there understand their problem and blah blah blah. Now, these you have to do on at your own cost. You do not do it at the cost of the company who is your potential client. So, that cost of per sales visit is 20 dollars and the cost of sales visit from the side of the large companies is around 100 dollars; and number of sales visit per prospect that is required for small companies in general you need less number of visits 2, probably 2 days ─ 2 visits only and in the large companies case it is 9 visits. The turnover per customer during first year is 1200 dollars here and 44000 dollars in that case obviously in the large companies and margin of sales is 360 dollar in case of this guy and 8500 dollars in case of large companies and because they have less number oflarge companies you have approached 8000 large companies in a particular year and 40000 small companies in a particular year. So, this is the last one year’s figures. Now based on this they have to decide that which of this group is more profitable in the long run, whom I should majorly focus in the next year also so that I can make lot more money in the long run. So, what is the sales expense per prospect? So, per prospect ─ that means, they have not become the customer yet, the expenditure is basically the number of sales visit into the cost of sales visit right. And I drag it from here to here so, I drag it then I get 900 here
and 40 here. So, the conversion ratio is 15% and 5%. So, what is the cost to acquire a customer?The cost to acquire a customer is this divided by 0.15. Why this is divided by 0.15? Because if you know that, that if you ─ 15% is the conversion rate right? That means, out of 100 prospects you will generate 15 customers and out of 100 prospects, what will be the cost? 100 into 40 plus 40 dollars is the cost for each prospect. So, 100 into 40 so, 100 into 40 dollar will get 15 customers. So, cost for 1 customer is 100 into 40 divided by 15, in other words, 40 divided by 0.15.So, that’s what I am doing here. So, 40 divided by ─ 40 divided by this into 100. So, that will be the cost and if I just drag it this is 900 divided by 0.05 comes up to be 18000.So, now this is you understand that your cost to acquire a customer is pretty high here and that is this is the benefit that you get. So, net contribution, what is the net contribution for the acquired customer, how much money that you generate in the first year, you only generate 93 because 360 is your margin first year and 267 is your cost. So, 93 is your margin and here it is negative margin minus 9500. How many customers do we acquired? 15% of this and how many customers you have acquired here? 5% of this so 400, what is the initial investment in expansion of customer database? So, there is an ─ there is an investment that you have to do. So, I will not focus on this let’s keep this aside and the net contribution for newly acquired customer what is the net contribution we have already calculated that. So, this one is, if I am not wrong: to buy this thing there was some cost. So, let’s forget about
these two. I forgot what was written in the case okay. So, let’s forget about these two. So, this is the situation that I have got. Now the question is, that which one should I go for, Out of these two small companies and large companies whom should I focus on? This is the next big question that we are trying to answer. So, now, here I can see that here I am making 93 profit, but here I am making 9500 loss. So, why will I even think about them, I will think about large class of companies because in the first year I might make a loss, but as I have shown in the previous video that in the next year onward, in the second year or third year or fourth year onward I can make some profit and that profit can be very high for the large companies. So, what to do then? So, then we will calculate a customer lifetime value in the next stage. So, what I do here is I take certain assumptions. So, in the first year the sells is 1200, 30% was the margin. So, 360 is the margin and the marketing expense is 267 this has been told to us, the sales I assumed to be increasing, increasing and slowly it will be probably let’s say, saturate at 2700. At one point it will saturate will not further increase and the margin also slowly
increases let say 45, 45 and then 50, 50, 50, that is the margin let us. And the marketing expense slowly comes down. So, 50, 50 let’s say, 30, 30, 30.
Marketing expenses why it comes down, because as I told before that you know your customer mode. So, even that 30 small customer expenditure marketing expenditure can also lead to a closer. So, and also another important thing is the retention rate slowly goes up. So, whoever joined in 1998 only 75% stays in 1999, but whoever stayed in 1999 80% of them stays in 19. Also just think about this situation one easy example and you will answer will be able to answer. That at what year of your marriage after marriage at what year the chances of divorce is highest, in the 1st year or in the 2nd year or in the 5th year or in the 10th year or after 25 years or 30 year? So, after 25-30 year the chances of divorce is almost 0, it is much
Commented [A1]: ?2000? Time: 10.41higher in the initial year, So that means, the retention rate is much higher as the age of the relationship goes away, goes up. So, I let’s say, that it will come up to 90% when I reach here fair enough, now based on these assumptions now these some values you got from the figures and some values are assumptions. Based on these assumptions, you should not make the assumptions always this exact
trend you should get it from your database. That data should be there in the database. What is the retention percentage you can get it from Markov Chain Models. I will not go to that, but you can try to find out what is the exact probability to change from 10 years of relationship, 9 years of relationship to 10th year of relationship how I can get that probability. So, you should be able to find out that from the database. So, you can ask as a marketing manager to your data science team that: give me these values. But once we have these values what is the contribution? This is, this minus the marketing expense that is a contribution per account that I got, what is the discounted
value of the contribution per account. So, what is the discounted, discounting rate that I have taken 20% is the cost of money. So, this divided by 1.2 to the power 1998 minus this year. So, just assume what I have written, G point 2 divided by 1.2 to the power 0 for the first one. If I now drag it, this is 520 divided by 1.2 to the power 1 because 1999 minus A3 which is basically or it is actually the other way sorry. So, it is A2 minus 1998, otherwise it is
A2 minus 1998. So, now, that is the discounted price it is a little bit lower than 520 and so on. So, this is the discounted contribution; and what are the number of accounts in a year, how many accounts are there? So, the initial year I have acquired 6000 customers, first year, 75%of them stays in the second year, 80% of that stays in the next year, and so on. So, these are the number of accounts that stay in respective years out of these 6000 guys
who I have acquired on 1998. So, then what is the net yearly discounted contribution: this into this. So, here if I see I get the average also. So, I get the total contribution is basically a sum of all this first year I make this much money, for the next years I make this much money. So, these if I just add I get a total segments’ customer value and customer lifetime value and average customer lifetime value is nothing, but this divided by 6000 because I have
acquired 6000 customers. So, N6 by N3 gives me 1790. So, from each client over the time period I will make 1790. So, now, question is what happens for large customers? For large customers I will try to see the same thing. So,let’s say, it starts with 44000 and I assume that it will be saturating at 95000 let’s say: 90,000 90,000 and then 95,000 and the margin let’s say, saturates to be 24, 26, 27 let’s say, 29, 28 for twice and then 29, 29, and 30 let’s say, that is how the gross margin works. So, then what is the margin? The margin is basically, I would remove this, the margin is basically, this into this by 100 that is the margin, here do the same calculation here, just one minute I did no sorry yes I did the calculations. So, the margin is nothing, but B2*C2 divided by 100 and that is the margin fair enough. Now what is the marketing expenditure? I think that the marketing expenditure will come to 300 and then saturate and the retention percentage will go up to 70, 70, 70, and
then 75, something like that and that is how it will be let say this is also 70.
Then, what is the contribution? So, what I will do is the equations are more or less same, but still what is the contribution. The contribution is the margin minus the marketing expenditure as you know the first year it will be a loss, but the second year onward it will be a benefit. And what is the discounted price? Discounted price is this divided by 1.2 to the power this one minus 1998 ─ as we did for the previous chart also, previous excels. So, this is
the discounted contribution I got. How many in the first year they did, they acquire? They acquired 400 customers in the first year so, 400 and then this multiplied by 100 so, these many customers stays back. What is the net money then? This into 400 and so on. So see, we can see here that here after the calculation and etcetera I can see that it is 1081 and this is 1790 if I am not wrong. So, this is much higher in an overall sense and why it is much higher, you can see that if ─ there are lots of assumptions, but this is a
very big assumption that first year 40 and then 55, 65, 70. Why if I just make this one as let’s say, 50 this values becomes very high 3761. So, it is important that how many you retain from first year to second year that’s because the most amount of money that is getting generated is in the second year here. So, how many you can retain for one single year for the large customers can make a change. If you make it 45 this becomes higher from 40. If you make it 42 or 43 this becomes higher than before. So, how many you can retain, for one single year ─ large customer is sometimes define your whether you will be able to get lots of money from the large customers or not. So, that is how we can do in a B2B sectors the calculation of customer lifetime value.


Video 2

Now customer lifetime value is also an important factor for a B2C sector, now in a B2C sector there are referrals involved in B2B there is generally less number of referrals butinto B2C sector there is up selling, there is cross selling, there is B2B. So, there is B2C referrals. So, all of these things becomes handy in the context of B2C. So, I will give you a idea about how to calculate the lifetime value for a resort. So, let’s say, a resort is making… So, so it is a year 2 to year 5 they have decided 5 years of life cycle. And there are certain assumptions. The first assumption is: that to incur you have, to get a customer you have to incur 2000 rupees of acquisition cost in the first year and then you first year acquisition cost is one time and then for the next of the time, the first year the cost is 9600 and that cost increases by 8% per year. And after doing acquisition by paying 2000 rupees in the first year 0, year 1 when the customer comes as a customer they do generate revenue of 12000 in that particular resort by staying in that resort. So, 2000 rupees is your cost, to catch the customer, after the customer comes to your customer base you have to they give you 12000 rupees revenue and out of this 12000 revenue 9600 is your cost. Now the revenue slowly goes up and the costs slowly comes down over year, cost come down at a, cost also goes up, but not at a similar rate revenue goes up at a 10% per year rate and cost goes up at 8% per year rate. So, how much is the revenue here then, into 1.1and here it is into 1.1 so, I can drag it and this is the revenue for the next 4 years; and
what is the cost, cost increase at 8% per year rate. So, this is the cost for next 4 years. So, now, we will be calculating the profits. So, what is the profit? The profit is basically, revenue minus cost. So, first year there is a loss and second year onwards you are making some money. Now you also make money from other things one is, see four things I have written here: one is, profit from increased purchase for rooms; that means, increased revenue I told you in the fourth. So, increased revenue upsell and cross sell referrals so, increased revenue. Profit from other service which is cross sell, reduced
overhead the cost of serving you comes down and referrals. So, there are four source of money that is there, what is the increased purchase upsell? In
the first year no upsell happens because there is the first time you were purchasing anything in the second year onwards upsell happens. So, upsell happens of 800 rupees and it increases at a rate of 20% per year. How does this assumption come from? Where does it the assumptions comes from? The assumptions come from the database that you already have. The customer database you have to join that customer database, you have to; you have to; you have to analyze the customer database and create these assumptions which are meaningful so, 1.2 and so on. And the profit from other services that also increases at rate of 1.2 rate. So, this into 1.2; 20% increase every year next year, it will be 1440 and then it is 1728. The referrals
increase at a 10% rate so, this into 1.1 and so on. And overheads reduce from revenue, 10% of the revenue from year 2. What is the 10% of revenue? This is 10% of revenue: this much is the reduction in the overheads that also contributes towards your profit. So, all of these 4 things contributes towards your profit. So, what is your total profit then? There is nothing, but a sum of these five things. The profit from sells, the profit from up sells, the profit from cross sells, the profit from reduction of overheads and the profit from referrals. All of these things contribute towards your ultimate profit overall. Now, you have to see carefully that the first year you are making a money of 2400, the second year it is 7900 which is huge, next year it is which is a huge jump because all these cross sell upsell all of these things comes in.Third year it is 9500 significant jump, but not very huge and then 10000 and then 12000. So, how do you decide it is 5 years you can go up to more so, whether it will be 5 year life cycle or 7 year life cycle or 10 year life cycle it also a critical decision I told that CLV calculation starts from lifetime calculation.
What is the lifetime? Here we have taken 10 years as lifetime I would have taken for 5 years and then did the calculation. So, that is also crucial decision and that comes from the transition probability, that what is the transition probability that from one year to another year how much will be the change in terms of the percentage. So, keeping that in your background we have to take whether it is 5 years, or 7 years, or 9 years, or something like that. Now I have taken a discounting factor of 15%. So, what I will do is I will just write 0, 1, 2, 3, 4, 5. So, first year the discounting factor is basically these divided by 1.15 to the power 0 sorry 1.15 to the power D 0 value and if I drag next
year it becomes 2400 divided by 1.15 to the power D 13 which is 1 and so on. If I just drag these things up to this point then I know that see the values have come down and the net present value is then how much: the sum of all of these things. So, the net present value for this customer is 2400, now what contributes in this 2400 you will see that this jump is basically contributing to this. So, the major contributor is these values and these values are coming not from the increase here, the increase here is not much, but these values are coming from the increase here ─ this cross sell upsell and reduction is something that is contributing the most, and out of them also the highest
contributing factor is the referrals you can see there are lots almost 2000 to 2500 rupees every year is coming from referrals. For a resort context its lifetime value for a resort, for a resort context that is why the referrals becomes a very important factor while you are calculating customer lifetime
value. So, I will stop here in terms of customer lifetime value calculation we have done two types of customer lifetime value calculation one in B2B, one in B2C both has huge applications in the economies of customer relationship management. And in the next video we will be discussing about certain cases where these concepts of customer lifetime value management and customer relationship management will be used to take business decisions that how I can take strategic decisions by makinginformed choice based on the based on hardcore monetary values rather than very softcore, very-very abstract marketing strategies. We have to take marketing decisions which are hardcore quantitative decisions and how that can be taken using this CLV calculation is something that we will be discussing in the next video.
Thank you for being with me in this particular video, I will see you in the next class. Thank you.