Calculate crash free sessions based on analytics and crashlytics data (BigQuery)

Question

Assuming the following definitions are in place:

1. The crash free sessions number is the percentage of sessions in the specified time range not ended by a crash of the application.

2. The crash free users is the percentage of distinct users who did not experience a crash during the specified time period.

Is it possible to calculate p1 of the above using analytical data exports into BigQuery? Closest thing I was able to find is this ticket on SO BigQuery Crashlytics - Crash free users / sessions but I think what it actually does is calculating p2 and not p1. To rephrase my question, how to identify user sessions and link them with crash experiences if any?

Answer 1

I took some of the information from these BigQuery examples to obtain and aggregate the info to get the overall sessions and the sessions with 'app_exception' events. From this you could calculate the percentage of crash free sessions:

SELECT
  SUM(sessions) as sessions,
  SUM(app_exception) as session_with_crash,
  1 - (SUM(app_exception) / SUM(sessions)) as crash_free_sessions
FROM
(
  SELECT 
    COUNT(user_pseudo_id) as sessions,
    SUM(IF (event_name = 'app_exception', 1, 0))  as app_exception,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
  FROM `Firebase_project_name.analytics_property_name.events_*` 
  -- WHERE event_name = 'app_exception'
  GROUP BY ga_session_id
)

This is the result I got:

sessions	sessions_with_crash	crash_free_sessions
282083	94	0.9996667

Keep in mind that in the above query all the data is being queried so make sure to adjust the required timeframe.