You can export your Crashlytics data into BigQuery for further analysis. BigQuery allows you to analyze the data using BigQuery SQL, export it to another cloud provider, and use it for visualization and custom dashboards with Google Data Studio.
Enabling BigQuery export
- Go to the Integrations page in the Firebase console.
- In the BigQuery card, click Link.
- Follow the on-screen instructions to enable BigQuery.
When you link your project to BiqQuery:
- Firebase exports a copy of your existing data to BigQuery.
- Firebase sets up daily syncs of your data from your Firebase project to BigQuery.
- By default, all apps in your project are linked to BigQuery and any apps that you later add to the project are automatically linked to BigQuery. You can manage which apps send data.
To deactivate BigQuery export, unlink your project in the Firebase console.
What data is exported to BigQuery?
Firebase Crashlytics data is exported into a BigQuery dataset named
firebase_crashlytics. By default, individual tables will be created inside the Crashlytics data set for each app in your project. Firebase names the tables based on the app's bundle identifier, with periods converted to underscores, and a platform name appended to the end. For example, data for an app with the ID
com.google.test would be in a table named
Each row in a table represents an event that occurred in the app, including both fatal and non-fatal errors.
What can you do with the exported data?
BigQuery exports contain raw crash data including device type, operating system, exceptions (Android apps) or errors (iOS apps), and Crashlytics logs, as well as other data.
Working with Firebase Crashlytics data in BigQuery
The following examples demonstrate queries you can run on your Crashlytics data. These queries generate reports that aren't available in the Crashlytics dashboard.
Examples of Crashlytics queries
The following examples demonstrate how to generate reports that aggregate crash event data into more easily-understood summaries.
Example 1: Crashes by day
After working to fix as many bugs as possible, a lead developer thinks her team is finally ready to launch their new photo-sharing app. Before they do, they want to check the number of crashes per day for the past month, to be sure their bug-bash made the app more stable over time:
SELECT COUNT(DISTINCT event_id) AS number_of_crashes, FORMAT_TIMESTAMP("%F", event_timestamp) AS date_of_crashes FROM `projectId.firebase_crashlytics.package_name_ANDROID` GROUP BY date_of_crashes ORDER BY date_of_crashes DESC LIMIT 30;
Example 2: Find most pervasive crashes
To properly prioritize production plans, a project manager ponders how to point out the top 10 most pervasive crashes in their product. They produce a query that provides the pertinent points of data:
SELECT DISTINCT issue_id, COUNT(DISTINCT event_id) AS number_of_crashes, COUNT(DISTINCT installation_uuid) AS number_of_impacted_user, blame_frame.file, blame_frame.line FROM `projectId.firebase_crashlytics.package_name_ANDROID` WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL 168 HOUR) AND event_timestamp < CURRENT_TIMESTAMP() GROUP BY issue_id, blame_frame.file, blame_frame.line ORDER BY number_of_crashes DESC LIMIT 10;
Example 3: Top 10 crashing devices
Fall is new phone season! A developer knows that also means it's new device-specific issues season. To get ahead of the looming compatibility concerns, they put together a query that identifies the 10 devices that experienced the most crashes in the past week:
SELECT device.model, COUNT(DISTINCT event_id) AS number_of_crashes FROM `projectId.firebase_crashlytics.package_name_ANDROID` WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 168 HOUR) AND event_timestamp < CURRENT_TIMESTAMP() GROUP BY device.model ORDER BY number_of_crashes DESC LIMIT 10;
Example 4: Filter by custom key
A game developer wants to know which level of their game experiences the most crashes. To help them track that stat, they set a custom Crashlytics key
current_level, and update it every time the user reaches a new level.
CrashlyticsKit setIntValue:3 forKey:@"current_level";
Crashlytics.sharedInstance().setIntValue(3, forKey: "current_level");
With that key in their BigQuery export, they then write a query to report the distribution of
current_level values associated with each crash event:
SELECT COUNT(DISTINCT event_id) AS num_of_crashes, value FROM `projectId.firebase_crashlytics.package_name_ANDROID` UNNEST(custom_keys) WHERE key = "current_level" GROUP BY key, value ORDER BY num_of_crashes DESC
Example 5: User ID extraction
A developer has an app in early access. Most of their users love it, but three have experienced an unusual number of crashes. To get to the bottom of the problem, they write a query that pulls all the crash events for those users, using their user IDs:
SELECT * FROM `projectId.firebase_crashlytics.package_name_ANDROID` WHERE user.id IN ("userid1", "userid2", "userid3") ORDER BY user.id
Example 6: Find all users facing a particular crash issue
A developer has released a critical bug to a group of beta testers. The team was able to use the query from Example 2 above to identify the specific crash issue ID. Now they would like to run a query to extract the list of app users who were impacted by this crash:
SELECT user.id as user_id FROM `projectId.firebase_crashlytics.package_name_ANDROID` WHERE issue_id = "YOUR_ISSUE_ID" AND application.display_version = "
" AND user.id != "" ORDER BY user.id;
Example 7: Number of users impacted by a crash issue, broken down by country
Now the team has detected a critical bug during the rollout of a new release. They were able to use the query from Example 2 above to identify the specific crash issue ID. The team would now like to see if this crash has spread to users in different countries around the world.
To write this query, the team will need to:
Enable BigQuery exports for Google Analytics for Firebase. See Export project data to BigQuery.
Update their app to pass a user ID into both the Google Analytics for Firebase SDK and the Crashlytics SDK.
CrashlyticsKit setUserIdentifier:@"123456789"; FIRAnalytics setUserID:@"123456789";
Write a query that uses the user ID field to join events in the Google Analytics BigQuery data set with crashes in the Crashlytics BigQuery data set:
SELECT DISTINCT c.issue_id, a.geo.country, COUNT(DISTINCT c.user.id) as num_users_impacted FROM `projectId.firebase_crashlytics.package_name_ANDROID` c INNER JOIN `projectId.analytics_YOUR_TABLE.events_*` a on c.user.id = a.user_id WHERE c.issue_id = "YOUR_ISSUE_ID" AND a._TABLE_SUFFIX BETWEEN '20190101' AND '20200101' GROUP BY c.issue_id, a.geo.country, c.user.id
Understanding the Firebase Crashlytics schema in BigQuery
When you link Crashlytics with BigQuery, Firebase exports recent fatal and non-fatal crash events, including events from up to two days before the link.
From that point until you disable the link, Firebase exports Crashlytics events on a daily basis. It can take a few minutes for the data to be available in BigQuery after each export.
Firebase Crashlytics creates a new dataset in BigQuery for Crashlytics data. The dataset covers your entire project, even if it has multiple apps.
Firebase Crashlytics creates a table in the dataset for each app in your project, unless you've opted out of exporting data for that app. Firebase names the tables based on the app's bundle identifier, with periods converted to underscores, and a platform name appended to the end. For example, data for an app with the ID
com.google.test would be in a table named
Tables contain a standard set of Crashlytics data in addition to any custom Crashlytics keys defined by the developers.
Each row in a table represents an error the app encountered.
The columns in a table are identical for fatal and non-fatal errors. The columns within the export are listed below.
Without stack traces
Columns present in rows that represent events without stack traces.
|Field Name||Data Type||Description|
|platform||STRING||Android or iOS|
|bundle_identifier||STRING||The bundle ID, e.g. com.google.gmail|
|event_id||STRING||A unique ID for the event|
|is_fatal||BOOLEAN||Whether the app crashed|
|issue_id||STRING||An issue associated with the event|
|event_timestamp||TIMESTAMP||When the event occurred|
|device||RECORD||The device the event occurred on|
|device.manufacturer||STRING||The device manufacturer|
|device.model||STRING||The device model|
|device.architecture||STRING||X86_32, X86_64, ARMV7, ARM64, ARMV7S, or ARMV7K|
|memory||RECORD||The device's memory status|
|memory.used||INT64||Bytes of memory used|
|memory.free||INT65||Bytes of memory remaining|
|storage||RECORD||The device's persistent storage|
|storage.used||INT64||Bytes of storage used|
|storage.free||INT64||Bytes of storage remaining|
|operating_system||RECORD||The device's OS details|
|operating_system.display_version||STRING||The OS version|
|operating_system.name||STRING||The OS name|
|operating_system.modification_state||STRING||MODIFIED or UNMODIFIED, i.e. whether the device has been jailbroken/rooted|
|application||RECORD||The app that generated the event|
|application.build_version||STRING||The app's build version|
|user||RECORD||Optional: Info collected on the app's user|
|user.name||STRING||Optional: The user's name|
|user.email||STRING||Optional: The user's email address|
|user.id||STRING||Optional: An app-specific ID associated with the user|
|custom_keys||REPEATED RECORD||Developer-defined key-value pairs|
|custom_keys.key||STRING||A developer-defined key|
|custom_keys.value||STRING||A developer-defined value|
|installation_uuid||STRING||An ID that identifies a unique app & device installation|
|crashlytics_sdk_versions||STRING||The Crashlytics SDK version that generated the event|
|app_orientation||STRING||PORTRAIT, LANDSCAPE, FACE_UP, or FACE_DOWN|
|device_orientation||STRING||PORTRAIT, LANDSCAPE, FACE_UP, or FACE_DOWN|
|process_state||STRING||BACKGROUND or FOREGROUND|
|logs||REPEATED RECORD||Timestamped log messages generated by the Crashlytics logger, if enabled|
|logs.timestamp||TIMESTAMP||When the log was made|
|logs.message||STRING||The logged message|
|breadcrumbs||REPEATED RECORD||Timestamped Google Analytics breadcrumbs, if enabled|
|breadcrumbs.timestamp||TIMESTAMP||The timestamp associated with the breadcrumb|
|breadcrumbs.name||STRING||The name associated with the breadcrumb|
|breadcrumbs.params||REPEATED RECORD||Parameters associated with the breadcrumb|
|breadcrumbs.params.key||STRING||A parameter key associated with the breadcrumb|
|breadcrumbs.params.value||STRING||A parameter value associated with the breadcrumb|
|blame_frame||RECORD||The frame identified as the root cause of the crash or error|
|blame_frame.line||INT64||The line number of the file of the frame|
|blame_frame.file||STRING||The name of the frame file|
|blame_frame.symbol||STRING||The hydrated symbol, or raw symbol if it's unhydrateable|
|blame_frame.offset||INT64||The byte offset into the binary image that contains the code, unset for Java exceptions|
|blame_frame.address||INT64||The address in the binary image which contains the code, unset for Java frames|
|blame_frame.library||STRING||The display name of the library that includes the frame|
|blame_frame.owner||STRING||DEVELOPER, VENDOR, RUNTIME, PLATFORM, or SYSTEM|
|blame_frame.blamed||BOOLEAN||Whether Crashlytics's analysis determined that this frame is the cause of the crash or error|
|exceptions||REPEATED RECORD||Android only: Exceptions that occurred during this event. Nested exceptions are presented in reverse chronological order (read: the last record is the first exception thrown)|
|exceptions.type||STRING||The exception type, e.g. java.lang.IllegalStateException|
|exceptions.exception_message||STRING||A message associated with the exception|
|exceptions.nested||BOOLEAN||True for all but the last-thrown exception (i.e. the first record)|
|exceptions.title||STRING||The title of the thread|
|exceptions.subtitle||STRING||The subtitle of the thread|
|exceptions.blamed||BOOLEAN||True if Crashlytics determines the exception is responsible for the error or crash|
|exceptions.frames||REPEATED RECORD||The frames associated with the exception|
|exceptions.frames.line||INT64||The line number of the file of the frame|
|exceptions.frames.file||STRING||The name of the frame file|
|exceptions.frames.symbol||STRING||The hydrated symbol, or raw symbol if it's unhydrateable|
|exceptions.frames.offset||INT64||The byte offset into the binary image that contains the code, unset for Java exceptions|
|exceptions.frames.address||INT64||The address in the binary image which contains the code, unset for Java frames|
|exceptions.frames.library||STRING||The display name of the library that includes the frame|
|exceptions.frames.owner||STRING||DEVELOPER, VENDOR, RUNTIME, PLATFORM, or SYSTEM|
|exceptions.frames.blamed||BOOLEAN||Whether Crashlytics's analysis determined that this frame is the cause of the crash or error|
|error||REPEATED RECORD||iOS only: non-fatal errors|
|error.queue_name||STRING||The queue the thread was running on|
|error.code||INT64||Error code associated with the app's custom logged NSError|
|error.title||STRING||The title of the thread|
|error.subtitle||STRING||The subtitle of the thread|
|error.blamed||BOOLEAN||Whether Crashlytics's analysis determined that this frame is the cause of the error|
|error.frames||REPEATED RECORD||The frames of the stacktrace|
|error.frames.line||INT64||The line number of the file of the frame|
|error.frames.file||STRING||The name of the frame file|
|error.frames.symbol||STRING||The hydrated symbol, or raw symbol if it's unhydrateable|
|error.frames.offset||INT64||The byte offset into the binary image that contains the code|
|error.frames.address||INT64||The address in the binary image which contains the code|
|error.frames.library||STRING||The display name of the library that includes the frame|
|error.frames.owner||STRING||DEVELOPER, VENDOR, RUNTIME, PLATFORM, or SYSTEM|
|error.frames.blamed||BOOLEAN||Whether Crashlytics's analysis determined that this frame is the cause of the error|
|threads||REPEATED RECORD||Threads present at the time of the event|
|threads.crashed||BOOLEAN||Whether the thread crashed|
|threads.thread_name||STRING||The thread's name|
|threads.queue_name||STRING||iOS only: The queue the thread was running on|
|threads.signal_name||STRING||The name of the signal that caused the app to crash, only present on crashed native threads|
|threads.signal_code||STRING||The code of the signal that caused the app to crash; only present on crashed native threads|
|threads.crash_address||INT64||The address of the signal that caused the application to crash; only present on crashed native threads|
|threads.code||INT64||iOS only: Error code of the application's custom logged NSError|
|threads.title||STRING||The title of the thread|
|threads.subtitle||STRING||The subtitle of the thread|
|threads.blamed||BOOLEAN||Whether Crashlytics's analysis determined that this frame is the cause of the crash or error|
|threads.frames||REPEATED RECORD||The frames of the thread|
|threads.frames.line||INT64||The line number of the file of the frame|
|threads.frames.file||STRING||The name of the frame file|
|threads.frames.symbol||STRING||The hydrated symbol, or raw symbol if it's unhydreatable|
|threads.frames.offset||INT64||The byte offset into the binary image that contains the code|
|threads.frames.address||INT64||The address in the binary image which contains the code|
|threads.frames.library||STRING||The display name of the library that includes the frame|
|threads.frames.owner||STRING||DEVELOPER, VENDOR, RUNTIME, PLATFORM, or SYSTEM|
|threads.frames.blamed||BOOLEAN||Whether Crashlytics's analysis determined that this frame is the cause of the error|
Visualizing exported Crashlytics data with Data Studio
Google Data Studio turns your Crashlytics datasets in BigQuery into reports that are easy to read, easy to share, and fully customizable.
To learn more about using Data Studio, try the Data Studio quickstart guide, Welcome to Data Studio.
Using a Crashlytics report template
Data Studio has a sample report for Crashlytics that includes a comprehensive set of dimensions and metrics from the exported Crashlytics BigQuery schema. You can use the sample as a template to quickly create new reports and visualizations based on your own app's raw crash data:
- Open the Crashlytics Data Studio Dashboard template.
- Click Use Template in the upper-right corner.
- In the New Data Source dropdown, select Create New Data Source.
- Click Select on the BigQuery card.
- Select a table containing exported Crashlytics data by choosing My Projects > [your-project-name] > firebase_crashlytics > [your-table-name].
- Under Configuration, set Crashlytics Template level to Default.
- Click Connect to create the new data source.
- Click Add to Report to return to the Crashlytics template.
- Finally, click Create Report to create your copy of the Crashlytics Data Studio Dashboard template.