This example query contains all following Google Analytics event 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.
Event tracking dimensions
- event category
- event action
- event label
Event tracking metrics
- total events
- unique events
- event value
- avg. value
- sessions with event
- events / session with event
Example query
select
-- event category (dimension)
hits.eventinfo.eventcategory as event_category,
-- event action (dimension)
hits.eventinfo.eventaction as event_action,
-- event label (dimension)
hits.eventinfo.eventlabel as event_label,
-- total events (metric)
count(*) as total_events,
-- unique events (metric),
count(distinct concat(cast(fullvisitorid as string), cast(visitstarttime as string))) as unique_events,
-- event value (metric)
sum(hits.eventinfo.eventvalue) as event_value,
-- avg. value (metric)
sum(hits.eventinfo.eventvalue) / count(*) as avg_value,
-- sessions with events (metric)
count(distinct case when hits.type = 'EVENT' then concat(cast(fullvisitorid as string), cast(visitstarttime as string)) else null end) as sessions_with_events,
-- events / session with event (metric)
ifnull(safe_divide(count(*),count(distinct case when hits.type = 'event' then concat(cast(fullvisitorid as string), cast(visitstarttime as string)) else null end)),0) as events_session_with_event
from
`bigquery-public-data.google_analytics_sample.ga_sessions_20160801`,
unnest(hits) as hits
where
totals.visits = 1
and hits.type = 'EVENT'
group by
event_category,
event_action,
event_label
having
event_category is not null
order by
total_events desc