Google Analytics 4

Integrating GA4 Session Manual Term into a Query

Posted by Sebastian on 15 November 2022 at 2:18 pm

Hey there, I’m trying to weave the Session Manual term into the following query:

SELECT *
  FROM nth<span class="hljs-operator">-</span>glider<span class="hljs-number">-369017.</span>analytics_316822874.events_<span class="hljs-operator">*</span>, UNNEST(event_params) as param
 WHERE event_name IN ("sr_job_application_started", "sr_job_application_continued", "sr_job_completed_application")
   AND param.key IN ("term", "campaign", "source", "medium", "engaged_session_count")
   AND _table_suffix BETWEEN format_date('%Y%m%d',date_sub(current_date(), interval 30 day))
                         AND format_date('%Y%m%d',date_sub(current_date(), interval 1 day));

I tried slipping in “term”, but zilch, nada, nothing! Even though my UTM’s have the UTMTerm value in them.

Also, I experimented with this, but “URL” tripped me up:

SELECT 
URL, REGEXP_EXTRACT(url, r'utm_content=([^&]+)') AS utm_content
FROM nth<span class="hljs-operator">-</span>glider<span class="hljs-number">-369017.</span>analytics_316822874.events_<span class="hljs-operator">*</span>
CROSS JOIN UNNEST(event_params) AS param
WHERE event_name IN ("sr_job_application_started", "sr_job_application_continued", "sr_job_completed_application")
  AND param.key IN ("term", "campaign", "source", "medium", "engaged_session_count")
  AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                       AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY));

Olivia replied 1 year, 4 months ago 3 Members · 2 Replies

2 Replies

Brooke

Member
24 April 2023 at 11:42 pm

The term “Session Manual” isn’t used in your original query, and it seems like you’re trying to filter results based on specific values present in your UTM parameters. Regarding the attempt with “term”, please ensure that it is a valid event parameter in your provided dataset; otherwise, no data will be returned. If you’re sure “term” is a valid key and should have values, then it is possible that the query isn’t running as expected due to other portions of the WHERE clause.

For the part of the query that’s giving you the issue with a URL, it seems you’re trying to parse out the “utm_content” value from a URL or a set of URLs. It appears the confusion might be stemming from the fact that “URL” is called without being selected or joined from any table because it’s not included in the event_params. If URL is a field in your analytics event datatable, then you need to point to this field properly and it should function correctly. If it’s not a field, you will need to adjust the query to pull in the URLs in question from the correct location.

Without more specific details on your dataset and the precise role supposed to be played by “Session Manual”, it’s tricky to provide a more precise answer. I would recommend making sure that “term” and “URL” are valid inclusions in your WHERE and SELECT clauses.
Olivia

Member
3 May 2023 at 1:15 pm

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.

Integrating GA4 Session Manual Term into a Query

Brooke

Olivia