https://support.google.com/analytics/answer/1257084?hl=en-GB#pageviews_vs_unique_views
我正在尝试计算每天独立页面浏览量的总和,这是Google Analytics界面上显示的数据。如何使用BigQuery获得相应的数据?
https://support.google.com/analytics/answer/1257084?hl=en-GB#pageviews_vs_unique_views
我正在尝试计算每天独立页面浏览量的总和,这是Google Analytics界面上显示的数据。如何使用BigQuery获得相应的数据?
这个功能有两种使用方式:
1) 第一种方式是按照原始文档链接所述,将完整的访客用户ID和他们不同的会话ID:visitId组合起来,然后进行计数。
SELECT
EXACT_COUNT_DISTINCT(combinedVisitorId)
FROM (
SELECT
CONCAT(fullVisitorId,string(VisitId)) AS combinedVisitorId
FROM
[google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
WHERE
hits.type='PAGE' )
2) 另一种方法是仅计算不同的fullVisitorIds数量
SELECT
EXACT_COUNT_DISTINCT(fullVisitorId)
FROM
[google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
WHERE
hits.type='PAGE'
SELECT COUNT(1) as unique_pageviews
FROM (
SELECT
hits.page.pagePath,
hits.page.pageTitle,
fullVisitorId,
visitNumber,
COUNT(1) as hits
FROM [my_table]
WHERE hits.type='PAGE'
GROUP BY
hits.page.pagePath,
hits.page.pageTitle,
fullVisitorId,
visitNumber
)
所以,在2022年,EXACT_COUNT_DISTINCT()似乎已经被弃用了..
对于我来说,以下组合
fullvisitorid+visitNumber+visitStartTime+hits.page.pagePath
一直比上述解决方案更精确:
SELECT
SUM(Unique_PageViews)
FROM
(SELECT
COUNT(DISTINCT(CONCAT(fullvisitorid,"-",CAST(visitNumber AS string),"-",CAST(visitStartTime AS string),"-",hits.page.pagePath))) as Unique_PageViews
FROM
`mhsd-bigquery-project.8330566.ga_sessions_*`,
unnest(hits) as hits
WHERE
_table_suffix BETWEEN '20220307'
AND '20220313'
AND hits.type = 'PAGE')
对于唯一页面浏览量,你最好使用这样的代码:
SELECT
date,
SUM(uniquePageviews) AS uniquePageviews
FROM (
SELECT
date,
CONCAT(fullVisitorId,string(VisitId)) AS combinedVisitorId,
EXACT_COUNT_DISTINCT(hits.page.pagePath) AS uniquePageviews
FROM
[google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
WHERE
hits.type='PAGE'
GROUP BY 1,2)
GROUP EACH BY 1;