BigQuery-ShoppingBehaviorFunnel+LandingPage
-
将原标题翻译为中文：“BigQuery - 购物行为漏斗 + 登陆页面”
-
针对购物行为漏斗和登陆页面，使用BigQuery查询语句进行数据调取和分析，示例如下：
购物行为漏斗：
SELECT
IF(S2.is_cart IS NOT NULL AND S3.is_checkout IS NULL, 'Cart', '') AS action,
COUNT(DISTINCT fullVisitorId) AS users
FROM (
SELECT
fullVisitorId,
(SELECT MAX(IF(index=10, value, NULL)) FROM UNNEST(hits.customDimensions)) AS is_cart,
(SELECT MAX(IF(index=11, value, NULL)) FROM UNNEST(hits.customDimensions)) AS is_checkout
FROM
bigquery-public-data.google_analytics_sample.ga_sessions_*,
UNNEST(hits) AS hits
WHERE
_TABLE_SUFFIX BETWEEN '20160801' AND '20160831')
GROUP BY
action
HAVING
action != ''
ORDER BY
action
登陆页面：
SELECT
hits.page.pagePath,
COUNT(DISTINCT fullVisitorId) AS users
FROM
bigquery-public-data.google_analytics_sample.ga_sessions_*,
UNNEST(hits) AS hits
WHERE
_TABLE_SUFFIX BETWEEN '20160801' and '20160831'
AND hits.page.pagePath LIKE '/login%'
GROUP BY
hits.page.pagePath
ORDER BY
users DESC