BigQuery-购物行为漏斗+登录页

Question

我创建了以下查询，以在会话级别复制标准UA报告：

SELECT
`date` AS date,
(
SELECT
CASE
WHEN REGEXP_CONTAINS(page.hostname, r'^something.com$') THEN page.hostname
ELSE
'Other'
END
FROM
UNNEST(hits)
WHERE
isentrance = TRUE) AS hostname,
CASE
WHEN totals.newvisits = 1 THEN 'New visitor'
ELSE
'Returning visitor'
END
AS user_type,
device.deviceCategory AS device_category,
trafficsource.source AS source,
trafficsource.medium AS medium,
trafficsource.campaign AS campaign,
(
SELECT
CASE
WHEN REGEXP_CONTAINS(page.pagepath, r'(?i)?variant=') THEN page.pagepath
WHEN REGEXP_CONTAINS(page.pagepath, r'(?i)?') THEN REGEXP_REPLACE(page.pagepath,r'(?:?|&)((?:[^=]+)=(?:[^&]*))', '')
WHEN REGEXP_CONTAINS(page.pagepath, r'(?i)#') THEN REGEXP_REPLACE(page.pagepath,r'#.*', '')
ELSE
page.pagepath
END
FROM
UNNEST(hits)
WHERE
isentrance = TRUE) AS landing_page,
COUNT(DISTINCT fullvisitorid) AS users,
COUNT(DISTINCT(CASE
WHEN totals.newvisits = 1 THEN fullvisitorid
ELSE
NULL
END
)) AS new_users,
COUNT(DISTINCT CONCAT(fullvisitorid, CAST(visitstarttime AS string))) AS sessions,
COUNT(DISTINCT
CASE
WHEN totals.bounces = 1 THEN CONCAT(fullvisitorid, CAST(visitstarttime AS string))
ELSE
NULL
END
) AS bounces,
SUM(totals.pageviews) AS pages,
SUM(totals.timeonsite) AS timeonsite,
ifnull(SUM(totals.transactions),
0) AS transactions,
ifnull(SUM(totals.totaltransactionrevenue),
0)/1000000 AS revenue,
FROM
`analytics-12345.ga_sessions_*`
WHERE
totals.visits = 1
AND _table_suffix = '20220821'
GROUP BY
date,
hostname,
user_type,
device_category,
source,
medium,
campaign,
landing_page

正如你所看到的，我并没有在FROM子句后点击，但为了获得着陆页，我使用了一个子查询。我想添加购物行为漏斗特定阶段的会话计算，例如：

count(distinct case when hits.ecommerceaction.action_type = '2' then concat(fullvisitorid, cast(visitstarttime as string)) else null end) as sessions_with_product_views,

但要做到这一点，我需要先打出最少的安打。在这种情况下，我很难将登录页添加为维度，我尝试使用FIRST_VALUE OVER会话id，但GROUP BY中不允许使用分析功能。

有没有一种聪明的方法可以在同一个查询中为特定的购物行为阶段设置登录页维度和会话？

Answer 1

好的，找到了一个解决方案：

COUNT(DISTINCT
CASE
WHEN ( SELECT ecommerceaction.action_type FROM UNNEST(hits) WHERE ecommerceaction.action_type = '2' LIMIT 1 ) = '2' THEN CONCAT(fullvisitorid, CAST(visitstarttime AS string))
ELSE
NULL
END
) AS sessions_with_pdp_views,
COUNT(DISTINCT
CASE
WHEN ( SELECT ecommerceaction.action_type FROM UNNEST(hits) WHERE ecommerceaction.action_type = '3' LIMIT 1 ) = '3' THEN CONCAT(fullvisitorid, CAST(visitstarttime AS string))
ELSE
NULL
END
) AS sessions_with_add_to_cart,