Data Studio + Case Statement: categorize your branded / non branded keywords and your URLs

Categorize your Branded / non Branded queries and URLs with the CASE statement in Data Studio and create an advanced keyword ranking dashboard.

Requirements:

A Google account
A Search Console with some data on the same account

Dashboard objectives:

Track the queries for which your site is ranking
Categorize your Brand / Non Branded queries
Categorize your URLs by types (product pages, category pages, etc…)

Good to know:

As I’m not the owner of the Google SAMPLE datasets, it is impossible for me to add custom formulas to the dashboard
So the dataset below is randomly generated in a spreadsheet but you’ll get the exact same results with the Search Console connector (in our case, the data won’t update while changing the date range)

Download the Dashboard

Final Result

Before creating our dashboard, here is a quick recap about the CASE statement. The latter will allow us to filter our Branded / Non Branded keywords and to categorize our URLs.

Case statement in Data Studio

The CASE statement allows you to create new fields / metrics based on conditional expressions. More simply, it is used to create new categories or groupings of data (so not directly in your dataset but in Data Studio). For example, in Google Analytics we want to gather countries within different regions, we could use the following CASE statement: case function datastudio

Important things about the CASE statements:

In order to add CASE statements to your Data Studio you need to be able to modify the data source on which it will be added
Depending on the language used in Data Studio, you’ll need to adapt some fields. For example COUNTRY will be PAYS if you’re using Data Studio in French
If you wanna know more about CASE statements

Now that we know how to use the CASE statement, we can use it to create an advanced keyword ranking dashboard

using case statements to categorize branded / non branded queries

Categorizing your queries will allow you to:

display your Branded / Non Branded queries’ proportion

directly export in CSV those queries already filtered and don’t be limited to the 1000 queries export of the Search Console (Data Studio is using the Search Console API which allows you to remove this limitation, super useful specially for bigger websites)

easily visualize your rankings, clics, impressions and CTR evolution on those queries

Clics & Impressions numbers for Branded / Non Branded queries

Easily see your Branded queries and their metrics

In order to apply this segmentation on your queries you need to add this CASE statement in your Search Console data sources (SITE + URL), then you’ll just have to change “site”, “mysite” by your brand name and its derivatives.

CASE
WHEN REGEXP_MATCH(Query, ".*site.*|.*monsite.*") then "Marque" 
else "Hors marque" 
end

Remarks :

.* is used in the REGEX to include everything before and after
| means”or” and is used to include more words to take into account

How to add a new field in a data source in Data Studio :

Necessary steps to add a new field in a data source

Adding the CASE statement with our brand and its derivatives

case statement to categorize your url by type

Categorizing your URL by type will allow you to:

divide your site into segments (example: Product Pages, Category Pages, Author Pages, etc…)
see which segments are performing the best / worst
display clics, impressions and CTR by segment

Display your total clicks and impressions for each of your segments

Filter by type in order to display URLs and metrics of an exact segment

Adding CASE statements with your different URLs types

Previously we added a CASE statement on “Query“, here we will add it on “Landing Page“. To do so you simply need to add the following CASE statements to your Search Console URL data source and adapt the segment to your website.

CASE
When REGEXP_MATCH(Landing Page, ".*/.*page-produit.*|.*/.*product.*") then "Product Pages"
When REGEXP_MATCH(Landing Page, ".*/.*category.*|.*/.*categorie.*") then "Category Pages"
When REGEXP_MATCH(Landing Page, ".*/.*brands.*|.*/.*marque.*") then "Brand Pages"
When REGEXP_MATCH(Landing Page, ".*/.*home.*") then "Home"
else "Autre"
End

Remarks :

.* is used in the REGEX to include everything before and after
| means”or” and is used to include more words to take into account

use case

Here is an example of URL segmentation for a client in the real estate field. Those segments are written in French but we decided to display the “buying“, “renting” or “tools” segments. Moreover, we decided to do a focus on the renting / buying facets we just opened on their website. This segmentation allows us to quickly see the SEO metrics on certain pages we’re working on.

Clicks and Impressions total by segments + Focus on the renting & buying facets

wrapping it up

Thanks to the CASE statement you can easily create categories / data gathering directly in Data Studio allowing you to create an advanced keywords ranking dashboard . Pairing up the CASE statement with the Search Console connector you can track your site’s positions, clicks, impressions and CTR while filtering on your Branded / Non Branded queries and your URL segments.