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.
We’ll build the report in Looker Studio, leveraging the Google Search Console connector, a case statement, and a data blend.
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.
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:
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.
First, add the Google Search Console data source to Looker Studio:
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:
The blend will end up looking like this:
Close the blend window.
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
At this point, you can configure the chart to your taste. A few things I like to do:
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.
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.
This month we cover the Google announcements of AI Mode and AI Max, ad placement…
Proactive monitoring with our new tool, Google's AI Mode, Looker Studio updates, and selected analytics…
Identify tracking problems with our GA Insights Monitoring Tool. Connect GA4 custom insights with a…
View Comments
Hey, thanks for this Nico - Great tutorial! I've been trying to do this with URL level Search Console data (rather than site), as I want to filter it to only show the stacked rankings for a specific group of pages rather than the whole site. Everything works fine until I want to extend the date range beyond the default 28 days, then it breaks the chart. Any ideas if this is something that is possible, and what the problem could be?
It is definitely possible. Filters, blends and long date ranges don't always play well together, but here are a couple of things to try:
1. If you are not already, try adding your filter to each data source in the blend, versus adding it to the chart.
2. If that doesn't work, try creating an Extract Data data source of the GSC data, and include the filter in the Extract Data config. Then create the blend from the Extract Data source.
Thanks for this, super helpful! One minor issue I am having. I cannot get the position groups to sort as you have in the chart. The Above 50 appears in the middle of the groups after 10-20. I have tried putting the case statement in a different order, changing the sort order on the breakdown dimension - no matter what i do the 'Above 50' always shows up in the third position. I tried changing the name to 50+ and it still is in the third position?
If you set your 'Breakdown dimension sort' to Position Group it should sort correctly, as long as you name the groups similar to what I did in the video. Let me know if that doesn't work.
Thank you! Yes, I was hasty and missed that. That does work. cheers!
I'm liking your analytics work. I built this following your instructions but my stacked area chart is mimicking the 100% stacked area chart. I'm not really sure why that's the case because switching the chart type back and forth doesn't really change anything. Do you have any recommendatins?
Sorry for not responding sooner - are you still having this issue? I've been experiencing some bugs with the Looker Studio modern charts experience - I wonder if this is one of those. I've also found that sometimes a chart just gets stuck in an uncooperative state and I have to start over to get it to work right.
I'm finding something weird going on with my table, I can't seem to get all the data for my selected date range, which is an 1 2024 to May 20, 2025. I'm only getting Feb 2024 to July 2024. Do you know what could be wrong?
Search Console only allows you to retrieve the last 16 months of data - given that January 2024 is more than 16 months ago, that's probably the issue. If you want to store data for longer, you'll need to set up the BigQuery transfer, or use a third-party tool such as Jepto.