How to replicate the 'Behavior | Events | Top Events' report (UA)
1 min read

How to replicate the 'Behavior | Events | Top Events' report (UA)

A query to generate the Google Analytics Top Events report in BigQuery. In this report you'll find data about your events, segmented by event category, event action and event label.
How to replicate the 'Behavior | Events | Top Events' report (UA)

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.

Top Events report

In the Behavior | Events | Top Events report you'll find data about your events, segmented by event category, event action and event label.

Let's query!

select
  hits.eventinfo.eventcategory as event_category,
  -- hits.eventinfo.eventaction as event_action,
  -- hits.eventinfo.eventlabel as event_label,
  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'
  and hits.eventinfo.eventcategory is not null
group by
  event_category
  -- ,event_action
  --, event_label
order by total_events desc

Enjoying this post? Subscribe to the GA4BigQuery newsletter!

You will receive regular updates (max 1x/week) straight in your inbox. You can unsubscribe anytime.