Page tracking: dimensions & metrics

This example query contains all following Google Analytics page tracking dimensions and metrics. If you only need one dimension or metric, look at the -- comments in the example query and copy the part you need from the select clause. Make sure that you also add any additional conditions (in the from, where, group by and order by) that are necessary to calculate the results correctly.

Note: see also this tutorial on how to replicate the All pages report in Google Analytics with more context about some of these metrics.

Page Tracking dimensions

• hostname
• page
• previous page path
• page path level 1
• page path level 2
• page path level 3
• page path level 4
• page title
• landing page
• second page
• exit page

Page Tracking metrics

• entrances
• pageviews
• unique pageviews (see separate example query)
• pages / session
• exits
• % exit
• avg. time on page (see separate example query)

Example query

select
  hostname,
  page,
  previous_page,
  page_path_level_1,
  page_path_level_2,
  page_path_level_3,
  page_path_level_4,
  page_title,
  landing_page,
  second_page,
  exit_page,
  -- entrances (metric)
  countif(isentrance = true) as entrances,
  -- pageviews (metric)
  count(*) as pageviews,
  -- pages per session (metric)
  count(*) / count(distinct concat(fullvisitorid, cast(visitstarttime as string))) as pages_per_session,
  -- exits (metric)
  countif(isexit = true) as exits,
  -- exit rate (metric)
  countif(isexit = true) / count(*) as exit_rate
from (
  select
    -- hostname (dimension)
    hits.page.hostname as hostname,
    -- page (dimension)
    hits.page.pagepath as page,
    -- previous page (dimension)
    lag(hits.page.pagepath, 1) over (partition by fullvisitorid, visitstarttime order by hits.hitnumber asc) as previous_page,
    -- page path level 1 (dimension)
    hits.page.pagepathlevel1 as page_path_level_1,
    -- page path level 2 (dimension)
    nullif(hits.page.pagepathlevel2,'') as page_path_level_2,
    -- page path level 3 (dimension)
    nullif(hits.page.pagepathlevel3,'') as page_path_level_3,
    -- page path level 4 (dimension)
    nullif(hits.page.pagepathlevel4,'') as page_path_level_4,
    -- page title (dimension)
    hits.page.pagetitle as page_title,
    -- landing page (dimension)
    case when hits.isentrance = true then hits.page.pagepath else null end as landing_page,
    -- second page (dimension)
    case when hits.isentrance = true then (lead(hits.page.pagepath, 1) over (partition by fullvisitorid, visitstarttime order by hits.hitnumber asc)) else null end as second_page,
    -- exit page (dimension)
    case when hits.isexit = true then hits.page.pagepath else null end as exit_page,
    hits.isentrance,
    fullvisitorid,
    visitstarttime,
    hits.isexit
  from
    `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`,
    unnest(hits) as hits
  where
    totals.visits = 1
    and hits.type = 'PAGE')
group by
  hostname,
  page,
  previous_page,
  page_path_level_1,
  page_path_level_2,
  page_path_level_3,
  page_path_level_4,
  page_title,
  landing_page,
  second_page,
  exit_page
order by
  pageviews desc

Example query: Unique Pageviews

select
  page,
  -- unique pageviews (metric)
  sum(unique_pageviews) as unique_pageviews
from (
  select
    -- page (dimension)
    hits.page.pagepath as page,
    concat(hits.page.pagepath,hits.page.pagetitle) as page_concat,
    count(distinct concat(cast(fullvisitorid as string), cast(visitstarttime as string))) as unique_pageviews
  from
    `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`,
    unnest(hits) as hits
  where
    totals.visits = 1
    and hits.type = 'PAGE'
  group by
    page,
    page_concat)
group by
  page
order by
  unique_pageviews desc

Example query: Average Time On Page

select
  pagepath as 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,
    countif(isexit is not null) as exits,
    sum(time_on_page) as total_time_on_page
  from (
    select
      fullvisitorid,
      visitstarttime,
      pagepath,
      pagetitle,
      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,
        hits.page.pagepath,
        hits.page.pagetitle,
        hits.time / 1000 as hit_time,
        hits.type,
        hits.isexit,
        max(if(hits.isinteraction = true,hits.time / 1000,0)) over (partition by fullvisitorid, visitstarttime) as last_interaction,
        lead(hits.time / 1000) over (partition by fullvisitorid, visitstarttime order by hits.time / 1000) as next_pageview
      from
        `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`,
        unnest(hits) as hits
      where
        hits.type = 'PAGE'
        and totals.visits = 1))   
  group by
    pagepath)
order by
  avg_time_on_page desc