Sql to sum up weekly retention with Firebase Analytics

script

WITH start AS (
  SELECT 
    user_pseudo_id,
    EXTRACT(DATE FROM TIMESTAMP_MICROS(event_timestamp)) AS start_date
  FROM
    `your.database.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(DATE_TRUNC(CURRENT_DATE(), WEEK), INTERVAL 5 WEEK)) AND FORMAT_DATE("%Y%m%d", DATE_SUB(DATE_TRUNC(CURRENT_DATE(), WEEK), INTERVAL 1 DAY))
    AND event_name = "your event name"
    AND EXTRACT(DATE FROM TIMESTAMP_MICROS(event_timestamp))  BETWEEN DATE_SUB(DATE_TRUNC(CURRENT_DATE(), WEEK), INTERVAL 5 WEEK) AND DATE_SUB(DATE_TRUNC(CURRENT_DATE(), WEEK), INTERVAL 1 DAY)
  GROUP BY
    user_pseudo_id,
    start_date
),
usedate AS (
  SELECT 
    user_pseudo_id,
    EXTRACT(DATE FROM TIMESTAMP_MICROS(event_timestamp)) AS use_date
  FROM
    `your.database.events_*`, UNNEST(user_properties) AS params
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(DATE_TRUNC(CURRENT_DATE(), WEEK), INTERVAL 5 WEEK)) AND FORMAT_DATE("%Y%m%d", DATE_SUB(DATE_TRUNC(CURRENT_DATE(), WEEK), INTERVAL 1 DAY))
    AND params.key = "your user property"
  GROUP BY
    user_pseudo_id,
    use_date
),
retention AS (
  SELECT
    start.user_pseudo_id,
    start.start_date,
    IFNULL(DATE_DIFF(DATE_TRUNC(usedate.use_date, WEEK), DATE_TRUNC(start.start_date, WEEK), WEEK), 0) AS retention_week
  FROM
    start
    INNER JOIN usedate ON start.user_pseudo_id = usedate.user_pseudo_id AND usedate.use_date >= start.start_date
  GROUP BY
    start.user_pseudo_id,
    start.start_date,
    retention_week
),
retention_sum AS (
  SELECT
    DATE_DIFF(DATE_SUB(DATE_TRUNC(CURRENT_DATE(), WEEK), INTERVAL 1 DAY), DATE_TRUNC(retention.start_date, WEEK), WEEK) as start_week,
    retention.retention_week,
    count(retention.user_pseudo_id) AS uu
  FROM
    retention
  GROUP BY
    start_week,
    retention.retention_week
)
 
SELECT
  FORMAT_DATE("%m/%d", DATE_SUB(DATE_TRUNC(CURRENT_DATE(), WEEK), INTERVAL start_week + 1 WEEK)) AS from_day,
  FORMAT_DATE("%m/%d", DATE_SUB(DATE_SUB(DATE_TRUNC(CURRENT_DATE(), WEEK), INTERVAL start_week WEEK), INTERVAL 1 DAY)) AS to_day,
  SUM(CASE WHEN retention_week = 0 THEN uu ELSE NULL END) AS startuu,
  SUM(CASE WHEN retention_week = 0 THEN uu ELSE NULL END) / SUM(CASE WHEN retention_week = 0 THEN uu ELSE NULL END) AS zero_week,
  SUM(CASE WHEN retention_week = 1 THEN uu ELSE NULL END) / SUM(CASE WHEN retention_week = 0 THEN uu ELSE NULL END) AS one_week,
  SUM(CASE WHEN retention_week = 2 THEN uu ELSE NULL END) / SUM(CASE WHEN retention_week = 0 THEN uu ELSE NULL END) AS two_week,
  SUM(CASE WHEN retention_week = 3 THEN uu ELSE NULL END) / SUM(CASE WHEN retention_week = 0 THEN uu ELSE NULL END) AS three_week,
  SUM(CASE WHEN retention_week = 4 THEN uu ELSE NULL END) / SUM(CASE WHEN retention_week = 0 THEN uu ELSE NULL END) AS fourth_week
FROM
  retention_sum
GROUP BY
  start_week
ORDER BY
  start_week ASC