In the past, we shared a blog series about BigQuery tips for Universal Analytics. Today, we're kicking off a new series diving into Google Analytics 4 (GA4) in BigQuery, starting with an overview of users and sessions. We'll use real examples that will through this series gradually become more complex. Just like with Universal Analytics we will be diving into Google Analytics 4 (GA4) metrics and dimensions and exploring different ways to access them using BigQuery.
The GA4 BigQuery export is a big step forward in making data accessible for everyone as it's now available for standard and 360 properties (standard properties are limited to an export of a million events daily). GA4 has a brand-new schema and there are many things you should know about before you begin querying the data.
If you have experience or are interested in the differences between the Universal Analytics and GA4 exports, take a look at our Universal Analytics to GA4 – BigQuery Export Guide article.
As we write our queries and look at the calculated metrics, you’ll notice that your BigQuery results are not matching the UI numbers exactly. There could be many reasons for that, but remember that GA4 UI often uses estimates.
The reason for estimating metrics in the UI stems from the resources required to calculate the reporting tables. In short, when you want exact metric numbers, but are okay with taking it slightly longer to calculate, use BigQuery1.
The number of users in GA4 is calculated using either the user_pseudo_id field, which is a unique device identifier (clientId in the UA export), or user_id if it's being set manually in the property tracking.
Therefore, to calculate the number of users on a property on a given day, you can take advantage of the count distinct.
SELECT count(distinct user_pseudo_id) FROM analytics_1234.event_20221201
You could also take advantage of the user_pseudo_id stickiness to create a simple segment of all the users who visited your website in January.
SELECT DISTINCT user_pseudo_id FROM `analytics_1234.events_202201*`
Now you can look at how many of those users also visited each day in September.
WITH userVisitedInJan2022 AS (
SELECT DISTINCT
user_pseudo_id
FROM
`analytics_1234.events_202201*`)
SELECT
_table_suffix as date,
COUNT(DISTINCT user_pseudo_id) as users
FROM
`analytics_1234.events_202209*`
WHERE user_pseudo_id in (SELECT * FROM userVisitedInJan2022)
GROUP BY 1
ORDER BY date ASC
As a note, data from Google signals is not exported to BigQuery. To quote the documentation directly, "Google Analytics 4 exports event data associated with anonymous cookies to BigQuery". This may result in different numbers between the GA4 UI and BigQuery in event counts as well as user counts when Google signals are turned on.2,3
The GA4 UI offers three different user definitions; device-based is the only one that does not rely on Google Signals.
You can observe sessions in the BigQuery export in two different ways. You can either look at the number of sessions that started or at the total number of sessions present during the time period.
A few characteristics of the GA4 sessions1:
The following query will calculate the number of sessions starting on a given day using the session_start event:
SELECT COUNTIF(event_name = "session_start") FROM `analytics_1234567.events_20221018`
To look at the actual number of sessions on a given day (sessions that started on the day as well as those that carried over from the previous day), you have to concatenate the user identifier (user_pseudo_id) and event parameter that uniquely describes the current session of the user (ga_session_id).
You'll notice that the query gets a bit more complex since you're extracting a ga_session_id value from the repeated field event_params.
SELECT
COUNT(DISTINCT
concat(user_pseudo_id, " ",
(SELECT value.int_value FROM unnest(event_params) WHERE key = "ga_session_id")
)) as sessions
FROM
`analytics_1234567.events_20221018`
It's possible that the value of user_pseudo_id will come through to BigQuery as null. This means that the user identifier was not sent to Google Analytics. This will impact the second of the two calculations and may even cause the number of total sessions in the selected time period to be smaller than the number of sessions that started during the same time.
Stay tuned for part two, which will cover more detailed session information, such as attribution, time on site, time engaged, and similar. In the meantime, please reach out with any questions.