BigQuery Abfrage für Google Analytics (UA): Seiten-Tracking: Dimensionen und Metriken

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

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

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