How to get the definition of unique views in Google Analytics in Bigquery
https://support.google.com/analytics/answer/1257084?hl=en-GB#pageviews_vs_unique_views
I am trying to calculate the sum of unique pageviews per day that Google Analytics has on its front-end How do I get the equivalent using bigquery?
+3
source to share
3 answers
There are two ways to use this parameter:
1) One is that the original linked documentation says to combine the full visitor user id and his session id: visitId and count.
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) The other just counts different fullVisitorIds
SELECT
EXACT_COUNT_DISTINCT(fullVisitorId)
FROM
[google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
WHERE
hits.type='PAGE'
If anyone wants to try this on a sample public dataset, there is a tutorial on how to add a sample dataset .
+3
source to share
Other requests did not match the Unique Pageviews tag in my Google Analytics account, but the following:
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
)
+1
source to share
For uniquePageViews it is better to use something like this:
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;
0
source to share