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.

🚧

COMING SOON

We are in the process of creating this query for you. It's quite complex and we are hoping to provide you a 30 Day retention cohort query that you can run very soon.

--COMING SOON

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);