How to Extract URL Query Parameters in BigQuery

If you’re working with GA4 data in BigQuery, you’ve likely come across URLs with query parameters. These bits of data, like UTM tags (think utm_source or utm_medium), can be invaluable for tracking the details of your marketing campaigns and understanding your audience’s behaviour. In this post, I’ll take you through why query parameters matter, how to extract them, and when each method might come in handy.

Why Bother with Query Parameters?

Query parameters contain powerful information, often revealing how users found your site or which specific campaign brought them in. Here’s why they’re worth tracking:

Campaign Attribution: Want to know which email campaign brought in the most traffic or which social ad drove conversions? Query parameters like utm_source and utm_campaign let you tie user actions back to specific campaigns, giving you a clearer picture of what’s working.

Audience Segmentation: Query parameters help you break down users by their entry point or journey, whether they arrived from paid search, organic social, or direct links. This lets you create audience segments that reflect real behaviour patterns.

Content Performance: Parameters can track which blog post or product page brought in the most users, helping you assess content engagement and make data-driven decisions.

By pulling these parameters into their own columns in BigQuery, you’ll be able to analyse this data quickly and gain clearer insights into campaign performance, audience segments, and user journeys.

Two Ways to Extract Query Parameters

There are two ways to approach this. The first is quick and works well if you’re only after a single parameter (like utm_source). The second method is a bit more versatile, allowing you to pull all parameters into an array, which you can filter for whichever ones you need.

Method 1: Extracting a Single Parameter with regexp_extract

If you’re just looking to pull out a single query parameter, regexp_extract is the way to go. Let’s say you want utm_source—use this query:

select
  regexp_extract(
    (select value.string_value from unnest(event_params) where key = 'page_location'), 
    r'(?:\?|&)utm_source=([^&]+)'
  ) as utm_source
from
  `<your-project>.<ga4-dataset>.<ga4-table>`

In this example, we’re telling BigQuery to look at the page_location URL and pull out the value for utm_source. This is ideal when you only need one or two specific parameters and want a quick and easy solution.

This approach works well for use cases like:

Evaluating a single campaign (e.g., checking the performance of traffic from one source like Facebook Ads).

Testing specific parameters that you use frequently and want to monitor individually.

Example Output

Let’s say we’re looking at URLs like these:

1. https://example.com/landing-page?utm_source=facebook&utm_medium=social&utm_campaign=spring_sale

2. https://example.com/product-page?utm_source=google&utm_medium=cpc&utm_campaign=summer_campaign

3. https://example.com/contact?utm_source=linkedin&utm_medium=social&utm_campaign=consultation

The output for utm_source would look like this:

utm_source
facebook
google
linkedin

This single-column result works well if you’re only interested in one parameter (e.g., to quickly identify traffic sources).

Method 2: Extracting All Parameters into an Array

For a more flexible solution—especially if you have multiple query parameters to track—the array method is better. Here’s how it works:

with url_params as (
  select
    array(
      (
        select
          as struct split(keyval, '=') [safe_offset(0)] as key,
          split(keyval, '=') [safe_offset(1)] as value
        from
          unnest(
            split(
              split((select value.string_value from unnest(event_params) where key = 'page_location'), '?') [safe_offset(1)],
              '&'
            )
          ) as keyval
      )
    ) as url_query,
  from
    `<your-project>.<ga4-dataset>.<ga4-table>`
)
select
  *,
  (select value from unnest(url_query) where key = 'utm_source') as utm_source
from
  url_params

In this query, we’re creating an array that contains all query parameters from the URL. This method is especially handy for:

Campaign Analysis Across Multiple Parameters: If you need to analyse utm_source, utm_medium, and utm_campaign together, this array method saves you from having to extract each one separately.

Flexible Tracking Needs: As your campaigns evolve, you can add or remove parameters without changing your core query structure.

Data Exploration: This method lets you experiment with new parameters without additional setup.

This query creates an array that contains all query parameters from the URL. Here’s how the output might look:

url_query	utm_source	utm_medium	utm_campaign
[{"key": "utm_source", "value": "facebook"}, {"key": "utm_medium", "value": "social"}, {"key": "utm_campaign", "value": "spring_sale"}]	facebook	social	spring_sale
[{"key": "utm_source", "value": "google"}, {"key": "utm_medium", "value": "cpc"}, {"key": "utm_campaign", "value": "summer_campaign"}]	google	cpc	summer_campaign
[{"key": "utm_source", "value": "linkedin"}, {"key": "utm_medium", "value": "social"}, {"key": "utm_campaign", "value": "consultation"}]	linkedin	social	consultation

Which Method Should You Use?

Each method has its benefits:

Method 1 (Single Parameter): Use this for a quick, specific parameter grab. It’s fast and straightforward if you only need one or two parameters.

Method 2 (Array of Parameters): Ideal if you need a flexible solution for multiple or changing query parameters. This method makes it easy to filter by any parameter without additional setup.

Putting Query Parameters to Work

Once you’ve extracted your query parameters, the real fun begins. You can use them to track campaign performance, measure engagement from specific sources, or even create segments based on user behaviour. This kind of insight can be a game-changer for refining marketing strategies, improving attribution, and better understanding how users interact with your site.

Here are a few ways to make the most of them:

Campaign Performance: Track the performance of different campaigns to see which sources drive the most conversions.

Audience Behaviour Analysis: Use query parameters to analyse visitor patterns across different entry points, whether from organic search, paid campaigns, or social media.

Customer Journey Mapping: Pull parameters to analyse specific touchpoints in the customer journey, helping you identify where users are dropping off or converting.

Funnel Analysis: Use parameters to build custom funnels in Looker Studio (or your reporting tool of choice), giving you a better view of the path to conversion for each campaign.

Final Thoughts

If you’ve not dived into query parameters before, they can feel a bit technical. But they’re so worth it! Once you’ve got them set up in BigQuery, the level of detail you can access is fantastic for anyone looking to dig deeper into campaign and audience insights. So, give these methods a go and see how they can help boost your analytics.

As always, if you have any questions or want help setting up, just reach out—I’d love to chat!