Export Firebase Crashlytics data to BigQuery

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.

Enable BigQuery export

  1. Go to the Integrations page in the Firebase console.
  2. In the BigQuery card, click Link.
  3. Follow the on-screen instructions to enable BigQuery.

When you link your project 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.
  • Firebase exports a copy of your existing data to BigQuery. For each linked app, this includes a batch table containing the data from the daily sync.
  • If you enable Crashlytics BigQuery streaming export, all linked apps will also have a realtime table containing constantly updating 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 com_google_test_ANDROID. This batch table is updated once every day. If you enable Crashlytics BigQuery streaming export, Firebase Crashlytics data will also be streamed in realtime to com_google_test_ANDROID_REALTIME.

Each row in a table represents an event that occurred in the app, including crashes, non-fatal errors, and ANRs.

Enable Crashlytics BigQuery streaming export

You can stream your Crashlytics data in realtime with BigQueryStreaming. You can use it for any purpose that requires live data, such as presenting information in a live dashboard, watching a rollout live, or monitoring application problems that trigger alerts and custom workflows.

Crashlytics BigQuery streaming export is not available for BigQuery sandbox.

When you enable Crashlytics BigQuery streaming export, in addition to the batch table you will have a realtime table. Here are the differences you should be aware of between the tables:

Batch Table Realtime Table
  • Data exported once daily
  • Events durably stored before batch writing to BigQuery
  • Can be backfilled up to 90 days prior
  • Data exported in real time
  • No backfill available

The batch table is ideal for long-term analysis and identifying trends over time because we durably store events before writing them, and they can be backfilled to the table for up to 90 days. When we write data to your realtime table, we immediately write it to BigQuery, and so it is ideal for live dashboards and custom alerts. These two tables can be combined with a stitching query to get the benefits of both. See query Example 9 below.

By default, the realtime table has a partition expiration time of 30 days. To learn how to modify this, see Updating the partition expiration.

Enable Crashlytics BigQuery streaming

To enable streaming, navigate to the Crashlytics section of the BigQuery integrations page and select the Include streaming checkbox.

Data Studio Template

To enable realtime data in your Data Studio template, follow the instructions in Visualizing exported Crashlytics data with Data Studio.

Views

You can turn the example queries below into views using the BigQuery UI. See Creating views for detailed instructions.

What can you do with the exported data?

