This guide explains how you can access custom Google Analytics reports
from BigQuery using the [Google Analytics Sheets add-on](https://workspace.google.com/u/0/marketplace/app/ga4_reports_builder_for_google_analytics/589269949355) and
[Google Drive BigQuery connector](https://cloud.google.com/bigquery/docs/external-data-drive).

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](https://developers.google.com/analytics/devguides/reporting/data/v1/predefined-reports#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](https://workspace.google.com/u/0/marketplace/app/ga4_reports_builder_for_google_analytics/589269949355).

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

   ![Open the Google Analytics Sheets Add-on](https://developers.google.com/static/analytics/bigquery/images/query-analytics-reports-install-add-on.png)
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:

   ![Google Analytics Sheets Add-on Report Configuration Part 1](https://developers.google.com/static/analytics/bigquery/images/query-analytics-reports-add-on-config-part1.png)

   ![Google Analytics Sheets Add-on Report Configuration Part 2](https://developers.google.com/static/analytics/bigquery/images/query-analytics-reports-add-on-config-part2.png)

   ![Google Analytics Sheets Add-on Report Configuration Part 3](https://developers.google.com/static/analytics/bigquery/images/query-analytics-reports-add-on-config-part3.png)
6. Click **Create Report** to create the report configuration
   in the current Sheets document:

   ![Google Analytics Sheets Add-on Report Configuration Sheet](https://developers.google.com/static/analytics/bigquery/images/query-analytics-reports-add-on-config-sheet.png)
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.

   ![Google Analytics Sheets Add-on report data](https://developers.google.com/static/analytics/bigquery/images/query-analytics-reports-add-on-report-data.png)
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**.

   ![Google Analytics Sheets Add-on schedule](https://developers.google.com/static/analytics/bigquery/images/query-analytics-reports-add-on-schedule.png)

## 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](https://cloud.google.com/bigquery/docs/external-data-drive#create_external_tables)
from the BigQuery documentation.

### Google Cloud console

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

   [Go to BigQuery](https://console.cloud.google.com/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](https://cloud.google.com/bigquery/docs/external-data-drive#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:

![BigQuery external table configuration part 1](https://developers.google.com/static/analytics/bigquery/images/query-analytics-reports-bq-table-config-part1.png)

![BigQuery external table configuration part 2](https://developers.google.com/static/analytics/bigquery/images/query-analytics-reports-bq-table-config-part2.png)

## 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](https://developers.google.com/bigquery/external-data-sources#external_data_source_limitations)
on external data sources.

1. Open the `user_acquisition_report` table to inspect its schema:

   ![BigQuery external table schema](https://developers.google.com/static/analytics/bigquery/images/query-analytics-reports-bq-table-schema.png)
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:

![BigQuery external table query](https://developers.google.com/static/analytics/bigquery/images/query-analytics-reports-bq-table-query.png)