You made a great game/app and it has started giving you revenue. You are able to consider buying users, and it is very important that to get the right player lifetime value (LTV) first so you can have an idea on how much cost per install (CPI) you can afford. This article will explain where to get the raw data and how to calculate the LTV in a spreadsheet.

TLDR; Put your numbers and calculate the LTV in this spreadsheet.

Note that we use separated sheets for iOS and Android, so we can get the LTV for each platform individually.

The Formula

There’s a lot of different ways to calculate LTV and it can turn out to be a complicated mathematic formula, but we found the easiest way that gives the accurate result as follow: multiplying the average revenue per daily active user (ARPDAU) with the average player retended days.

LTV = ARPDAU x RetendedDays

To get the ARPDAU, we simply need DAU and the revenue.

Getting DAU

We use Data Explorer in Unity Analytics to get the DAU of the month and then average them, you can export to a CSV and paste them back into the LTV calculation spreadsheet.

Getting Revenue

Depends on your app/games, you may have revenue from IAP and Ads.

For iOS, you can get the proceed revenue (after 30% profit share) in App Store Connect → App Analytics. You can export the clipboard and paste back to the LTV spreadsheet.

For Android, revenue is located in Google Play Console → Financial reports → Revenue, note that the revenue in Google Play is pre-revenue, so we will have to multiply 70% in the spreadsheet.

Finally, for the Ads revenue, it depends on the Ads platforms you are using. For example, in Yodo1 MAS:

After summing up the revenue, we again average it and get the revenue per day.

Calculating ARPDAU

Now, ARPDAU is simply dividing the revenue and DAU:

ARPDAU = Revenue (Per Day) / DAU

Getting Retended Days

In the spreadsheet example, we fit the retention data (1-day, 7-days and 30-days retentions) into a formula to simulate the retention for a new player:

(Retention on Day X) = a * (Day X) ^ b

After getting the a, b coefficients and fitting the simulation formula, we list down the all the retention of ONE new player, i.e. how many left on 2nd day, 3rd day, and so on till the 180th day. Then, we sum all retention numbers (and divided by 100 because we used percentage as retention before) and that will be the average reteneded days of the players.

For most causal games, the retended days should be range around 2~5 days.

Result

Finally, the LTV can be obtained by multiplying the ARPDAU with the average player retended days.

Source: CLV by Modeling Retention Function with a Spreadsheet