Crashlytics + Google Analytics Automatic Query for App Crash-Free Users Rate

---

ℹ️ℹ️ℹ️ The following content is translated by OpenAI.

Click here to view the original Chinese version. | 點此查看本文中文版

---

Crashlytics + Google Analytics Automatic Query for App Crash-Free Users Rate

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

In the previous article “Crashlytics + Big Query Creating a More Immediate and Convenient Crash Tracking Tool”, we exported Crashlytics crash logs as Raw Data to Big Query and used Google Apps Script to schedule automatic queries for the Top 10 crashes and publish messages to a Slack Channel.

This article continues the automation of an important metric related to app crashes — Crash-Free Users Rate, the percentage of users not affected by crashes. Many app teams likely track and record this metric continuously, which has traditionally been done manually. The goal of this article is to automate this repetitive task and avoid potential errors when manually querying data. As mentioned earlier, Firebase Crashlytics does not provide any API for users to query, so we will similarly need to connect Firebase data to other Google services and then query the relevant data through that service’s API.

Initially, I thought this data could also be queried from Big Query; however, this approach was completely wrong because Big Query contains the Raw Data of crashes and does not include data for users who did not crash, making it impossible to calculate the Crash-Free Users Rate. There is not much information available online regarding this requirement, but after some searching, I found someone mentioning the keyword Google Analytics. I knew that Firebase’s Analytics and Events could be connected to GA for querying, but I didn’t realize that the Crash-Free Users Rate was also included. After reviewing the GA API, Bingo!

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)

Now that I know the way forward, I can start implementing it!

Connecting Firebase -> Google Analytics

You can refer to the official documentation for setup steps, which will be omitted in this article.

GA4 Query Explorer Tool

Before we start writing code, we can use the official web GUI tool to quickly build query conditions and obtain query results. After experimenting and confirming that the results are what we want, we can then start writing code.

Go to >>> GA4 Query Explorer

• In the upper left corner, remember to select GA4.
• After logging in on the right, choose the corresponding GA Account & Property.

• Start Date, End Date: You can directly enter dates or use special variables to represent dates ( 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 separately for both platforms.

Scroll down and click “Make Request” to view the results, and we will obtain the Crash-Free Users Rate for the specified date range.

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

Here, I noticed that the numbers on both sides might have slight discrepancies (one number differed by 0.0002), the reason is unclear, but it is within an acceptable margin of error; if we consistently use GA’s Crash-Free Users Rate, it wouldn’t even be considered an error.

Using Google Apps Script to Automatically Fill Data into Google Sheets

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

Assuming our Google Sheet looks like the image above.

You can click on Extensions -> Apps Script in Google Sheets to create a Google Apps Script, or click here for Google Apps Script -> and click on “New Project” in the upper left corner.

Once inside, you can click on the unnamed project name at the top to give it a project name.

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

Return to the previous GA4 Query Explorer tool, and next to the Make Request button, you can check “Show Request JSON” to obtain 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 previous GA4 Query Explorer tool:

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

• googleSheetID: This can be obtained from the Google Sheet URL https://docs.google.com/spreadsheets/d/ googleSheetID /edit
• googleSheetName: The name of the sheet in Google Sheets where crash records are stored.

Paste the above code into the code section on the right in Google Apps Script & select the “execute” function from the dropdown at the top, then click Debug to test if it can retrieve data correctly:

The first execution will prompt an authorization window:

Follow the steps to complete account authorization.

If executed successfully, the Crash-Free Users Rate will be printed in the Log below, indicating that the query was successful.

Next, we just need to add the automatic filling into Google Sheets, and we will be all set!

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 the Run or Debug button again to execute the “execute” function.

Return to Google Sheets, and the data has been successfully added!

Add Trigger for Automatic Execution

Select the clock button on the left -> click “+ Add Trigger” in the lower right corner.

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

After setting, click Save.

Completion

From now on, the recording and tracking of App Crash-Free Users Rate data will be fully automated; no manual querying or filling is required; everything is handled automatically by the machine!

We only need to focus on solving App Crash issues!

p.s. Unlike the previous article where using Big Query required payment for querying data, this article’s query for Crash-Free Users Rate and Google Apps Script is completely free to use.

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

Further Reading

• Ultimate Beginner’s Guide to Google Analytics 4 (NEW 2023 Interface) (Thanks to Emma for providing the information )
• Crashlytics + Big Query Creating a More Immediate and Convenient Crash Tracking Tool
• Using Python+Google Cloud Platform+Line Bot to Automate Routine Tasks
• Creating a Fully Automated WFH Employee Health Status Reporting System with Slack
• Using Google Apps Script to Forward Gmail Messages to Slack

If you have any questions or suggestions, feel free to contact me 。

---

This article was first published on Medium ➡️ Click Here

Automatically converted and synchronized using ZMediumToMarkdown and Medium-to-jekyll-starter.

Improve this page on Github.

Follow Me on Medium 999+ Followers

1,126 Total Views
Last Statistics Date: 2025-03-25 | 1,009 Views on Medium.