BigQuery exports contain raw crash data including device type, operating system, exceptions (Android apps) or errors (Apple 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.

Objective-C

CrashlyticsKit setIntValue:3 forKey:@"current_level";

Swift

Crashlytics.sharedInstance().setIntValue(3, forKey: "current_level");

Java

Crashlytics.setInt("current_level", 3);

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:

  1. Enable BigQuery exports for Google Analytics. See Export project data to BigQuery.

  2. Update their app to pass a user ID into both the Google Analytics SDK and the Crashlytics SDK.

    Objective-C
    CrashlyticsKit setUserIdentifier:@"123456789";
    FIRAnalytics setUserID:@"12345678 9";
    
    Swift
    Crashlytics.sharedInstance().setUserIdentifier("123456789");
    Analytics.setUserID("123456789");
    
    Java
    Crashlytics.setUserIdentifier("123456789");
    mFirebaseAnalytics.setUserId("123456789");
    
  3. 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
    

Example 8: Top 5 issues so far today

Requires enabling Crashlytics BigQuery streaming export

SELECT
  issue_id,
  COUNT(DISTINCT event_id) AS events
FROM
  `your_project.firebase_crashlytics.package_name_ANDROID_REALTIME`
WHERE
  DATE(event_timestamp) = CURRENT_DATE()
GROUP BY
  issue_id
ORDER BY
  events DESC
LIMIT
  5;

Example 9: Top 5 issues since DATE, including today

Requires enabling Crashlytics BigQuery streaming export.

In this example, we combine batch and realtime tables to add realtime information to the reliable batch data. Since event_id is a primary key, we can use DISTINCT event_id to dedupe any common events from the two tables.

SELECT
  issue_id,
  COUNT(DISTINCT event_id) AS events
FROM (
  SELECT
    issue_id,
    event_id,
    event_timestamp
  FROM
    `your_project.firebase_crashlytics.package_name_ANDROID_REALTIME`
  UNION ALL
  SELECT
    issue_id,
    event_id,
    event_timestamp
  FROM
    `your_project.firebase_crashlytics.package_name_ANDROID`)
WHERE
  event_timestamp >= "2020-01-13"
GROUP BY
  issue_id
ORDER BY
  events DESC
LIMIT
  5;

Understanding the Firebase Crashlytics schema in BigQuery

When you link Crashlytics with BigQuery, Firebase exports recent events (crashes, non-fatal errors, and ANRs), including events from up to two days before the link, with the option to backfill up to ninety days.

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.

Datasets

Firebase Crashlytics creates a new dataset in BigQuery for Crashlytics data. The dataset covers your entire project, even if it has multiple apps.

Tables

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 Android app with the ID com.google.test would be in a table named com_google_test_ANDROID, and realtime data (if enabled) would be in a table named com_google_test_ANDROID_REALTIME

Tables contain a standard set of Crashlytics data in addition to any custom Crashlytics keys defined by the developers.

Rows

Each row in a table represents an error the app encountered.

Columns

The columns in a table are identical for crashes, non-fatal errors, and ANRs. If Crashlytics BigQuery streaming export is enabled, then the realtime table will have the same columns as the batch table. 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 Apple or Android apps
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
error_type STRING Error type of the event (FATAL, NON_FATAL, ANR)
issue_id STRING The issue associated with the event
variant_id STRING The issue variant associated with this event
Note that not all events have an associated issue variant.
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 INT64 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 details of the OS on the device
operating_system.display_version STRING The version of the OS on the device
operating_system.name STRING The name of the OS on the device
operating_system.modification_state STRING Whether the device has been modified, for example jailbroken/rooted (MODIFIED or UNMODIFIED)
operating_system.type STRING The type of OS running on the device (for example, IOS, MACOS); only available for Apple platforms apps
operating_system.device_type STRING The type of device (for example, MOBILE, TABLET, TV, etc.); also known as "device category"
application RECORD The app that generated the event
application.build_version STRING The app's build version
application.display_version STRING
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 Apple apps 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 Apple apps 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 Apple apps 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
unity_metadata.unity_version STRING The version of Unity running on this device
unity_metadata.debug_build BOOLEAN If this is a debug build
unity_metadata.processor_type STRING The type of processor
unity_metadata.processor_count INT64 The number of processors (cores)
unity_metadata.processor_frequency_mhz INT64 The frequency of the processor(s) in MHz
unity_metadata.system_memory_size_mb INT64 The size of the system's memory in Mb
unity_metadata.graphics_memory_size_mb INT64 The graphics memory in MB
unity_metadata.graphics_device_id INT64 The identifier of the graphics device
unity_metadata.graphics_device_vendor_id INT64 The identifier of the graphics processor's vendor
unity_metadata.graphics_device_name STRING The name of the graphics device
unity_metadata.graphics_device_vendor STRING The vendor of the graphics device
unity_metadata.graphics_device_version STRING The version of the graphics device
unity_metadata.graphics_device_type STRING The type of the graphics device
unity_metadata.graphics_shader_level INT64 The shader level of the graphics
unity_metadata.graphics_render_target_count INT64 The number of graphical rendering targets
unity_metadata.graphics_copy_texture_support STRING Support for copying graphics texture as defined in the Unity API
unity_metadata.graphics_max_texture_size INT64 The maximum size dedicated to rendering texture
unity_metadata.screen_size_px STRING The size of the screen in pixels, formatted as width x height
unity_metadata.screen_resolution_dpi STRING The DPI of the screen as a floating point number
unity_metadata.screen_refresh_rate_hz INT64 The refresh rate of the screen in Hz

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. If you have enabled Crashlytics BigQuery streaming export, you can view that data on the Realtime trends page of the Data Studio template.You can use the sample as a template to quickly create new reports and visualizations based on your own app's raw crash data:

  1. Open the Crashlytics Data Studio Dashboard template.
  2. Click Use Template in the upper-right corner.
  3. In the New Data Source dropdown, select Create New Data Source.
  4. Click Select on the BigQuery card.
  5. Select a table containing exported Crashlytics data by choosing My Projects > [your-project-name] > firebase_crashlytics > [your-table-name]. Your batch table is always available to select; if Crashlytics BigQuery streaming export is enabled, you can select your realtime table instead.
  6. Under Configuration, set Crashlytics Template level to Default.
  7. Click Connect to create the new data source.
  8. Click Add to Report to return to the Crashlytics template.
  9. Finally, click Create Report to create your copy of the Crashlytics Data Studio Dashboard template.