The Complete Guide to SQL for Google Analytics 4 in BigQuery: Building the Traffic Acquisition Report (Part 2)

In the first part of this guide, we laid the technical foundation for working with "raw" GA4 data in BigQuery: we explored the export structure, learned how to work with nested parameters using scalar subqueries, and built a basic Pages and Screens report. Essentially, we learned how to correctly extract data from the raw export.

But our goal is to move beyond simple data retrieval to creating complex business logic. Today, we will explore how traffic attribution is implemented in BigQuery and why traffic sources exist at different data levels (user, session, event). These are the areas where errors most frequently occur—situations where a query looks correct, but the results are misleading. After that, we will move to practice and build a full Traffic Acquisition report in BigQuery step-by-step.

Section 1. Operators for Raw Data Transformation and Preparation

• 1.1. PARSE_DATE: converting text to date
• 1.2. COALESCE: how to handle NULLs without breaking calculations
  • Example 1. Protecting numerical calculations from NULL
  • Example 2. Working with text fields
• 1.3. CASE WHEN operator

Section 2. Attribution and Traffic Sources

• 2.1. What is attribution and why is it important?
• 2.2. Three levels of traffic data
  • How GA4 level logic translates to BigQuery
  • Traffic source fields in BigQuery: traffic_source, session_traffic_source_last_click, collected_traffic_source

Section 3. Building the Traffic Acquisition report based on session_traffic_source_last_click

• 3.1 Structure of the Traffic Acquisition report
  • 3.1.1 Report Dimension: Session source / medium
  • 3.1.2 Metric: Sessions
  • 3.1.3 Metric: Engaged Sessions
  • 3.1.4 Metric: Engagement rate
  • 3.1.5 Metric: Average engagement time per session
  • 3.1.6 Metric: Events per session
  • 3.1.7 Metric: Event count
  • 3.1.8 Metric: Key events
  • 3.1.9 Metric: Session key event rate
  • 3.1.10 Metric: Total revenue
• 3.1.11 Final query: assembling the Traffic Acquisition report in BigQuery
  • Parameterization: how to make the report universal

Conclusions

• A look into the future (Pipe syntax)

Section 1: Operators for Raw Data Transformation and Preparation

Before building complex reports, we need a toolkit for "cleaning" and transforming data. We already covered basic operators and functions in the first part of the guide, and today we will expand this arsenal.

1.1 PARSE_DATE: converting text to date

The event_date field in the GA4 BigQuery export is stored as a string (STRING) in the format YYYYMMDD (e.g., 20240118). Even though it looks like a date, for SQL, it is plain text. Consequently, you cannot perform typical time-based operations with it — such as calculating the difference between dates, grouping data by weeks or months, or using other standard date functions.

The PARSE_DATE operator allows you to explicitly tell SQL the format in which the date is written and convert the text value into a full DATE type. After this, the value becomes suitable for any time-based calculations.

Basic example of event_date conversion:

In this example:

• %Y%m%d describes the string date format (the first four digits represent the year, the next two represent the month, and the final two represent the day. All without delimiters).
• PARSE_DATE converts the text value into a DATE type.
• The new field event_date_parsed can now be used in functions like DATE_DIFF, DATE_TRUNC, or EXTRACT.

Let's look at a practical example using PARSE_DATE for weekly grouping:

As a result of the query, we get the number of events that occurred during each calendar week within the selected dataset and specified period. For this, all dates are truncated to the start of the week using DATE_TRUNC, and then events are aggregated via COUNT(*).

Another common use case for PARSE_DATE is working with _TABLE_SUFFIX for more convenient and accurate data filtering over a specific period. For example, in the query below, we return data from January 15 to January 28, 2024.

1.2 COALESCE: how to handle NULLs without breaking calculations

In raw GA4 data, NULL is not an exception—it is the norm. It can appear because an event does not always have certain parameters, such as value, transaction_id, etc. This means the field exists in the schema but is not actually populated for a specific event.

The problem is that:

1. NULL can break arithmetic calculations;
2. Aggregations can return unexpected results;
3. Data becomes harder to interpret in reports.

This is exactly why we need COALESCE. COALESCE is an SQL function that returns the first non-NULL value from a list of arguments. Simply put: if a value is missing (NULL), substitute it with another one that we specify. The syntax looks like this:

BigQuery checks arguments from left to right and returns the first one that is not NULL.

Let's break down examples of usage:

