Player Activity & Retention

Retention

WITH install_data AS (  
  SELECT
    user_id,
    install_ts as install_date,
    checkpoint as snapshot_date,
    DATE_DIFF(checkpoint, install_ts, DAY) as days_since_install
  FROM 
    `live_publicdemo_35f6889f_checkpoints.player_checkpoint`
  WHERE
    game_id = 237761
    AND checkpoint →= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    AND install_ts →= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
),
returning_users AS
  (
  SELECT
    install_date,
    COUNT(DISTINCT user_id) as number_of_installs,
    COUNT(DISTINCT IF(days_since_install = 1, user_id, NULL)) as returning_d1,
    COUNT(DISTINCT IF(days_since_install = 2, user_id, NULL)) as returning_d2,
    COUNT(DISTINCT IF(days_since_install = 3, user_id, NULL)) as returning_d3,
    COUNT(DISTINCT IF(days_since_install = 4, user_id, NULL)) as returning_d4,
    COUNT(DISTINCT IF(days_since_install = 5, user_id, NULL)) as returning_d5,
    COUNT(DISTINCT IF(days_since_install = 6, user_id, NULL)) as returning_d6,
    COUNT(DISTINCT IF(days_since_install = 7, user_id, NULL)) as returning_d7,
  FROM
    install_data
  GROUP BY 1
)
SELECT 
  install_date,
  number_of_installs,
  SAFE_DIVIDE(returning_d1, number_of_installs) as retention_d1,
  SAFE_DIVIDE(returning_d2, number_of_installs) as retention_d2,
  SAFE_DIVIDE(returning_d3, number_of_installs) as retention_d3,
  SAFE_DIVIDE(returning_d4, number_of_installs) as retention_d4,
  SAFE_DIVIDE(returning_d5, number_of_installs) as retention_d5,
  SAFE_DIVIDE(returning_d6, number_of_installs) as retention_d6,
  SAFE_DIVIDE(returning_d7, number_of_installs) as retention_d7,
FROM 
  returning_users
ORDER BY 1

Ad views per day

SELECT
  DATE(checkpoint) AS date,
  SUM(ad_impressions) AS total_ad_views
FROM
  `live_publicdemo_35f6889f_checkpoints.adactivity_checkpoint`
WHERE
  game_id = 237761
  AND checkpoint = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) -- only yesterday's data
GROUP BY
  date
ORDER BY
  date DESC;

Most popular hours to play

SELECT
  TIMESTAMP_TRUNC(event_arrival_ts, HOUR) AS hour,
  SUM(publisher_revenue_usd_cents) AS revenue_usd
FROM
  `live_publicdemo_35f6889f_events.impression_event`
WHERE
  event_arrival_ts →= TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 1 DAY)
  AND event_arrival_ts < TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)
  AND game_id = 237761
GROUP BY
  hour
ORDER BY
  hour;