Tracking Google Analytics 4 UTM Parameters in BigQuery

Follow me on LinkedIn - AI, GA4, BigQuery

Follow these 5 Simple Steps to Track GA4 UTM Parameters (aka Custom Campaigns) in BigQuery.

Step-1: Calculate the values of the ‘Campaign Name’ column by extracting the values from the ‘collected_traffic_source. manual_campaign_name’ top level field.

Exclude the values where ‘Campaign Name’ is ‘null’, ‘(organic)’ or ‘(referral)’.

Step-2: Calculate the values of the ‘Campaign Source’ column by extracting the values from the ‘collected_traffic_source. manual_source’ top level field.

Note: Clarifying the field hierarchy in your text prompt by specifying that it’s a “top-level field” is very important. Otherwise, ChatGPT could make an incorrect assumption and try to extract ‘manual_campaign_name’, ‘manual_source’, and ‘manual_medium’ from the ‘event_params’ array using a subquery. But these fields need to be accessed directly through ‘collected_traffic_source’.

Step-3: Calculate the values of the ‘Campaign Medium’ column by extracting the values from the ‘collected_traffic_source. manual_medium’ top level field.

Step-4: Calculate the values of the ‘Sessions‘ column for each’ Campaign Source’ by counting each unique combination of ‘user_pseudo_id’ and ‘ga_session_id’.

Step-5: Sort the table in descending order for ‘sessions’.

GA4 BigQuery Fields and their Corresponding UTM Parameters.

GA4 BigQuery Field	UTM Parameter	Description	Expected Value Format	Example Value
collected_traffic_source.manual_campaign_id	utm_id	Unique identification number of the campaign	Numeric ID	17099389748
collected_traffic_source.manual_campaign_name	utm_campaign	Name of the custom campaign	Text, numbers, or a combination of both	Winter_Sale_2025
collected_traffic_source.manual_source	utm_source	Information related to the traffic source	Text indicating the origin of the traffic	google, facebook, email
collected_traffic_source.manual_medium	utm_medium	Information related to the traffic medium	Text indicating the category of the traffic	cpc, social, referral
collected_traffic_source.manual_term	utm_term	Information related to the campaign term (paid search keyword)	Text indicating the paid keyword	conversion-optimization
collected_traffic_source.manual_content	utm_content	Information related to campaign content (ad version)	Text indicating the content variant	banner1, banner2
collected_traffic_source.manual_source_platform	utm_source_platform	Name of the marketing platform that sent traffic to your website	Text indicating the platform	Google Ads, Search Ads 360
collected_traffic_source.manual_creative_format	utm_creative_format	Information related to the creative format (ad version)	Text indicating the creative format	video_ad, display_ad
collected_traffic_source.manual_marketing_tactic	utm_marketing_tactic	Information related to marketing tactics like targeting strategies, bid strategies, etc.	Text indicating the tactic	prospecting, remarketing

Use the following SQL (automatically generated via a text prompt in ChatGPT) to track UTM parameters in BigQuery:

-- Calculate traffic source and medium for Custom Campaigns.

WITH
  campaign_data AS (
    SELECT
      COALESCE(NULLIF(collected_traffic_source.manual_campaign_name, ''), NULL)
        AS campaign_name,
      COALESCE(NULLIF(collected_traffic_source.manual_source, ''), NULL)
        AS campaign_source,
      COALESCE(NULLIF(collected_traffic_source.manual_medium, ''), NULL)
        AS campaign_medium,
      CONCAT(
        user_pseudo_id,
        '-',
        CAST(
          (
            SELECT ep.value.int_value
            FROM UNNEST(event_params) AS ep
            WHERE ep.key = 'ga_session_id'
          )
          AS STRING)) AS session_id
    FROM
      `dbrt-ga4.analytics_207472454.events_*`
    WHERE
      _TABLE_SUFFIX BETWEEN '20241001' AND '20241031'
      AND collected_traffic_source.manual_campaign_name
        NOT IN ('null', '(organic)', '(referral)')
  )
SELECT
  campaign_name,
  campaign_source,
  campaign_medium,
  COUNT(DISTINCT session_id) AS sessions
FROM
  campaign_data
GROUP BY
  campaign_name,
  campaign_source,
  campaign_medium
ORDER BY
  sessions DESC;

I am not a fan of cookie-cutter reports. But if you want one, this one could be for you.

All you have to do is supply your table ID to generate SQL that works for you.

If you want to customize this report according to your unique data analysis requirements then you need to understand the “logic” used behind calculating the various metrics in this report.

Once you understand the logic, customizing this report is easy peasy.

I teach you such logic in my GA4 BigQuery course, where you can learn to automate SQL generation via text prompts in ChatGPT.

There could be ‘N’ use cases, and it won’t be possible to provide ready-made SQL code for every possible case.

So, it is better that you understand the logic to scale SQL generation to the moon.

The future belongs to those who can query raw GA4 data in BigQuery, apply SQL logic, automate SQL generation via AI and drive insights beyond what GA4 UI can offer.

GA4 BigQuery Fields and their Corresponding UTM Parameters.

Related Articles:

Himanshu Sharma