Search Console: Looker Studio template to run a full analysis

Search Console: Looker Studio template to run a full analysis
Photo by Chino Rocha / Unsplash

We often get wrong when analysing lots of information from the same angle. The first data that Search Console gives falls in this case. How many charts are shared on Twitter or elsewhere where the curves are rising? A fortiori, we never know what is behind these curves.

The report I created on Looker Studio tends to run a in depth analysis of the evolution of impressions and clicks. It’s a straightforward template that retrieves data from Search Console (Site and URL) and tends to make them speak easily to highlight certain areas of analysis that deserve more investigation.

This template is all about running a “risk-free” SEO analysis so don’t expect it to be a complete Search Console template.

Two choices from there:

  • Get the template.
  • Follow the explanations (sure the best one!).

Basic stuff to keep in mind when using the template

Before using the template:

  • Make sure to create a copy of the template.
  • Select your website by using the dropdown selector.
  • Make sure to update both Segmentation and Keyword type fields.

You can sort the results by Country and Query. You can also use the Data range filter to narrow your analysis. This dashboard is built as a one page template for convenience reasons.

Calculated fields added to Search Console as a data source

To help with the analysis by focusing both on group of pages and type of keywords, I have created two calculated fields. Looker Studio gives you the ability to do so by adding a new field on top of the existing data pulled from Search Console (or any other data source actually). In below examples, I use the CASE scenario with the REGEXP_CONTAINS: you have the ability to update all of that and to use other operators if best suited.

Group keywords by number of words

WHEN REGEXP_MATCH(Query,'^[A-Za-z]+') THEN '1 word'
WHEN REGEXP_MATCH(Query,'^[A-Za-z]+\\s[A-Za-z]+') THEN '2 words'
WHEN REGEXP_MATCH(Query,'^[A-Za-z]+\\s[A-Za-z]+\\s[A-Za-z]+') THEN '3 words'
WHEN REGEXP_MATCH(Query,'[0-9+]') THEN 'Numbers'
ELSE '4 words and more'
Number of impressions by day split by n-gram

Group URLs by paths

WHEN REGEXP_MATCH(Landing Page,'|') THEN 'Homepage'
WHEN REGEXP_CONTAINS(Landing Page,'larry|sergei') THEN 'Google'
WHEN REGEXP_CONTAINS(Landing Page,'steve') THEN 'Apple'
WHEN REGEXP_CONTAINS(Landing Page,'xavier') THEN 'Free'
ELSE 'Articles and Other'
Don’t forget to update the CASE statement accordingly to your URLs to keep this graph relevant 😉

Traffic share and impression share

How to use them

This is what this template is all about. Transforming both clicks and impressions into traffic and impressions share in order to better understand the distribution of your performances.

Overview of traffic share and impression share by query.

Basically here, you can easily identify an interesting query (line 5) for whom you already generate lots of impression despite an average position of 34.68. If the keyword is relevant, then it’s worth it to work on it and try to drive traffic through it.

On the above example, the query on line 1 refers to the brand name. So an important traffic share doesn’t mean a “great performance” (except you are pushing a lot of efforts into your brand awareness). Also on this example, it means that more than 50% of all the traffic only rely on your brand keyword.

Here’s how to convert clicks and impressions into “share”:

Click on Edit field and then select Comparison calculation

Track canibalized pages

Blended data: Search Console Web & URL as datasets

To identify duplicate among pages, we use the Landing page field as a metric and then use Aggregation > Count distinct to get the number of pages ranking for this query.

Understand impressions variation

As I said above, impressions trend can say everything and nothing at the same time. Variations can depend on your business seasonality, external factors and so on. At first sight, we could distinguish brand keywords from the others (not available in this template but could be if you ask for it 🥲).

Using the number of distinct pages we rank for as well as the queries can help in understanding the variations.

Understand variations through your most important queries in terms of impressions

By editing the Impressions field, you can select Running Calculation > Running delta: it will give you the absolute difference between values day to day.

Detect anomalies by using reference lines

Reference lines are super useful especially to detect anomalies. By using Max, Average and Min, you are able to categorise your impressions’ evolution. You can also define your own value as a min, max or even average and better track what is going wrong.

You can also use the Percentile to see which keywords perform better than 90% of the keyword group.

Happy to receive your feedback and continue improving this template. Let me know your thoughts!