Example 1. Protecting numerical calculations from NULL

To simplify the explanation, let's break it down not on GA4 data, but on a simplified example. Imagine a table where three records have values and one record is NULL:

If you just write:

The result will be 175. BigQuery simply ignores NULL.

And now let's explicitly say that NULL = 0:

The result will also be 175.

And it might seem that there is no difference. But that's not true. Now let's break down an example with the same data and the AVG function.

Note. NULL and 0 are different analytical states:

• NULL means that there was no data at all (there is no relevant row).
• 0 means that there were events, but the value is zero or not passed.

To uniquely manage the process, rather than relying on "SQL magic" — do not forget about using COALESCE.

Example 2. Working with text fields

COALESCE is useful not only for numbers. For example, if a traffic source is not defined, we can change NULL to a more understandable (not set):

As a result:

• instead of NULL, there will be a clear value in the report;
• tables and dashboards become more readable.

We will talk about another example of using COALESCE in the section Engaged Sessions metric.

1.3 CASE WHEN operator

In BigQuery, the CASE operator is a tool that returns different values depending on the fulfillment of specified conditions. In a simplified form, CASE logic looks like this: "Check if this condition is met. If yes — return the specified value. If no — check the next condition..."

Thanks to this, CASE allows you to:

• create custom categories;
• group content;
• form user segments;
• calculate analytical attributes without changing raw data and without additional tables.

In fact, CASE is a way to add analytical meaning to event data directly in the SELECT statement. The general structure looks like this:

Let's break down a simple example of using this function to create a custom channel group.

What is happening here:

• CASE creates a new field channel_grouping;
• each WHEN is a separate logical condition;
• END completes the logic and returns a value;
• COUNT(DISTINCT ...) counts sessions in each group;
• GROUP BY aggregates data for each group.

Section 2: Attribution and Traffic Sources

2.1 What is attribution and why is it important?

Attribution and traffic sources are one of the most complex topics in web analytics. One and the same user can come to the site from an ad, return from organic search, then enter directly (Direct) and subscribe to a newsletter, and make a conversion after clicking from an email newsletter. Which source a session or conversion is "credited" to depends on the attribution model and source prioritization rules. Accordingly, attribution determines which channels and campaigns look "effective" in reports and which do not.

In the GA4 interface, most of this logic is hidden from the user because traffic sources, attribution models, and prioritization rules are applied automatically. In BigQuery, it's different: we work with raw data, and therefore we must clearly understand two points:

1. which attribution model we are reproducing (because reports can be based on different approaches);
2. which specific value in the GA4 export to BigQuery corresponds to which data level.

We will start with the second one.

2.2 Three levels of traffic data

One of the key reasons for confusion when working with GA4 data in BigQuery is ignoring the fact that traffic sources exist at different levels (scopes). As in the GA4 interface, there is no single "correct" source / medium field — instead, there are several levels of data, each of which answers its own analytical question.

In BigQuery, these levels are implemented at the level of the data structure, and they are fundamentally not interchangeable. If you use a field "from the wrong level," the query might look logical and return neat figures, but the interpretation of such data will be false.

In the context of traffic analysis in the GA4 export data to BigQuery, it is worth clearly distinguishing three levels (they are similar to those in the interface):

• user level (User),
• session level (Session),
• event level (Event).

How GA4 level logic translates into BigQuery

As we already know, the GA4 export in BigQuery has an event structure: one row of the table corresponds to one event. However, this does not mean that all data in this row has the same analytical meaning. On the contrary — inside each "event" row, information is stored that logically belongs to different levels of entities: user, session, and event.

• User-level (User level) — you analyze the user as a separate entity. The basic unit of such analysis is user_pseudo_id, which identifies the user throughout their interaction with the site or app. From the perspective of the traffic source, here we are talking about the source that first acquired the user to our site.
• Session-level (Session level) — you analyze individual user visits. One user can have many sessions and you already know that each session is defined by the combination of user_pseudo_id and ga_session_id. When we talk about sources at this level, we usually mean the traffic source from which the session started.
• Event-level (Event level) — you work with specific user actions within a session. Here, each row of the table is a separate event with its own name (event_name) and execution time (event_timestamp), and the additional context of the event is passed through parameters. From the point of view of traffic sources, this is the "raw level." We have information about every event and we can do whatever we want with it; in other words, it is precisely on data from this level that we can build our own attribution models.

