-
It appears that you’re trying to extract UTM parameters from URL in your Google Analytics BigQuery dataset. One issue that might be occurring is the variable names may not be exactly ‘term’, ‘campaign’, ‘source’, ‘medium’, ‘engaged_session_count’ in your dataset, try running a query to list all unique parameter keys to confirm these param keys are present and correctly spelled.
Moreover, it’s worth noting that the term ‘URL’ in your second query isn’t predefined. You need to replace ‘URL’ with the correct column name that stores the URL from the table in BigQuery. The column that holds this information is often named ‘page_location’ or ‘page_path’ in the BigQuery export of Google Analytics data, not ‘URL’. And the regular expression you are using in REGEXP_EXTRACT function is specifically for extracting ‘utm_content’ not the term. You would need to replace ‘utm_content’ with ‘utm_term’ or the correct UTM parameter you want to extract.
For URL parameter extraction from ‘page_location’, it should be something like this:
SELECT page_location, REGEXP_EXTRACT(page_location, r’utm_term=([^&]+)’) AS utm_term …
Here ‘page_location’ should be replaced by the actual name of the column that contains the URL in your table. If you can’t find the column, you may need to unnest ‘hits’ or another nested field. Please check the scheme of your BigQuery GA export for exact column names.
