BigQuery 实战宝典

此功能仅在 Google Marketing Platform 随附的 Analytics 360 中提供。
详细了解 Google Marketing Platform。

本文将举例说明如何构建查询来选出要导出到 BigQuery 的 Google Analytics（分析）数据。我们提供了一个样本数据集，以便您可以练习使用本文中列举的部分查询。

本文包含的主题：

查询优化

您运行的每个查询都会消耗您每月的数据处理限额。如果您选择无关的字段，则会增加需要处理的数据量，从而无端浪费每月限额。优化查询可使您的每月数据处理限额得到有效利用。

详细了解定价。

仅选择所需字段

在构建查询时，建议您在 SELECT 语句中只选择相关的字段。由于没有调用无关字段，因而减少了处理查询所需的数据量和时间长度。

例如：避免使用通配符运算符

反面示例：使用通配符运算符
SELECT *
FROM [table name];

 

优秀示例：使用字段名称以避免不必要的处理操作
SELECT field1, field2
FROM [table name];

允许缓存

如果可能，尽量避免将函数用作字段。函数（例如 NOW() 或 TODAY()）会返回变化的结果，这就阻止了对查询进行缓存，无法更快地返回结果。最好使用具体的时间和日期。

目前，使用通配符对多个表执行查询时，即使选中了“使用缓存的结果”选项，也不支持缓存的结果。如果多次运行相同的通配符查询，系统会对每个查询计费。了解详情

将中间表用于常用的子查询

如果您发现自己重复使用了某个特定查询作为子查询，可以通过点击查询结果上方的保存为表将此查询保存为中间表。然后，您可以在查询的 FROM 部分引用此表，这样就能减少需处理的数据量，并缩短处理时间。

使用中间表
SELECT field1, field2
FROM [Dataset name.table name];

查询调试

BigQuery 会在您构建代码时对其进行调试。在撰写窗口中，调试信息会显示在查询正下方。也可以通过包含 dryRun 标志的 API 提供调试。

有效查询带有一个绿色指示器，点击之后会显示由该查询处理的数据量。此功能允许用户在运行查询之前考虑数据优化，以避免不必要的数据处理。

Query Debugging - Success

 

无效查询带有一个红色指示器，点击之后会显示有关错误的信息，并指出发生错误的行和列。在以下示例中，GROUP BY 语句为空，并且此错误已被精确定位。

Query Debugging - Error

 

技巧和最佳做法

使用样本数据集

以下示例使用了 Google Analytics（分析）样本数据集。

要对您自己的数据使用这些查询，只需使用您自己的项目和数据集名称替换示例中的项目和数据集名称即可。

使用标准 SQL 与使用旧版 SQL

BigQuery 支持以下两种 SQL 方言：

有关这两种方言之间的差异，请参阅迁移到标准 SQL。

现在，标准 SQL 是查询 BigQuery 中存储的数据的首选 SQL 方言。

如需了解如何在 BigQuery 界面、CLI、API 或您使用的任何界面中启用标准 SQL，请参阅启用标准 SQL 一文。

最简单的入门方法是在标准 SQL 查询的顶部添加注释“standardSQL”，如后续示例所示。

在旧版 SQL 中，系统每天都会将 Google Analytics 360 数据传递到新表中。如后续示例所示，要同时查询多个表，可以使用英文逗号分隔表名，使用 TABLE_DATE_RANGE 表通配符函数，或使用多个以英文逗号分隔的 TABLE_DATE_RANGE 函数。

查询多个表

以下示例介绍了标准 SQL 查询和旧版 SQL 查询在处理相同数据上的不同之处。

3 天

标准 SQL
3 天（使用 UNION ALL）
#standardSQL
WITH ga_tables AS (
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160802`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160803`
GROUP BY date

)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC
旧版 SQL
3 天（使用以英文逗号分隔的表名）
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
[bigquery-public-data.google_analytics_sample.ga_sessions_20160801],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160802],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160803]
GROUP BY
date
ORDER BY
date ASC

 

过去 1095 天

标准 SQL
过去 1095 天（使用 _TABLE_SUFFIX）
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1095 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
旧版 SQL
过去 1095 天（使用 TABLE_DATE_RANGE）
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -1095, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

过去 36 个月

