Creating Custom Search Query Categories using CASE statements in Data Studio

In this post I’ll show a method for creating custom Search Query Categories. This method involves creating custom categories using CASE statements.

Google Data Studio announced yesterday that they had added some new text functions. One of them was;

  • CONTAINS_TEXT — Returns true if the specified text is found in the field or expression, otherwise returns false.

I thought I’d try it out by playing around with some Search Console data. The actual Data Studio report is at the end of the blog post.

1. Adding your Search Console Data to Google Data Studio

The first step is to add your Search Console data to Google Data Studio. Create new Data Studio report and choose Search console as your data source.

Adding Search console

Make sure to choose ‘Site Impression’ and not ‘URL impression’ when adding the Search Console data.

If you’ve added in the data it should look like what is shown below.

Adding Search Console Data to Google Data Studio

2. Creating a new field for custom Search Query Categories

Now you need to click on Add a Field to create our custom search query category.

This is the CASE statement I created, as I wanted to try divide the search queries into topics that would be relevant to my website.

Google Search Console CASE statement to create custom Search Query Categories

The terms I use will likely not be relevant for you, so here’s a blank CASE statement you could use;

WHEN CONTAINS_TEXT(Query,"xxxxxxxxxx") THEN "XXXX"
WHEN CONTAINS_TEXT(Query,"yyyyyyyyyy") THEN "YYYY"
WHEN CONTAINS_TEXT(Query,"zzzzzzzzzz") THEN "ZZZZ"
ELSE "Other"

Now that we have our ‘Query Category’, we can add it to our Data Studio report.

3. Creating a Query Category table in the Data Studio report

Once we’ve created our custom query category can build a table summarizing how many clicks, impressions, the Click-Through-Rate and the average position of each Query Category.

custom Search Query Categories table in Data Studio

And below is the finish Data Studio report. You can click on any of the Query Categories to filter the Query table on the left.

Hope you enjoyed this post about creating custom Search Query Categories.

Download Google Data Studio Templates

If you’d like to download one of my Google Data Studio reports as a template to use for your own data, you can visit Data Studio Templates and purchase one.

Google Data Studio Templates

Other Blog posts

If you liked this article you might like my other blog posts about Google Search Console.

Or you can follow me on Twitter!

Michael Howe-Ely