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 theUser acquisition
reportas an example, but you can apply these steps to any report type.
Step 1: Configure a custom report in Google Sheets
ClickCreate Reportto create the report configuration
in the current Sheets document:
SelectExtensions→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.
Open theExtensions→GA4 Reports Builder for Google Analytics→Schedule reportsdialog to set up periodic polling of the
latest report data. Specify the schedule and clickSave.
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.
In theExplorerpanel, expand your project and select a dataset.
Expand themore_vertActionsoption and clickOpen.
In the details panel, clickCreate tableadd_box.
On theCreate tablepage, in theSourcesection:
ForCreate table from, selectDrive.
In theSelect Drive URIfield, enter theDrive URIof the Google Sheet document containing the report, in the formhttps://docs.google.com/spreadsheets/d/[file_id].
ForFile format, selectGoogle Sheet
On theCreate tablepage, in theDestinationsection:
ForDataset name, choose the appropriate dataset, and in theTable namefield, enter the name of the table you're creating in
BigQuery.
Verify thatTable typeis set toExternal table.
In theSheet 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_idfor a cell range. For our example report, the range can be specified asUser acquisition report!A:G. Note how the sheet corresponding to
the custom report name is mentioned in the range.
In theSchemasection, enableAuto detect.
Expand theAdvanced optionssection, in theHeader rows to skipbox specify the number of rows to omit. In our example, the report
data starts with row15, which is the value that should be set in the
input box.
On theCreate tablepage, in theDestinationsection:
ForDataset, choose the appropriate dataset, and in theTablefield, enter the name of the table you're creating in
BigQuery.
Verify thatTable typeis set toExternal table.
ClickCREATE TABLE.
If necessary, select your account and then clickAllowto 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 thelimitationson external data sources.
Open theuser_acquisition_reporttable to inspect its schema:
ClickQuery. In the suggested query, changeSELECT FROMtoSELECT * FROMto obtain all report fields, and thenRun 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:
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Missing the information I need","missingTheInformationINeed","thumb-down"],["Too complicated / too many steps","tooComplicatedTooManySteps","thumb-down"],["Out of date","outOfDate","thumb-down"],["Samples / code issue","samplesCodeIssue","thumb-down"],["Other","otherDown","thumb-down"]],["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:"]]
