seo report from search console: looker studio template

s
This Search Console report helps you understand your website’s SEO performance. The ‘Search Console’ data connector in Looker Studio gives you two options to connect: 1. URL Impressions and 2. Site Impressions. You will need to create two data sources, one for ‘URL Impressions’ and the other for ‘Site Impressions’.


Important: Some calculated fields need to be customized for your website. I will explain in this post how to customize those formulae. A basic knowledge of calculated fields and CASE in Looker Studio is a pre-requisite. Scroll down to find the customization details.

If you want a custom Looker studio report built by a professional, you can hire me here.



CLICK HERE TO GET THE TEMPLATE


I) Search Intent: It categorizes the keywords based on the purpose behind the online search.

Charts on which the calculated field ‘Search intent’ is used:
Page 1 (Overview): ‘Search intent’ chart in the bottom row. Both ‘dimension’ and ‘breakdown dimension’ use the calculated field ‘Search intent’.
Page 3 (Search intent): All charts on this page

Let us first understand how this report categorizes search intent and then we will see how to customize and create different categories for your report.

For this report, I have categorized search intent into ‘hire’, ‘information’ and ‘template’. A query will fall under the ‘Hire’ category if it consists of any of the following words:
  • freelancer
  • agency
  • expert

Similarly it categorizes certain queries as ‘information’ and ‘template’ based on certain words contained in the query. Below is the CASE formula that is used in this report

CASE
WHEN REGEXP_MATCH(Query, “((?i).*freelancer).*|((?i).*agency).*|((?i).*expert).*”) then “Hire”
WHEN REGEXP_MATCH(Query, “((?i).*report).*|((?i).*dashboard).*|((?i).*template).*|((?i).*chart).*|((?i).*graph).*|((?i).*gallery).*”) then “Template”
ESE “Information”
END

The above formula looks for certain words in the query to categorize it as ‘Hire’ and ‘Template’. Everything else is categorized as ‘Information’.

Customizing the ‘Search Intent’ formula:

Let us say that you want to categorize your report into ‘Informational’, Navigational’, ‘Commercial’ and ‘Transactional’.

I will not get into much detail about what words should be considered for the above categories. You can find a table in ahref’s blog post here.

Let’s say that you want to categorize a query as ‘Transactional’ and ‘Informational’ if it has the below words in it.

Transactional: buy, order, purchase
Informational: how, what, who

The formula for this categorization will be
CASE
WHEN REGEXP_MATCH(Query, “((?i).*buy).*|((?i).*order).*|((?i).*purchase).*”) then “Transactional”
WHEN REGEXP_MATCH(Query, “((?i).*how).*|((?i).*what).*|((?i).*who).*|((?i).*chart).*”) then “Informational”
ESE “Others”
END

The above formula categorizes queries as ‘Transactional’ and ‘Informational’ based on certain words in the query. Every thing else is categorized as ‘Others’. This formula can be customized as per your requirements.

II) Branded vs Non-branded Queries: Queries that contain your brand name in them are categorized as Branded queries. When visitors are searching for your brand directly, you will see branded queries.

Charts on which the calculated field ‘Branded vs Non-branded queries’ is used:
Page 1 (Overview): ‘Branded vs Non-branded queries’ chart in the bottom row. Both ‘dimension’ and ‘breakdown dimension’ use the calculated field ‘Branded vs Non-branded queries’.
Page 4 (Branded vs Non-branded queries): All charts on this page

The below formula is used in the report for Branded vs Non-branded queries.

CASE
WHEN REGEXP_MATCH(Query, “((?i).*looker studio).*|((?i).*loker studio).*|((?i).*loooker studio).*”) then “Branded”
ELSE “Non-branded”
END

The above formula searches for the brand “looker studio”. It also takes into consideration any queries that may contain typos like ‘gogle’, ‘gooogle’. The formula can be customized to look for the brand ‘Data Sasi’ as below:

CASE
WHEN REGEXP_MATCH(Query, “((?i).*Data Sasi).*|((?i).*DataSasi).*|((?i).*Data Saasi).*”) then “Branded”
ELSE “Non-branded”
END

III) Landing Page Directory: This categorizes the Landing pages by its directory. This categorization is possible only if the Page URLs contain the directory name. For example: example.com/blog/page-title; example.com/products/page-title. ‘/blog/’ and ‘/products/’ are the page directories in the examples.

Charts on which the calculated field ‘Landing Page Directory’ is used:
Page 1 (Overview): ‘Landing pages by Directory’ chart in the bottom row. Both ‘dimension’ and ‘breakdown dimension’ use the calculated field ‘Landing Page Directory’.
Page 4 (Landing pages): All charts on this page

The below formula is used to categorize Landing Page Directories in this report:

CASE
WHEN REGEXP_MATCH(Landing Page, “((?i).*/page/).*”) then “Page”
WHEN REGEXP_MATCH(Landing Page, “((?i).*/blog/).*”) then “Blog”
ELSE “Others”
END

It categorizes the Landing pages into ‘Page’ and ‘Blog’ directories.

You can customize the formula as per your requirement. An example below:

CASE
WHEN REGEXP_MATCH(Landing Page, “((?i).*/page/).*”) then “Page”
WHEN REGEXP_MATCH(Landing Page, “((?i).*/blog/).*”) then “Blog”
WHEN REGEXP_MATCH(Landing Page, “((?i).*/products/).*”) then “Products”
WHEN REGEXP_MATCH(Landing Page, “((?i).*/about/).*”) then “About us”
WHEN REGEXP_MATCH(Landing Page, “((?i).*/location/).*”) then “Locations”
ELSE “Others”
END


I hope you found this template helpful. If you have any queries, please comment below.

About the author

sasi

I share practical knowledge that I have gained as a Google Data Studio freelancer since 2017. Subscribe for Google Data Studio Templates, Tutorial and Tips.

By sasi
I share practical knowledge that I have gained as a Google Data Studio freelancer since 2017. Subscribe for Google Data Studio Templates, Tutorial and Tips.
Sasi - DataSasi.com