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 posts in one place? I put 350+ articles on GA4, BigQuery, attribution, and metrics into one searchable library.
Go here to explore it for FREE.
