BigQuery Crashlytics - Crash free users / sessions

Question

I've linked my firebase crashlytics data to bigquery and setup the data studio templates provided by google. A lot of great data in there except the most important metrics required for my dashboard: crash free users and crash free sessions as a percentage.

Nothing stands out in the schema which I could be used to calculate this.

Any ideas how I might get this value? It's displayed in the firebase dashboard so it must be available..

Answer 1

I looked into the documentation and found event_name='app_exception'. With that you can write a query like

WITH userCrashes AS (
  SELECT user_pseudo_id, MAX(event_name = 'app_exception') hasCrash 
  FROM `firebase-public-project.analytics_153293282.events_20181003` 
  GROUP BY 1
)

SELECT
  IF(hasCrash,'crashed','crash-free') crashState,
  COUNT(DISTINCT user_pseudo_id) AS users,
  ROUND(COUNT(DISTINCT user_pseudo_id) / SUM(COUNT(DISTINCT user_pseudo_id)) OVER (),2) AS userShare
FROM userCrashes
GROUP BY 1

But there is also a flag 'fatal' in the event parameters. In the example data, it's always true, but in case you wanted to take it into respect, you could do something like

WITH userCrashes AS (
  SELECT 
    user_pseudo_id, 
    MAX(event_name = 'app_exception') hasCrash,
    MAX(event_name = 'app_exception' 
      AND (select value.int_value=1 from unnest(event_params) where key='fatal')
    ) hasFatalCrash
  FROM `firebase-public-project.analytics_153293282.events_20181003` 
  GROUP BY 1
)

SELECT
  IF(hasCrash,'crashed','crash-free') crashState,
  IF(hasFatalCrash,'crashed fatal','crash-free') fatalCrashState,
  COUNT(DISTINCT user_pseudo_id) AS users,
  ROUND(COUNT(DISTINCT user_pseudo_id) / SUM(COUNT(DISTINCT user_pseudo_id)) OVER (),2) AS userShare
FROM userCrashes
GROUP BY 1,2

Disclaimer: I never worked with firebase, so this is all just based on documentation and example data. Hope it helps, though.