标准 SQL
过去 36 个月（使用 _TABLE_SUFFIX）
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 36 MONTH))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
旧版 SQL
过去 36 个月（使用 TABLE_DATE_RANGE）
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -36, 'MONTH'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

过去 3 年

标准 SQL
过去 3 年（使用 _TABLE_SUFFIX）
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
旧版 SQL
过去 3 年（使用 TABLE_DATE_RANGE）
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

特定日期范围

标准 SQL
特定日期范围（使用 _TABLE_SUFFIX）
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20160801' AND '20170731'
GROUP BY date
ORDER BY date ASC
旧版 SQL
特定日期范围（使用 TABLE_DATE_RANGE）
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2016-08-01'), TIMESTAMP('2017-07-31')))
GROUP BY
date
ORDER BY
date ASC

过去 3 年加上今天的数据（当日）

标准 SQL
过去 3 年加上今天的数据（当日）（使用 UNION ALL 和 _TABLE_SUFFIX）
注意：此查询示例不适用于 Google Analytics（分析）公开数据集，因为目前没有当日表。
#standardSQL
WITH ga_tables AS ( SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
GROUP BY date
)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC

旧版 SQL

过去 3 年加上今天的数据（当日）（使用多个 TABLE_DATE_RANGE）
注意：此查询示例不适用于 Google Analytics（分析）公开数据集，因为目前没有当日表。
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))),
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_intraday_],
DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), CURRENT_TIMESTAMP()))
GROUP BY
date
ORDER BY
date ASC

 

 

基本查询示例

本节说明如何使用来自示例 Google Analytics（分析）数据的指标和维度构建基本查询。

每个 [维度] 的总 [指标] 是多少？

下面的示例脚本用以回答这个问题：2017 年 7 月每个设备浏览器上产生的总交易次数是多少？

标准 SQL

2017 年 7 月每个设备浏览器的总交易次数
#standardSQL
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
device.browser
ORDER BY
total_transactions DESC

旧版 SQL

2017 年 7 月每个设备浏览器的总交易次数
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
device.browser
ORDER BY
total_transactions DESC

 

 

每个 [维度] 的平均跳出率是多少？

实际跳出率是指网页浏览次数只有一次的访问所占的百分比。下面的示例脚本用以回答这个问题：每个流量来源的实际跳出率是多少？

标准 SQL

2017 年 7 月每个流量来源的跳出率
#standardSQL
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
source )
ORDER BY
total_visits DESC

旧版 SQL

2017 年 7 月每个流量来源的跳出率
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
source )
ORDER BY
total_visits DESC

 

 

按购买者类型（购买者或非购买者）划分的平均产品网页浏览量

下面的示例脚本用以回答这个问题：2017 年 7 月购物用户的平均产品网页浏览量是多少？

标准 SQL

2017 年 7 月购物用户的平均产品网页浏览量
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions >=1
GROUP BY
users )

旧版 SQL

2017 年 7 月购物用户的平均产品网页浏览量
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions >=1
GROUP BY
users )

 

 

下面的示例脚本用以回答这个问题：2017 年 7 月未购物用户的平均产品网页浏览量是多少？

标准 SQL

2017 年 7 月未购物用户的平均产品网页浏览量
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions IS NULL
GROUP BY
users )

旧版 SQL

2017 年 7 月未购物用户的平均产品网页浏览量
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions IS NULL
GROUP BY
users )

 

 

每位购买者的平均交易次数

下面的示例脚本用以回答这个问题：2017 年 7 月每位购物用户的平均总交易次数是多少？

标准 SQL

2017 年 7 月每位购物用户的平均交易次数
#standardSQL
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

旧版 SQL

2017 年 7 月每位购物用户的平均交易次数
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

 

 

每次会话的平均支出金额

下面的示例脚本用以回答这个问题：2017 年 7 月每次会话的平均支出金额是多少？

标准 SQL

2017 年 7 月每次会话的平均支出金额
#standardSQL
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

旧版 SQL

2017 年 7 月每次会话的平均支出金额
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

 

 

命中顺序

下面的示例脚本用以回答这个问题：网页浏览顺序是什么？

标准 SQL

2017 年 7 月用户的网页浏览顺序
#standardSQL
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
UNNEST(hits) as hits
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

旧版 SQL

2017 年 7 月用户的网页浏览顺序
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

