How to create GA4 Site Search Tracking report in BigQuery

Follow me on LinkedIn - AI, GA4, BigQuery

Through the GA4 site search tracking (also known as ‘Internal Site Search Tracking’) feature, you can determine the search terms used by your website visitors to find information on your website:

Once you have implemented site search tracking in your GA4 property, you can see the list of keywords (called ‘Search terms’) used by your website visitors to find information on your website:

The ‘view_search_results’ event

Once you have enabled/setup site search tracking in your GA4 property, whenever a user inputs a query and clicks on the search button on your website, GA4 fires the ‘view_search_results’ event.

You can see the ‘view_search_results’ event in your GA4 property via the ‘Events’ report:

You should check how the ‘view_search_results’ events are being recorded in the GA4 BigQuery data table and what event parameters are being passed along with this event type.

Take a close look at the various event parameters which are sent along with the ‘view_search_results’ events:

search_term
ignore_referrer
engagement_time_msec
session_engaged
ga_session_id
page_referrer
engaged_session_event
unique_search_term
content_group
page_location
batch_ordering_id
batch_page_id
ga_session_number
page_title

While creating a text prompt, we will refer to these event parameters (esp., ‘search_term’).

Creating Site Search Tracking report in GA4 BigQuery.

Here is how you can create an ‘Internal Site Search’ report in GA4 BigQuery to track what users are searching on your website.

Step-1: Make sure that the ‘Site Search’ tracking is set up and that you have collected at least a couple of days of ‘view_search_results’ event data in your GA4 property and BigQuery data set.

Step-2: Navigate to your GA4 BigQuery data table and run a query to check how the ‘view_search_results’ events are being recorded and what event parameters are being passed along with this event type. This will give you a good idea of what site search term data to extract.

Step-3: Calculate the values of the ‘Search Term’ column by extracting the values from the ‘search_term’ event parameter for each ‘event_name’ top-level field where ‘event_name’ is ‘view_search_results’.

Step-4: Calculate the values of the ‘Event Count’ column by counting the total number of ‘view_search_results’ events for each ‘search_term’ event parameter.

Step-5: Sort the table in descending order for ‘Event Count’.

Step-6: The top row of the data table should show ‘Total’ under the ‘Search Term’ column and total number of ‘Event Count’ across all ‘search_term’ under the ‘Event Count’ column.

Once you understand the logic, creating site search tracking report is easy.

The SQL below is automatically generated via a text prompt in ChatGPT.

-- Internal Site Search Tracking.

WITH
  search_data AS (
    SELECT
      COALESCE(
        (
          SELECT ep.value.string_value
          FROM UNNEST(event_params) AS ep
          WHERE ep.key = 'search_term'
        ),
        '(not set)') AS search_term,
      COUNT(*) AS event_count
    FROM
      `dbrt-ga4.analytics_207472454.events_*`
    WHERE
      _TABLE_SUFFIX BETWEEN '20241001' AND '20241031'
      AND event_name = 'view_search_results'
    GROUP BY
      search_term
  ),
  total_row AS (
    SELECT
      'Total' AS search_term,
      SUM(event_count) AS event_count
    FROM
      search_data
  )
SELECT *
FROM total_row
UNION ALL
SELECT *
FROM search_data
ORDER BY
  event_count DESC;

If you want to learn to automate SQL generation via text prompts in ChatGPT, enrol in my GA4 BigQuery course.

Instead of teaching you SQL, I teach you to create effective text prompts in chatGPT, which will automatically create your desired SQL.

The focus is entirely on teaching the underlying logic (instead of the actual SQL). So that you can scale across ‘N’ SQL use cases and generate consistent outputs repeatedly with 100% accuracy.

[No prior knowledge of BigQuery or SQL required.]

The ‘view_search_results’ event

Creating Site Search Tracking report in GA4 BigQuery.

Once you understand the logic, creating site search tracking report is easy.

Related Articles:

Himanshu Sharma