Effortless Ways to handle Google Analytics 4 data in BigQuery like a pro

Photo by Vincent van Zalinge on Unsplash
  • Cross-device and cross-platform tracking
  • Different reporting interface
  • Even pageview is considered an event as GA4 uses event-based measures
  • Tracking ID starts with ‘G-’
  • Create data streams instead of views
  • Different event tracking setup
  • A new set of engagement metrics
  • Default IP anonymization feature

GA4 is a TRIUMPH of Data Tracking

Universal Analytics buzz off

Event-based measurement model

  • As the name says, these events are automatically collected events that are triggered by GA4 on predefined user activities.
  • This event is the same as automatically collected events but with a difference in enabling options. These events will be logged only if the user enables them.
  • Recommended events do not automatically get collected or triggered unless the user manually implements them.
  • These events are not tracked by default as other event types, rather it enables users to create and use their events. Hence, Google recommends that users double-check existing events whether the event the user needs already exists or not. For instance, button click, sign up events, etc.

Quick Integration Setup

  • events_ (<number of days>) — all the session data from the previous <number of days> is present in the table.
  • events_ intraday_(<current date>) — gives the session data of the current day.

Query multiple events with Static Date

Query multiple sessions with Dynamic Date

Query multiple sessions with a Hybrid Date

Data filter option in BQ
GA4 structure of events
GA4 analytics data table in JSON format
Overview of the structure of the GA4 data table

How to handle repeated records in SQL?

GA4 table Repeated records in BQ

How to handle multiple repeated records?

The output of an above query

How to convert Date in string type into a Date format?

Output for the above query
Sample output of queries

Conclusion

Further Links

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store