Thus, although at first glance the export in BigQuery has an event logic, an analyst always works not with "event rows," but with a hierarchy of entities: user → session → event. The level at which you aggregate data directly determines the meaning of metrics and the correctness of conclusions. And an incorrect understanding of the level you have to work with/are working with leads to incorrect calculations and false conclusions.

Understanding this logic becomes critically important when we move to the analysis of traffic sources. Therefore, below I have prepared a table that will help you once and for all figure out and remember which level each of the traffic source fields belongs to and what analytical question it is intended to solve.

Traffic source fields in BigQuery: traffic_source, session_traffic_source_last_click, collected_traffic_source

And to finally reinforce the material, let's break down an example to see the difference. Imagine such a user:

1. The user first enters the site from Google organic search (google / organic) and, for example, subscribes to a newsletter.
2. A few days later, he goes to the site from an email newsletter (esputnik / email).
3. Later, he opens the site directly by entering the URL in the browser (direct / (none)), and during this visit makes a purchase.

In this scenario, depending on the data level we use in BigQuery, the information will be as follows:

• traffic_source (User-level) For this user, the traffic source will always be google / organic, since it was from this channel that he got to the site for the first time. Even a purchase made during a direct visit will be associated with google / organic at the user-level. That is why this field is suitable for analyzing the acquisition of new users, but not for evaluating campaign effectiveness.
• session_traffic_source_last_click (Session-level) The session within which the purchase took place will be attributed to esputnik / email (this was the last non-direct channel). This field reproduces the logic of the standard Traffic Acquisition report in GA4 and is suitable for analyzing traffic effectiveness and conversions at the session level.

• collected_traffic_source (Event-level) At the event level, raw data will be waiting for you. By themselves, without additional processing, they are not very useful. But it is precisely based on these data that you can build your own attribution logic. For example, using Linear non-direct attribution (linear attribution without taking direct into account), we can assign 50% of the purchase value to google / organic, and the other 50% to esputnik / email.

Section 3: Building the Traffic Acquisition report on session_traffic_source_last_click

Despite these discrepancies, and especially considering that they were noticed not on all projects, the use of session_traffic_source_last_click fields is the best start for getting acquainted with attribution in BigQuery. Especially if you consider that officially Google positions them as an analog to data from the Traffic Acquisition report, which is exactly what we need.

3.1 Structure of the Traffic Acquisition report

Here we are getting to the most interesting and practical part. But to start writing an SQL query, you need to understand very well exactly what the Google team outputs in this report. The Traffic Acquisition report analyzes traffic effectiveness precisely at the level of sessions, not individual events or users. That is why it is based on such principles:

• key entity — session;
• traffic source is determined for the session;
• all metrics are aggregated around sessions.

Dimensions:

• Session primary channel group - the main channel group of the session, which shows the main way users come to the site or app.
• Session default channel group - the standard channel group of the session, which defines the main channels through which users come to the site (for example, organic search, direct interaction, referrals, etc.).
• Session source / medium - the combination of the source and channel of traffic that brought users to the site or app.
• Session medium - the traffic channel (for example, organic search, paid search).
• Session source - the traffic source (for example, google, facebook).
• Session source platform - the platform of the traffic source through which users came to the site or app.
• Session campaign - the traffic source campaign through which users came to the site or app.

Metrics:

• Sessions - the number of sessions that started from a certain source, channel, or campaign.
• Engaged sessions - the number of sessions in which users actively interacted with the content.
• Engagement rate - the percentage of engaged sessions of the total number of sessions.
• Average engagement time per session - the average interaction time per session.
• Events per session - the number of events per session.
• Event count - the total number of events.
• Key events - key events that took place on the site.
• Session key event rate - the percentage of sessions in which key events occurred of the total number of sessions.
• Total revenue - total revenue obtained from users who came through a certain source, channel, or campaign.

Next, we will step-by-step recreate each column of this report and break down their nuances, and at the end, we will merge everything into a final SQL query.

3.1.1 Report Dimension: Session source / medium

The most frequently used parameter in the Traffic Acquisition report is Session source / medium, so we will start our report with it. For this, we will use session_traffic_source_last_click.cross_channel_campaign. This is a part of the session_traffic_source_last_click object, which contains the attributed source of the session.

3.1.2 Sessions Metric

We already defined it above. Let me remind you that in BigQuery we define a session as a unique combination of user_pseudo_id and ga_session_id:

3.1.3 Engaged Sessions Metric

