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.
