LTV Prediction Tutorial in Google Spreadsheets

Overview

What is LTV?

Customer Lifetime Value (LTV) is a simple concept that gives you insight into the amount of money you can expect to earn from a single user over their entire journey in your app.

LTV prediction made easy

A lot of firms rely on LTV prediction to understand what their users are worth to them at a given point in the future.

With this use case, you will learn how to predict Day 7 LTV with a simple (ready-to-go)  formula. The linear regression in Google Spreadsheets will help you predict user LTV on a campaign, site and app level—all without the help of a data scientist. The calculation for the linear regression model (using a least squares method) can be visualised in the graph below.

Note: We find that this use case is specially relevant for hyper-casual game publishers.

If you are interested in a more mature analysis in Python, that can help you predict LTV values until Day 90, then have a look at our use case on LTV prediction in DataVault.

Table of Contents

How to predict user LTV in Google Spreadsheets:
Equation and method
4-Step Process
Model Limitations
Frequently asked questions about LTV
Send us your feedback

How to predict user LTV in Google Spreadsheets

Equation and method

The least-squares method is one of the most common methodologies for predicting simple linear regression models. The model attempts to make the most accurate prediction of dependent variable (Day 7 LTV per user) based on already available data on the independent variables, namely Day 0,1,2, and 3 ad revenue LTV per user.

The method is built on top of the following formula:

y = a(ln x)+b

where y is the dependent variable (Day 7 LTV per user), x is the step, a is the slope or gradient of the curve, and b is the y-intercept.

There are 2 methods of calculation you can choose from:

• Using Google Sheets – This is a basic model where you would need access to aggregated data. If you’re a Tenjin customer, then you can do this with Tenjin’s Data Exporter using the steps mentioned in the “Process” below. If you are not a Tenjin customer, you can still use this as long as you have the data on Day 0 to Day 3 LTV. The only limitation for you would be that without Day 7 LTV values, you won’t be able to get R-squared values on how accurate your prediction is. If you’re confused about how to do this you can contact us at info@growthfullstack.com, and our customer success team will get back to you as soon as possible
• Using a Python Script – This method is more advanced, and is discussed in a separate use case. We recommend users with larger data sets, or more campaigns, to use this model. The model requires you to have access to granular data e.g.  Tenjin’s DataVault, which is a paid product.

4-Step Process

The first step to implement this simple linear regression model is to make a copy of this Google sheet.

Next, you can follow the steps below to calculate your LTV:

Step 1: (For Tenjin customers): Log into the Tenjin dashboard, click on the “Data Exporter” button on the top right, select a date range, choose “Daily” as your granularity, the app you want to use for the prediction, group by campaign/site or app (you can do all three separately) and download CSV for Day 0, 1,2,3 and 7 ad revenue LTV/user.

Step 2: Import this data on to the “Test” tab by selecting “Append to current sheet”. If you have separate campaigns for different countries, you need to create a new sheet for each country. Your data will be displayed below the sample data. At this point you can copy paste it on top of the sample data to get your prediction values.

Step 3: Change the cells H1, I1 and J1,K1 to numbers ‘1’,’2′, ‘3’,’4′ and L1 to ‘8’. Note that we treat Day 0 LTV as the first step, and Day 1 LTV as the second step because natural logarithms can not take 0 into account. You can then see the D7 ad revenue LTV/user forecast in column “P”.

Note: If you are a non-Tenjin customer you might not have the 7-Day Ad Revenue LTV/user values displayed in cell “L”. In that case, you can leave this column blank. The only difference would be that you wouldn’t get an R-squared value in column “S” and won’t know how accurate your predictions are.

For those who have the 7-Day Ad Revenue LTV/user values displayed in cell “L”, you can determine if the linear regression works or not by checking your R-squared value in cell “S2”. The R-squared value displayed there tells you how accurately your model predicts the actual. In the case above, my regression predictions have an accuracy of 93%. Usually, anything above 0.8 or 80% is considered good!

Step 4: The Graph tab can be used to visualise this data. You would need to unhide columns A – G, copy columns “H” “I” “J” “L” and “P” from your “Test” tab worksheet and “Paste values only” in the Graph sheet. 

Limitations of this model

• This model is computer intensive as it carries out calculations for each and every row. Therefore, we do not recommend this model for those who are working with very large data sets. For such use cases, we recommend that you use our Basic LTV prediction model that uses a python script to generate predictions.
• The maturity of the campaign is an important limitation. In order to predict Day 7 LTV, your campaign has to be active for 4 days as the formula requires Day 0, 1, 2, and 3 LTV values.
• This template only predicts until Day 7 LTV, which is not too far into the future. For a more advanced LTV prediction model, have a look at our LTV in Data warehouse use case.

Frequently asked questions about LTV

What is LTV? Why should I use it? 

You can find a detailed answer to this question here.

What is the difference between Revenue and LTV? 

Revenue is a non-cohort value; it includes the revenue generated by all users during a specific time period, irrespective of when they were acquired. LTV is a cohort metric, which is the lifetime revenue generated by users acquired within that specific cohort period. You can read more about it here.

Thanks for your attention!

Send us your feedback

Your feedback helps us constantly improve and update our current use cases, or in creating new use cases as per demand. If you have any questions regarding this use case, or would like to have a further discussion on the topic, then reach out to us at info@growthfullstack.com.