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.

collected traffic source. manual campaign name

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.

collected traffic source. manual source

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.

collected traffic source. manual medium

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: