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
Due to its 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
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.
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
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.
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.