This is the 6th section in a series of 6 posts. Here is a summary post explaining all the different methods.
In section 5 we modeled the retention function with a spreadsheet. Here we will use similar statistical methods like regression and log functions to model out the revenue function.
This method is great if you want to build a full model that takes any number of data points and accounts for both revenue and retention.
The spreadsheet 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 C)
NOTE: If you have datapoints on ARPU to date instead you can override column F instead of putting the inputs in columns B and C.
- 180 day LTV
This model assumes that the revenue function is a logarithmic function – y=a*ln(x)+b
The input data (known x’s and known y’s) is used in order to calculate the ‘a’ and ‘b’ paramters with statistical regression. This is done in cells H5 and I5.
Once we have the function it’s easy to calculate 180d LTV – a*ln(180)+b.
The only modification is that we are comparing the 30d ARPU given by the function to the data we already know and adjust the 180d LTV result accordingly.
- Very flexible and can be applied with any number of data points
- Accounts for variable ARPDAU
- Complicated (although slightly simpler compared to modeling retention function)
- Calculates every segment on it’s own without using data from previous segments
Advanced hack – working with partial data
You can easily use this spreadwheet with less than 30 days of data:
- Put the user and arpdau data in columns B and C or simply put the ARPU to date in column F
- As long as you have days in column A and the repective ARPU to date in column F the spreadsheet would work
- If you are using less than 30 points of data simply adjust the formulas in cells H5 and I5 to reference the right ranges
More methods to calculate user value
If you have data from one segment you can use this data to model the LTV in other segments more easily. Here is the post
You can also try other spreadsheet method – modeling your retention fucntion