In our previous blog, we discussed how to set up advanced site search tracking in GA4. Now, we’re taking it a step further to help you transform that data into actionable insights. By exporting your site search data and performing key calculations, you can identify opportunities to optimise your search functionality, improve user experience, and drive revenue growth.
Step 1: Export Your GA4 Site Search Insights data
Start by exporting your search data from GA4. In this example, we’re using dummy data as the Google Merchandise Store Demo Account does not allow exporting. However, your analytics account will enable you to export directly from the Explore report you created in the previous blog. Ensure that your export includes the dimensions and metrics we outlined previously, such as "Page Path + Query String" and metrics like page views, sessions, item-list view events, item-list click events, add-to-basket events, and exits. Filter your data to include only pages containing your search query parameter, such as /search?keyword=.
Step 2: Prepare Your Data in Google Sheets
Once you’ve exported your data, open it in Google Sheets. Add the following column titles to structure your dataset for analysis:
Exit Rate
Item Click Through Rate
Add to Basket Rate
% Lower than Search
These additional columns will help you calculate key performance metrics that reveal how users interact with your search results. Ensure all data is aligned correctly under the relevant headings before proceeding to the next steps.
Step 3: Clean Up the Search Data
To focus only on the search terms, use the Find and Replace feature:
Press Ctrl + H.
Replace everything including and before /search?keyword= with a blank space.
This leaves you with clean search terms for better analysis.
Step 4: Calculate Exit Rate
Use the formula below to populate the "Exit Rate" column. These formulas assume you have followed all previous steps correctly and the data is structured as described:
Formula: =D2/B2
This calculates the percentage of sessions that ended after a search, helping you identify search terms that may frustrate users. Drag the formula down the column to apply it to all rows.
Step 5: Calculate Item Click Through Rate
Next, calculate the "Item Click Through Rate" with the formula:
Formula: =F2/E2
This metric shows the percentage of search result views that led to item clicks, highlighting the effectiveness of your search results. Drag the formula down the column to apply it to all rows.
Step 6: Calculate Add to Basket Rate
To measure the baseline effectiveness of search terms, calculate the ratio of sessions to add-to-basket actions. Use the formula:
Formula: =G2/C2
Alternatively, you can use page views or item views instead of sessions, depending on what baseline comparison makes the most sense for your analysis. This calculation provides a straightforward metric to compare search terms against each other and identify areas for improvement. Drag the formula down the column to apply it to all rows.
Step 7: Calculate Percentage Difference from Average Add to Basket Rate
To compare individual search terms against the site average, follow these steps:
Create Sum Formulas for Totals: At the bottom of columns B to G, calculate the totals for each metric using the formula:
Formula: =SUM(B2:B21)
Adjust the row range (e.g., B2:B21) to fit your dataset.
Calculate Averages Using Existing Formulas: Use the same formulas provided earlier to calculate average rates at the bottom of the dataset:
Exit Rate: =D22/B22
Item Click Through Rate: =F22/E22
Add to Basket Rate: =G22/C22
For example, if the average Add to Basket Rate is calculated to be 5.07%, this becomes the baseline for comparison.
Calculate Differences from the Average: Use this formula to compare individual terms to the site average and identify if they are higher or lower:
Formula: =IF(J2="", "", ROUND(((J2 - 0.0507) / 0.0507), 2))
This comparison highlights search terms that perform above or below the site average, providing actionable insights for optimisation.
Step 8: Apply Conditional Formatting
To visually identify search terms that are performing below the site average, apply conditional formatting with a colour scale:
Select the column containing the percentage differences from the site average.
Go to Format > Conditional Formatting in Google Sheets.
Choose a colour scale option and set the following:
Minimum value: Red (for lowest values).
Midpoint: White (set to 0 for the average).
Maximum value: Green (for highest values).
This will create a gradient from red to white to green, making it easy to spot underperforming search terms (red) and overperforming ones (green).
Step 9: Convert to a Table
To organise your data for better presentation, convert your dataset into a table:
Highlight your dataset, including column titles.
Navigate to the Format tab in Google Sheets.
Select Create a Table from the menu options.
This step standardises the layout and allows you to filter and sort data easily. In the next section, we’ll show you an example of how your table will look once formatted.
Step 10: Analyse the Results
To draw meaningful insights from your table, sort and filter the data:
Order by Page Views or Sessions:
Click the dropdown arrow on the "Page Views" or "Sessions" column.
Select "Sort Z-A" to organise the data from highest to lowest.
Filter Search Terms Below the Site Average:
Click the dropdown arrow on the "Lower/Higher Than Site Search" column.
Hover over "Filter by Condition" in the dropdown menu.
Select "Less than" and input 0 into the field.
This process allows you to identify underperforming search terms quickly, helping you prioritise areas for optimisation.
Step 11: Visualise and Share Insights
And that's it! You now know which of your search terms are searched the most and are performing below your site average. Use these insights to target your worst performers by:
Reordering products to improve their visibility.
Stocking new products to meet customer demand.
Reviewing and optimising pricing strategies.
For more guidance on setting up your ga4 site search insights tracking, refer to our previous blog.
Comments