BigQuery product level custom dimensions

Question

I'm trying to query basic productlevel dimension from GA in combination with custom dimensions at the product level and I can't get it to work:( Any help and/or suggestions are more than welcome! (current query is below)

SELECT
  date,
  hits.product.v2ProductName AS productName,
  hits.product.productSKU AS SKU,
  MAX(IF(hits.product.customDimensions.index=126,hits.product.customDimensions.value, null)) WITHIN hits AS customDimesion126,
  COUNT(hits.transaction.transactionId)
FROM
  TABLE_DATE_RANGE([XXX.ga_sessions_],TIMESTAMP('2016-10-16'), TIMESTAMP('2016-10-16'))

WHERE
  hits.transaction.transactionId IS NOT NULL
  
GROUP BY
  date,
  productName,
  SKU

@Nebi, Invalid mixture of scoped and unscoped aggregation functions — Arjen, Oct 18 '16 at 13:01
Have you seen this past question? You may be able to adapt the answer to your needs: http://stackoverflow.com/questions/39998428/select-several-event-params-in-a-single-row-for-firebase-events-stored-in-google/40000034. I will see if I can come up with a query as well. — Elliott Brossard, Oct 18 '16 at 14:21
@ElliottBrossard, no i did not see that one, thanks! but i tried a few changes based on that suggestion did not work either. — Arjen, Oct 18 '16 at 14:36

score 1 · Answer 1 · answered Oct 19 '16 at 17:48

I don't have access to a table with quite the same schema to be able to try this out, but does something like this query work? You'll need to enable standard SQL to run it (uncheck "Use Legacy SQL" under "Show Options"):

SELECT
  date,
  hits.product.v2ProductName AS productName,
  hits.product.productSKU AS SKU,
  (SELECT value
   FROM UNNEST(hits.product.customDimensions)
   WHERE index=126) AS customDimension126,
  COUNT(*)
FROM
  `your-dataset.ga_sessions_*` t, t.hits hits
WHERE
  _PARTITION_TIME = '2016-10-16' AND
  hits.transaction.transactionId IS NOT NULL
GROUP BY
  date,
  productName,
  SKU;

score 0 · Answer 2 · answered Oct 19 '16 at 18:56

Same here - I don't have access to a table with quite the same schema to be able to try this out - so below is shot in the air :o)

For BigQuery Legacy SQL - as I wanted to preserve your original query as much as possible

SELECT
  DATE,
  productName,
  SKU,
  customDimesion126,
  SUM(cnt) AS transactions
FROM (
  SELECT
    DATE,
    hits.product.v2ProductName AS productName,
    hits.product.productSKU AS SKU,
    MAX(IF(hits.product.customDimensions.index=126,hits.product.customDimensions.value, NULL)) WITHIN hits AS customDimesion126,
    COUNT(hits.transaction.transactionId) WITHIN hits AS cnt
  FROM
    TABLE_DATE_RANGE([XXX.ga_sessions_],TIMESTAMP('2016-10-16'), TIMESTAMP('2016-10-16'))
  WHERE
    hits.transaction.transactionId IS NOT NULL
)  
GROUP BY
  DATE,
  productName,
  SKU,
  customDimesion126

BigQuery product level custom dimensions

2 Answers2