Analytics - Code

It will take some time to learn what queries to write for your game. You'll have to learn and understand the schema, and you'll have to get a firm understanding of what data is being stored and what it means to you and your reporting needs.

This section is designed to provide you with some out-of-the-box queries that you can write that will give you a head start. These queries are designed to give you the following.

• MAU, DAU, Average Session Length
• Players Spending, Players Spending over time
• Daily Retention Report that you can use for a cohort chart

MAU, DAU & Average Session Length

MAU (Monthly Active Users), DAU (Daily Active Users) & Average Session Length are great metrics to measure the engagement of your player base. These are critical metrics needed to see if you are acquiring users and if they are staying in your game and potentially enjoying it.

For example, If you find that your average session length is decreasing over time but you are gaining plenty of users per month / day. Then you probably have something in your game that is making players not want to play any more.

📘

Query Notes:

When using PopSQL you can put -30 in the variable field. If not using PopSQL you can replace {{days}} with -30 for MAU. For DAU put 0.

--MAU DAU
select 
    count(event_id) METRIC 
from platform_session_session 
where 
    "e.firstDailySession"='true'
    and act_time >= date_add('day', {{days}}, CURRENT_TIMESTAMP);

--Average Session Length    
select 
    round(avg(cast("e.session_length_minutes" as integer)),2) average_session_length 
from platform_session_session_end 
where 
 act_time >= date_add('day', {{days}}, CURRENT_TIMESTAMP);

30 Day Retention Cohort

Now that you have engaged players, you will want to ensure they they are sticky. This means that they will come back and play your game and this report data shows you how often they come back. This also shows you if players are churning out from your game. Churn is when a player stops playing your game and you've likely lost them for good. Churn is bad, and if you want to prevent it you need to understand how well you are retaining your players.

📘

Query Notes:

When using PopSQL, you'll need the PID realm ID and a start and end date. If not using PopSQL, replace the {{pid}} field and the {{start_date}} and {{end_date}} fields with correct values.

WITH 
activities as (
 select DATE(act_date) as start, gamer_tag 
 from {{pid}}.platform_session_session
 where DATE(act_date) BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}')
 AND gamer_tag NOT IN (
   SELECT DISTINCT gamer_tag FROM {{pid}}.platform_session_install
   WHERE act_date < '{{start_date}}'
 )
),
new_users as (
  SELECT gamer_tag, date_trunc('day', MIN(DATE(act_date))) as start_time
  FROM {{pid}}.platform_session_install
  GROUP BY gamer_tag
  ORDER BY 1, 2
),
user_activities as (
  SELECT A.gamer_tag, date_diff('day', C.start_time, date_trunc('day', A.start)) AS period_number
  FROM activities A
  LEFT JOIN new_users C ON A.gamer_tag = C.gamer_tag
  GROUP BY 1, 2
  HAVING date_diff('day', C.start_time, date_trunc('day', A.start)) < 10
  ORDER BY 1, 2
),
cohort_size as (
  SELECT start_time, COUNT(1) AS num_users
  FROM new_users
  GROUP BY 1
  ORDER BY 1
),
retention_table as (
  SELECT C.start_time, A.period_number, COUNT(1) AS num_users
  FROM user_activities A
  LEFT JOIN new_users C ON A.gamer_tag = C.gamer_tag
  GROUP BY 1, 2
)
SELECT B.start_time as act_date, B.period_number, S.num_users as new_users, B.num_users as retained_users, (B.num_users/cast(S.num_users as double) * 100) as retention
FROM retention_table B
LEFT JOIN cohort_size S ON B.start_time = S.start_time
WHERE B.start_time IS NOT NULL AND B.period_number != 0
ORDER BY 1, 3

Monthly Players Spending (Total Revenue Per Month)

When you have an engaged & retained player base and you offer any sort of IAP (in app purchases) then this report will be important for you to understand how much money players are spending and how much money you are making per month. This query will show you how much your players are spending each month as an aggregated total.

📘

Query Notes:

This is a very simple way to get the total dollar amount spent across all players and all sessions during a 30 day period. When using PopSQL you can put -30 in the variable field. If not using PopSQL you can replace {{days}} with -30.

select 
    sum(cast("e.spendTotal" as integer)) SpendTotal
from platform_session_session
where 
 act_time >= date_add('day', {{days}}, CURRENT_TIMESTAMP);

30 Day - Daily Spending Report

Knowing how much money your game is making from IAP each day is highly valuable. If you monitor this over time and you start seeing a dip, this could be a sign that something is wrong in your game. It is also a great success metric to show that your game monetizes well.

📘

Query Notes:

The below query will give you any spending that was done across all sessions on a particular date. If no spending was done and there were sessions there will be a record with 0. When using PopSQL you can put -30 in the variable field. If not using PopSQL you can replace {{days}} with -30.

select 
    date(act_time) SessionDate, sum(cast("e.spendTotal" as integer)) SpendTotal
from platform_session_session
where 
 act_time >= date_add('day', {{days}}, CURRENT_TIMESTAMP)
group by date(act_time)
order by date(act_time);