Periscope Data
The world’s fastest visualization platform for data analysts.

The High-Performance SQL Blog

Game Revenue Metrics — What Matters, and When

Game Revenue Metrics — What Matters, and When
August 13, 2015

You’ve put a ton of work into your game. Your engaging story, carefully balanced bosses, and beautiful art are paying off and bringing in revenue.

Now your money in the bank is growing, how do you measure success? In a previous game metrics post, we discussed the different measures of retention. In this one, we will turn our critical eye to the common revenue metrics of the free-to-play game industry.

Average Revenue per User

Average Revenue per User (ARPU) is one of the most popular revenue metrics. Not only is it easy to calculate, it combines two of the most important pieces of information to tell you how much you can expect to earn from each new user.

How to Calculate ARPU

This metric is calculated as total revenue divided by total users. To calculate your users, simply sum up the number of installs in your install table:

select count(1) as count
from installs

Next, we calculate revenue:

select sum(price) as sum
from purchases

We can then combine these two queries, and divide revenue by installs to get ARPU:

select 
  revenue.sum::float/total_installs.count::decimal(8,2) as arpu
from 
  (select sum(price) as sum
  from purchases) as revenue
  ,
  (select count(1) as count
  from installs) as total_installs

If we have a rolled-up users table, we can even calculate ARPU by install cohort:

select
  install_date
  , sum(total_spent)/count(1)
from users
group by 1

If you do not have a users table, we can do some easy joining to get the same result:

select
  date(installs.created_at) as date 
  , sum(purchases.price)/count(distinct installs.user_id) as arpu
from installs left outer join purchases
  on installs.user_id = purchases.user_id
group by 1

Caveats

Due to it’s simplicity, there are caveats to relying too much on ARPU as a metric.

The biggest of these is timing. ARPU may tell you how much you can expect from your users, but it does not tell you when you can expect it. Also, if you do not calculate ARPU by cohort, you will pull down your average by including new users who haven’t had a chance to spend much money.

ARPU by install cohort will show a natural downward trend due to more recent users not having as much time to spend money in the game. Keep in mind when looking at ARPU by install cohort is that user quality varies significantly by acquisition source.

Average Revenue per Paying User

Average Revenue per Paying User (ARPPU) gives you the average spend across just paying users, as opposed to all users with ARPU. If you have a good conversion rate, you can get by with a lower ARPPU since it’s easier to get users to buy again than buy for the first time.

How to Calculate ARPPU

Calculating ARPPU is easier than calculating ARPU. You only need your purchases table, and away you go:

select
  sum(price)/count(distinct user_id) as arppu
from purchases

If you want to calculate ARPPU by install cohort, it’s an easy change from our ARPU by install cohort query above. Instead of a left outer join, we use an inner join:

select
  date(installs.created_at) as date 
  , sum(purchases.price)/count(distinct installs.user_id) as arpu
from installs join purchases
  on installs.user_id = purchases.user_id
group by 1

Caveats

The total revenue per paying player is not normally distributed. It is almost certain your cheapest package will be your median. For a more granular picture of your packages, look at what portion of revenue comes from “whales” vs. other types of purchasers.

Conversion Rate

While there are many ways to define conversion rate, we define it in this post as the percentage of users who have made an in-game purchase. This is an important metric for determining how much you can spend acquiring each user.

How to Calculate Conversion Rate

Conversion rate is calculated as the number of players who make a purchase in your game, divided by the number of total players.

To calculate the conversion rate for all players, we can re-use the total player calculation above, combine it with a similar calculation for total payers, and divide.

select 
  purchasers.count::float/total_installs.count::decimal(8,2
    as conversion
from 
  (select count(distinct user.id) as count
from purchases) as purchasers
,
(select count(1) as count
from installs) as total_installs

If you want to calculate conversion rate by install cohort, we can make some small changes to the ARPPU calculation to count users instead of summing revenue:

select
  date(installs.created_at) as date 
  , count(distinct purchases.user_id) / 
      count(distinct installs.user_id) as conversion
from installs join purchases
  on installs.user_id = purchases.user_id
group by 1

Caveats

Conversion rate does not give you a sense of when players convert or how much they are likely to spend when they do.

Average Revenue per Daily Active User

Average Revenue per Daily Active User (ARPDAU) tells you if users are spending more in your game today over yesterday or even a week ago. This is particularly useful if you are running an event or sale and want to see how much it boosts spending.

How to Calculate ARPDAU

ARPDAU is calculated as the daily revenue divided by the number of daily active users. This query is pretty straightforward:

First we get the number of active users per day:

select
  date(created_at) as date
  , count(distinct user_id) as count
from gameplays
group by 1

Next we get the daily revenue:

select 
  date(created_at) as date 
  , sum(price) as sum
from purchases
group by 1

Finally, we put the two pieces together:

select 
  dau.date
  , revenue.sum/dau.count as arpdau
from 
  (select
    date(created_at) as date
  , count(distinct user_id) as count
  from gameplays
  group by 1) dau
left outer join
  (select 
  date(created_at) as date 
  , sum(price) as sum
  from purchases
  group by 1) revenue
  on dau.date = revenue.date

We use a left outer join in the query in case there is a day without revenue.

Caveats

ARPDAU is one of the most convoluted game metrics. It hides layers of intricacies under a seemingly simple and straightforward calculation. It does not tell you that revenue for any given day is impacted by many factors such as seasonality, in-game sales, or the progress players have made in the game.

Twists to Existing Calculations

There are many modifications to the metrics above that can help you answer additional questions. Limiting the metric to a user’s first 1, 7, 14 or 30 days of gameplay lets you compare more fairly across install cohorts, as earlier players have not had much time to make purchases.

Splitting your data out by new and repeat purchases gives you more clarity on what type of spenders you are attracting. For example, if you are running a sale, you’ll want to know whether it incentivizes players to convert, or attracts repeat purchases from existing payers.

Cumulative ARPU by days in game will let you know when you can expect players to start paying, and when you can expect to see your hard work start to pay off.

ARPDAU by progression tells you how spending behavior changes as they engage more with your game. The most common ways to measure progression are by mission, time in game, or sessions.

Now that you know how to query your SQL database for popular revenue metrics, you can use them to get a high-level view of your game’s revenue.

Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?