google-analytics - 使用谷歌分析数据将嵌套行转置为bigquery中的列

Question

我有兴趣使用自定义维度属性来吸引访问者，其中每一行都是一个唯一的 fullvisitorid，列是所需的 customdimension.values。

以伦敦 Helm 为例，这里我用我感兴趣的两个自定义尺寸来吸引访问者:

SELECT fullvisitorid, customDimensions.index, customDimensions.value
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
where customDimensions.index in (2,3)
group by fullvisitorid, customDimensions.index, customDimensions.value

它给出的结果如下:

+---------------+------------------------+------------------------+
| fullvisitorid | customDimensions_index | customDimensions_value |
+---------------+------------------------+------------------------+
|             1 |                      2 | Bronze                 |
|             1 |                      3 | Yes                    |
|             2 |                      2 | Bronze                 |
|             2 |                      3 | No                     |
|             3 |                      2 | Bronze                 |
|             3 |                      3 | Yes                    |
|             4 |                      2 | Platinum               |
|             4 |                      3 | Yes                    |
+---------------+------------------------+------------------------+

我想要转置值，其中 customDimension_index 2 是颜色，customDimension_value 3 是 yesno，因此结果将如下所示:

+---------------+----------+-------+
| fullvisitorid |  color   | yesno |
+---------------+----------+-------+
|             1 | Bronze   | Yes   |
|             2 | Bronze   | No    |
|             3 | Bronze   | Yes   |
|             4 | Platinum | Yes   |
+---------------+----------+-------+

我可以分别提取一个然后另一个并加入 fullvisitorid，但希望能够通过这种方式一步提取数据。谢谢!

Answer 1

2020-01 更新:#standard SQL 更新

SELECT
  fullvisitorid,
  (SELECT value FROM UNNEST(customDimensions) WHERE index=2) color, 
  (SELECT value FROM UNNEST(customDimensions) WHERE index=3) yesno, 
FROM `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`

<小时/>

以前:

Mosha 的答案是正确的，但我想添加这个答案，因为它利用了 GA 记录的嵌套性质:

SELECT
  fullvisitorid,
  FIRST(IF(customDimensions.index=2, customDimensions.value, NULL)) WITHIN RECORD color, 
  FIRST(IF(customDimensions.index=3, customDimensions.value, NULL)) WITHIN RECORD yesno
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
WHERE customDimensions.index in (2,3)

原因:WITHIN RECORD 不运行 GROUP BY(这会消耗资源，因为它必须查找可能具有相同 customerid 的任何记录并按其分组)，而只在各个行内部查找。

如果客户 ID 有多于一行(例如，他们访问过一次青铜级/是，后来访问过白金级/否)，结果将发出每一行和组合，而不是仅第一行。