The (almost) magic formula of Lifetime Value estimation
In a previous blog post, Reinier wrote about the importance of assessing LTV. He brings as an example a corporate case study, and says “It’s not the calculation that is so interesting […]What is most interesting is that this infograpic underlines the importance of using Lifetime Value (LTV) in your acquisition strategy.”
And he is right for 2 reasons:
First of all because every organization has his own needs, and an approach based on a “one size fits all” formula wouldn’t last long;
But also because the calculations used in that particular case study are not reliable! (I will not go into details here, I explained the reasons of my perplexity in the comments.)
The truth is that, sooner or later, we need to look at the math of LTV as well. Out there on the web you can read many statements like “your LTV will grow by XX%”: this calls for some clarity, about defining and calculating Life Time Value, to see who’s for real and who is just pulling figures out of thin air.
The question at the base of Reinier’s blog is:
How much can we afford to spend, to get on board, and keep, one donor? Let’s have a look at two possible approaches to this problem.
First and foremost: look back at your data, make an assessment based on the facts.
If you have a donor database, and know how to interrogate it, then use it: it will give you safe and sound actual figures, so that you can avoid making assumptions, and you can leave the crystal ball to rest in the forbidden tower!
Look at the data: single out at a group of donors that started giving during a period, a few years back in time. How much revenue did they generate so far? How many of them are remaining to date?
Let’s say, for example, that you focus on a group of donors that gave for the first time in September 2006. They were 500 at the time. They generated, until September 2011, 100,000 euros. The figure you need here is: 100,000/500=200. The calculation is straight forward, but it does take into account attrition and missed payments, because we are dividing the total revenue by the original number of donors.
You can then say that “the Average Gross Life Time Revenue over 5 years for Donors that gave for the first time in September 2006 is 200 Euros”.
That says much more than just “the LTV is 200”. LTV gross or net? Is it from one single donor, or the average of a group of donors? What was the timeframe of the LTV calculation? 5 years? 10 years? Forever???
That’s yet another lesson learned: be specific in your definition. Specially if your aim is to benchmark your progress, or your position compared to some peer organization.
But this solution could not be possible (no raw data available), or could be not suiting your needs:
Maybe you don’t want to look in the rear view mirror, you want a projection of the LTV that’s in line with your current (or future?) performance indicators and plans. You need to do some forecasting. Time to get the dust off the crystal ball…
Forecasting donors’ LTV: a minimalistic approach.
The average Life Time Value of a donor will depend on a broad number of factors, which vary in importance from organization to organization, and even from campaign to campaign! Therefore, what I show you here is just an example: the simplest LTV calculation that I could came up with so far.
The input needed is:
– the average yearly pledged donation per donor, (D)
– the attrition rate year over year, for each year (A1),(A2) etc. (or their good twins, the Retention Rates (1-A1) etc…)
Let’s try to do the calculation on a yearly basis, on a period of 3 years.
We start, of course, from the yearly donation (D). We could be tempted now, to get the first year income, to multiply D*(A1). But there is a problem with that: we are overestimating the attrition, assuming that all the donors leave at the beginning of the year. That’s not always the case, and if you have a lot of monthly or quarterly donors (like my organization does), also those had the time to make some donations before you lost them.
My favorite way to make up for that is to use not the retention itself, but its square root (that’s (1-A1)^(1/2) ), to simulate an attrition that happens through the year at a constant “month by month” rate.
The formula becomes (*):
1st year LTV: D*((1-A1)^(1/2))
2nd year LTV: D*(1-A1)*((1-A2)^(1/2))
3rd year LTV: D*(1-A1)*(1-A2)*((1-A3)^(1/2))
3 years gross Average LTV per sign-up = (1st year LTV)+(2nd year LTV)+(3rd year LTV)
It can look complicated, but it’s quite easy to put it in a spreadsheet that you can re-use overtime.
Hint: you can download an Excel document right now where these formulas are implemented. Free trial! ;-)
Of course, this is just an example, not a universal solution. As I write, I see that many features could be added, and that the estimation could be improved in many ways. To name just a few:
– We could need the LTV per sign-up instead than per donor: then we need to consider pre-debit attrition in our calculations.
– A missed payments rate or a felony rate are not included in this forecast.
– Money now is worth more than money later: you could reflect this by including a discount rate “i” (eg. 10%) and then dividing each year’s income by (1+i)^n (n being the number of years from now).
– We are assuming that attrition is constant month-over-month through the year. That’s often not true: specially for monthly direct debits, months 1-4 are the ones with the highest attrition. A month by month detailed model is needed to simulate this.
– Why only 3 years? You could need to look over 5, or 10, or 15 years in the future!
– You name it…
As you see, a “simple calculation” can get complicated very quickly, and adding those is only a very small part of the factors that will influence your fundraising results. There is no absolute “best” practice: decide the level of detail that you need, and find out the level of complexity that you can handle.
I end this post with a word of caution:
When you are reading a case study about LTV (or any other report containing complex indicators), always check if the logic is sound. It’s not “only about the numbers”: it’s about being able to assess the quality of a consultancy job. If you’re not into the numbers yourself, see if in your organization there is a geek hiding somewhere: find him, give him a break from the boring paperwork he’s doing at the moment, and ask him his opinion.
Avoid getting carried away by good-looking Power Points, full of fuzzy logic and shiny infografics!
20 Comments
Ajay Ochani · November 11, 2011 at 05:14
Luca –
This is a wonderful entry – very rarely do I see blogs that take us back to our building blocks and are brave enough to take on the details (which are so so important). I have downloaded your excel sheet – thanks for sharing.
Best, Ajay
Luca Schippa · November 11, 2011 at 21:29
Thanks Ajay!
Let me know what you think about the spreadsheet, there is a lot to discuss around this topic, I think.
Best Regards
Ajay Ochani · November 14, 2011 at 05:04
Hey Luca –
I used the excel sheet that you had developed and I did find it much more user friendly (at Save the Children we have quite a complex model for our forecasting and planning – but I love it!). I have two very minor comments…
-1- Delinquency is a real nuisance and particularly in Germanic markets, where donor recruitment can bring in annual and semi-annual pledgers, it can have a serious impact on income and LTV, so I was wondering how one could go about including this in your LTV model. May be same as attrition – delinquency 1st year, 2nd year and so on?
-2- As we all know the donors that we are recruiting now in many markets are very different to some of the older segments – no-show, attrition, delinquency is much much higher. So maybe to make the LTV tool more forward looking (without adding to the complexity) perhaps the user can apply optimistic/pessimistic scenarios through simple drop-downs so for e.g. ‘pessimistic 1’ could be mean that fulfilment drops to 80% and attrition increases 5%-8%, cost of recruitment eases up and so on. So then we can use the actuals going back 5 years (if the database is that old) and for planning apply these various sensitivities and see the projections side-by-side.
Perhaps just as easy to create various tabs and do scenario 1, scenario 2 etc but in my experience you soon lose track. What do you think?
Luca Schippa · November 17, 2011 at 16:16
Hi Ajay,
1. yes, in the post I admit that missed payments are not considered in my simple LTV calculation. And indeed, a “rule of thumb” method would be the one you suggest: create an input for a “delinquency rate” per year and apply it to the LTV calculation. That will need some reworking of the formulas as well.
2. well, this calculation is so small that you could have 2 scenarios one under the other in the same worksheet! That’s the advantage of keeping things simple.
Anyway, I am afraid that when you need to make some detailed planning for your program on the whole, then you will still need your complex model. It’s difficult to develop a forecasting tool that is both complete AND user-friendly…it’s a work in progress!
Best Regards
Mitch from WWF · August 8, 2012 at 05:20
Dear Luca and Ajay
For disclosure reasons, Luca, I will say I know Ajay, and Reiner!
Thanks for your postings, there is not a more important topic than this one. For that reason, I wanted to emphasize a few things (that Luca and Ajay already pointed out, but I think they are worthy highlighting):
First, I don’t think the core question is: How much can we spend (to get and keep) a donor?. I think the REAL question most of us face in our NGO is: how much can we JUSTIFY spending, to our Upper Management, and make them think we are doing a good job? (Hence, sadly, we must at some point introduce the Evil Brother of LTV, ROI, which is the far more dangerous of the two). I”m not saying I like it, I’m just saying we must do it. So I would say that perhaps a good solution for this is to make sure you use the two “brothers” together; if your SMT makes you show your ROI for acquisition, be sure to include the LTV along with it, to show the value BEHIND the ROI.
Second, two details on the models. I’ve made many, many of them over the years, and one detail that I have found that is especially important is to look at average gift/year. In many markets (Indonesia for example, or Brasil), its quite common for ‘regular’ donors to run out of money one month on their bank account or credit card, and then ‘re-fill’ it again the next month. So the “real” average numberf of gifts per year (for an “active” donor is closer to 11, or even 10.5, than to 12).
The second point, which is really important if you are using face-to-face to acquire your new donors, is to calculate the MONTHLY drop off rate at least for the FIRST year (instead of simply splitting it in half). I agree, Luca, I have ALSO done the 1/2 model, it makes it easy, but the drop-offs in face to face happen SO quickly in the first 6 months, that it really helps to average your first year by month, because you will lose a LOT of first year revenues quickly. From then on, you can average the year.
Overall, Luca, you get a 10 out of 10 for this! Great issue. I make my comments just to help people who are a bit more concerned about cash flow, and need to cut things closer to the bone.
An open question (to you both) is: what do we do when we have only 1 or 2 years of total data (as in a start up market, like the Philippines?). My answer: make best guesses based on what you DO have, assume some improvement in attrition (as your marketing gets better), and adjust as you go.
Happy hunting, looking forward to seeing you both at IFC!
Mitch
WWF International
(now based in Singapore)
Beth Ann Locke · November 13, 2011 at 23:00
This is a *terrific* and practical measurement. Complex, but this is where the rubber hits the road rather than just guessing… Thanks for thinking, writing and sharing! ~ @FundraiserBeth
Luca Schippa · November 17, 2011 at 16:23
Thank you very much Beth Ann,
it’s good to hear the confirmation that this can have a practical value for fundraisers!
Kind Regards
Gijsbert van der Meulen · November 18, 2011 at 17:22
Dear Luca,
First of all thanks for making this sheet and the clear explanation. I think a lot of people will help to clarify revenue and expenses. I am also quite a numbers and excel geek, and I noticed something on the sheet that biased the results.
Because one can only enter the total donation amount in cell B3 and because there is no additional cell to fill in the number of donors who would have been required to get to that amount the costs are no longer correct. The cost for the ‘sign up’ to each donor, and the annual cost for support are therefore incorrect.
This way the total cost is not calculated, so the overall result appears way too positive. In other words, the sheet is incorrect. Sorry!
I would therefore advise you to re-create the sheet and add (for example) a cell B2 to deal with the number of donors. You can re-use cell B3 for the average donation amount per year. Those two values are enough to continue changing the formulas below to get to the correct amount of LTV. If you need some help with that, be sure to let me know!
Good luck!
Regards, Gijs
Luca Schippa · November 21, 2011 at 16:40
Dear Gijs,
thanks for the feedback, good to see that the readers take time to check the calculations in detail!
However, I think that at the base of you remark there is a misunderstanding: the input for the cell “B3” is the total donations given in 1 year from *one single donor* (in the article I call this quantity “D”), and the average LTV, gross or net, is calculated on a *per sign up* base:
that’s why there is no input for number of donors, and the costs are not multiplied by any factor!
Maybe the text in cell “A3”, that reads “Donations in 1 year” is misleading, and let you think that that was the space to input the donations from a group of donors and not an individual.
Of course you could modify the spreadsheet adding an initial number of sign-ups, and making it show the number of donors left at the end of each year, and the total income and expenditure. This way you could also separate the costs that incur on a “per person” base, and the costs that are fixed whatever the amount of people you sign up is.
Of course, this would add complexity to the model, and arise the need to make further assumptions.
Kind Regards
Gijsbert van der Meulen · November 22, 2011 at 09:45
Dear Luca,
You’re right, I’ve assumed a situation in which multiple donors add up to a total amount of money. The company I work generate about 40,000 new donors for various charities, so I tend to look at your sheet in that specific way. Although I must say that I find difficult to use your attriotion rates in a situaation where it only concerns one single donor. How am I supposed to use for example the 40%? The donor stays or leaves, so it would either be 0% or 100%, right?
Regardless of my additions, I welcome your sheet. I still see far too many organizations dealing with numbers like it were Chinese, or that it was too ‘commercial’ to look at funding that way. Your sheet will surely help people to better understand what they are doing, for which I salute you!
Regards, Gijs
Luca Schippa · November 23, 2011 at 15:00
Thanks Gijs,
you ask: “How am I supposed to use for example the 40%? The donor stays or leaves, so it would either be 0% or 100%, right?”. The concept could be confusing to some, but it makes sense if you consider the LTV you get as an average, an expected value that you calculate based on the parameters of a population.
To make an example from another field:
a person either lives past a certain age or she/he doesn’t, but yet demography scientists (and insurance companies…) calculate the Average Life Expectancy based on the death rates observed in a population.
Also, if reasoning in abstract with averages confuses you, you can do a simple proof:
1-in the “dummy example” of the LTV calculation sheet, multiply all the inputs in the yellow cells except attrition (thus donation and costs), for an arbitrary number of “original sign-ups”. This way you are doing the input for the whole group of original sign-ups.
2-divide the output (figures in the blue cells) for that same number, to get the average LTV per original sign-up.
The results you will get will be the same as before!
“…I still see far too many organizations dealing with numbers like it were Chinese”
No comment on that ;-)
Kind Regards
cNukaus · January 21, 2012 at 11:48
don’t see much difference between this one and the one you cited about starbuck (traditional LTV calc).
1/2 to simulate monthly attrition is interesting.
But where is the interest rate?
Luca Schippa · January 23, 2012 at 19:59
Hi cNokaus,
thanks for you comment. To answer to your question:
– There are a some of differences between the Starbucks “classic LTV” formula and the one presented here. For example, in my formula the LTV is calculated year by year, and the attrition is inputted per year, while in the Starbucks formula the timeframe is indefinite, (see my comments to that article for a more detailed explanation).
– The interest rate, as I say in the article, is one of the features that *could* be put into the calculation, but in this case I didn’t put in :-)
After all, the calculation is meant to be an example, and is in no way exaustive of all the possibilities.
Kind Regards
Luca Rossi · February 8, 2012 at 06:53
Luca, thanks for this and the model! Would you be able to briefly discuss how you would set up the month by month model? I guess I am trying to figure out the maths behind the square root and how we could apply to a monthly calculation.
Best
Luca
Luca Schippa · February 12, 2012 at 14:10
Hi Luca,
if you set up a month-by-month calculation, you won’t need to use the square root of the attrition, you can just use the monthly attrition.
Anyway, a monthly model will get much complex than this yearly one, depending on the level of detail that you need.
If you have some detailed questions we could talk about it by email, volendo anche in italiano ;-)
Regards
Dem Jardel · November 20, 2012 at 14:47
I’m very happy to see more thought being put into these calculations.
I completely agree with the fact that LTVs have to be clearly defined about what they are actually calculating.
I have a couple of questions about looking at individual campaigns from different years. In these formulae, what is your definition for the attrition rate? Is this the attrition after a year of the start of a campaign? Is it the attrition after a year from the donor’s first donation? They are very different things and one is very dependant on the length of the campaign and the other puts you out of synch for what the first year of income would be.
Jagmohan Khosla · January 3, 2013 at 19:25
I definitely appreciate all the tough labor that you’ve put into keeping this blog going for everyone. I truly hope this is around for a very long time.
web page · June 24, 2020 at 14:59
I pay a quick visit every day some web page sites aand blogs to
read articles or reviews, but this webpage provides feature based posts.
tunnel rush · April 25, 2023 at 08:10
To avoid overestimating the attrition rate, we can use a more precise calculation. Let’s say we have 100 donors, each pledging 100 euros per year. Our attrition rate for the first year is 30%, which means we will lose 30 donors. This leaves us with 70 donors, who will each give 100 euros over the next year, resulting in a revenue of 7,000 euros.
mapquest directions · June 20, 2023 at 09:00
Mango trees don’t need a ton of water, but it’s important to water the new tree every other day for the first week. Then, water it only once or twice a week for the first year