ToddKerpelman

A handful of BigQuery SQL queries that you can use to analyze your own Google Analytics for Firebase data. To find out more about how they work, check out our presentation from Cloud Next 2018 (Video link to be added soon)

Please note that these scripts have been updated to make sure of the new Firebase schema, which was rolled out to Analytics products in July of 2018.

Note that none of these scripts will work out of the box -- you'll need to update the values in brackets with the names of your actual datasets, and you'll most likely also need to replace the names of events and event properties with ones that are appropriate for your app.

The contents of this gist are licensed under the Apache License, version 2.0

ToddKerpelman

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name, 
    UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*7 AS one_week_micros
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE _table_suffix BETWEEN '20180731' AND '20180829'
  AND user_pseudo_id IN ( 
    -- Anybody who received 20 initial extra steps
    SELECT DISTINCT(user_pseudo_id) FROM 

ToddKerpelman

SELECT DISTINCT(user_pseudo_id) FROM 
  (SELECT user_pseudo_id,
      (SELECT value.string_value 
        FROM UNNEST(user_properties) 
        WHERE key = "initial_extra_steps") AS initial_steps
    FROM `firebase-public-project.analytics_153293282.events_*`
    WHERE _table_suffix BETWEEN '20180731' AND '20180829'
  )
WHERE initial_steps = "20"

ToddKerpelman

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name,
    UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*7 AS one_week_micros
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE _table_suffix BETWEEN '20180731' AND '20180829'
  AND user_pseudo_id IN ( 
    -- Anybody who's ever encountered a level_retry_quickplay event!
    SELECT DISTINCT(user_pseudo_id) FROM 

ToddKerpelman

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name, 
    UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*7 AS one_week_micros
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE _table_suffix BETWEEN '20180731' AND '20180829'
  AND user_pseudo_id IN (  
    SELECT DISTINCT(user_pseudo_id) FROM 
      (SELECT user_pseudo_id,
             (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = "initial_extra_steps") AS initial_steps

ToddKerpelman

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name, device.mobile_model_name,
    UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*3 AS one_week_micros   -- Note the *3 at the end here
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE _table_suffix BETWEEN '20180731' AND '20180829'
)
...rest of the query goes here

ToddKerpelman

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name, 
    device.mobile_model_name, -- This parameter is new
    UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*7 AS one_week_micros
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE _table_suffix BETWEEN '20180731' AND '20180829'
    AND device.mobile_model_name LIKE "iPad Pro%"  -- This is also new
)
... Rest of the query goes here

ToddKerpelman

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name, 
    app_info.version AS app_version,   -- This is new!
    UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*7 AS one_week_micros
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE _table_suffix BETWEEN '20180731' AND '20180829'
)

SELECT week_0_cohort / week_0_cohort AS week_0_pct,

ToddKerpelman

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name, 
    -- This value is different
    UNIX_MICROS(TIMESTAMP("2018-07-29 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*7 AS one_week_micros
  FROM `firebase-public-project.analytics_153293282.events_*`
  -- These values have changed                          
  WHERE _table_suffix BETWEEN '20180728' AND '20180827'
)
SELECT week_0_cohort / week_0_cohort AS week_0_pct,

ToddKerpelman

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name, 
    UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*7 AS one_week_micros
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE _table_suffix BETWEEN '20180731' AND '20180829'
)

SELECT week_0_cohort / week_0_cohort AS week_0_pct,
 week_1_cohort / week_0_cohort AS week_1_pct,