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

This query uses Google Analytics for Firebase with BigQuery to compute daily user retention. This query calculates retention for a group of users who started using an app on September 1.

To use this query:

1. Enable exporting Google Analytics data to BigQuery: https://support.google.com/firebase/answer/6318765?hl=en
2. Open BigQuery UI at: http://console.cloud.google.com/bigquery
3. Copy this query into the BigQuery query editor
4. Replace "projectId.analytics_YOUR_TABLE.events_*" in lines 9 & 26 with your analytics table
5. Set date on line 13 to match a cohort of new users of your app.
6. Adjust _TABLE_SUFFIX BETWEEN line 14 to include days immediately before and after the date your target date.
   • This will unsure all analytics events corresponding to your user cohort will be included (factoring in time zones and late arriving events)
7. Set date range on line 29 for engaged user calculation. The first day should be 1 day before cohort started using app. The last day will be last day you would like to see retention calculated for.
8. Run query

Hello. Thank you for this code. But I have a question. When I use this code I do not get exact match to retention in Firebase. I always get smaller number then I compared to firebase. Do you maybe know why? Was there a change in calculating retention in Firebase.