BigQuery 实战宝典

此功能仅在 Analytics 360(Google Marketing Platform 的组成部分)中提供。
详细了解 Google Marketing Platform

本文将举例说明如何构建可导出到 BigQuery 的 Google Analytics(分析)数据的查询。我们提供了一个样本数据集,以便您可以练习使用本文中列举的这些查询。

本文包含的主题:

查询优化

您运行的每个查询都会消耗您每月的数据处理限额。如果您选择无关的字段,则会增加需要处理的数据量。结果,您甚至用完了超出必要数量的每月限额。优化查询可使您的每月数据处理限额得到有效利用。

有关定价的详细信息,请点击此链接

仅选择您需要的内容

在构建查询时,建议您只选择 SELECT 语句内的相关字段。不要调用无关字段,这样就减少了处理查询所需的数据量和时间量。

例如:避免使用通配符

无效形式:使用通配符
SELECT *
FROM [表格名称];

 

有效形式:使用字段名称以避免不必要的处理操作
SELECT 字段 1, 字段 2
FROM [表格名称];

允许缓存

如果可能,尽量避免将函数用作字段。函数(例如 NOW()TODAY())会返回变化的结果,这就阻止了查询缓存,因而返回速度更快。相反,应使用具体的时间和日期。

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

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

使用中间表格
SELECT 字段 1, 字段 2
FROM [数据集名称.表格名称];

查询调试

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
使用 UNION ALL 的 3 天
#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
使用 _TABLE_SUFFIX 的过去 1095 天
#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
使用 TABLE_DATE_RANGE 的过去 1095 天
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
使用 _TABLE_SUFFIX 的过去 36 个月
#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
使用 TABLE_DATE_RANGE 的过去 36 个月
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
使用 _TABLE_SUFFIX 的过去 3 年
#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
使用 TABLE_DATE_RANGE 的过去 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
(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
使用 UNION ALL&_TABLE_SUFFIX 的过去 3 年加上今天的数据(当日)
注意:此查询示例不适用于 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

使用多个 TABLE_DATE_RANGE 的过去 3 年加上今天的数据(当日)
注意:此查询示例不适用于 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

在这个查询中,我们将匹配类型限制为 PAGES,以避免看到事件或交易互动。输出的每一行代表一次网页浏览,按照 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 hitsWITHIN RECORD 对 BigQuery 中的重复字段里的条件进行求值。
  • 对于每个自定义维度,都会对 MAX 里的条件进行求值,但对于那些既不符合 index=1 (hits) 也不符合 index=2 (sessions) 的自定义维度,它将返回 NULL
  • 返回最大的值,对于匹配而言,将返回自定义维度 1 的值,对于会话而言,将返回自定义维度 2 的值,因为其他所有的值均为 NULL

高级查询示例

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

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

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

由购买了 A 产品的客户购买的产品(传统型电子商务)
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [‘数据集名称’ ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘数据集名称’ ]
  WHERE hits.item.productName CONTAINS '产品名称 A'
   AND totals.transactions>=1
  GROUP BY fullVisitorId
)
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != '产品名称 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)。

顶级查询(绿色)中的规则(WHEREAND 语句)排除了当 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 中,这称为“广播式 JOIN”。如果容量大小超出 8 MB,则需要触发一个“洗牌式 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 数据集名称’ ]
 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 数据集名称’ ]
 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 数据集名称’ ]
  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’),还包括 product-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 数据集名称’ ]
    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 [ ‘导入的数据集名称’ ] AS 'Imported_DataSet'
    JOIN (
      
SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [‘GA 数据集名称’ ]
      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 )
);

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

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

本文是否对您有帮助?
您有什么改进建议?