Looker Studio SEO Report – A Free & Effective Tool to Monitor Average Keyword Positions

Table of contents

• Keyword Position Monitoring – Basics
• Benefits of Using Google Looker Studio to Monitor Keyword Position
• Why Does GSC Show the Average Keyword Position Differently Than Other Tools?
  • Hidden Clicks in Google Search Console
• What Data Should You Choose to Make Your GSC Dashboard Useful?
• How to Prepare Looker Studio Dashboard for Keyword Monitoring
  • Connecting Looker Studio with Google Search Console
  • Customizing Looker Studio Dashboard for Keyword Monitoring
  • Sifting Through the Information You Want to Monitor
• Data Visualization
• Setting Dates for Keyword Monitoring
• Adding Impression Tracking to the Dashboard
• Use the Delante-Made Looker Studio SEO Report Template to Monitor Your Keywords
• Summary – Looker Studio SEO Report Template for Wiser Decisions

You need hard data to make the right decisions.

When it comes to running a website, one of the worth-collecting items of data is the position of keywords that are meaningful in your business. Actually, it’s not the position that matters most, but rather a growing, or sometimes even underlying trend the keyword reflects (more on that later).

Naturally, you can analyze prevailing keyword trends using some of the available paid tools. But what if I told you that you can get the exact same data without spending literally anything?

Indeed, having access to such crucial information is within your reach. You can inspect the keyword trends completely for free thanks to Google Search Console and Looker Studio (formerly Google Data Studio).

Let me walk you through the basics. In a moment, you will learn how to set the Looker Studio dashboard correctly. As a bonus, you’ll receive a dashboard – a kind of Looker Studio SEO report template – that is already up and running so that you can use it anytime, anywhere.

Delante’s Looker Studio template screenshot

Naturally, if you would like to adjust the template, tweaking it a bit to suit your current business objectives better, you will be perfectly able to do so. I’ll tell you how to create a fully-customized Looker Studio dashboard, too.

What will you learn:

Keyword Position Monitoring – Basics

Keyword position monitoring is meant to identify prevailing or upcoming search trends. Understanding the changes in the way internet users search for products and services helps you verify whether the SEO activities you’re undertaking currently are likely to bear fruit.

In other words, a keyword monitoring report gives you a clear indication of your SEO strategy. This way you verify whether the path you’re following is right, or whether you should make some amendments to achieve better results.

Benefits of Using Google Looker Studio to Monitor Keyword Position

Popular tools for keyword rank monitoring happen to be a bit pricey.

Truth be told, the price isn’t even that off-putting when you realize that most of the tools offer other functions, keyword rank monitoring being just one of them. However, spending money on the whole packet with the intention of using just one function doesn’t seem to be a budget-friendly move. Just take a look at the below table: 

As you can see, there is a free solution that gives you the hard data you need to bring more organic traffic to your website.

The solution allows you to display the keyword rank and impressions (as equivalent to Search Volume) in one place – on one dashboard. Without spending anything, you maintain full control over your business data, which may be not so obvious when reaching for paid tools. Why?

If you would like to connect these two pieces of data together – keyword position and impressions – using an external tool, it will cost you an arm and a leg mostly because such a tool must be pre-customized.

You may also find some generic tools, but this solution has one big downside. When you decide to use it, you agree to give up all the data generated by Google Search Console. This option is out of the table for many companies carrying strict privacy and security policies.

Luckily, the data you need can be found in Google Search Console. The thing is that its UI has some limitations in terms of presenting data. Truth be told, there is little you can do with the obtained information. Yet, there is a way to change that.

Connecting the GSC with Looker Studio allows you to extract the very data you need. Later, upload it to the dashboard to perform a detailed and comprehensive analysis of the keyword position and impressions.

Why Does GSC Show the Average Keyword Position Differently Than Other Tools?

Google Search Console collects the data on the position a website appears in SERP, considering at the same time the elements of personalization of a particular user.

What does it mean?

