Session: dimensions & metrics

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(

Stack OverflowWillian Fuks

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.