Sarjak Patel Sarjak Patel - 6 months ago 230
SQL Question

BigQuery - Selecting multiple Product-scoped custom dimensions in BigQuery

I am trying to query multiple product-scoped custom dimensions in Google BigQuery. The result is something like this:

Current set of results

I would like to get the combined rows in the results, something like this:

Expected set of results

Is this possible with a query in BigQuery?

Edit

Please see the existing query that I used:

SELECT IF(hits.product.customDimensions.index=21, hits.product.customDimensions.value, NULL) cd21,
IF(hits.product.customDimensions.index=22,
hits.product.customDimensions.value, NULL) cd22
FROM [<table_id>.ga_sessions_20160510]
WHERE hits.product.productListName LIKE "%test%"
AND hits.product.isImpression IS TRUE
AND hits.product.customDimensions.index IN (21,22)

Answer

I rewrote to an example using public data set:

SELECT fullVisitorId, visitId, hits.hitNumber, hits.time,
  max(IF(hits.customDimensions.index = 1, hits.customDimensions.value, NULL)) within RECORD cd21,
  max(IF(hits.customDimensions.index = 2, hits.customDimensions.value, NULL)) within RECORD cd22,
FROM
  [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
WHERE
  hits.customDimensions.index IN ( 1,2 )
and fullVisitorId = '712553853382222331'

The output is:

+-----+--------------------+------------+----------------+-----------+-------+--------+--+
| Row | fullVisitorId      | visitId    | hits_hitNumber | hits_time | cd21  | cd22   |  |
+-----+--------------------+------------+----------------+-----------+-------+--------+--+
| 1   | 712553853382222331 | 1378804218 | 1              | 0         | Vests | Bronze |  |
+-----+--------------------+------------+----------------+-----------+-------+--------+--+
| 2   | 712553853382222331 | 1378804218 | 2              | 9611      | Vests | Bronze |  |
+-----+--------------------+------------+----------------+-----------+-------+--------+--+
| 3   | 712553853382222331 | 1378804218 | 3              | 10664     | Vests | Bronze |  |
+-----+--------------------+------------+----------------+-----------+-------+--------+--+
| 4   | 712553853382222331 | 1378804218 | 5              | 25377     | Vests | Bronze |  |
+-----+--------------------+------------+----------------+-----------+-------+--------+--+

As you may notice it uses WITHIN syntax to read multiple entries from the same RECORD. This way you were get scoped aggregation.

Please note that on GA data, you may have multiple hits, and you may need to further aggregate this output if you want to retain only one row.


The Google Analytics team provides a sample dataset that you can use to understand the nature of the data generated. You can add this data to your view in the BigQuery UI by adding the project google.com:analytics-bigquery using the project menu (the drop-down next to the project name, Switch to Project ➪ Display Project). Alternately, you can navigate to the BigQuery UI link https://bigquery.cloud.google.com/project/google.com:analytics-bigquery After you add the project, the sample dataset (LondonCycleHelmet) appears in the navigation panel. It contains two tables:

  • ga_sessions_20130910
  • refunds_201309