[BigQuery + Google Analytics for Firebase] Daily N-Day User Retention of September 1 Cohort

retention.sql

#standardSQL
####################################################################
# PART 1: Cohort of New Users starting on SEPT 1
####################################################################
WITH 
new_user_cohort AS (  
  SELECT DISTINCT user_pseudo_id as new_user_id
  FROM 
    `projectId.analytics_YOUR_TABLE.events_*`
  WHERE
   event_name = 'first_open' AND
   #geo.country = 'France' AND
   FORMAT_TIMESTAMP("%Y%m%d", TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), DAY, "Etc/GMT+8")) = '20180901' AND
  _TABLE_SUFFIX BETWEEN '20180830' AND '20180902'),
num_new_users AS (
  SELECT count(*) as num_users_in_cohort  FROM new_user_cohort
),

####################################################################
# PART 2: Engaged users from Sept 1 cohort
####################################################################
engaged_user_by_day AS (  
  SELECT 
    FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), DAY, "Etc/GMT+8")) as event_day, COUNT (DISTINCT user_pseudo_id) as num_engaged_users
  FROM 
    `projectId.analytics_YOUR_TABLE.events_*` INNER JOIN new_user_cohort on new_user_id = user_pseudo_id 
  WHERE
    event_name = 'user_engagement' AND
    _TABLE_SUFFIX BETWEEN '20180830' AND '20180907'
  GROUP BY (event_day)
)

####################################################################
# PART 3: Daily Retention = [Engaged Users / Total Users]
####################################################################
SELECT event_day, num_engaged_users, num_users_in_cohort, ROUND((num_engaged_users / num_users_in_cohort), 3) as retention_rate
  FROM engaged_user_by_day CROSS JOIN num_new_users
ORDER BY (event_day)