Good night,
I am trying to create a query on BigQuery which include the following dimensions: Date, ProductListName, ProductSKU, ProductListPosition and the following metrics:Product List Views, Product List Clicks, Quantity and Number units added to cart. Nevertheless, Quantity and Units added to cart are not working as expected. Both always show the same result (0). I have already check with Google Analytics the correct results so I know the figure I would have got if the query was correct.
Below these lines, the query I did Could anyone please help me with that? Thanks in advance
SELECT
PARSE_DATE("%Y%m%d",date) AS Fecha,
product.productListName AS Lista_Producto,
product.productSKU AS SKU,
product.productListPosition AS Posicion_En_Lista,
SUM(IF(product.isImpression = true,1,0)) AS Vistas_Producto,
SUM(IF(product.isClick = true,1,0)) AS Clics_Producto,
SUM(IF(hits.eCommerceAction.action_type = "3",1,0)) AS AddToCart,
SUM(IF(hits.eCommerceAction.action_type = "6",1,0)) AS Cantidad_Comprada
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
,UNNEST(hits) hits
,UNNEST(hits.product) product
WHERE _TABLE_SUFFIX BETWEEN "20170730" AND "20170731"
--AND product.productSKU = "GGOEYFKQ020699" AND product.productListName = "Category" AND product.productListPosition = 1
AND product.productListName != "(not set)"
GROUP BY Fecha, SKU, Lista_Producto, Posicion_En_Lista
ORDER BY Fecha DESC;