You've successfully subscribed to GA4BigQuery
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info is updated.
Billing info update failed.

Session: dimensions & metrics

Join 2,600+ other professionals and receive a monthly newsletter packed with GA4 & BigQuery news, tips & tricks

Great! Check your inbox and click the link to confirm your subscription
Please enter a valid email address!
Already have an account? Sign in
๐Ÿ’ก
This article is about GA3 - Universal Analytics

This 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:

Total Sessions in BigQuery vs Google Analytics Reports
Iโ€™m just learning BigQuery so this might be a dumb question, but we want to get some statistics there and one of those is the total sessions in a given day. To do so, Iโ€™ve queried in BQ: select sum(

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.