Crashlytics + Google Analytics Automatically Query App Crash-Free Users Rate

Using Google Apps Script to query Crashlytics through Google Analytics and automatically fill it into Google Sheet

In the previous article, “Crashlytics + Big Query to Create a More Real-Time and Convenient Crash Tracking Tool”, we exported Crashlytics crash records as Raw Data to Big Query and used Google Apps Script to automatically schedule queries for the Top 10 Crashes & post messages to the Slack Channel.

This article continues to automate an important metric related to app crashes — Crash-Free Users Rate, the percentage of users not affected by crashes. Many app teams continuously track and record this metric, which was traditionally done manually. The goal here is to automate this repetitive task and avoid potential errors in manual data entry. As mentioned earlier, Firebase Crashlytics does not provide any API for querying, so we need to connect Firebase data to other Google services and then use those service APIs to query the relevant data.

Initially, I thought this data could also be queried from Big Query; however, this approach is entirely wrong because Big Query contains Raw Data of crashes and does not include data of users who did not experience crashes, making it impossible to calculate the Crash-Free Users Rate. There is limited information on this requirement online, and after extensive searching, I found a mention of Google Analytics. I knew that Firebase’s Analytics and Events could be connected to GA for queries, but I did not expect the Crash-Free Users Rate to be included. After reviewing GA’s API, Bingo!

