Learn business growth with Google Analytics 4 Forums Google Analytics 4 Identifying session-level traffic sources in BigQuery using CASE statements

  • Identifying session-level traffic sources in BigQuery using CASE statements

    Posted by Sophia on 23 June 2023 at 10:11 am

    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'
    END 
    

    You’re unsure if this kind of multi-condition CASE statement is feasible in BigQuery, is that correct?

    Sofia replied 1 year, 3 months ago 3 Members · 2 Replies
  • 2 Replies
  • Jayden

    Member
    3 July 2023 at 7:14 pm

    Yes, that’s correct. In BigQuery, a CASE statement is typically used for creating a single column, not multiple ones. Therefore, your query won’t work as intended. However, you could run the CASE expression twice, once for each new column (B and C in your example). Alternatively, you could create a STRUCT type 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 like SELECT new_struct.B, new_struct.C, where ‘new_struct’ is whatever name you decide to give your new struct column.

  • Sofia

    Member
    4 July 2023 at 4:41 pm

    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.