How to measure your userbase’s net worth and LTV – in SQL

I’m following up here on my previous posts about having game-centric metrics. With this query you should be able to have in one same data output all you need to assess your userbase’s “net worth“. You’ll also be able to track the LTV of your active users (which might help change your perspective is this is something you haven’t done before).

The final output you are aiming for here is something that is aggregated at the level of activity date. You want to be tracking at the same time – and for every day your title is active

1. Your title’s lifetime and cumulative totals. To that date, how much revenue has been generated, and how many users have installed your game?
2. How much of those lifetime totals are coming from users active on that date. Of your lifetime installs to that point, how many are active on a given date – and how much of your lifetime revenue is coming from those active users?

You will need to have the same output as below.  The idea is that you track cumulative totals on a given date – you then need to identify what part of those lifetime total are attributed to users active on that date (I’ve identified them as “0” not active and “1” active)

So if you look at your data for April 1st, out of 10,000,000 installs to that point, 100,000 are active. And your game has generated $10,000,000 to that point. $8.75 million has come from users not active in your game on that date, and $1.25 million has come from your 100,000 users active on April 1st.

Also, once you have this input, you have all you need to determine your active user’s LTV: on April 1st it’s $1,250,000/100,000 = $12.50

[While the data above is fictitious, if your game has been active for a year or two, this is ballpark what you might be seeing – if you’re with a mid or hardcore game it can easily be higher]

The first part of the query involves getting data for each day the title is active. Specifically how many installs could have been active on a given day. I’m not going to explain in details here and will refer to my previous post for that one.

What you then want is to identify for each of those days where a user could have potentially been active:

1. Was that user in fact active or not?
2. What was that user’s LTV on that day (regardless if s/he was active on that date)?

Your output will look something like this:

So you want to left join what you had in step 1 with your activity table and your revenue table. You then determine for each potential activity date if the user was active and what his/her LTV was.

Once you have this intermediary status, you just need to group everything by activity date and activity status. By doing this you are dropping the user-centric perspective to focus only on the game’s performance.

The above query is the most barebones way to get your userbase’s net worth. Below is what I consider the “best practice” version of the query. You want to be tracking country and platform. Also, if you add fields to track your customers (and measure your customer concentration), your daily installs, daily IAP and daily revenue this provides you with a very complete overview that covers all KPIs to assess your live game’s performance.

Closing side notes:

1. It’s one thing to have the output, it’s another to shape it into something visualizable (I’m choosing to believe that’s a word) and usable. The above data source will require some manipulation to visualize the data. I’ve put this together with the expectation I’ll ultimately be using Tableau. You might need to tweak the query to adapt to your visualization tool.
2. This can be a pretty heavy query – depending on how big your userbase and/or how long your game has been live. One easy way to make things more manageable is to switch your focus from days to weeks or months. To do that use date_trunc (or the equivalent for you). Keep in mind that in that case you are no longer looking at daily unique actives, or arpdau but then monthly unique actives and arpmau. If your game has been live for years and you’ve had a lot of installs looking at the evolution of your game’s performance per week or month rather than days might actualy make more sense anyways. Doing that would help you filter out a lot of noise (and you can always go dig deeper into a given month if/when you notice something out of the ordinary).