SEO keyword audit with Google Data Studio – Free template

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Get the template here.

Like most of you, I’m a SEO guy. 

Chances are you’re kind of obsessed with tracking keywords rankings. 

You also know about the opportunity of long-tail and short-tail keywords, and why they matter, but do you know how much traffic you get from long-tail keywords, for instance? 

And also, you may have a huge list of keywords on a Google Sheet, but how do you prioritize which one you should optimize for? 

And finally, you know Keyword cannibalization matters, but how to know if you’re being affected by it? 

Now you will, with this week’s template. 

If you want to install it, follow this setup guide—most  Google Data Studio templates work the same. 

But this time you don’t need to create a copy to visualize your website’s data. 

How this template works

This time, you don’t need to create a copy. Instead, go to the Google Search Console widget in the dropdown menu and choose your website.

You should be using the Gmail or G Suite account that matches such sites. 

Long-tail vs Short tail keywords

Visualize how much organic traffic and the number of keywords your site is ranking for broken down in short-tail and long-tail keywords. 

Find keyword cannibalization

Keyword Cannibalization is that several pages in your site are competing for a keyword. 

The threat happens when the lowest-quality pages outranks the one that you were actually intended to rank. 

We use color format conditionals to expose critical keywords, where you have more than 5 pages ranking for them. It is usual for your brand name — many pages of your site will rank for them. 

Pro tip: click each keyword to visualize the pages that are ranking for it. That’s the beauty of interactive filters within visualizations. 

Spot keywords and choose between updating and creating content

We created three tables; the first one shows the keywords your site is ranking for at the first page (1-5 position); the second one from 6 to 20; and the third one for keywords where your site is ranking +20. 

If your site is showing in the 1st page of Google, it’s a win; if you’re in top 3, that’s better. 

The first table shows where you’re already winning—and what keywords you should prevent competitors to outrank you. 

Those keywords which you rank for in the 5th to 20th position are, to me, the ones you should prioritize. 

You’re already ranking for them with your current content. You can spot an opportunity when you’re ranking for a keyword which you don’t even have content specifically targeting it.  

If you’re already indirectly ranking for such keywords with an unintended page, you can update such a page by adding more content around such keywords to match the user’s intent or create a new one from scratch. Updating content is easier and faster than creating it. 

How to set up a copy of this template

Setting up this template requires a few more steps than regularly.

The reason is that we use some custom formulas that are not transferred when a copy is created.

These formulas are for calculating the length of a query to determine if it is a short tail or long tail keyword.

Once you create a copy of this report, some of the charts will break.

I will help you fix it.

First, go to edit the report.

In the menu, click on Resource – Manage Added Data Sources.

You will find two Google Search Console data sources. Use the one for URL Impressions. You can tell it is URL and not Site as the metrics name contain the word URL.

Then, you will create the following field:

Copy and paste: LENGTH(REGEXP_REPLACE(Query, “[^\t\n\f\r ]”, “”)) + 1

If you’re not familiar with Regular Expressions, this one is calculating the length of the Queries. Every time it finds a word, counts 1. Call this new field Query length.

Now, we will create a new field to determine if these queries have 3 or fewer words to define them as short-tail, and if they have more than 3 words, they will be defined them as long tail.

Copy and paste this formula:

CASE
WHEN Query length >3 THEN “Long tail”
ELSE “Short tail”
END

Finally, set up your visualizations like this:

Dimension should be keyword type; you can use any metric.

Since you’re here

At Porter, we want to help you build beautiful visualizations with Google Data Studio. 

More posts

Love Data Studio?

Download our 8-page template kit for agencies and teams—for free.