On one of the previous posts, I explained the main ways to improve LTV informs marketers about how much they can pay to acquire 1 user and keep ROI positive. I explained the main ways to improve LTV in this post and this article explains how to calculate expected LTV manually on Google Sheets. It might seem as it requires technical and statistical knowledge, yet you do not need an engineering degree to evaluate this projection.
As LTV estimation differs between industries; SAAS, e-commerce, gaming etc.; this example is made for the mobile gaming industry.
To be able to calculate expected LTV manually, you only need daily average revenue per user (ARPU) for the specific cohort you chose. When you get ARPU values, you got to create a table including the number of days since Day 0 (the day when your users installed your app) and cumulative ARPU values. I created an example table below; the numbers are obviously fictitious.
Once you have the table, now it is time for writing your formula down.
Where Y is expected LTV and X is the number of days after the first install;
Y = ( c *LN(X)) +b )
is your main equation for regression analysis. “c” and “b” are constants we can estimate based on regression analysis of historical data.
To find out c and b, you need to use your cumulative ARPU as LTV.
c= INDEX(LINEST(all available Y’s, LN(all available X’s)),1)
b=INDEX(LINEST(all available Y’s,LN(all available X’s)),1,2)
When I typed equations above on Google Sheets, c=0.2076086996 and b= -0.09658964539 were found. Now let’s rewrite the main equation as following:
Expected LTV = ( 0.2076086996 * LN(X)) +(-0.09658964539)
Here is the regression analysis results both in table and graph:
According to this bottom up approach we could estimate based on the amount spent by a person, that the projected LTV at day 60 and 90 would be €0.753 and €0.838, respectively. Projected LTV mainly shows the value user delivers in the future which enables the marketer to control budget for their user acquisition campaigns without falling down under break-even point. So, in our case CAC (customer acquisition cost) at day 60 and 90 should be less than €0.753 and €0.838.
Day 1 LTV is a minus value, which is impossible in the real world. That problem is caused by minus b (Y intercept) in the linear equation. This simply means that the expected dependent variable, LTV, will be less than 0 when all independent variables are set to 0. A negative constant value generally doesn’t cause for a concern unless the variables in the equation don’t make a good sense. In the end of the day, what regression model provides us is the ability to understand how the dependent variable’s response to changes in predictor variables. When we look at our graph, LTV increases in large increments during the first 30-35 days, and its pace gets slower once there only retained but less numbers of users left in the platform which matches with general LTV behavior in the industry.
Another point is some of the expected LTV values after day 20 are lower than actual ARPU. It is probably caused by the lack of virality or referral value. As you may remember from the previous virality post, referral value is represented by the new audience a user will bring to your app and estimations neglect this value. On the other hand, actual ARPU includes it, naturally.
Gaming industry is quite dynamic and apps require regular updates and new features. It affects the consistency of your projections. Even though there are more advanced predictive models such as Pareto, no model could provide 100% certainty. What is recommended is evaluating projection models continuously.