By Alice Roussel in Data — Jul 2, 2021

Search Console: Looker Studio template to run a full analysis

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!).

Get the Looker Studio template

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

CASE
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'
END

Number of impressions by day split by n-gram

Group URLs by paths

CASE
WHEN REGEXP_MATCH(Landing Page,'https://example.com|https://example.com/') 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'
END

Don’t forget to update the CASE statement accordingly to your URLs to keep this graph relevant 😉

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.