Imagine that your keyphrase average position is 50, and your website appeared in SERP twice on a particular day. This means that one user may have seen your website as the first result, whereas the other user saw it as a 99 result. How is that? Because Google displays the results on the basis of the gathered elements of personalization.

Naturally, such an extreme scenario isn’t very common. On top of that, the more impressions your website receives, the smaller the chance of such a deflection. Still, it’s worth being aware of it.

Undoubtedly, Google Search Console collects data differently than other keyword position tracking tools. Still, what matters most is the general trend, not the position of a keyword on a particular day, so both GSC and paid tools allow you to observe the trend.

Hidden Clicks in Google Search Console

Before we move on to creating the dashboard in Looker Studio, there is one thing you need to realize. While checking the number of clicks, the narrower the criteria are, the fewer clicks you will see.

The easiest way to see this is by exporting the search results from Google Search Console either to Google Sheets or Excel. Do so and you will be surprised how different the results are: the total number of clicks in GSC is way higher than what Excell shows you.

How’s that?

This glaring discrepancy was first noticed by Patric Stox from Ahrefs. He analyzed almost 150,000 websites that together collected nearly 9 billion clicks. The study proved that almost 46% of all the clicks were missing. Keep this in mind and don’t be surprised to see such a discrepancy when comparing the number of clicks shown in Google Search Console and in Looker Studio.

However, we’re going to focus mainly on the average keyword position. The number of clicks or impressions is just auxiliary information, thus being of secondary importance in this case.

What Data Should You Choose to Make Your GSC Dashboard Useful?

As mentioned above, a keyword position is our main focus today, therefore the dashboard we’re going to create together will report on the weekly and monthly trends.

If you aim to analyze just several individual keywords, then it’s super simple. You just need to analyze two charts.

FIRST CHART is actually a table presenting an average keywords position (similar to a GSC report). Here we see a comparison of two periods or moments in the site’s history. However, we do not see a longer trend.

To illustrate this, we can use a line graph, where each phrase is a line in a different color. However, we can only have data on a few phrases in one graph.

It gets a bit more tricky when you intend to use the same method to analyze more phrases. Actually, it may not be as effective. Setup will take too much time, it would be hardly possible to find the right chart, and even if you do find it, the data presented will be illegible.

Your solution here is to make use of the pivot tables. They allow you to monitor a large number of keywords, filtering them by the desired criteria.

How to Prepare Looker Studio Dashboard for Keyword Monitoring

Now, that you understand why Google Search Console alone may not be the best tool to get information about the keywords trends, let’s see how you can prepare a Looker Studio report template.

In this step-by-step manual, I’m going to show you how to create such a dashboard yourself. Alternatively, you can use the already-set Looker Studio dashboard I’ve prepared for you and customize it to your needs and preferences. In a moment, you will learn how it all works.

Connecting Looker Studio with Google Search Console

The good news is that all you need is access to Google Search Console to get the report generated by Looker Studio.

STEP 1 Sign into your Google account and open Google Looker Studio. Choose Create (top left corner) and then click Report. See the screenshot below:

STEP 2 At this stage, Looker Studio will ask you to choose the data source. You can use either the connectors provided either by Google or other developers.

You can connect Looker Studio with various external tools to process and visualize the data they gather. A few examples of such tools below:

As you have probably guessed by now, for your dashboard, you need to find a connector that gathers data from Google Search Console.

STEP 3 Once you choose GSC, Looker Studio will ask you to select the sites you want to take the data from.

If you use many sites, just as I do, the list may be pretty long.

STEP 4 Select the domain you want to monitor the keyphrases for to see combobox (check box): Tables and Search types.

STEP 5 Now choose the URL and Web as a search type.

Side note: If you would like to monitor image positions, select Image, and Web will cover all text results.

 STEP 6 Click Add and you will see the basic dashboard with the number of clicks your website generated.

Ok, you’ve just completed the first stage. Now, we’re going to play a bit with customization.

