I use realy often BigQuery for analysis since the free Google Analytics 4 has the ability to send “raw” data to BQ.
If I get more advanced questions what the UI and Data Studio can’t answer I have to use BQ. I realy like it and I would like to share some quick tips to you.
The fist tip is about the session id. Luckily we have session id parameter in BQ. It helpful if you would like to connect a dimension session wide.
In my example I would like to add the campaign parameters to all the events within a session. Why I have to do it? Because in BQ only the first few event (which occurs on the landing page) has the campaign parameters and I would like to add these parameters to all of the events within the session.
Here you can find the BQ export schema
Session ID
If you would like to do something within a session, you should use the session id. It is a no brainer choice. GA4 BigQuery schema has this field and as the name suggest it identify the sessions 🙂
What is the problem?
When I first use this field to connect the events within the session I realized that something wrong when I only use session id as a key eg. on a join.
The session id is not a unique id, more than one user can get the same session id. It is not a good news.
The solution
Also not a big deal, if an id is not as unique as you desired, just add another field which makes it unique.
If you combine session id with the client id (user pseudo id), you will get the right id. That’s it.
Just use the client id and the session id together on the join.
It is a simple tip, but this helped me to get the right join.
As you can see, the session id is a nested field. GA4 BQ schema use a lot of nested fields, so the next tip coming soon about how to retrieve data from nested fielsd.
Be First to Comment