Engaged Sessions — here it gets more interesting. According to the official GA4 logic, a session is considered "engaged" if at least one of the conditions is met:

• the session lasted more than 10 seconds, or
• contained a key event (conversion), or
• had 2 or more page/screen views.

At the same time, in the GA4 settings themselves, the number of seconds for the condition "session lasted more than 10 seconds" can be changed.

But don't worry, you won't have to take all these conditions into account in the SQL query yourself. In the GA4 export, there is a special parameter session_engaged, which marks sessions that meet these criteria.

The session_engaged parameter can have the following values:

• "1" — the session meets the rules of an engaged session;
• "0" or NULL — the session does NOT meet the rules of an engaged session.

Let's break it down more practically:

If you don't go into details and nuances, the query for obtaining sessions with interaction would look something like this: we simply count unique real_session_id where the value of session_engaged equals 1. And although the query below returns a certain result — in most cases, it will be incorrect.

The reason for the error is that there are nuances in GA4 export data. One of which is that whole numeric values can be recorded in both event_params.value.string_value and event_params.value.int_value.

Therefore, a correct query must take this feature into account. For comparison, below you can see the results of the "first" solution and the correct one:

As seen in the screenshot above, the difference in this specific case was 1 − (3633 / 4372) ≈ 17%, which is quite significant.

Explanation of query nuances:

• UNNEST(event_params) is used to access the session_engaged parameter since it is stored in a nested array.
• COALESCE + CAST is our protection against "dirty" data. We check the text field string_value. If it is empty, we take the numeric int_value, safely convert it to text using CAST and pass it on. This guarantees that we will find our "one," however GA4 might have recorded it.
• IF forms a conditional value of real_session_id:
  • If after all checks session_engaged equals '1' (i.e., the session meets the criteria of "engaged"), we return the unique session identifier.
  • If the session is not engaged, the expression returns NULL.
• CONCAT forms this same unique identifier. It combines user_pseudo_id and ga_session_id. Thanks to the mechanism of implicit coercion, BigQuery itself understands that the numeric session ID value needs to be converted to text for concatenating.
• COUNT(DISTINCT…) counts only unique identifiers of engaged sessions. This is critically important because one session contains many events (page_view, scroll, user_engagement), and if we did not use DISTINCT, we would simply count the number of events instead of the number of sessions. And the NULL value (unengaged sessions) is simply ignored by the COUNT function.

Phew.. This was one of the hardest for today. It will be easier from here on.

3.1.4 Engagement rate Metric

Engagement rate is the share of engaged sessions of the total number of sessions. To calculate it, we need to divide engaged_sessions by sessions. We already have both of these values, so theoretically we could simply use the "/" symbol, but we are learning to write correct and safe code right away, so we will do it using SAFE_DIVIDE.

This function performs the division of two values and prevents a division by zero error. The calculation will look like this:

We additionally multiply by 100 to get the value immediately as a percentage.

3.1.5 Average engagement time per session Metric

Interaction time is stored inside event_params in the engagement_time_msec parameter. How to work with this nesting we analyzed above. And I already broke down the nuances of calculating this indicator in the previous article in the Average engagement time per user Metric block. The only difference is that there we divided by the number of users, and here it will be the number of sessions.

In the query, we convert milliseconds to seconds and divide by the number of sessions. If you want it immediately in minutes, you need to divide by another 60.

ROUND() - can be added as desired to immediately round the value to the usual presentation of percentages with 2 decimal places.

3.1.6 Events per session Metric

Events per session shows the average number of events that occur in one session. Since each individual row in the export table is a separate event, counting the number of events is one of the easiest operations: COUNT(*). The final formula is then:

3.1.7 Event count Metric

By default in the interface, this column is the total count of all events, which equals a simple COUNT(*). But if you want to reproduce the logic of choosing a specific event from a dropdown list (for example, only purchase), we use the COUNTIF function, which counts only those rows where the condition is met:

3.1.8 Key Events Metric

Although in the interface, for an event to be counted as key, you need to mark it accordingly, in BigQuery there are no such conventions. If needed, any event can instantly become a key one — it's enough just to give it the corresponding name :)

3.1.9 Session key event rate Metric

This metric answers the question: in what share of sessions did at least one key event occur? Let's break it down using the purchase event as an example. The formula will be as follows:

Session key event rate = number of sessions that had a purchase event / total number of sessions.