Customizing Looker Studio Dashboard for Keyword Monitoring

FIRSTLY, you need to select the preferred data view.

In this case, we’re going to use a pivot table. This function allows you to summarize and compare big chunks of data in one place. Even though creating a pivot table requires taking a few steps, once you do this, you will be able to comprehend the data quicker and analyze it in detail.

Click Table. From the menu, choose Pivot table.

SECONDLY, you need to choose the data you want to get exported to the pivot table.

In this case, which is keyword monitoring, the measurements (i.e. row and column dimension) are Query and Date, and the metric is Average Position.

A USEFUL TIP: If you feel like comparing and analyzing other types of data, you can do this at this very stage. Just remember that metrics must be expressed in digits, but for the measurements, you can use words. The rest is entirely up to you.

In this simple way, you created a pivot table presenting the average position of a specific keyword on a specific day. This, in turn, facilitates introducing other limitations for authoritative and more effective keyword monitoring.

Sifting Through the Information You Want to Monitor

When you gather all the information in one place, you need to sift out what isn’t relevant to you. Here’s how you ask Looker Studio to pick only the information concerning the keyword positions.

It can’t be denied, the larger your website is the more keywords to find in Google Search Console. This means that calling certain keywords out every time you want to check their rank would be pretty mundane and tedious – simply inefficient.

Fortunately, Looker Studio allows you to select only the very keywords you wish to keep monitoring. You can do it in two ways:

• make use of Advanced filters which, from what I observed, is more convenient provided that you want to monitor literally just several keywords (e.g. keywords used for optimizing a single landing page), or
• through the Regular expression, also known as RegExp, for monitoring a group of keywords taken from various landing pages.

Let’s go through the first option, which is

OPTION 1: Keyword Filtering Through Advanced filters

Using a given filter, you sift through the keywords you intend to check. When it comes to keyword monitoring, you may use Query which allows you to choose either a specific phrase or all phrases containing a given keyword. You can also have a go at Landing page to monitor all the phrases that call out a chosen landing page.

HOW TO ADD ADVANCED FILTERS

Look at the top navigation bar, click Add a control, and then Advanced filter.

Then select a filter of your choice (e.g. the mentioned Query or Landing page) and insert a value (Query – a given keyword; Landing page – a URL to the landing page).

As you can see, this way the table you set before shows only the filtered data.

Take for example the below screenshot: it includes only the keywords connected with the landing page introduced to the filter.

This method is convenient in two cases:

1. to check the position of your landing pages (you set the filter just once and then just change the URL)
2. if you want to have a ready-made dashboard, where you introduce a keyword every time you want to check its position

What should you do if you need to monitor 40 – or more! – keywords, and you want them to be displayed right after opening the dashboard? In such a case, Regular expression comes in handy.

OPTION 2: Keyword Filtering Through Regular expression

Indeed, there is no better way to monitor a fixed group of keywords than using the Regular expression filter.

A SIDE NOTE: Regular expression applies to a number of characters arranged in a certain order that defines a search pattern. You may have used it in MS Office or Google Docs when you wanted to replace a repetitive word or phrase through Find and replace.

HOW TO ADD REGULAR EXPRESSION

In Excel or Sheets, prepare a list of the keywords you want to create the regular expression with. To do so, I suggest using the TEXTJOIN function. Fill it as shown below, selecting the keywords you want to insert in the phrase:

(the range includes the cells containing the keywords)

Here’s an example (Google Sheets):

This way you generate a list of keywords, each one separated with $|^ characters.

To finish creating the Regular expression, place

• ^ – right before the first keyword, and
• $ – after the last keyword.

You have to do this manually. The string should look like this:

^KEYWORD 1$|^KEYWORD 2$|^KEYWORD 3$|^KEYWORD 4$|^KEYWORD 5$|^KEYWORD 6$|^KEYWORD 7$|^KEYWORD 8$|^KEYWORD 9$|^KEYWORD 10$|^KEYWORD 11$|^KEYWORD 12$

To make it even easier, we prepared a template you can use to create a RegExp by only copy-pasting the keywords you want to monitor – you can find it here.

Your RegExp is ready. Now you can move back to Looker Studio. This time, look to the right to find the menu Setup. Then select Add a filter in the Pivot table filter.

Name the filter the way you want (note: use some meaningful name so that it still makes sense to you in a few months). Later, from the menu choose Include, Query, RegExpMatch, and insert the prepared regular expression. Here is what it may look like:

Click Save. Once saved, your filter will generate the keywords you’ve chosen. They will be listed in a pivot table.

Data Visualization

At this stage, you have your pivot table up and running. It shows you the average positions of the chosen keywords. Other good news is that there are only two steps left to create a more descriptive and convenient-to-use dashboard, and that is:

• graphical representation of the data and
• defining the period you want to cover in your analysis

Starting with the graphical representation of data will make more sense simply because later you will just copy and paste the chart with the predefined data visualization style. The only variable is the period you want to take into consideration while monitoring keyword position.

Although the data is presented in a pretty legible way, analyzing the increase or drop may occupy too much time. You will need to compare the numbers on your own, which is impossible to do by just glancing at the long rows and columns that seem to look identical.

To make reading the numbers easier, you may try using colors to mark which keyword ranks higher, and which keyword ranks lower. Assigning a particular color to a keyword position will make analysis way quicker.

Here is how to do it:

STEP 1 From the right sidebar Chart choose Style. In the Conditional formatting section, click Add. This way you call out the menu that allows you to add colors and styles to the cells.

STEP 2 Naturally, pick the colors at your will. It’s important though to select Color scale because this allows you to adjust the color saturation scheme to represent the keyword position. Additionally, you need to define the values so that the color is more or less vivid. There is a 5-step saturation scheme that you can adjust as in the example below.

Once you have done this, your table will look similar to this one:

Assigning colors to particular values makes it easier for you to distinguish the keywords that made it to the top 10, from those that occupy lower positions, and finally from the keyphrases that are way below the top 50.

At this stage the table shows the average daily position of a keyword, meaning the changes that you spot won’t be dramatic. To make more sense out of such data visualization, it’s way better to create pivot tables showing the records gathered on a weekly or monthly basis.

Average daily position may be useful to check which keywords may have caused a sudden increase or drop in your website visibility. This may happen after introducing some big change on a website, or after a Google algorithm update.

Setting Dates for Keyword Monitoring

Your pivot table is ready. Now you need to prepare alternative versions to monitor the long-term performance of the chosen keywords.

My suggestion for you is to prepare 3 versions of the same pivot table, showing the daily, weekly, and monthly keyword positions. This way you will be able to quickly switch between each report, and dig deeper in case of some fuzziness.

Naturally, you don’t have to create each of the tables separately from scratch. Instead, do the following:

STEP 1 Find Report pages on the right sidebar and click Duplicate. This way you duplicate the table together with all the data it contains.

STEP 2 Now, define the period you want to look at. To do so, open the table’s Setup and check Date.

STEP 3 Select the Date & Time. From the drop-down menu, click ISO Year Week.

STEP 4 Then, in the Date section, select Ascending to show the results arranged from the oldest dates to the most recent.

STEP 5 In the Default date range choose Custom and open the menu by clicking on the arrow.

STEP 6 Naturally, you can select the fixed dates but this won’t make much sense simply because that would require you to change them every time you want to generate the current data. It’s way better to choose the Advanced option and set the data there.

I’d suggest typing minus 6 weeks for the Start Date, and minus 1 for the End Date. Why minus 1? To see the data collected throughout whole weeks, not just three days in case you use the table on Wednesday, for example.

By doing so, you will always see the data taken from the last five full weeks.

The above table shows how easy it is to monitor a keyword trend because it gives you the data collected throughout a longer period. Simply put, if you notice a keyword going either upwards or downwards in ranks, then you see a trend.

The process of preparing a visualization of data gathered throughout a month is almost identical.

STEP 1 First, duplicate the table and then go to Setup and open Date. This time, instead of ISO Year Week, select Year Month.

STEP 2 Later, move to Advanced and select minus 7 and minus 1 for the Start Date and End Date respectively. This way you will see the average position of a keyword measured out on a basis of the last full 6 months.

In this simple way, you’ve just prepared 3 dashboards showing you up-to-date information on keyword position on a daily, weekly, and monthly basis.

Naturally, most people will find this completely satisfactory. Yet, we can go a step further.

Adding Impression Tracking to the Dashboard

Keyword position is just one element that needs to be monitored regularly. Another worth-tracking piece of information is the number of impressions your website generated. Having those two statistics at your disposal, you will be able to draw better conclusions.

Naturally, Looker Studio is the perfect tool to enable you to check the impression rate, and there are two reasons for that. Firstly, it’s free, and secondly, you keep your data private, meaning that you don’t have to share your data with any of the third-party tool owners.

Here is how you can add the information on the impressions (or e.g. click-throughs) to the dashboards.

STEP 1 On the right sidebar, in the Metric section, add the data you want to get a visual representation of. In this case, you need to choose Impressions.

STEP 2 Now use the colors the same way you did it to mark the average keyword position. Naturally, I suggest choosing a new set of colors to avoid confusion.

Instead of matching fixed values with colors, this time you may go for the percentages. When measuring impressions, all we care for is either the increase or decrease, not the positions as it was with the keywords, therefore percentages will fit the purpose perfectly.

In this simple way, you create a pivot table that gives you enough data to draw conclusions from. Taking a quick look at the table, you will see the impression rate and average keyword positions, colored in a way that gives you a clear signal of what you should do with your SEO strategy.

On top of that, your dashboard gives you the possibility to switch between three tabs, showing you the daily, weekly, and monthly results to analyze the keywords.

Use the Delante-Made Looker Studio SEO Report Template to Monitor Your Keywords

No Google Looker Studio guide is complete without an SEO report template, so I prepared one for you, too.

After clicking on the link, you will be taken to Looker Studio where you can get the template from. The thing is that you won’t be able to edit it until you do one simple thing.

Click on the three dots, which you can find in the top right corner, and choose Make a copy.

This way you copy the dashboard to your Looker Studio, gaining editing rights.

One more thing, since you won’t have access to our Google Search Console, Looker Studio will ask you to choose the site you already have the access to. Just use the domain you want to monitor, and that’s it. You have your own Looker Studio dashboard template.

 Now, all you need to do is use the Regular expression or filters to define the keywords you want to monitor. All the rest is already set by me, and fully ready to use for you.

Summary – Looker Studio SEO Report Template for Wiser Decisions

Evaluating your website’s SEO strength without reliable data is impossible. You need to obtain clear information about the key performance indicators on a regular basis. Otherwise, you won’t be able to make sensible decisions to move your business forward toward a brighter future. Neither will you be able to clearly understand your SEO standing.

This is why you need to keep monitoring the average position of the keywords your website is optimized for. Spotting some regularities and trends soon enough may give you a leg up, and help you make the right strategic decisions quicker, way before your competitors do. Combine it with the information on impressions, and you have a clear indicator of whether your current SEO strategy is effective or if it needs some modifications.

Lucky for us, there are some clever tools to collect these crucial pieces of information on our website – and our – performance. In case you don’t feel like paying full price for a tool to use just one feature from the dozens it offers, simply connect your Google Search Console with Looker Studio. This way you create a tool that:

• monitors average keyword position
• tracks impressions
• is customizable, thus convenient to use
• keeps your data safe
• is completely free

I tested it myself, and therefore I know it works wonderfully.

Go ahead and download the Looker Studio SEO report template, and use it to your advantage. Keep monitoring your website performance to base your decisions not on gut feeling but on hard data.