BigQuery 问题 - 我无法提取添加到购物车中用于产品列表的数量和产品(谷歌分析 - UA)

Question

晚安

我试图在BigQuery上创建一个查询，其中包括以下维度:日期，ProductListName, ProductSKU, ProductListPosition和以下指标:产品列表视图，产品列表点击，添加到购物车的数量和数量单位。然而，数量和单位添加到购物车不工作如预期。两者总是显示相同的结果(0)。我已经用谷歌分析检查了正确的结果，所以我知道如果查询是正确的，我会得到的数字。

在这几行下面是我执行的查询有人能帮我一下吗?提前感谢

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;

Answer 1

尝试使用下面的add_to_cart:

CASE
WHEN LEAD(productListName) OVER (PARTITION BY sessionID, productSKU ORDER BY hitNumber) = "(not set)" THEN LEAD(product_add_to_cart) OVER (PARTITION BY sessionID, productSKU ORDER BY hitNumber)+product_add_to_cart
ELSE
product_add_to_cart
END