Skip to content

Instantly share code, notes, and snippets.

@Osiyuks

Osiyuks/Dynamic Bracket-dependent Return User Retention in BigQuery Firebase Project

Last active October 8, 2020 14:09
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Osiyuks/66196b928d8578246624867c8a3b4439 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Osiyuks/66196b928d8578246624867c8a3b4439 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Dynamic Bracket-dependent Return User Retention in BigQuery Firebase Project
-- Source: https://t.me/BigQuery
-- Сalculation example bracket-dependent return retention (https://applift.com/blog/user-retention)
-- weekly (1 week = day2-8, 2 week = day9-15 ...)
WITH download AS (
SELECT
user_pseudo_id, app_info.version,
EXTRACT (DATE FROM (SELECT TIMESTAMP_MICROS(event_timestamp))) AS day_0
FROM `your_project.your_table.events_*`
where _TABLE_SUFFIX between FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -360 DAY))
and FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
and event_name = 'first_open'
group by 1,2,3
), engagement AS (
SELECT
user_pseudo_id,
EXTRACT (DATE FROM (SELECT TIMESTAMP_MICROS(event_timestamp))) AS engage
FROM `your_project.your_table.events_*`
where _TABLE_SUFFIX between FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -360 DAY))
and FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
-- and event_name = 'engagement_name'
group by 1,2
), f as (
SELECT * from download
left join engagement using (user_pseudo_id)
)
SELECT version, CEIL(IEEE_DIVIDE(DATE_DIFF(engage, day_0, DAY),7)) as week, count(distinct user_pseudo_id) as users
FROM f
where DATE_DIFF(engage, day_0, DAY)>-1
group by 1,2
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment