Join 2,600+ other professionals and receive a monthly newsletter packed with GA4 & BigQuery news, tips & tricks
Already have an account? Sign inThis example query contains all following Google Analytics session 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.
Session dimensions
-
Session metrics
- sessions
- bounces
- bounce rate
- avg. session duration
Example query
select
-- sessions (metric)
count(distinct concat(fullvisitorid, cast(visitstarttime as string))) as sessions,
-- bounces (metric)
count(distinct case when totals.bounces = 1 then concat(fullvisitorid, cast(visitstarttime as string)) else null end) as bounces,
-- bounce rate (metric)
count(distinct case when totals.bounces = 1 then concat(fullvisitorid, cast(visitstarttime as string)) else null end) / count(distinct concat(fullvisitorid, cast(visitstarttime as string))) as bounce_rate,
-- average session duration (metric)
sum(totals.timeonsite) / count(distinct concat(fullvisitorid, cast(visitstarttime as string))) as average_session_duration
from
`bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
where
totals.visits = 1
Remark #1: Sessions with/without interaction hits
Data Discrepancy in Session Count Between GA UI and GA in BQ โ In the GA UI, sessions are only counted if they contain an interaction hit. If the hit is set to non-interaction=true, then it wouldnโt initiate a session to be captured for GA. However, in BigQuery we get all the hit level data. Therefore, we can get all those hits where non-interaction=true. Now, in order to compare the total sessions between GA and BigQuery, we need to check all the sessions with interaction hits. (source)
Remark #2: Why not use totals.visits?
Letโs say we want to see the amount of sessions per country for August 1st 2016.
select
geonetwork.country as country,
sum(totals.visits) as sessions
from
`bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
group by
country
order by
sessions desc
As expected, most sessions come from the United States.
Is this the only way to query sessions in BigQuery? false
. There are a few other possibilities, each with their own definition. Check out this explanation:

Personally I like to understand whatโs happening under the hood, so count(distinct concat(fullvisitorid, cast(visitstarttime as string)))
is my preferred count of sessions, as it deals with midnight-split-sessions immediately. It can be used in all cases, even when you use the unnest
feature (which will have an impact on the amount of rows in your table and can result in multiple counts of the same sessions):
select
geonetwork.country as country,
count(distinct concat(fullvisitorid, cast(visitstarttime as string))) as sessions
from
`bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
where
totals.visits = 1
group by
country
order by
sessions desc
Note that we need to add a where
clause, in which we only count visits that contain interaction: totals.visits = 1
. By default and opposing to the Google Analytics UI, the ga_sessions_ tables contains all sessions, including the ones without interactions: totals.visits = 0
.