How to Parse URLs in BigQuery?

Pascal Landau has come up with an excellent solution for this.

To achieve this, you can leverage regular expressions and the REGEXP_EXTRACT_ALL function. As a result, you will obtain two arrays – one containing the query parameters in the URL and the other containing their values.

This approach is highly convenient when you need to quickly retrieve necessary parameters, especially when they are (for some unclear reasons) only available in the URL. Afterwards, you will, of course, need to process the arrays, and for that purpose, BigQuery offers various operators like STRUCT, STRING_AGG, and others.

Want all my best GA4-BQ queries in one place? I turned them into a Chrome extension — top SQL queries you can search and copy in seconds.

Go here to install it for FREE.

Prefer the web version? It's here.