Creating Custom Search Query Categories using CASE statements in Data Studio

Creating Custom Search Query Categories in 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;

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

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.

If you liked this article you might like my other blog posts.

Or you can follow me on Twitter!

Michael

Author Michael

More posts by Michael

Join the discussion 2 Comments

  • Adil says:

    Hi Michael, would CONTAINS_TEXT be case sensitive? I usually use this one that someone shared with me. I believe it converts all to lowercase:
    CASE
    WHEN REGEXP_MATCH(Ad set name, “((?i).*product 1).*”)
    THEN “Product1”

    What do you think?

    • Michael says:

      Hi Adil, that’s a great point. I believe it is case sensitive unfortunately. I’ll need to look into it and update the article if necessary. Thanks for that CASE statement, I’ll see if it works. Thanks a lot for the comment – sorry for late response.

Leave a Reply