Try the MCP server for Google Analytics. Install from
GitHub, and see the
announcement for more details.
Access Google Analytics custom reports from BigQuery
Stay organized with collections
Save and categorize content based on your preferences.
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.
Install the Google Analytics Sheets add-on from the Google Workspace
Marketplace.
Open the Extensions → GA4 Reports Builder for Google Analytics →
Create new report dialog.
Configure the report by specifying the Analytics property, date range and report fields.
Use the following fields for the User acquisition report:
Dimensions
firstUserDefaultChannelGroup
Metrics
eventCountkeyEventstotalRevenuenewUsersengagedSessionsengagementRate
Leave the Dimensions filters and Metrics filters fields empty.
Here's an image of the correct report settings:
Click Create Report to create the report configuration
in the current Sheets document:
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.
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
In the Google Cloud console, open the BigQuery page.
Go to BigQuery
In the Explorer panel, expand your project and select a dataset.
Expand the
more_vert
Actions option and click Open.
In the details panel, click Create table
add_box.
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
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.
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.
In the Schema section, enable Auto detect.
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.
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.
Click CREATE TABLE.
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.
Open the user_acquisition_report table to inspect its schema:
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:
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2024-10-09 UTC.
[null,null,["Last updated 2024-10-09 UTC."],[[["\u003cp\u003eThis guide outlines how to access Google Analytics custom reports within BigQuery using the Google Analytics Sheets add-on and Google Drive BigQuery connector.\u003c/p\u003e\n"],["\u003cp\u003eThe process involves generating a custom report in Google Sheets, exposing its results sheet as a BigQuery external table, and then querying the report data directly from BigQuery.\u003c/p\u003e\n"],["\u003cp\u003eThis approach is applicable to any Google Analytics report type, with the User acquisition report serving as an example in the guide.\u003c/p\u003e\n"],["\u003cp\u003eThe guide provides step-by-step instructions for configuring the report in Google Sheets, connecting it to BigQuery as an external table, and finally querying the data within BigQuery.\u003c/p\u003e\n"]]],["This guide outlines the process of accessing custom Google Analytics reports from BigQuery. First, install the Google Analytics Sheets add-on and configure a custom report, such as the User acquisition report, specifying dimensions and metrics. Then, generate and schedule the report within Google Sheets. Next, link the report as a BigQuery external table using its Drive URI and specifying the sheet and cell range. Finally, query the external table directly from BigQuery.\n"],null,["# Access Google Analytics custom reports from BigQuery\n\nThis guide explains how you can access custom Google Analytics reports\nfrom BigQuery using the [Google Analytics Sheets add-on](//workspace.google.com/u/0/marketplace/app/ga4_reports_builder_for_google_analytics/589269949355) and\n[Google Drive BigQuery connector](//cloud.google.com/bigquery/docs/external-data-drive).\n\nThis guide shows how to generate a custom report using the Google\nAnalytics Sheets add-on, expose the report results sheet as a\nBigQuery external table, and then query the report data from BigQuery.\n\nThis guide uses the [User acquisition\nreport](/analytics/devguides/reporting/data/v1/predefined-reports#user_acquisition_report)\nas an example, but you can apply these steps to any report type.\n\nStep 1: Configure a custom report in Google Sheets\n--------------------------------------------------\n\n1. Install the Google Analytics Sheets add-on from the [Google Workspace\n Marketplace](//workspace.google.com/u/0/marketplace/app/ga4_reports_builder_for_google_analytics/589269949355).\n\n2. Open the **Extensions** → **GA4 Reports Builder for Google Analytics** →\n **Create new report** dialog.\n\n3. Configure the report by specifying the Analytics property, date range and report fields.\n\n4. Use the following fields for the User acquisition report:\n\n **Dimensions**\n - `firstUserDefaultChannelGroup`\n\n **Metrics**\n - `eventCount`\n - `keyEvents`\n - `totalRevenue`\n - `newUsers`\n - `engagedSessions`\n - `engagementRate`\n5. Leave the **Dimensions filters** and **Metrics filters** fields empty.\n\n Here's an image of the correct report settings:\n\n6. Click **Create Report** to create the report configuration\n in the current Sheets document:\n\n7. Select **Extensions** → **GA4 Reports Builder for Google Analytics** →\n **Run reports** to generate the report. A new tab with your\n report name will be created once the report is complete.\n\n8. Open the **Extensions** → **GA4 Reports Builder for Google Analytics** →\n **Schedule reports** dialog to set up periodic polling of the\n latest report data. Specify the schedule and click **Save**.\n\nStep 2: Connect a Google Sheet report document as a BigQuery external table\n---------------------------------------------------------------------------\n\nWith BigQuery, you can create a permanent table linked to\nyour external data source in Google Sheets. Use this\nfeature to connect the Google Analytics report spreadsheet\ncreated in the previous step as a BigQuery external table.\n\nThis step requires following the\n[Create Google Drive external tables guide](//cloud.google.com/bigquery/docs/external-data-drive#create_external_tables)\nfrom the BigQuery documentation. \n\n### Google Cloud console\n\n1. In the Google Cloud console, open the BigQuery page.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the **Explorer** panel, expand your project and select a dataset.\n\n3. Expand the\n more_vert\n **Actions** option and click **Open**.\n\n4. In the details panel, click **Create table**\n add_box.\n\n5. On the **Create table** page, in the **Source** section:\n\n - For **Create table from** , select **Drive**.\n\n - In the **Select Drive URI** field, enter the [Drive URI](//cloud.google.com/bigquery/docs/external-data-drive#drive-uri)\n of the Google Sheet document containing the report, in the form\n `https://docs.google.com/spreadsheets/d/[file_id]`.\n\n - For **File format** , select **Google Sheet**\n\n6. On the **Create table** page, in the **Destination** section:\n\n - For **Dataset name** , choose the appropriate dataset, and in the\n **Table name** field, enter the name of the table you're creating in\n BigQuery.\n\n - Verify that **Table type** is set to **External table**.\n\n7. In the **Sheet range**\n box, specify the sheet name and cell range to query. This should be the\n Sheet containing your report data:\n \u003cvar translate=\"no\"\u003esheet_name!top_left_cell_id:bottom_right_cell_id\u003c/var\u003e\n for a cell range. For our example report, the range can be specified as\n `User acquisition report!A:G`. Note how the sheet corresponding to\n the custom report name is mentioned in the range.\n\n8. In the **Schema** section, enable **Auto detect**.\n\n9. Expand the **Advanced options** section, in the **Header rows to skip**\n box specify the number of rows to omit. In our example, the report\n data starts with row *15*, which is the value that should be set in the\n input box.\n\n10. On the **Create table** page, in the **Destination** section:\n\n - For **Dataset** , choose the appropriate dataset, and in the\n **Table** field, enter the name of the table you're creating in\n BigQuery.\n\n - Verify that **Table type** is set to **External table**.\n\n11. Click **CREATE TABLE**.\n\n12. If necessary, select your account and then click **Allow** to give the\n BigQuery client tools access to Drive.\n\nHere is an example of an external BigQuery table configuration screen:\n\nStep 3: Query a Google Sheets report table from BigQuery\n--------------------------------------------------------\n\nYou can now run a query against the Google Sheets report table as if it\nwere a standard\nBigQuery table, subject to the [limitations](/bigquery/external-data-sources#external_data_source_limitations)\non external data sources.\n\n1. Open the `user_acquisition_report` table to inspect its schema:\n\n2. Click **Query** . In the suggested query, change `SELECT FROM` to\n `SELECT * FROM` to obtain all report fields, and then **Run query** to\n run a query against the external table.\n\nThe query in the following example will output a snippet of data in the\ntable for all columns:"]]