在此查询中，我们将命中类型限制为 PAGE，以避免看到事件或交易互动。输出的每一行代表一次网页浏览，按照 SELECT 语句中的默认字段顺序显示。

 

 

匹配级或会话级的多个自定义维度

命中级的自定义维度
SELECT fullVisitorId, visitId, hits.hitNumber, hits.time,
MAX(IF(hits.customDimensions.index=1,
hits.customDimensions.value,
NULL)) WITHIN hits AS customDimension1,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

 

会话级的自定义维度
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

在每个查询中：

SELECT 语句用于查询相关的维度和指标字段。

MAX 函数：

  • 将自定义维度作为新列返回。您可以重复调用该函数，将多个自定义维度作为新列返回。
  • WITHIN hits 和 WITHIN RECORD 对 BigQuery 中的重复字段里的条件进行求值。
  • 系统会评估每个自定义维度是否满足 MAX 内的条件，但对于不满足 index=1（命中）或 index=2（会话）条件的自定义维度，则会返回 NULL。
  • 返回最大的值，对于命中而言，此值为自定义维度 1 的值，对于会话而言，此值为自定义维度 2 的值，因为其他所有的值均为 NULL。

高级查询示例

在熟悉了简单查询之后，现在您可以使用 BIgQuery 中提供的高级函数和功能来构建其他一些查询了。

由购买了 A 产品的客户购买的产品（传统型电子商务）

以下骨架脚本用来回答这个问题：购买了 A 产品的客户还购买了其他什么产品？

由购买了 A 产品的客户购买的产品（传统型电子商务）
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [‘Dataset Name’ ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘Dataset Name’ ]
  WHERE hits.item.productName CONTAINS 'Product Item Name A'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != 'Product Item Name A'
GROUP BY other_purchased_products
ORDER BY quantity DESC;
  1. 在第一行中，我们选择了用户购买的其他所有产品，并使用汇总函数 COUNT() 来计算其他每种产品的购买数量。然后将结果显示在一个标记为 quantity 的字段中，其关联产品显示在标记为 other_purchased_products 的产品字段中。
  2. 在灰色子查询中，我们只选择已经完成交易 (totals.transactions>=1) 并且在某次交易中购买了产品 A (WHERE hits.item.productName CONTAINS '产品名称 A') 的唯一身份用户 (fullVisitorId)。

顶级查询（绿色）中的规则（WHERE 和 AND 语句）排除了当 hits.item.productName 为 null 以及包含产品 A 时的那些值。

以下示例要查询的是，购买了 Brighton Metallic Pens - Set of 4 的客户还购买了其他什么产品？

由购买了“Brighton Metallic Pens (Set of 4)”的客户在 2013 年 6 月 24 日购买的其他产品
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [GoogleStore.ga_sessions_20130624]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [GoogleStore.ga_sessions_20130624]
  WHERE hits.item.productName CONTAINS 'Brighton Metallic Pens - Set of 4'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='Brighton Metallic Pens - Set of 4'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

在 Dremel/BigQuery 中，使用 WHERE expr IN 会触发一个 JOIN，并且有大小限制；具体而言，JOIN 右侧的大小（本例中为访问者数量）必须小于 8 MB。在 Dremel 中，这称为“Broadcast JOIN”。如果大小超出 8 MB，则需要触发一个“Shuffle JOIN”，这可以使用 JOIN EACH 语法来完成。可惜，这不能使用 IN 来完成，但可以使用 JOIN 重写相同的查询。

由购买了 A 产品的客户购买的产品（增强型电子商务）

该查询类似于前面的骨架查询，但适用于增强型电子商务。该查询还会使用 TABLE_DATE_RANGE 来查询多天的数据。