$[API Dimensions & Metrics](https://developers.google.com/analytics/devguides/reporting/data/v1/api-schema?hl=en){:target="_blank"}$

API Dimensions & Metrics

Google Analytics Data API (GA4) provides two metrics:

crashAffectedUsers: The number of users affected by crashes
crashFreeUsersRate: The percentage of users not affected by crashes (expressed as a decimal)

Knowing the way forward, we can start implementing it!

Connect Firebase -> Google Analytics

You can refer to the official instructions for setup steps, which are omitted here.

GA4 Query Explorer Tool

Before writing code, we can use the Web GUI Tool provided by the official site to quickly build query conditions and obtain query results. Once the results are as desired, we can start writing code.

Go to »> GA4 Query Explorer

Remember to select GA4 in the top left corner.
After logging in with your account on the right, choose the corresponding GA Account & Property.

Start Date, End Date: You can directly enter the date or use special variables to represent the date (yesterday, today, 30daysAgo, 7daysAgo).

metrics: Add crashFreeUsersRate.

dimensions: Add platform (device type iOS/Android/Desktop…).

dimension filter: Add platform, string, exact, iOS or Android.

Query the Crash Free Users Rate for both platforms separately.

Scroll to the bottom and click “Make Request” to view the results. We can get the Crash-Free Users Rate within the specified date range.

You can go back and open Firebase Crashlytics to compare if the data under the same conditions is the same.

It has been observed that there might be slight differences in numbers between the two (we had a difference of 0.0002 in one number), the reason is unknown, but it is within an acceptable error range. If you consistently use GA Crash-Free Users Rate, it cannot be considered an error.

Using Google Apps Script to Automatically Fill Data into Google Sheet

Next is the automation part. We will use Google Apps Script to query GA Crash-Free Users Rate data and automatically fill it into our Google Sheet, achieving the goal of automatic filling and tracking.

Assume our Google Sheet is as shown above.

You can click Extensions -> Apps Script at the top of Google Sheet to create a Google Apps Script or click here to go to Google Apps Script -> click “New Project” at the top left.

After entering, you can click the unnamed project name at the top to give it a project name.

In the “Services” on the left, click “+” to add “Google Analytics Data API”.

Go back to the GA4 Query Explorer tool, and next to the Make Request button, you can check “Show Request JSON” to get the Request JSON for these conditions.

Convert this Request JSON into Google Apps Script as follows:

  
// Remember to add Google Analytics Data API to Services, or you'll see this error: ReferenceError: AnalyticsData is not defined
// https://ga-dev-tools.web.app/ga4/query-explorer/ -> property id
const propertyId = "";
// https://docs.google.com/spreadsheets/d/googleSheetID/
const googleSheetID = "";
// Google Sheet name
const googleSheetName = "App Crash-Free Users Rate";

function execute() {
  Logger.log(fetchCrashFreeUsersRate())
}

function fetchCrashFreeUsersRate(platform = "ios", startDate = "30daysAgo", endDate = "today") {
  const dimensionPlatform = AnalyticsData.newDimension();
  dimensionPlatform.name = "platform";

  const metric = AnalyticsData.newMetric();
  metric.name = "crashFreeUsersRate";

  const dateRange = AnalyticsData.newDateRange();
  dateRange.startDate = startDate;
  dateRange.endDate = endDate;

  const filterExpression = AnalyticsData.newFilterExpression();
  const filter = AnalyticsData.newFilter();
  filter.fieldName = "platform";
  const stringFilter = AnalyticsData.newStringFilter()
  stringFilter.value = platform;
  stringFilter.matchType = "EXACT";
  filter.stringFilter = stringFilter;
  filterExpression.filter = filter;

  const request = AnalyticsData.newRunReportRequest();
  request.dimensions = [dimensionPlatform];
  request.metrics = [metric];
  request.dateRanges = dateRange;
  request.dimensionFilter = filterExpression;

  const report = AnalyticsData.Properties.runReport(request, "properties/" + propertyId);

  return parseFloat(report.rows[0].metricValues[0].value) * 100;
}

GA Property ID: You can also obtain it from the GA4 Query Explorer tool:

In the initial Property selection menu, the number below the selected Property is the propertyId.

googleSheetID: You can obtain it from the Google Sheet URL https://docs.google.com/spreadsheets/d/ googleSheetID /edit
googleSheetName: The name of the Sheet in Google Sheets that records crash data

Paste the above code into the Google Apps Script code block on the right & select the “execute” function from the method dropdown at the top. Then click Debug to test if the data can be retrieved correctly:

The first time you run it, an authorization request window will appear:

Follow the steps to complete account authorization.

If the execution is successful, the Crash-Free Users Rate will be printed in the Log below, indicating a successful query.

Next, we just need to add automatic filling into Google Sheets to complete the task!

Complete Code:

  
// Remember to add Google Analytics Data API to Services, or you'll see this error: ReferenceError: AnalyticsData is not defined

// https://ga-dev-tools.web.app/ga4/query-explorer/ -> property id
const propertyId = "";
// https://docs.google.com/spreadsheets/d/googleSheetID/
const googleSheetID = "";
// Google Sheet name
const googleSheetName = "";

function execute() {
  const today = new Date();
  const daysAgo7 = new Date(new Date().setDate(today.getDate() - 6)); // Today is not counted, so it's -6

  const spreadsheet = SpreadsheetApp.openById(googleSheetID);
  const sheet = spreadsheet.getSheetByName(googleSheetName);
  
  var rows = [];
  rows[0] = Utilities.formatDate(daysAgo7, "GMT+8", "MM/dd")+"~"+Utilities.formatDate(today, "GMT+8", "MM/dd");
  rows[1] = fetchCrashFreeUsersRate("ios", Utilities.formatDate(daysAgo7, "GMT+8", "yyyy-MM-dd"), Utilities.formatDate(today, "GMT+8", "yyyy-MM-dd"));
  rows[2] = fetchCrashFreeUsersRate("android", Utilities.formatDate(daysAgo7, "GMT+8", "yyyy-MM-dd"), Utilities.formatDate(today, "GMT+8", "yyyy-MM-dd"));
  sheet.appendRow(rows);
}

function fetchCrashFreeUsersRate(platform = "ios", startDate = "30daysAgo", endDate = "today") {
  const dimensionPlatform = AnalyticsData.newDimension();
  dimensionPlatform.name = "platform";

  const metric = AnalyticsData.newMetric();
  metric.name = "crashFreeUsersRate";

  const dateRange = AnalyticsData.newDateRange();
  dateRange.startDate = startDate;
  dateRange.endDate = endDate;

  const filterExpression = AnalyticsData.newFilterExpression();
  const filter = AnalyticsData.newFilter();
  filter.fieldName = "platform";
  const stringFilter = AnalyticsData.newStringFilter()
  stringFilter.value = platform;
  stringFilter.matchType = "EXACT";
  filter.stringFilter = stringFilter;
  filterExpression.filter = filter;

  const request = AnalyticsData.newRunReportRequest();
  request.dimensions = [dimensionPlatform];
  request.metrics = [metric];
  request.dateRanges = dateRange;
  request.dimensionFilter = filterExpression;

  const report = AnalyticsData.Properties.runReport(request, "properties/" + propertyId);

  return parseFloat(report.rows[0].metricValues[0].value) * 100;
}

Click “Run or Debug” above to execute “execute”.

Go back to Google Sheet, data added successfully!

Add Trigger to Schedule Automatic Execution

Select the clock button on the left -> Bottom right “+ Add Trigger”.

For the first function, select “execute”
For time-based trigger, you can choose week timer to track & add data once a week

Click Save after setting.

Done

From now on, recording and tracking App Crash-Free Users Rate data is fully automated; no manual query & input needed; everything is handled automatically by the machine!

We only need to focus on solving App Crash issues!

p.s. Unlike the previous article using Big Query which costs money to query data, querying Crash-Free Users Rate and using Google Apps Script in this article are completely free, so feel free to use them.

If you want to sync the results to a Slack Channel, refer to the previous article:

Crashlytics + Google Analytics Automatically Query App Crash-Free Users Rate