How to Build a Search Engine Ranking Report in Looker Studio

What This Report Does

This report gives an aggregate view of how your website is ranking in search engines, by showing how many keywords the website is ranking for in different position ranges on Google Search. You’ll get a great visualization of current and historic ranking using free tools.

How We’ll Build It

We’ll build the report in Looker Studio, leveraging the Google Search Console connector, a case statement, and a data blend.

How It’s Helpful

Website managers and SEOs follow search engine rankings over time to monitor visibility increases or identify problems. Seeing the ranking distribution for all your keywords, over time, gives you an indication of the strength of your website in the eyes of search engines. Both Google Search Console and Looker Studio are free tools, so you can create a report without any new subscriptions.

The finished chart

Why tracking individual keyword rankings doesn’t work

I think a chart showing number of keywords ranked, bucketed by ranking, is a much more useful visualization than tracking individual keyword rankings. Sure, businesses often want to track the performance of a short list of specific keywords relevant to the products or services that the business sells. But here’s why that doesn’t work:

• It is well established that the vast majority of search activity is comprised on long tail queries that will never appear on a small list of curated keywords.
• Newer search/AI technologies like Google’s AI Mode actually review multiple queries when generating answers and links in order to better understand the topic, as the goal of these tools continues to move toward showing the best possible result given the unique context and intent of the search. These evolving search engines are attempting to evaluate the purpose and context of your content more holistically, rather than trying to match individual searches to content that targets specific keywords.

Why tracking total number of queries is better

Understanding the total number of keywords you are ranking for is a much better indication of how your content efforts are paying off, because it acts as a leading indicator.

After looking at these charts for a number of clients, I’ve observed that Google doesn’t tend to rank new content in top positions. It is much more likely that you will first show up with an average position in the ‘Above 50’ range. Because listings that far down the results rarely get clicks, you will see the ‘Above 50’ band in this chart start to grow well before you see an impact in traffic to your site.

Conversely, if you see the number of queries you are ranking for start to shrink, it’s a warning that you will start losing traffic if you don’t turn things around.

Watch this video walkthrough or continue scrolling to follow the written steps.

Step 1: Add Your Google Search Console Data Source

First, add the Google Search Console data source to Looker Studio:

1. Go to Resource > Manage Added Data Sources.
2. Click ‘Add a Data Source’. Scroll down and select Search Console.
3. Search for your site and select the table ‘Site Impression’ and Search type ‘web’.
4. Add the data source to your report.

Step 2: Create a Data Blend

This step is kind of weird, but the case statement we will be using in Step 3 to group queries into position ranges doesn’t work if you try to apply it directly to the Search Console data source. The reason is that a case statement can’t take metrics as arguments and return a dimension value. When you create the data blend, the Average Position metric will convert to a dimension and the case statement will work.

Learn more about using Blends in Looker Studio.

To create the blend:

1. Go to Resource > Manage Blends.
2. Add a new blend:
   • Select your Search Console data source.
   • Add Date and Query as dimensions. Don’t add any metrics.
3. Click ‘Join another table’ and add the same data source again:
   • Add Date and Query as dimensions. (If there are other Dimensions preloaded, like Google Property, remove them.)
   • Add Average Position as a metric.
   • Click ‘Configure join’ and select Left outer and match Query to Query and Date to Date.
4. Name this blended data source ‘GSC Self Blend’ and save.

The blend will end up looking like this:

Close the blend window.

Step 3: Create a Stacked Area Chart

1. Insert a new Stacked Area Chart.
2. Set the data source to ‘GSC Self Blend’.
3. If it didn’t populate automatically, set the Dimension to ‘Date’.
4. Under ‘Breakown Dimension’, add a new field named Position Group and set the formula to the following case statement:
   CASE
WHEN Average Position <= 10 THEN "0-10"
WHEN Average Position <= 20 THEN "10-20"
WHEN Average Position <= 30 THEN "20-30"
WHEN Average Position <= 40 THEN "30-40"
WHEN Average Position <= 50 THEN "40-50"
ELSE "Above 50"
END
5. Set the metric to ‘Query’. It will automatically select ‘CTD’ as the aggregation type, which will produce a count of unique query values.

Step 4: Refine the Chart

At this point, you can configure the chart to your taste. A few things I like to do:

1. Set the default date range for the chart to 12 months. The default ‘last 30 days’ isn’t enough to see significant changes in Google.
2. Under Dimension, enable Drill down, then add the ‘Date’ dimension again, and change the Data type to Date & Time > Year Month. Then set the default drill down level to Year Month.

Bonus Step 5: Add a Control to Filter by Query

To get even more value out of this report, Two Octobers’ Sr. SEO Brett Woodward suggests adding a way to filter the chart, so that you can see the trended ranking distribution for a single keyword or group of keywords. This is helpful when you want to dig into the ranking trends for a specific set of keywords, like for one of your product lines or blog categories.

• Add an “Advanced filter” control.
• The control should be automatically configured with the Control field being “Query”.

When using this filter on the chart, the “Contains” selection is particularly useful. It will aggregate data about any search queries including the word(s) you enter.