由购买了 A 产品的客户购买的产品（增强型电子商务）
SELECT hits.product.productSKU AS other_purchased_products, COUNT(hits.product.productSKU) AS quantity
FROM SELECT fullVisitorId, hits.product.productSKU, hits.eCommerceAction.action_type FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14')))
WHERE fullVisitorId IN (
  SELECT fullVisitorId
FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14'))
WHERE hits.product.productSKU CONTAINS '10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY fullVisitorId )
AND hits.product.productSKU IS NOT NULL
AND hits.product.productSKU !='10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

用户购买之前的平均互动次数

下面是一个 JOIN() [...] ON 命令查询示例，该查询只与 Google Analytics（分析）数据相关。

以下骨架脚本用来回答这个问题：用户购买之前的平均互动次数是多少？

用户购买之前的互动次数
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_1’
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_2’
ON Alias_Name_1.hits.item.productSku = Alias_Name_2.hits.item.productSku;
  1. 第一行执行主要的数学运算，以计算每个产品的用户平均互动次数。此外，此查询还在两个分别名为“Alias_Name_1”和“Alias_Name_2”的子查询之间建立了一个联接。
  2. ‘Alias_Name_1’ 用来产生一个使用汇总函数 SUM() 的字段，针对某个产品的所有已记录的匹配次数进行汇总。
  3. “Alias_Name_2”用于通过 COUNT() 函数计算每种产品由用户产生的命中数。
  4. 最后一行显示共用字段 (hits.item.productSku)，由两个连接起来的数据集共享。

以下示例要查询的是，在 2013 年 9 月 10 日，用户购买之前的平均互动次数是多少？

2013 年 9 月 10 日用户购买之前的互动次数
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku  ) AS one
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS two
ON one.hits.item.productSku = two.hits.item.productSku;

每种产品的库存销售百分比

下例中的查询不仅与 Google Analytics（分析）数据相关，还与非 Google Analytics（分析）数据相关。通过将这两种数据集结合起来，您可以更细致地了解用户行为。您可以将非 Google Analytics（分析）数据导入 BigQuery 中，但是请注意，这一操作会产生当月的数据存储费用。

以下骨架脚本用来回答这个问题：每种产品的库存销售百分比是多少？

每种产品的库存销售百分比
SELECT AnalyticsImport.product_data_20130728.productId, ((( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold )) * 100 ) AS percentage_of_stock_sold
FROM [ ‘Imported_DataSet’ ]
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM [‘GA Dataset’ ]
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS ‘Alias_Name’
ON Imported_DataSet.’productId_field’ = Alias_Name.hits.item.productSku;
  1. 第一行会输出两个字段：一个字段包含所有的产品 ID，另一个是数学运算，会显示与相应产品 ID 对应的库存销售百分比。
  2. 由于此查询与两个数据集有关，因此需要使用 JOIN() ... ON 函数。该命令会根据两个数据集的共用字段，将它们的各行联接起来。在本例中，这两个数据集是“Imported_DataSet”和“Alias_Name”。
  3. [ ‘Imported_DataSet’ ] 是非 Google Analytics（分析）数据。此数据集包含指明剩余库存的指标字段 (Imported DataSet.’stock_left_field’) 和产品 ID 维度字段 (Imported_DataSet.’productId_field’)。
  4. ‘Alias_Name’ 是给灰色子查询所返回的数据指定的名称。该子查询利用 Google Analytics（分析）数据来计算每种产品的已售总数量。
  5. 最后一行使用 ON 语句来显示这两个数据集的共用字段和联接部分。

此查询中的许多变量都用所属数据集的名称作为前缀（例如，Imported_DataSet.’productId_field’、Alias_Name.quantity_sold）。这是为了指明您所选择的字段，并指明该字段所属的数据集。

以下示例要查询的是，2013 年 7 月 28 日每种产品的库存销售百分比是多少？

2013 年 7 月 28 日每种产品的库存销售百分比
SELECT AnalyticsImport.product_data_20130728.productId, ( ( ( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold ) ) * 100 ) AS percentage_of_stock_sold
FROM AnalyticsImport.product_data_20130728
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM GoogleStore.ga_sessions_20130728
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS one
ON AnalyticsImport.product_data_20130728.productId = one.hits.item.productSku
ORDER BY percentage_of_stock_sold DESC;

每种产品的盈利能力

以下骨架脚本用来回答这个问题：每种产品的盈利能力是多少？

每种产品的利润
SELECT Alias_Name.hits.item.productSku, ( Imported_DataSet.’product profit field’ * Alias_Name.quantity ) AS profit
FROM (
  SELECT Alias_Name.hits.item.productSku, Imported_DataSet.’product profit field’
  FROM [ ‘Imported Data Set’ ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ ‘GA Dataset Name’ ]
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS ‘Alias_Name’
  ON Imported_DataSet.productId = Alias_Name.hits.item.productSku );
  1. 第一行包含数学运算，以计算每种产品的总利润。
  2. 灰色子查询利用非 Google Analytics（分析）数据，收集关于产品获得多少销售利润的数据。
  3. 红色子查询是 Google Analytics（分析）数据子查询，将与非 Google Analytics（分析）数据进行连接。它计算出每种产品的已销售数量。
  4. 最后一行使用 ON 语句来指明两个数据集的共享字段。在本例中，该字段为产品 ID 编号。

