How to count unique keywords in top X in Data Studio

Tracking the number of unique keyword rankings in the top X is an apt reflection of your clients’ organic growth and search visibility. Especially when plotted periodically you can observe the increase (I sure hope not a decrease) of the number of top X rankings over time.

It makes only sense that the clients who want a performance dashboard usually ask:

‘Can we track the instances of top 3 / 10 / 20 rankings in our dashboard for a given period of time? ‘

As some of you are familiar with; Google’s Data Studio is notorious for being everything but lenient in the ways to realize your desired charts or visualisations. It is, although with some work arounds, definitely possible.

In this article I give you the solution to achieving this in Data Studio through a clear step by step approach. With loads of screenshots. Ain’t that fun.

Also, you do not need any extra plugins.

At the end of this article, you can build static scorecards as well as the periodic time series and bar charts:

Navigate to:

1. Why can’t we just count the unique number of queries in the top X?
2. Step 1: If statements in calculated fields for different top X rankings
3. Step 2: Blend data source with the same data source
4. Step 3: Make scorecard for Top 3 and 20+
5. Step 4: Make scorecard for Top 10 & Top 20
6. Step 5: Time series chart unique keyword instances top 10 (#1 – 10)
7. Step 6: Bar stacked column chart

Why can’t we just count the unique number of queries in the top X?

Unfortunately, counting the unique number of keywords for a particular ranking range in the SERP and a specific period of time requires you to aggregate the metric ‘average position’ in Search Console. As with most data source in Data Studio, the metrics data is aggregated automatically upon collection and processing.

This means that you cannot perform calculations in calculated fields on metrics with formulas that aggregate that metric.

Data Studio calls this re-aggregating.

For example, you cannot perform the following calculation:

When you edit the data source Search Console, you will see blue fields (metrics). You can see default aggregation is set to AUT, which means the metrics data is aggregated automatically.

In order to count the unique query instances that rank in the top X, we need to take a few steps.

Step 1: If statements in calculated fields for different top X rankings

We need if statements that state the condition under which we count the distinct instances of keywords for a particular SERP range.

Let’s follow the setup as illustrated in the header image and include 4 type of ranges: top 3, 10, 20 and 20+ (rest).

Go to Resource > Manage added data resources > Search Console Site and click ‘Edit‘

Next, in the upper right corner, select ‘Add a field’

Next, we formulate the if statement for the first SERP range rankings. Let’s do the top 3.

Write down the following code:

If(Average Position<=3, COUNT_DISTINCT(Query),0)

This formula says: if the average position is equal to or smaller than 3, then count the unique number of the value ‘query’.

The aggregation applies to the dimension ‘query’, which is not a metric and can be aggregated.

Give the Field a name, such as ‘Unique keywords Top 3’.

Save the formula.

Now repeat this step for the other 3 SERP ranges: top 10, top 20 and 20+.

I know you’re lazy, so, here you go:

• Top 10: If(Average Position<=10, COUNT_DISTINCT(Query),0)
• Top 20: If(Average Position<=20, COUNT_DISTINCT(Query),0)
• 20+: If(Average Position >20, COUNT_DISTINCT(Query),0)

Step 2: blend data source with the same data source

Say what now? Can’t we just make a scorecard of the new metric and be done?

Unfortunately not.

Let’s say you create a scorecard with metric ‘unique keywords top 3’, this is what you’ll see:

This happens because Data Studio can’t aggregate data that has already been aggregated automatically. The custom metrics are set to ‘Auto’.

To better understand, let’s see what happens if we make a table with dimension ‘query’ and our newly made metric ‘unique keywords top 3’.

The table lists all queries and returns a 1 if it matches the condition in the if statement. Well, as the image shows, we at least have 6 instances, right?

However, the Grand total row is 0.

The table works fine if you don’t need to aggregate the data. But, we want to have to total count of unique keywords in the top 3.

And you might have guessed it: Data Studio cannot aggregate the data, hence the summary row returns a big fat 0.

The workaround to bypass the auto-aggregation of Data Studio is by blending the Search Console – Site data source. By data blending the auto-aggregation is no longer in effect.

First, select your scorecard (that one with N/A).

Then, in the right upper corner, click ‘Blend data‘.

Add a table and select the same Search Console – Site data source.

In the Left Table, add your custom made metrics.

Select as join key: Query

Leave other fields empty.

Save the Data blend with a distinctive name, for example: Keyword Count Blent – Scorecards.

Step 3: Make scorecard for Top 3 and 20+ positions

Let’s first make the scorecard for the Top 3.

Make sure you have only selected the metric ‘unique keywords top 3’ in your scorecard under the tab ‘data’. You’re scorecard should now include a number:

We can do the same for the metric ‘positions 20+’.

Simply duplicate the scorecard and replace the metric by the correct one.

Step 4: Make scorecard for Top 10 & Top 20

The logic for the middle 2 metrics is slightly different.

You can’t just repeat the same as what we did in the previous step. Namely, to get positions 4 to 10, we need to exclude all top 3 rankings. We can easily do this through a simply subtraction.

First, create a scorecard and select as data source the Data blend you created in step 3.

Then click on the metric and click ‘Create Field‘ at the bottom

Then, write down the following formula:

This formula does nothing more than subtracting the number of unique query instances that rank in the top 3 from the total number of unique query instances that rank in the top 10. That leaves you with the unique instances of rankings for positions 4 to 10.

This number will most likely (but doesn’t have to) be larger in count than the instances of top 3 unique keywords.

The same logic applies to Top 20 (#11 – 20). The formula is as follows:

• Unique keywords top 20 – Unique keywords top 10

If you have done this all correctly, it should look something like this (I added some paragraph boxes and lines)

Step 5: Create time series chart of unique keyword instances in top 10 (#1 – 10)

Almost there.

Add a chart and select smoothed-line time series.

If the blended data source is the last data source you’ve worked with, you’ll get the following error:

Remember that our data blend had no specific dimensions appointed? A time series chart requires the ‘date’ dimension. So in order to create a time series chart, we need to add the date as a dimension to the blended data source.

But here is the catch.

You cannot use your previous data blend as data source.

For illustrative purposes, let’s do that anyways.

Note: the italic text indicates what not to do.

Edit your existing data blend by clicking the pencil under the tab ‘Data’.

Next, add ‘Date’ as dimension in the left Table. Leave the right as it is.

What you will notice is that all your scorecard values will recalibrate and suddenly be significantly larger

This is happens when you add ‘Date’ as dimension to non-time serie charts, tables or scorecards that are built with blended data sources.

To get a properly functioning time series line chart, without braking the scorecards, you need to create a second data blend.

1. First, select your time series chart (that one that gives the chart configuration incomplete error)
2. Remove the old data source by clicking the cross
3. Add new data source: add your Search Console – Site
4. Click ‘Blend data’
5. Add again the same Search Console – Site as second data source.
6. This time, add ‘Date’ as dimension to the left Table.
7. Select custom metrics ‘top 3‘, ‘top 10‘ and ‘Top 20’
8. Save your data blend and give it a name.

It should look like this:

The line series chart is almost finished. We just need to configure the settings:

• Under tab ‘Data’, make sure ‘Breakdown dimension‘ is empty
• Set ‘default date range’ to custom.
  • Select from the dropdown ‘Advanced’. Configure as follows to compare last 60 days with the previous 60 days.

• Set comparison date range to ‘previous period’ or ‘previous year’
• Under tab ‘Style’, make sure these boxes are ticked:
  • Show axes
  • Show axis title (Y-axis)
  • Show points
• Adjust colors to your liking

Then it should look something like this:

Step 6: Bar stacked column chart

Before we can create a stacked column chart, we first need to add a custom dimension to create week numbers.

Go to Resource > Manage added data resources > Search Console Site and click ‘Edit‘

Next, in the upper right corner, select ‘Add a field’

Write down the following code:

Week(Date)

Hit save and go back to the dashboard

Go to ‘Add a chart’ and choose ‘stacked column chart‘ in the category ‘bar charts’

First, add the custom metric ‘Unique keywords top 3’

For the range #4 -10 (top 10) and #11 -20 (top 20), we need to make a custom field, just like in step 4.

To create the metric for the top 10, click ‘Create Field‘ at the bottom

Then, write down the following formula:

Repeat this for the top 20.

You now should have 3 metrics of which 2 are custom fields.

Next configure the settings as follows:

You can set the date range to whatever range you prefer. Just like the colour settings: whatever floats your or your clients’ boat.

Good luck and enjoy!

I am happy to hear your thoughts!

Author