Unfortunately, there's not just one answer to this question. There are more than two, in fact.
We're talking about working with GA4 export in BigQuery and constructing session attribution based on this dataset. Here are at least three challenges:
1. The presence of multiple traffic source parameters (those in event_params, traffic_source, and collected_traffic_source).
2. Limited documentation from Google on this matter.
3. BigQuery bugs - for example, with the reattribution of Google organic traffic.
All of this creates ample room for errors. And since there's no one-size-fits-all solution, one can look for answers among those who have already navigated through the complexities of this process. I've gathered known solutions for session attribution in BigQuery, along with my comments and links to code.
1. Attribution in the GA4SQL service by Ahmed Ali. This service allows you to compose SQL queries right in the browser, which is very convenient. However, the query takes the traffic source from the traffic_source field, which, in my opinion, isn't always accurate. Link: https://www.ga4sql.com
2. A solution from Mark Rittman, which I've previously discussed in my blog. It's great that he has compiled solutions for all popular attribution models in his code. However, he also uses the traffic_source field, which is typically associated with a user's first session and is not very reliable. Link.
3. A solution from Ivan Nikitin. It extensively and precisely describes corner cases in determining the source by the referrer. It's not quite clear why only page_view and session_start events are used. The only downside is that it's in Russian. Link.
4. GA4 attribution by Luka Cempre. Very clean and understandable code, recommended by Simo Ahava. It includes the creation of session tables and several calculation options for attribution. It uses the collected_traffic_source parameter, which is much more reliable. Link.
5. Last non-direct click attribution by Taneli Salonen. I like this one the most as it offers solutions for BigQuery bugs: accounting for the reattribution of Google organics and errors when adding user_id during a session. Link.
I couldn't find a solution for within-session attribution. Plus, not everyone uses event_params parsing, which I consider the most reliable method, albeit more costly.
If you have your preferences for session attribution in BigQuery, be sure to write about it in the comments.
