How to get the Google Analytics definition of uniq

Question

https://support.google.com/analytics/answer/1257084?hl=en-GB#pageviews_vs_unique_views

I'm trying to calculate the sum of unique page views per day which Google analytics has on its interface How do I get the equivalent using bigquery?

Answer 1

The other queries didn't match the Unique Pageviews metric in my Google Analytics account, but the following did:

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
)

Answer 2

For uniquePageViews you better want 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;

Answer 3

There are two ways how this is used:

1) One is as the original linked documentation says, to combine full visitor user id, and their different session id: visitId, and count those.

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 is just counting distinct fullVisitorIds

SELECT
  EXACT_COUNT_DISTINCT(fullVisitorId)
FROM
  [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
WHERE
  hits.type='PAGE'

If someone wants to try out this on a sample public dataset there is a tutorial how to add the sample dataset.