使用Google Analytics(分析)数据将嵌套行转换为bigquery中的列 [英] Transpose nested rows into columns in bigquery with google analytics data

问题描述

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

I am interested in pulling visitors with custom dimension attributes, where each row is a unique fullvisitorid and columns are desired customdimension.values.

以伦敦头盔为例，在这里，我用感兴趣的两个自定义尺寸吸引访客:

Using the london helmets as an example, here I am pulling visitors with the two custom dimensions I am interested in:

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，所以结果看起来像这样:

I would like the values transposed, where customDimension_index 2 is color, and customDimension_value 3 is yesno, so results would look like this instead:

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

我可以分别拉一个然后再拉另一个，然后加入fullvisitorid，但是希望能够在一个步骤中以这种方式拉出数据.谢谢！

I could pull one then the other separately and join on fullvisitorid, but hoping to be able to pull out data this way in a single step. Thanks!

推荐答案

以下是解决方法:

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

它依赖于以下事实:包括FIRST在内的任何聚合函数都会忽略NULL

It relies on the fact that any aggregation function, including FIRST, ignores NULLs

这篇关于使用Google Analytics(分析)数据将嵌套行转换为bigquery中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！