Standard metrics and charts queries
Below are the queries for the out-of-the-box metrics in Analytics:
Activity
| Metric name | Description | Query |
|---|---|---|
| DAU | The number of players active in your game. An active player is any player that starts a session. Daily active users (DAU) is the number of players active on each calendar day. | with __ADDITIONAL_CTES_CLAUSE__ DAUs AS ( SELECT data.event_date, COUNT(distinct data.user_id) AS DAU __GROUP_BY_SELECT_CLAUSE__ FROM QUERY_COMPOSER_VIEWS.fact_user_sessions_day data __ADDITIONAL_JOIN_TABLE__ __WHERE_CLAUSE__ GROUP BY 1 __EXTRA_GROUP_BY_CLAUSE__ ) select d.event_date, DAU __GROUP_BY_OUTER_SELECT_CLAUSE__ FROM DAUs d order by 1 |
| WAU | Weekly active users (WAU) is the number of players active in a calendar week. A calendar week is Monday to Sunday. Use this chart to see how your player activity changes week-to-week and trends over time. For example, a WAU of 7,500 for the week of June 15th means 7,500 players have been active between June 15th and June 21st. | WITH_CTES_CLAUSE SELECT event_date, COUNT(distinct user_id) as WAU GROUP_BY_SELECT_CLAUSE FROM QUERY_COMPOSER_VIEWS.fact_wau_users data ADDITIONAL_JOIN_TABLEWHERE_CLAUSE GROUP BY event_date EXTRA_GROUP_BY_CLAUSE ORDER BY event_date |
| MAU | Monthly active users (MAU) is the number of players active in a calendar month. A calendar month is the first day of the month to the last. Use this chart to see how your player activity changes month-to-month and trends over a large period of time. For example, an MAU of 35,000 for the month of July means 35,000 players have been active between July 1st and July 31st. | WITH_CTES_CLAUSE SELECT event_date, COUNT(distinct user_id) as MAU GROUP_BY_SELECT_CLAUSE FROM QUERY_COMPOSER_VIEWS.fact_mau_users data ADDITIONAL_JOIN_TABLEWHERE_CLAUSE GROUP BY event_date EXTRA_GROUP_BY_CLAUSE ORDER BY event_date |
| New users | The number of players that have launched your game for the first time, each calendar day. Use this chart to see how many new players each acquisitional channel has brought to your game. | WITH_CTES_CLAUSE SELECT event_date, COUNT(DISTINCT CASE WHEN event_date = player_start_date THEN user_id ELSE null END) AS daily_new GROUP_BY_SELECT_CLAUSE FROM QUERY_COMPOSER_VIEWS.fact_user_sessions_day data ADDITIONAL_JOIN_TABLEWHERE_CLAUSE GROUP BY event_date EXTRA_GROUP_BY_CLAUSE ORDER BY event_date |
| Session length | The amount of time that has passed between when the user starts the app, and when the user takes an action to exit the app. | with __ADDITIONAL_CTES_CLAUSE__ d AS ( SELECT data.event_date, data.user_id, data.session_id, SUM(data.total_time_ms) AS total_time_ms __GROUP_BY_SELECT_CLAUSE__ FROM QUERY_COMPOSER_VIEWS.fact_user_sessions_day data __ADDITIONAL_JOIN_TABLE__ __WHERE_CLAUSE__ GROUP BY event_date, user_id, session_id __EXTRA_GROUP_BY_CLAUSE__ ) SELECT event_date, AVG(total_time_ms) / 60000 AS session_length __GROUP_BY_OUTER_SELECT_CLAUSE__ FROM d WHERE total_time_ms/60000 BETWEEN 0.05 AND 180 GROUP BY event_date __EXTRA_GROUP_BY_CLAUSE__ ORDER BY event_date |
| Number of sessions | The count of sessions played on that day. | WITH __ADDITIONAL_CTES_CLAUSE__ d AS ( SELECT event_date, user_id, session_id, SUM(total_time_ms) AS total_time_ms __GROUP_BY_SELECT_CLAUSE__ FROM QUERY_COMPOSER_VIEWS.fact_user_sessions_day data __ADDITIONAL_JOIN_TABLE__ __WHERE_CLAUSE__ GROUP BY event_date, user_id, session_id __EXTRA_GROUP_BY_CLAUSE__ ) SELECT event_date, COUNT(DISTINCT session_id) AS Sessions __GROUP_BY_OUTER_SELECT_CLAUSE__ FROM d WHERE total_time_ms / 60000 BETWEEN 0.05 AND 180 GROUP BY event_date __EXTRA_GROUP_BY_CLAUSE__ ORDER BY event_date |
| Sessions per user | The average number of sessions per user playing on that day. | WITH __ADDITIONAL_CTES_CLAUSE__ d AS ( SELECT event_date, user_id, session_id, SUM(total_time_ms) AS total_time_ms __GROUP_BY_SELECT_CLAUSE__ FROM QUERY_COMPOSER_VIEWS.fact_user_sessions_day data __ADDITIONAL_JOIN_TABLE__ __WHERE_CLAUSE__ GROUP BY event_date, user_id, session_id __EXTRA_GROUP_BY_CLAUSE__ ), daily_count AS ( SELECT event_date, user_id, COUNT(DISTINCT session_id) as sessions __GROUP_BY_OUTER_SELECT_CLAUSE__ FROM d WHERE total_time_ms/60000 BETWEEN 0.05 AND 180 GROUP BY event_date, user_id __EXTRA_GROUP_BY_CLAUSE__ ) SELECT event_date, AVG(sessions) AS avg_sessions_per_user __GROUP_BY_OUTER_SELECT_CLAUSE__ FROM daily_count GROUP BY event_date __EXTRA_GROUP_BY_CLAUSE__ ORDER BY event_date |
| Total daily play time | The cumulative playing time of all people playing on a given day. | WITH __ADDITIONAL_CTES_CLAUSE__ d AS ( SELECT event_date, user_id, session_id, SUM(total_time_ms) AS total_time_ms __GROUP_BY_SELECT_CLAUSE__ FROM QUERY_COMPOSER_VIEWS.fact_user_sessions_day data __ADDITIONAL_JOIN_TABLE__ __WHERE_CLAUSE__ GROUP BY event_date, user_id, session_id __EXTRA_GROUP_BY_CLAUSE__ ) SELECT event_date, SUM(total_time_ms / 60000) AS playtime __GROUP_BY_OUTER_SELECT_CLAUSE__ FROM d WHERE total_time_ms / 60000 BETWEEN 0.05 AND 180 GROUP BY event_date __EXTRA_GROUP_BY_CLAUSE__ ORDER BY event_date |
| Daily play time per daily active user | The average playing time of users playing on that day. | with __ADDITIONAL_CTES_CLAUSE__ daily_playtime as ( SELECT event_date, user_id, SUM(total_time_ms)/60000 AS total_time_ms __GROUP_BY_SELECT_CLAUSE__ FROM QUERY_COMPOSER_VIEWS.fact_user_sessions_day data __ADDITIONAL_JOIN_TABLE__ __WHERE_CLAUSE__ GROUP BY event_date, user_id __EXTRA_GROUP_BY_CLAUSE__ HAVING SUM(total_time_ms)/60000 BETWEEN 0.05 AND 180 ) SELECT event_date, AVG(total_time_ms) AS avg_playtime_per_user __GROUP_BY_OUTER_SELECT_CLAUSE__ FROM daily_playtime GROUP BY event_date __EXTRA_GROUP_BY_CLAUSE__ ORDER BY event_date |
Retention
| Metric name | Description | Query |
|---|---|---|
| Player retention | The average percentage of players who play your game each day since installing. For example, a day 7 (D7) retention of 20% means on average, 20% of your players are playing on day 7 after installing and 80% did not play on that day. Any player that sends an event on a calendar day is considered active on that day. Use this chart to see how well your game retains its players and how many days it takes players to stop playing, on average. A gradual curve is likely favourable whilst a steep drop shows players are quickly stopping playing. | WITH datapoints AS ( select row_number() over (order by seq4()) AS dayssinceinstall from table(generator(rowcount => 30)) ), userRetention AS ( SELECT Datediff('day', player_start_date, event_date) AS daysSinceInstall, Count(DISTINCT user_id) AS actives FROM QUERY_COMPOSER_VIEWS.fact_user_sessions_day WHERE PLAYER_START_DATE between AGGREGATE_PERIOD_START and AGGREGATE_PERIOD_END AND Datediff('day', player_start_date, event_date) BETWEEN 0 AND 30 EXTRA_WHERE_CLAUSE GROUP BY 1 ), ret AS ( SELECT *, Max(actives) over () AS installs, actives / Max(actives) over () AS retention FROM userRetention ) SELECT datapoints.dayssinceinstall, ret.retention FROM datapoints left JOIN ret ON datapoints.dayssinceinstall = ret.dayssinceinstall ORDER BY 1 |
| New player retention | This chart shows the day 1, 7, 14, and 30 retention percentages for each install date. For example, for all players that installed on the 15th March you can see what percentage of those players were playing on 1, 7, 14, and 30 calendar days after installing. Since this is measuring historic retention, it takes 30 days for the chart to be complete for an install date. Today's data is still in flux so the latest data points takes some time to settle. In most cases this should be settled by 00:00 UTC. Use this chart to see how your retention trends over time. Dips and peaks might suggest players reacting to something in your game or a change in your player-base. | WITH dates AS ( select -1 + row_number() over(order by 0) i, start_date + i event_date from (select DATE_WINDOW_START::date start_date, DATE_WINDOW_END::date end_date) join table(generator(rowcount => 10000 )) x qualify i < 1 + end_date - start_date ), userRetention as( select user_id ,player_start_date ,max(case when event_date >= current_date then null when datediff('day',player_start_date,event_date) = 1 then 1 when datediff('day',player_start_date, current_date) > 1 then 0 end) as d1 ,max(case when event_date >= current_date then null when datediff('day',player_start_date,event_date) = 7 then 1 when datediff('day',player_start_date,current_date) > 7 then 0 end) as d7 ,max(case when event_date >= current_date then null when datediff('day',player_start_date,event_date) = 14 then 1 when datediff('day',player_start_date,current_date) > 14 then 0 end) as d14 ,max(case when event_date >= current_date then null when datediff('day',player_start_date,event_date) = 30 then 1 when datediff('day',player_start_date,current_date) > 30 then 0 end) as d30 from QUERY_COMPOSER_VIEWS.fact_user_sessions_day where player_start_date between AGGREGATE_PERIOD_START and AGGREGATE_PERIOD_ENDEXTRA_WHERE_CLAUSE group by 1,2 ) select dates.event_date as player_start_date , zeroifnull(avg(d1)) as D1RETENTION , zeroifnull(avg(d7)) as D7RETENTION , zeroifnull(avg(d14)) as D14RETENTION , zeroifnull(avg(d30)) as D30RETENTION from dates left JOIN userRetention ON dates.event_date = userRetention.player_start_date group by 1 order by 1 |
| Day 1 Retention | The percentage of users who returned to your game one day after playing for the first time. | WITH __ADDITIONAL_CTES_CLAUSE__ d AS ( SELECT player_start_date, datediff('day', player_start_date, event_date) AS daysSinceInstall, COUNT(distinct user_id) AS actives __GROUP_BY_SELECT_CLAUSE__ FROM QUERY_COMPOSER_VIEWS.fact_user_sessions_day data __ADDITIONAL_JOIN_TABLE__ WHERE datediff('day', player_start_date, event_date) BETWEEN 0 AND 1 AND PLAYER_START_DATE between __AGGREGATE_PERIOD_START__ and __AGGREGATE_PERIOD_END__ __EXTRA_WHERE_CLAUSE__ GROUP BY player_start_date, daysSinceInstall __EXTRA_GROUP_BY_CLAUSE__ ), ret AS ( SELECT *, MAX(actives) OVER (partition by player_start_date __EXTRA_GROUP_BY_CLAUSE__) AS installs, actives / MAX(actives) OVER (partition by player_start_date __EXTRA_GROUP_BY_CLAUSE__) AS retention FROM d ) SELECT player_start_date, retention __GROUP_BY_OUTER_SELECT_CLAUSE__ FROM ret WHERE daysSinceInstall = 1 GROUP BY player_start_date, retention __EXTRA_GROUP_BY_CLAUSE__ ORDER BY player_start_date, retention |
| Day 7 Retention | The percentage of users who returned to your game seven days after playing for the first time. | WITH __ADDITIONAL_CTES_CLAUSE__ d AS ( SELECT player_start_date, datediff('day', player_start_date, event_date) AS daysSinceInstall, COUNT(distinct user_id) AS actives __GROUP_BY_SELECT_CLAUSE__ FROM QUERY_COMPOSER_VIEWS.fact_user_sessions_day data __ADDITIONAL_JOIN_TABLE__ WHERE datediff('day', player_start_date, event_date) IN (0,7) AND PLAYER_START_DATE between __AGGREGATE_PERIOD_START__ and __AGGREGATE_PERIOD_END__ __EXTRA_WHERE_CLAUSE__ GROUP BY player_start_date, daysSinceInstall __EXTRA_GROUP_BY_CLAUSE__ ), ret AS ( SELECT *, MAX(actives) OVER (partition by player_start_date __EXTRA_GROUP_BY_CLAUSE__) AS installs, actives / MAX(actives) OVER (partition by player_start_date __EXTRA_GROUP_BY_CLAUSE__) AS retention FROM d ) SELECT player_start_date, retention __GROUP_BY_OUTER_SELECT_CLAUSE__ FROM ret WHERE daysSinceInstall = 7 GROUP BY player_start_date, retention __EXTRA_GROUP_BY_CLAUSE__ ORDER BY player_start_date, retention |
| Day 30 Retention | The percentage of users who returned to your game thirty days after playing for the first time. | WITH __ADDITIONAL_CTES_CLAUSE__ d AS ( SELECT player_start_date, datediff('day', player_start_date, event_date) AS daysSinceInstall, COUNT(distinct user_id) AS actives __GROUP_BY_SELECT_CLAUSE__ FROM QUERY_COMPOSER_VIEWS.fact_user_sessions_day data __ADDITIONAL_JOIN_TABLE__ WHERE datediff('day', player_start_date, event_date) IN (0, 30) AND PLAYER_START_DATE between __AGGREGATE_PERIOD_START__ and __AGGREGATE_PERIOD_END__ __EXTRA_WHERE_CLAUSE__ GROUP BY player_start_date, daysSinceInstall __EXTRA_GROUP_BY_CLAUSE__ ), ret AS ( SELECT *, MAX(actives) OVER (partition by player_start_date __EXTRA_GROUP_BY_CLAUSE__) AS installs, actives / MAX(actives) OVER (partition by player_start_date __EXTRA_GROUP_BY_CLAUSE__) AS retention FROM d ) SELECT player_start_date, retention __GROUP_BY_OUTER_SELECT_CLAUSE__ FROM ret WHERE daysSinceInstall = 30 GROUP BY player_start_date, retention __EXTRA_GROUP_BY_CLAUSE__ ORDER BY player_start_date, retention |
Revenue
| Metric name | Description | Query |
|---|---|---|
| Daily revenue | The revenue generated by your game each calendar day. This is gross revenue and does not include any deductions from app stores. Use this chart to see how much revenue is being generated by players purchasing IAPs. | WITH dates AS ( select -1 + row_number() over(order by 0) i, start_date + i event_date from (select DATE_WINDOW_START::date start_date, DATE_WINDOW_END::date end_date) join table(generator(rowcount => 10000 )) x qualify i < 1 + end_date - start_date ), data as( select event_date , sum(revenue / 100) AS IAPs from QUERY_COMPOSER_VIEWS.FACT_USER_SESSIONS_DAY WHERE_CLAUSE group by 1 ) select dates.event_date AS event_date, zeroifnull(data.IAPs) AS IAPs from dates left JOIN data ON dates.event_date= data.event_date order by 1 |
| Weekly revenue | The revenue generated by your game each calendar week. This is gross revenue and does not include any deductions from app stores. Use this chart to see how much revenue is being generated by players purchasing IAPs. Use this chart to see trends over a longer period of time than days. | select date_trunc('week',event_date) as week , sum(revenue / 100) as IAP from QUERY_COMPOSER_VIEWS.fact_user_sessions_day where event_date between date_trunc('week',AGGREGATE_PERIOD_START ::date ) and last_day( AGGREGATE_PERIOD_END ::date , 'week' ) EXTRA_WHERE_CLAUSE group by 1 order by 1 |
| Monthly revenue | The revenue generated by your game each calendar month. This is gross revenue and does not include any deductions from app stores. Use this chart to see how much revenue is being generated by players purchasing IAPs. Use this chart to see trends over a long period of time. | select date_trunc('month',event_date) as month , sum(revenue / 100) as IAP from QUERY_COMPOSER_VIEWS.fact_user_sessions_day where event_date between date_trunc('month',AGGREGATE_PERIOD_START ::date ) and last_day( AGGREGATE_PERIOD_END ::date , 'month' ) EXTRA_WHERE_CLAUSE group by 1 order by 1 |
| Average revenue per DAU | The average revenue generated by active players each calendar day. This chart shows, for each player that is active in your game, how much revenue they generate on average. For example, an IAP ARPDAU of $0.20 on May 7th means active players, on average, generated $0.20 of gross revenue by purchasing IAPs. If you see dips or peaks in this chart, it suggests something has changed in your game or your player-base. The average is calculated by taking the total IAP revenue for each day and dividing it by the total number of active players. | WITH dates AS ( select -1 + row_number() over(order by 0) i, start_date + i event_date from (select DATE_WINDOW_START::date start_date, DATE_WINDOW_END::date end_date) join table(generator(rowcount => 10000 )) x qualify i < 1 + end_date - start_date ), data as( select event_date , sum(revenue / 100) / count(distinct user_id) as IAP from QUERY_COMPOSER_VIEWS.fact_user_sessions_day WHERE_CLAUSE group by 1 ) select dates.event_date AS event_date, zeroifnull(data.IAP) AS IAP from dates left JOIN data ON dates.event_date= data.event_date order by 1 |
| Daily conversion | The average in-app purchase (IAP) conversion for each calendar day. This chart shows the percentage of active players that purchase an IAP on each calendar day. For example, a conversion rate of 3% on June 2nd means 3% of your total active players on that day purchased an IAP. | select event_date , (count(distinct case when revenue > 0 then user_id end) / count(distinct user_id)) as Conversion from QUERY_COMPOSER_VIEWS.fact_user_sessions_day WHERE_CLAUSE group by 1 order by 1 |