16

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..

enter image description here

Ollie Jones
  • 437
  • 1
  • 9
  • 16
  • do you know if there's a sample shared table to write queries from – Felipe Hoffa Feb 07 '19 at 18:05
  • @FelipeHoffa there's a sample datastudio report but I don't think you can write queries on the underlying data https://datastudio.google.com/u/0/reporting/10TMAKxL0ZxcNGTLDQy1LAF5V7uNDYxRC/page/1xZU/preview – Ollie Jones Feb 08 '19 at 11:14

1 Answers1

11

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.

Martin Weitzmann
  • 4,430
  • 10
  • 19
  • 2
    As per my understanding, this will be helpful to see the result by ruining this query in google cloud platform Query Editor. Can anyone help me on how this data can be added in Google data studio dashboard ? – Vijay Apr 25 '19 at 06:55
  • Very help, Martin! I'm struggling to put something together for per-day, as seen in Crashlytics so I can graph it. How could I do that with your query? I'd like to do this because its not possible to have crash-free per platform (ios/android) overlay over x days. Would also take just a replication of the graph in crashlytics. Thanks! – Frank B. Sep 10 '21 at 21:38
  • So what is the output of the query? I think it's not crash-free-sessions. – ror Nov 08 '22 at 10:51