How to create a session based GA4 traffic acquisition report in BigQuery
A query to generate the Google Analytics 4 traffic acquisition report in BigQuery. In this report you'll find data about acquisition at session level: users, (engaged) sessions, engagement time and rate, segmented by session default channel group, session medium, session source and session campaign.
This article contains a query that you can use to create a custom session based traffic acquisition report, based on your Google Analytics 4 export data in BigQuery.
The result of the query will not be an exact replicate of any report in the GA4 user interface. While designing this report, we took into account:
- the available data fields in GA4 export
- current (GA4) and legacy (GA3/UA) user interface reports
- data definitions according to documentation (if available)
- scope (user / session / event / item)
- common sense
đĄ
It is expected that the results for this query will not match with any GA4 user interface report. This can occur for a variety of reasons.
This query can be used:
- to create a result table that can be opened in Google Sheets or exported as csv
- to save as a BigQuery table, for further analysis
- to create (and frequently update) a BigQuery table that feeds a dashboard (e.g. Looker Studio)
- to use as a starting point to design your own report
Report dimensions
The report contains the following dimensions:
- date
- session default channel group
- session source
- session medium
- session source / medium
- session campaign
Report metrics
The report contains the following metrics:
- total users
- active users
- sessions
- engaged sessions
- average engagement time in seconds
- engaged sessions per user
- engagement rate
- events per session
- total event count
- specific event count
- conversions
- session conversion rate
- purchase revenue
đĄ
Unless stated otherwise, all calculated user metrics are based on total users.
