ga:itemQuantity in ga_sessions_YYYMMDD (Big Query)

Question

I'm trying to replicate the GA Quantity metric (ga:itemQuantity) using standardSQL and querying the GA export to BigQuery date partitioned tables (ga_sessions_YYYYMMDD).

I have tried the following, but 'quantity' is always null:

#standardSQL
SELECT
sum(hit.item.itemQuantity) as quantity
FROM `precise-armor-133520.1500218.ga_sessions_20170801` t
CROSS JOIN
UNNEST(t.hits) AS hit
order by 1 ASC;

Other metrics work and match 100% with the GA UI so I am assuming it's not a data export problem. For example:

SELECT 
sum( totals.totalTransactionRevenue ) as revenue, sum( totals.transactions ) as transactions
FROM `precise-armor-133520.1500218.ga_sessions_201708*` t
CROSS JOIN
UNNEST(t.hits) AS hit
group by `date`
order by `date` asc 

These totals match Revenue and Transactions (metrics) in GA UI respectively.

What is the standardSQL query for the GA metric quantity (ga:itemQuantity)?

Answer 1

In order to match "Quantity" in GA's web UI by each date, use the following standard SQL:

SELECT
   SUM(product.productQuantity)
   ,`date`
FROM
  `precise-armor-133520.1500218.ga_sessions_*`
  ,UNNEST(hits) AS hits
  ,UNNEST(hits.product) AS product
WHERE hits.eCommerceAction.action_type = "6"
and _TABLE_SUFFIX between '20170801' and FORMAT_DATE("%Y%m%d", CURRENT_DATE)
group by 2
order by 2 asc