6.9. Using CASE to Extract URL Query Parameters

How can we extract search query parameters from a URL dimension?

We can do it using CASE, CONTAINS_TEXT(), and REGEXP_EXTRACT() functions which we will demo on this lesson.

A useful application of the CASE function is extracting URL query parameters, such as custom dimensions for reporting and aggregation. In this example, we want to extract search query parameters from page URLs and aggregate the number of page views for each search query.

Example

1. Identify if the page URL is a search URL by looking for the query parameter (e.g., ?q=).
2. If found, return the search query.
3. If not, return "Not a Search".

Here is the CASE function:

CASE
  WHEN CONTAINS_TEXT(page_url, 'q=') THEN REGEXP_EXTRACT(page_url, 'q=(.*)')
  ELSE 'Not a Search'
END

You can use a similar pattern to extract any other query parameter relevant to your reporting or business requirements.

Filtering and Creating a Dropdown Menu

To create a dropdown menu for users to filter unique values (e.g., specific search query IDs), you can use the same CASE function in a filter control. If you define the CASE function at the data source level, it will be available as a control field. Otherwise, you can create it at the chart or filter control level.

In this chapter:

→ 6.10. Mixing Dimension & Metrics in CASE

→ 6.11. CASE Function: Aggregating Results

→ 6.12. Misc Functions: IMAGE() & HYPERLINK()

Enjoying the free version of Looker Studio Masterclass 2022?

Join the new & updated Looker Studio Masterclass Live 2024 →

📩 Receive my weekly Looker Studio tips

🎥  Subscribe to my YouTube channel

🖇 Connect with me on LinkedIn