Access Google Analytics custom reports from BigQuery

    • Access custom Google Analytics reports in BigQuery using the Google Analytics Sheets add-on and the Google Drive BigQuery connector.

    • Generate a custom report in Google Sheets, expose it as a BigQuery external table, and then query the data from BigQuery.

    • This method can be applied to any report type, using the User acquisition report as an example.

    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 reportdialog.

      Open the Google Analytics Sheets Add-on

    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 filtersand Metrics filtersfields empty.

      Here's an image of the correct report settings:

      Google Analytics Sheets Add-on Report Configuration Part 1

      Google Analytics Sheets Add-on Report Configuration Part 2

      Google Analytics Sheets Add-on Report Configuration Part 3

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

      Google Analytics Sheets Add-on Report Configuration Sheet

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

      Google Analytics Sheets Add-on report data

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

      Google Analytics Sheets Add-on schedule

    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 Explorerpanel, expand your project and select a dataset.

    3. Expand the Actionsoption and click Open.

    4. In the details panel, click Create table .

    5. On the Create tablepage, in the Sourcesection:

      • For Create table from, select Drive.

      • In the Select Drive URIfield, 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 tablepage, in the Destinationsection:

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

      • Verify that Table typeis set to External table.

    7. In the Sheet rangebox, 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 Schemasection, enable Auto detect.

    9. Expand the Advanced optionssection, in the Header rows to skipbox 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 tablepage, in the Destinationsection:

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

      • Verify that Table typeis set to External table.

    11. Click CREATE TABLE.

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

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

    BigQuery external table configuration part 1

    BigQuery external table configuration part 2

    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:

      BigQuery external table schema

    2. Click Query. In the suggested query, change SELECT FROM to SELECT * FROM to obtain all report fields, and then Run queryto 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:

    BigQuery external table query

    Previous
    Unique count approximation with HLL++
    Next
    Backfill Google Ads traffic source data
    Design a Mobile Site
    View Site in Mobile | Classic
    Share by: