On SEMRush, there is a graph that makes all customers agree and leads everyone to take out a subscription. I am of course talking about the analysis of the number of keywords per position interval over time!
Yes, you do know it, that’s the one I’m talking about:
The challenge is to recreate it on Google Data Studio with Google Search Console as data source.
I love doing that!
Expected result
We have to set-up:
- A breakdown of the number of keywords by position range
- Selectors to show or hide the position interval
Here I am using a personal data source on BigQuery but you can do this with the native Search Console connector (the “Site” view) with less good loading performance.
We will look at the following concepts which are quite simple:
- Parameter
- IF() function
And yes, we will not use a CASE function!
Configure 1 parameter for each interval
The first step is to set up a parameter per desired position interval.
In my example I take: “Top 3” / “4-10” / “11-20” / “21-50” / “51-100
The parameter is “Boolean” and is configured as follows for each interval.
On the dashboard, you will add each parameter to a “Checkbox” command:
Remember to name your parameter correctly as this is what will appear on the front end:
Create the position interval
Let’s move on to the graph setup.
The idea is to create a metric for each interval that counts the number of queries under 3 conditions:
- Corresponding parameter = true
- Position > Minimum of the interval
- Position < Maximum of the interval
We therefore use the IF() function to create each of the metrics:
IF(Top-3=true and average_position>=1 and average_position<4,query,null)Configure the chart
For our graph, we use the “stacked combined graph” type
For each interval and according to the conditions seen previously, we will create a metric with the IF() function.
In the statistical part we get this:
In dimension, we add the dimension of Date to the format Month of the year and we configure the sorting of the graph by this dimension in an “ascending” way
For the style, no particular guideline, I kept the same colours as the default theme of Data Studio.
Test your widget
By clicking on either of the interval selectors, you can show or hide the desired intervals. This allows you to focus on the evolution of a particular interval, just like on SEMRush 🙂
On my example, I added a Date Selector to let the user choose the period to analyse and also a Date Granularity Selector (also done with a parameter!)
