从 BigQuery 访问 Google Analytics 自定义报告
使用集合让一切井井有条
根据您的偏好保存内容并对其进行分类。
本指南介绍了如何使用 Google Analytics 表格插件和 Google 云端硬盘 BigQuery 连接器，从 BigQuery 访问自定义 Google Analytics 报告。
本指南介绍了如何使用 Google Analytics 表格插件生成自定义报告，将报告结果表格公开为 BigQuery 外部表，然后从 BigQuery 中查询报告数据。
本指南以“用户获取情况”报告为例，但您可以将这些步骤应用于任何报告类型。
从 Google Workspace Marketplace 安装 Google Analytics 表格插件。
依次打开扩展程序 → 适用于 Google Analytics 的 GA4 报告制作工具 → 创建新报告对话框。
通过指定 Google Analytics 媒体资源、日期范围和报告字段来配置报告。
请为“用户获取情况”报告使用以下字段：
维度
firstUserDefaultChannelGroup
指标
eventCountkeyEventstotalRevenuenewUsersengagedSessionsengagementRate
将维度过滤条件和指标过滤条件字段留空。
以下图片显示了正确的报告设置：
点击创建报告，在当前的 Google 表格文档中创建报告配置：
依次选择扩展程序 → 适用于 Google Analytics 的 GA4 报告制作工具 → 运行报告，以生成报告。报告生成完毕后，系统会创建一个包含报告名称的新标签页。
依次打开扩展程序 → 适用于 Google Analytics 的 GA4 报告制作工具 → 安排报告生成时间对话框，以设置定期轮询最新报告数据。指定时间表，然后点击保存。
第 2 步：将 Google 表格报告文档连接为 BigQuery 外部表
借助 BigQuery，您可以在 Google 表格中创建一个链接到外部数据源的永久表。使用此功能将在上一步中创建的 Google Analytics 报告电子表格连接为 BigQuery 外部表。
此步骤要求您按照 BigQuery 文档中的“创建 Google 云端硬盘外部表”指南进行操作。
Google Cloud 控制台
在 Google Cloud 控制台中，打开 BigQuery 页面。
转到 BigQuery
在浏览器面板中，展开您的项目并选择数据集。
展开 more_vert 操作选项，然后点击打开。
在详情面板中，点击创建表
add_box。
在创建表页面的来源部分，执行以下操作：
在创建表页面的目标部分，执行以下操作：
在工作表范围框中，指定要查询的工作表名称和单元格范围。这应是包含报告数据的工作表：sheet_name!top_left_cell_id:bottom_right_cell_id（对于单元格范围）。对于我们的示例报告，范围可以指定为 User acquisition report!A:G。请注意，范围中如何提及与自定义报告名称对应的工作表。
在架构部分，启用自动检测。
展开高级选项部分，在要跳过的标题行数框中指定要省略的行数。在本例中，报告数据从第 15 行开始，这是应在输入框中设置的值。
在创建表页面的目标部分，执行以下操作：
点击创建表。
如有必要，选择您的账号，然后点击允许，以授予 BigQuery 客户端工具对 Google 云端硬盘的访问权限。
以下是外部 BigQuery 表配置屏幕的示例：
第 3 步：从 BigQuery 查询 Google 表格报告表
现在，您可以对 Google 表格报告表运行查询，就像对标准 BigQuery 表运行查询一样，但需遵守外部数据源的限制。
打开 user_acquisition_report 表以检查其架构：
点击查询。在建议的查询中，将 SELECT FROM 更改为 SELECT * FROM 以获取所有报告字段，然后点击运行查询以针对外部表运行查询。
以下示例中的查询将输出表中所有列的数据片段：
如未另行说明，那么本页面中的内容已根据知识共享署名 4.0 许可获得了许可，并且代码示例已根据 Apache 2.0 许可获得了许可。有关详情，请参阅 Google 开发者网站政策。Java 是 Oracle 和/或其关联公司的注册商标。
最后更新时间 (UTC)：2024-11-07。
[null,null,["最后更新时间 (UTC)：2024-11-07。"],[[["\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:"]]