I draw attention to a common error: in the numerator, we use not the number of purchases, but the number of sessions in which there was a purchase event. If in one session, for some reason, 2 purchase events occurred — it is still one session for this calculation.

SQL query logic is as follows:

1. identify the unique session,
2. select only those sessions in which event_name = 'purchase',
3. divide the number of such sessions by the total number of sessions.

We get this query:

3.1.10 Total revenue Metric

Calculating Total revenue is quite simple, since this value is stored in a separate column ecommerce.purchase_revenue. To avoid empty NULL values in the report for those sources that did not bring revenue, we will use the IFNULL function and replace the void with 0:

3.1.11 Final query: assembling the Traffic Acquisition report in BigQuery

Here we have reached the most interesting part. We have analyzed every brick, every metric, and parameter. Now it's time to merge them into a single SQL query that will reproduce the Traffic Acquisition report from the GA4 interface using the Session source / medium dimension.

For the query to work correctly, we cannot just dump all the formulas into one SELECT block. Why? Because metrics like Engagement rate or Events per session require dividing one metric by another (for example, engaged_sessions / sessions). The BigQuery engine does not allow using newly created column names for math in the same block (at least in normal mode).

That is precisely why we will use the WITH construction (Common Table Expression, or CTE).

The magic of the WITH operator

The WITH operator allows you to create a virtual temporary table (we will call it base_metrics) right during query execution. How it looks in code (Three-level architecture):

• Step 1 (prep_events): Preparation. We extract all nested parameters (session_id, session_engaged, engagement_time) and make convenient columns from them.
• Step 2 (base_metrics): Aggregation. We calculate our COUNT and SUM using already prepared clean columns.
• Step 3 (Final SELECT): Math. We calculate rates, percentages and round them.

Thanks to this, the code becomes modular, readable, and maximally safe.

Here is our final query:

Parameterization: how to make the report universal

In the GA4 interface, you can change the primary dimension of the report with one click in a dropdown list (for example, switch from Source / Medium to Campaign). Our SQL query can do that too! You don't need to rewrite the entire metric calculation logic.

It's enough to change just one first line in the WITH block. For example, if you want to see this same report in the context of advertising campaigns, just replace the session_source_medium definition line with:

Everything else will work automatically since all our metrics are tied to aggregation (GROUP BY 1 will group data by this new first column).

Conclusion

In the second part of my guide, we took another big step toward understanding and reproducing GA4 logic in BigQuery. In this article, we analyzed:

1. Preparing raw GA4 data for analysis in BigQuery — why PARSE_DATE, CAST, COALESCE, and CASE WHEN are needed, and exactly how these operators affect the correctness of metrics, aggregations, and subsequent conclusions.
2. The logic of attribution in GA4 export tables to BigQuery and why for traffic acquisition analysis you should use the session_traffic_source_last_click object.
3. How different data levels work (user / session / event) and why there is no single universal source / medium in GA4. I hope now you clearly distinguish traffic_source, collected_traffic_source, and session_traffic_source_last_click and understand which analytical tasks each of them is suitable for.
4. And step-by-step reproduced the Traffic Acquisition report in BigQuery.

In summary: after this part, you have not just a final SQL query, but a clear logic of how to reproduce another GA4 report in BigQuery — with transparent formulas that can be expanded for your own tasks.

If you are just starting to work with GA4 export in BigQuery or feel that you lack confidence in basic things, we recommend starting with the first part of this guide. In it, we analyzed in detail the GA4 export structure, working with nested parameters via UNNEST and scalar subqueries, basic SQL operators, and step-by-step built a Pages and Screens report.

And finally — for those who like to look a little further.

A look into the future (Pipe syntax)

We have just built a full-fledged, "adult" analytical report. It takes into account the features of data types (via COALESCE), protects against division by zero (via SAFE_DIVIDE), and works as transparently as possible.

For those who want to write even more elegant code, Google recently added support for Pipe syntax (|>) to BigQuery. This approach allows reading the query not from the inside out, but strictly from top to bottom — like a conveyor belt at a factory.

Our three-step architecture (Preparation ➝ Aggregation ➝ Metric Calculation) fits this syntax perfectly. Instead of WITH blocks, we use the EXTEND operator to create variables "on the fly."

Here is how stylish and modern our final report will look:

Advantages of this approach: You see all data movement as if on the palm of your hand. Every |> is the next stage of processing. Under the hood, both variants (both classic WITH and Pipe) work the same and give the same result, so the final choice remains only yours.