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.

If you work with GA4 to BigQuery exports, be sure to check out my SQL cheat sheet.