CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
Aaron Harris
Aaron Harris

Reputation: 415

Can't query hits.product row in Bigquery Google Analytics Export

Currently running into issues querying data from the hits.product row in BQ as it seems to be behaving differently to the other hit level data in the GA export. Here is my query below. Works fine if looking at hits.page but not so for the hits.product fields.

 SELECT
  case when hits.product.productbrand = "Gucci" then "gucci" else " " end
  FROM
  `xxx.xxx.ga_sessions_20170822`,
  unnest(hits) hits

The error I get is "Error: Cannot access field productBrand on a value with type ARRAY> at [2:26]"

Upvotes: 0

Views: 1429

Answers (1)

Willian Fuks
Willian Fuks

Reputation: 11787

hits.product is also an ARRAY field, so you also need to unnest it. Maybe this works for you:

 SELECT
   CASE WHEN prods.productbrand = "Gucci" THEN "gucci" ELSE " " END brand
 FROM
    `xxx.xxx.ga_sessions_20170822`,
 UNNEST(hits) hits,
 UNNEST(hits.product) prods

Upvotes: 2

Related Questions