Skip to content

Instantly share code, notes, and snippets.

@seyhunak
Forked from sbrissenden/retention.sql
Created August 31, 2021 19:23
Show Gist options
  • Save seyhunak/f9b4a388c16037179364e1d4db8ff6c2 to your computer and use it in GitHub Desktop.
Save seyhunak/f9b4a388c16037179364e1d4db8ff6c2 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment