Utilizing Google Analytics 4 Properties and BigQuery to Recreate Site Search Metrics From Universal Analytics

If you’ve recently switched over or added on a Google Analytics 4 property for your Google Analytics accounts, you may have noticed a few essential reports from the property that are missing. One key report that most people visit is the site search report, which helps you get a sense of what your site visitors are looking for on your platform along with engagement metrics.

With Google Analytics 4 Properties, we’re missing some of the metrics we’re used to from Universal Analytics that are not currently available with certain out-of-the-box (OOTB) features in the standard reports. This is a limitation, although through the use of BigQuery we can replicate metrics from the Universal Analytics site search report with our site data for the time being.

In this post, I’ll walk through calculating some of the fundamental metrics for replicating the site search report.

Pre-Exercise

A great feature of the Google Analytics 4 Properties interface is the Analysis hub. This is a powerful tool that when properly leveraged, can dive deep into your data with different analyses.

In this pre-exercise, you can use event data that’s being passed into the property to create an Exploration Report, this will be similar to the Custom Reports we have in Universal Analytics.

In this example, you can use custom dimensions of the view_search_results event, and search_term in order to see the event counts and active users for each. This is just a starting point, but let's dive into BigQuery in order to gain additional metrics.

Word of warning: Although this post discusses the site search report, which is one of the many insightful reports from Universal Analytics, I’ll also discuss technical details surrounding Google Analytics definitions, and the format of the BigQuery data. If you have not worked in BigQuery before, you may want to start off with an introduction.

1. To get started check out this blog, by Abigail Matchett, to connect your Google Analytics 4 property to BigQuery!

2. Once your Google Analytics 4 Properties data is connected to BigQuery, ensure that data is coming over successfully.

3. The next step is going to be running a test query that will show us all the current events we’re pulling in, and how they are stored.

In order to recreate the site search term report, we need to be able to first see which search terms are being searched by our site visitors. Within Google Analytics 4 Property data, the unique events and parameters are stored by an event_name, event_parameter_key, and an event_parameter_value.

A great way to test how the events are stored in your dataset is by running the following query:

SELECT
	event_name,
	params.key AS event_parameter_key,
	CASE
		WHEN params.value.string_value IS NOT NULL THEN ‘string’
		WHEN params.value.int_value IS NOT NULL THEN ‘int’
		WHEN params.value.double_value IS NOT NULL THEN ‘double’
		WHEN params.value.float_value IS NOT NULL THEN ‘float’
 END
AS event_parameter_value
FROM
	`style-exchange-boutique.analytics_235852471.events_20200724, 
	UNNEST(event_params_ AS params
WHERE
	_table_suffix BETWEEN ‘20200515’
	AND FORMAT_DATE (‘%Y%m%d’ DATE_SUB (CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY
	1,
	2,
	3
ORDER BY 
	1,
	2

Let’s Get Started

Primary Dimension - Search Term

We first want to run a report on the primary dimension, which is the search term. We know that search_term has the value of a string since it’s text. We will want to obtain the search_term from users who view the view_search_results page event, given the order of events that a user takes to perform a search event. That being said, the view_search_results will be our event_name, and search_term will be our event_parameter_key.

SELECT
(
SELECT
  value.string_value,
 FROM
  UNNEST(event_params)
 WHERE
 event_name = 'view_search_results'
  AND key = 'search_term') AS search_term,
  COUNT(*) AS total_searches
FROM
`style-exchange-boutique.analytics_235852471.events_20200914`
WHERE
event_name = 'view_search_results'
GROUP BY
1

Total Unique Searches

In Google Analytics, unique search terms represent the number of sessions during which a term was searched. In order to solve for total_unique_searches, we’ll want to do a count to find the number of sessions that have a search event performed.

To count unique searches, we need a unique way of identifying a session. By using ga_session_id, we are able to grab any search event that occurs during a single session, and then exclude multiple searches that occur during the session with the same keyword. Using this session identifier, we can now compute total unique searches.

COUNT(DISTINCT CONCAT('ga_session_id', 'search_term')) AS total_unique_searches,

Results Pageviews / Searches

In order to calculate the Results Pageview / Search metric, we will need to configure any pageviews from the view_search_results event during a session, and then divide that by total unique searches (which we showed how to calculate above).

COUNT(DISTINCT CONCAT('ga_session_id','view_search_results')) AS results_pageview,

COUNT('results_pageview')/COUNT('total_unique_searches') AS results_pageview_search

Putting It All Together

Finally, we’re able to put everything together in order to get our table output in BigQuery of the site search terms, and metrics to follow.

The data in your table will look different than mine!

SELECT
(
SELECT
  value.string_value,
 FROM
  UNNEST(event_params)
 WHERE
 event_name = 'view_search_results'
  AND key = 'search_term') AS search_term,  
   COUNT(*) as total_searches,
   COUNT(DISTINCT CONCAT('ga_session_id', 'search_term')) AS total_unique_searches,
   COUNT(DISTINCT CONCAT('ga_session_id','view_search_results')) AS results_pageview,
   COUNT('results_pageview')/COUNT('total_unique_searches') AS results_pageview_search
FROM
`style-exchange-boutique.analytics_235852471.events_20200914`
WHERE
event_name = 'view_search_results'
GROUP BY
1

Output:

Thinking Ahead

We’re all very excited to be working with Google Analytics 4 Properties! Looking ahead, making the move to add on the new property in conjunction with your Universal Analytics property is highly recommended. Starting to work now in your Google Analytics 4 Properties will get you up to speed and comfortable working in the new interface, and learning this essential new way to track web and app analytics.

Google Analytics 4 Properties gives us a clear indication of where Google is heading in terms of analytics. Despite the current lack of typical favorite reports that we may view on a day-to-day basis with Universal Analytics, it’s great to get in the habit of recreating these reports in BigQuery in the meantime and utilizing the Analysis hub.