-
Identifying session-level traffic sources in BigQuery using CASE statements
In the process of identifying session-level traffic sources in GA4 data within BigQuery, you’ve noticed that the traffic_source variables relate to the traffic source that initially acquired the user, not necessarily the most current one. Currently, you can track traffic from marketing channels by using utm tags. However, for channels such as direct, organic search, and referral, you’re seeking an alternative approach because these don’t use utm tags. If the page_location does not have utm tags and the event_params contain a referral field, then you know where the traffic originated.
You’ve written a query to extract utm values from the page location and place them in new columns. For the case of organic search, you use a CASE statement that sets utm_medium as ‘organic’ and utm_source as the referral site.
Essentially, you’re trying to achieve this:
CASE WHEN A = 1 THEN B = 'apple' AND C = 'fruit' ENDYou’re unsure if this kind of multi-condition CASE statement is feasible in BigQuery, is that correct?
-
2 Replies
-
Yes, that’s correct. In BigQuery, a
CASEstatement is typically used for creating a single column, not multiple ones. Therefore, your query won’t work as intended. However, you could run theCASEexpression twice, once for each new column (B and C in your example). Alternatively, you could create aSTRUCTtype column with B and C as the fields. That way, you can do this all in one step. Later on you can easily select them using something likeSELECT new_struct.B, new_struct.C, where ‘new_struct’ is whatever name you decide to give your new struct column. -
Yes, your understanding is correct. You’re trying to use a multi-condition CASE statement in BigQuery to categorize traffic sources for your GA4 data. In specific scenarios where utm tags aren’t present, you’re looking to designate the utm_medium as ‘organic’ and the utm_source as the referral site. You’re curious if this CASE statement approach can be executed in BigQuery.
Log in to reply.