以下示例要查询的是，2013 年 7 月 28 日每种产品的盈利能力是多少？

2013 年 7 月 28 日每种产品的利润
SELECT two.hits.item.productSku, ((AnalyticsImport.product_data_20130728.productprice-AnalyticsImport.product_data_20130728.productcost)*two.quantity) AS profit
FROM (
  SELECT two.hits.item.productSku, AnalyticsImport.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice
  FROM AnalyticsImport.product_data_20130728
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM GoogleStore.ga_sessions_20130728
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS two
  ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku );

利润的计算方法是，将产品的销售价格减去制造成本所得出的差额。此信息存放在非 GA 数据集中。

每种产品的真实盈利能力（考虑到退款）

以下骨架脚本用来回答这个问题：每种产品的真实盈利能力是多少？

每种产品的实际利润
SELECT Alias_Name.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT Alias_Name.hits.item.productSku, ( ( Imported_DataSet.productprice - Imported_DataSet.productcost ) * Alias_Name.quantity ) AS gross_profit, ( ( Imported_DataSet.refunddeliveryprice + Imported_DataSet.productprice ) * Imported_DataSet.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT Alias_Name.hits.item.productSku, Imported_DataSet.productcost, Alias_Name.quantity, Imported_DataSet.productprice, Imported_DataSet.refunddeliveryprice, Imported_DataSet.refundquantity
    FROM [ ‘Imported DataSet Name’ ] AS 'Imported_DataSet'
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [‘GA Dataset Name’ ]
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS 'Alias_Name'
    ON Imported_DataSet.productId = Alias_Name.hits.item.productSku ) );
  1. 此查询与查询“每种产品的盈利能力是多少？”非常类似。仅有的两点区别在于灰色子查询中的非 Google Analytics（分析）数据集和第一行中用于计算实际利润的数学运算。
  2. 在非 Google Analytics（分析）数据集中，我们还计算了花在退款上的总金额（在红色子查询的 SELECT 语句中）。
  3. 然后，我们在第一行执行了数学运算，以计算实际利润，方法是从毛利润中减去花在退款上的销售收入。

如需详细了解此查询，请参阅“每种产品的盈利能力”部分。

以下示例要查询的是，2013 年 7 月 28 日每种产品的真实盈利能力是多少？

2013 年 7 月 28 日每种产品的实际利润
SELECT two.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT two.hits.item.productSku, ( ( AnalyticsImport.product_data_20130728.productprice - AnalyticsImport.product_data_20130728.productcost ) * two.quantity ) AS gross_profit, ( ( AnalyticsImport.product_data_20130728.refunddeliveryprice + AnalyticsImport.product_data_20130728.productprice ) * AnalyticsImport.product_data_20130728.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT two.hits.item.productSku, Analytics.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice, AnalyticsImport.product_data_20130728.refunddeliveryprice, AnalyticsImport.product_data_20130728.refundquantity
    FROM AnalyticsImport.product_data_20130728
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM GoogleStore.ga_sessions_20130728
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS two
    ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku ) );

实际利润指的是考虑了退款产品之后的产品盈利能力。计算产品退款总额的方法如下：

产品退款总额 =（产品价格 + 产品退运费用）* 退款产品数量

该内容对您有帮助吗？

您有什么改进建议？

需要更多帮助？

请尝试以下步骤：

向帮助社区发帖 向社区成员寻求答案
与我们联系 向我们提供更多信息，以便我们帮您解决问题
true
learning path
选择适合您的学习路线

请访问 google.com/analytics/learn，这是一项新资源，有助于您充分利用 Google Analytics（分析）4。这个新网站包含各种关于 Google Analytics（分析）的视频、文章和引导式流程，还提供了指向 Discord、博客、YouTube 频道和 GitHub 代码库的相关链接。

立即开始学习！

搜索
清除搜索内容
关闭搜索框
主菜单
3371740899503396840
true
搜索支持中心
true
true
true
true
true
69256
false
false