Analytics - Code
Allow game maker to track player behavior [BUSI-Analytics-03]
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);
Updated 5 months ago