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!