Access Google Analytics custom reports from BigQuery

This guide explains how you can access custom Google Analytics reports from BigQuery using the Google Analytics Sheets add-on and Google Drive BigQuery connector.

This guide shows how to generate a custom report using the Google Analytics Sheets add-on, expose the report results sheet as a BigQuery external table, and then query the report data from BigQuery.

This guide uses the User acquisition report as an example, but you can apply these steps to any report type.

Step 1: Configure a custom report in Google Sheets

1. Install the Google Analytics Sheets add-on from the Google Workspace Marketplace.

2. Open the Extensions → GA4 Reports Builder for Google Analytics → Create new report dialog.

   

3. Configure the report by specifying the Analytics property, date range and report fields.

4. Use the following fields for the User acquisition report:

   Dimensions

   • firstUserDefaultChannelGroup

   Metrics

   • eventCount
   • keyEvents
   • totalRevenue
   • newUsers
   • engagedSessions
   • engagementRate

5. Leave the Dimensions filters and Metrics filters fields empty.

   Here's an image of the correct report settings:

   

   

   

6. Click Create Report to create the report configuration in the current Sheets document:

   

7. Select Extensions → GA4 Reports Builder for Google Analytics → Run reports to generate the report. A new tab with your report name will be created once the report is complete.

   

8. Open the Extensions → GA4 Reports Builder for Google Analytics → Schedule reports dialog to set up periodic polling of the latest report data. Specify the schedule and click Save.

   

Step 2: Connect a Google Sheet report document as a BigQuery external table

With BigQuery, you can create a permanent table linked to your external data source in Google Sheets. Use this feature to connect the Google Analytics report spreadsheet created in the previous step as a BigQuery external table.

This step requires following the Create Google Drive external tables guide from the BigQuery documentation.

Google Cloud console

1. In the Google Cloud console, open the BigQuery page.

   Go to BigQuery

2. In the Explorer panel, expand your project and select a dataset.

3. Expand the Actions option and click Open.

4. In the details panel, click Create table .

5. On the Create table page, in the Source section:

   • For Create table from, select Drive.

   • In the Select Drive URI field, enter the Drive URI of the Google Sheet document containing the report, in the form https://docs.google.com/spreadsheets/d/[file_id].

   • For File format, select Google Sheet

6. On the Create table page, in the Destination section:

   • For Dataset name, choose the appropriate dataset, and in the Table name field, enter the name of the table you're creating in BigQuery.

   • Verify that Table type is set to External table.

7. In the Sheet range box, specify the sheet name and cell range to query. This should be the Sheet containing your report data: sheet_name!top_left_cell_id:bottom_right_cell_id for a cell range. For our example report, the range can be specified as User acquisition report!A:G. Note how the sheet corresponding to the custom report name is mentioned in the range.

8. In the Schema section, enable Auto detect.

9. Expand the Advanced options section, in the Header rows to skip box specify the number of rows to omit. In our example, the report data starts with row 15, which is the value that should be set in the input box.

10. On the Create table page, in the Destination section:

    • For Dataset, choose the appropriate dataset, and in the Table field, enter the name of the table you're creating in BigQuery.

    • Verify that Table type is set to External table.

11. Click CREATE TABLE.

12. If necessary, select your account and then click Allow to give the BigQuery client tools access to Drive.

Here is an example of an external BigQuery table configuration screen:

Step 3: Query a Google Sheets report table from BigQuery

You can now run a query against the Google Sheets report table as if it were a standard BigQuery table, subject to the limitations on external data sources.

1. Open the user_acquisition_report table to inspect its schema:

   

2. Click Query. In the suggested query, change SELECT FROM to SELECT * FROM to obtain all report fields, and then Run query to run a query against the external table.

The query in the following example will output a snippet of data in the table for all columns: