How to make retention model and calculate LTV for mobile game

upd 2024: The latest version of the calculator is available here. It has been updated to include revenue sharing with the publisher and changes in game growth metrics by quarter. However, the fundamental principles of calculation described below have not changed.

I offer paid consultations on mobile game launches and live operations. If you think you could use the help, just shoot me a message on LinkedIn

To begin with, let’s understand why LTV forecasting is necessary. It can help you estimate roughly the amount of income aligned to your burn rate and compare it with the cost of attracting a player. As you get real data after the release of the project, this model will be replaced by another, more accurate one.

All calculations will be done using Google Spreadsheets. By the end of the article, you will have a LTV value that can be used for further estimations.

Let’s start by learning how to build a player’s return forecast

Imagine that we are developing a strategy game. To estimate retention rates for this genre, we could refer to the GameAnalytics report that was published a few years ago, titled Mobile Gaming Industry Analysis for H1 2019 — GameAnalytics

It’s important to note that these numbers are very average and will differ depending on the platform, type of traffic, and country.

Now, we need to obtain a function that describes the gradual decrease of retention rates depending on the given day.

Easy way

1. Make a chart selecting all data, to keep it smooth use Scatter chart type.

2. Double click on chart, go to Customize tab and Series section

3. To add a power series equation as a trendline, check the “trendline” checkbox on your graph. Then, select “Power Series” as the type of equation to use. Finally, check the “Use Equation as a Label” box to add the equation to your graph.

We use a power series because it most closely matches all the data points and corresponds to the actual user retention.

4. The equation will appear at the top of the chart and the trendline will be drawn illustrating your retention curve.

More complex but flexible way

To obtain the power series equation values, we need to perform data transformations first. In order to get a slope (-0.697) and an intercept (-0.317), we can use the LINEST function. However, since this function works with linear forms, we need to convert the day and retention values using the ln() function.

To make the process faster and convert all values in the column at once, we can use ARRAYFORMULA.

To obtain the slope and intercept values for the power series equation, you need to select your transformed day and retention values and apply the LINEST function. The formula for this is:

=LINEST(E2:E4, F2:F4, true, false)

Alternatively, you can use the SLOPE and INTERCEPT functions separately.

For the intercept, you will need to make the reverse transformation using the EXP function since the power series formula looks like EXP(y) = EXP(A) * x^B. In my case, i referred to F10 cell, =EXP(F10) to obtain the intercept value, which should be the same as the value obtained from the chart.

Now we could apply this values and make a retention model

Let’s create a new table where we enter all the days from 0 to the desired day, and use the power series equation to calculate the retention values. The formula for calculating retention values using the power series equation is:

=Intercept * (POW(Day, Linear Slope))

In my case, the formula would be:

=$G$8 * (POW(A13, $E$10))

Where G8 is the intercept value obtained from the LINEST function, A13 is the day value, and E10 is the linear slope value obtained from the LINEST function.

We obtained values that are fairly close to the initial values. Since we are building a rough model at this stage, we will not make corrections by calculating offsets.16:12

If you have used the manual method of calculating the slope and intercept, then changing the initial retention values will cause your predictive model to change automatically. Otherwise, you will have to adjust the formula yourself.

Now let’s calculate the LTV7 based on this retention for 7 days

To calculate LTV we will use two very approximate ways

LTV=Lifetime*ARPDAU.

This method is only suitable for a rough estimate since it assumes that ARPDAU is a constant value, which is not.

Lifetime of a user is the area under the retention curve from D0 to D-you-need (in our case, from D0 to D7). To find this area, we can use the trapezoidal rule or other numerical integration methods. Alternatively, you can look up how to use the Monte Carlo method, which can be more accurate.

Now all that’s left is to sum the areas of the squares from day 1 to day 7

The final step is to assume that our ARPDAU is $0.065 (from the same GameAnalytics report).

Using the first method, we can calculate LTV7 as follows:

LTV7 = 1.43 * 0.065 = $0.09295

For the second method, we will use the cumulative model where LTV = Cumulative ARPU. First, we calculate how much ARPDAU we are getting from the users who are returning in a hypothetical cohort and sum it with the daily ARPDAU. It’s important to note that this value may be different from what we obtained using the first method since all forecast methods can provide different results.

This method highlights the problem of assuming a constant ARPDAU. Estimating the profit from a new player using an average value from an unknown lifetime distance can be inaccurate. In reality, the behavior of paying users is highly dependent on many factors, including the genre of the game, the user’s country, the speed of their progress, and your paywalls.

If all estimates are so rough, why do we calculate them at all? In my opinion, even a rough estimate is better than nothing. It’s better to have some kind of model than to use random LTV numbers in your PnL forecasting. By estimating LTV, we can make more informed decisions about user acquisition costs, marketing budgets, and overall business strategy. However, it’s important to keep in mind that LTV calculations are only estimates and can be subject to significant variation.

WHAT’S NEXT

In the next article, we will analyze:

• how to use this LTV to predict the ROI of the project
• split LTV by tiers and traffic type
• replace the predictive model values with real ones.

Published:

1. How to make retention model and calculate LTV for mobile game
2. How to calculate ROI and predictive LTV with the first real data
3. How to check for data anomalies and outliers
4. Classification of users. KNN method
5. How to check the representativeness of the data sample

Upcoming:

1. How to identify the correlation between events. An example based on user’s actions on the first day and their impact on retention
2. What types of data do we usually work with in mobile games
3. Practical examples of the choice of statistical criteria
4. Bootstrap method. How to identify statistical significance on a limited date. What are its advantages
5. How to reduce the date accumulation time in a/b tests