How to replicate the 'Behavior | Events | Pages' report

As a Google Analytics user you are probably quite attached to the default reports in the user interface of Universal Analytics. It can be hard to make sense of the data in the BigQuery export tables.

Let me enable you to replicate the reports you're familiar with. I'll try to keep it basic here.

Event Pages report

In the Behavior | Events | Pages report you'll find data about your events, segmented by page and page title.

Let's query!

select
  hits.page.pagepath as page,
  -- hits.page.pagetitle as page_title,
  count(*) as total_events,
  count(distinct concat(cast(fullvisitorid as string), cast(visitstarttime as string))) as unique_events,
  ifnull(sum(hits.eventinfo.eventvalue),0) as event_value,
  ifnull(sum(hits.eventinfo.eventvalue) / count(*),0) as avg_value
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`,
  unnest(hits) as hits
where
  totals.visits = 1
  and hits.type = 'EVENT'
group by
  hits.page.pagepath
  -- ,hits.page.pagetitle
order by total_events desc