Skip to content

Instantly share code, notes, and snippets.

@sbrissenden
Last active May 31, 2022 00:51
Show Gist options
  • Save sbrissenden/cab9bd3a043f1879ded605cba5005457 to your computer and use it in GitHub Desktop.
Save sbrissenden/cab9bd3a043f1879ded605cba5005457 to your computer and use it in GitHub Desktop.
[BigQuery + Google Analytics for Firebase] Daily N-Day User Retention of September 1 Cohort
#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)
@sbrissenden
Copy link
Author

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

@Klemen-V
Copy link

Klemen-V commented Nov 3, 2021

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment