Recreating the google analytics pages report

01 Pages.sql

SELECT
  hits.page.pagePath
FROM
  'bigquery-public-data.google_analytics_sample.ga_sessions_20160801' AS GA,
  UNNEST(GA.hits) AS hits
GROUP BY
  hits.page.pagePath

02 Pageviews.sql

SELECT
  hits.page.pagePath,
  COUNT(*) AS pageviews
FROM
  'bigquery-public-data.google_analytics_sample.ga_sessions_20160801' AS GA,
  UNNEST(GA.hits) AS hits
WHERE
  hits.type = 'PAGE'
GROUP BY
  hits.page.pagePath
ORDER BY
  pageviews DESC

03 Unique Pageviews.sql

SELECT
  pagepath,
  COUNT(*) AS pageviews,
  COUNT(DISTINCT session_id) AS unique_pageviews
FROM (
  SELECT
    hits.page.pagePath,
    CONCAT(fullVisitorId, CAST(visitStartTime AS STRING)) AS session_id
  FROM
    'bigquery-public-data.google_analytics_sample.ga_sessions_20160801' AS GA,
    UNNEST(GA.hits) AS hits
  WHERE
    hits.type = 'PAGE')
GROUP BY
  pagePath
ORDER BY
  pageviews DESC

04 Average Time on Page.sql

SELECT
  hits.page.pagePath,
  COUNT(*) AS pageviews
FROM
  'bigquery-public-data.google_analytics_sample.ga_sessions_20160801' AS GA,
  UNNEST(GA.hits) AS hits
WHERE
  hits.type = 'PAGE'
GROUP BY
  hits.page.pagePath
ORDER BY
  pageviews DESC

05 Exits.sql

SELECT
  pagePath,
  SUM(exits) AS exits
FROM (
  SELECT
    hits.page.pagePath,
    CASE
      WHEN hits.isExit IS NOT NULL THEN 1
      ELSE 0
    END AS exits
  FROM
    'bigquery-public-data.google_analytics_sample.ga_sessions_20160801' AS GA,
    UNNEST(GA.hits) AS hits)
GROUP BY
  pagePath
ORDER BY
  exits DESC

06 Total Time on Page.sql

SELECT
  pagePath,
  pageviews,
  exits,
  total_time_on_page,
  CASE
    WHEN pageviews = exits THEN 0
    ELSE total_time_on_page / (pageviews - exits)
  END AS avg_time_on_page
FROM (
  SELECT
    pagePath,
    COUNT(*) AS pageviews,
    SUM(IF(isExit IS NOT NULL,
        1,
        0)) AS exits,
    SUM(time_on_page) AS total_time_on_page
  FROM (
    SELECT
      fullVisitorId,
      visitStartTime,
      pagePath,
      hit_time,
      type,
      isExit,
      CASE
        WHEN isExit IS NOT NULL THEN last_interaction - hit_time
        ELSE next_pageview - hit_time
      END AS time_on_page
    FROM (
      SELECT
        fullVisitorId,
        visitStartTime,
        pagePath,
        hit_time,
        type,
        isExit,
        last_interaction,
        LEAD(hit_time) OVER (PARTITION BY fullVisitorId, visitStartTime ORDER BY hit_time) AS next_pageview
      FROM (
        SELECT
          fullVisitorId,
          visitStartTime,
          pagePath,
          hit_time,
          type,
          isExit,
          last_interaction
        FROM (
          SELECT
            fullVisitorId,
            visitStartTime,
            hits.page.pagePath,
            hits.type,
            hits.isExit,
            hits.time / 1000 AS hit_time,
            MAX(IF(hits.isInteraction IS NOT NULL,
                hits.time / 1000,
                0)) OVER (PARTITION BY fullVisitorId, visitStartTime) AS last_interaction
          FROM
            'bigquery-public-data.google_analytics_sample.ga_sessions_20160801' AS GA,
            UNNEST(GA.hits) AS hits)
        WHERE
          type = 'PAGE')))
  GROUP BY
    pagePath)
ORDER BY
  pageviews DESC

07 Entrances.sql

SELECT
  pagePath,
  SUM(entrances) AS entrances
FROM (
  SELECT
    hits.page.pagePath,
    CASE
      WHEN hits.isEntrance IS NOT NULL THEN 1
      ELSE 0
    END AS entrances
  FROM
    'bigquery-public-data.google_analytics_sample.ga_sessions_20160801' AS GA,
    UNNEST(GA.hits) AS hits)
GROUP BY
  pagePath
ORDER BY
  entrances DESC

08 Bounce Rate.sql

SELECT
  fullVisitorId,
  visitStartTime,
  pagePath,
  CASE
    WHEN hitNumber = first_interaction THEN bounces
    ELSE 0
  END AS bounces
FROM (
  SELECT
    fullVisitorId,
    visitStartTime,
    hits.page.pagePath,
    totals.bounces,
    hits.hitNumber,
    MIN(IF(hits.isInteraction IS NOT NULL,
        hits.hitNumber,
        0)) OVER (PARTITION BY fullVisitorId, visitStartTime) AS first_interaction
  FROM
    'bigquery-public-data.google_analytics_sample.ga_sessions_20160801' AS GA,
    UNNEST(GA.hits) AS hits)

09 Sessions.sql

SELECT
  fullVisitorId,
  visitStartTime,
  pagePath,
  CASE
    WHEN hitNumber = first_hit THEN visits
    ELSE 0
  END AS sessions
FROM (
  SELECT
    fullVisitorId,
    visitStartTime,
    hits.page.pagePath,
    totals.visits,
    hits.hitNumber,
    MIN(hits.hitNumber) OVER (PARTITION BY fullVisitorId, visitStartTime) AS first_hit
  FROM
    'bigquery-public-data.google_analytics_sample.ga_sessions_20160801' AS GA,
    UNNEST(GA.hits) AS hits)

10 Bounce Rate.sql

select
pagePath,
bounces,
sessions,
CASE
    WHEN sessions = 0 THEN 0
    ELSE bounces / sessions
  END AS bounce_rate
  from (
SELECT
  pagePath,
  SUM(bounces) AS bounces,
  SUM(sessions) AS sessions
FROM (
  SELECT
    fullVisitorId,
    visitStartTime,
    pagePath,
    CASE
      WHEN hitNumber = first_interaction THEN bounces
      ELSE 0
    END AS bounces,
    CASE
      WHEN hitNumber = first_hit THEN visits
      ELSE 0
    END AS sessions
  FROM (
    SELECT
      fullVisitorId,
      visitStartTime,
      hits.page.pagePath,
      totals.bounces,
      totals.visits,
      hits.hitNumber,
      MIN(IF(hits.isInteraction IS NOT NULL,
          hits.hitNumber,
          0)) OVER (PARTITION BY fullVisitorId, visitStartTime) AS first_interaction,
      MIN(hits.hitNumber) OVER (PARTITION BY fullVisitorId, visitStartTime) AS first_hit
    FROM
      'bigquery-public-data.google_analytics_sample.ga_sessions_20160801' AS GA,
      UNNEST(GA.hits) AS hits))
GROUP BY
  pagePath)
ORDER BY
  sessions DESC

11 Exit rate.sql

SELECT
  pagePath,
  pageviews,
  exits,
  CASE
    WHEN pageviews = 0 THEN 0
    ELSE exits / pageviews
  END AS exit_rate
FROM (
  SELECT
    pagepath,
    COUNT(*) AS pageviews,
    SUM(exits) AS exits
  FROM (
    SELECT
      hits.page.pagePath,
      CASE
        WHEN hits.isExit IS NOT NULL THEN 1
        ELSE 0
      END AS exits
    FROM
      'bigquery-public-data.google_analytics_sample.ga_sessions_20160801' AS GA,
      UNNEST(GA.hits) AS hits
    WHERE
      hits.type = 'PAGE')
  GROUP BY
    pagePath)
ORDER BY
  pageviews DESC