This is the 5th section in a series of 6 posts. Here is a summary post explaining all the different methods.
In sections 1-4 we relied on basic math to calculate customer lifetime value. In this method we are taking it a step further and relaying on tools like statistical regression, logarithmic functions and integral calculations. To do this we will need to feed the retention data to a spreadsheet.
This method is useful if you want to build a model that can be used over and over while having varying number of data points from different apps, traffic sources and cohorts.
This method will be more useful for you if you have your own copy so feel free to get one:
- Option A – Open the Google Sheet and copy it to your google drive
- Option B – Download the excel version here
- Day 1-30 retention data (column B)
- Day 1-30 ARPDAU data (column D)
- The expected lifespan in user days (users*days)
- 180 day LTV
This model assumes that the retention function is a power function – y=a*x^b
It uses statistical regression to estimate the parameters a and b based on the input data (known x’s and known y’s). Statistical regression is for linear functions so to find a power function we are running the regression on ln(x) and ln(y). All this magic happens in cells F5 and G5 of the spreadsheet.
Once we have the function we are simply calculating all the values from x=1 to x=180 (day-1 to day-180 retention values). We are estimating the retention numbers from day-31 to day-180 using the retention fucntion. You can see this in column J.
The next step here is to sum all the retention values from day-1 to day-180. This is referred to as Integral and the result is the number of user days to date. You can see the integral to date in Column K and the 180d Integral in Column L.
The last step is to multiply the expected user days by the expected ARPDAU. We are averaging the revenue per user per day over the first 30 days to estimate the forward ARPDAU.
- Very flexible and can be applied with any number of retention data points
- Calculates every segment on it’s own without using data from previous segments
- Assumes ARPDAU is constent
Advanced hack – tweaking for partial retention data
If you only have partial retention data from the first 30 days you can adapt the spreadsheet to take any number of retention data points. For example you might only have day-1, day-7, day-14 and day-30. As long as you put the input in columns A and B as pairs of x,y where x is the day and y is the retention you should be ok. Simply modify the formulas in cells F5 and G5 to reference the new range of cells that contains the new input. Here are the current formulas with the reference highlighted –
More methods to calculate user value
Here is a simpler method that can be applied if you have data from 180 days on existing segments.
If you like the spreadsheet method you might also try modeling the revenue function as an alternative.