Calculating ARPU using SQL

I’ve been studying SQL for a minute (actually more like 20 hours).The post is more for myself to share what I’ve been up to. I see SQL as important for product managers. This is because you can query the database directly and get more access to metrics.

Lately, I’ve been using Codecademy. I’ve done 3 of the courses on there so far. The latest one is “Analyzing business metrics” which is a course with Periscope which makes dashboard tools. Its pretty good, though getting more complex and confusing as you go deeper in the course. They give you a problem with some SQL which you have to complete. This is the latest one I’ve done which is calculating ARPU (average revenue per user).

If you think about it objectively, its simply:

Divide daily revenue by daily number of players.

This will give you the average revenue per user per day.

Here are the two tables and their schema:

purchases 
id int
user_id int
price real
refunded_at text
created_at text
gameplays
id int
user_id int
created_at text
platform text

Some things I had to take into account is

* Remove refunds to calculate revenue
* One of the tables had total gameplays with user IDs. So to get the daily number of players you have to calculate the unique number of players each day. As a player could play a game 5 times, but its still only one player.
* We need to create a third table with 2 columns: date, ARPU

——–

with daily_revenue as (
  select
    date(created_at) as dt,
    round(sum(price), 2) as rev
  from purchases
  where refunded_at is null
  group by 1
),
daily_players as (
  select
    date(created_at) as dt,
    count(distinct user_id) as players
  from gameplays
  group by 1
)
select
    daily_revenue.dt,
  daily_revenue.rev / daily_players.players
from daily_revenue
    join daily_players using (dt);

——–

I’ll post some more as I do more of the course.

I’m out like ARPU,
Matt Ho

One thought on “Calculating ARPU using SQL

  1. I see quite a companies asking their PMs to have SQL skills (for example Freelancer). You do mention you also see SQL as being important for PMs.

    In my day-to-day role I do have access to the tools (Heap for example) or directly from our admin site (we can export reports and then I just use pivot tables) I need to get to the data I want, without the need for me to use any SQL.

    Do you think it’s mostly a large organisation issue (not giving access to their employees to the right tools / third-party services) or are there limitations you’ve encountered that pushed you to go the SQL way?

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.