Using Google Data Studio [template included]
Whenever I do my in-depth keyword research to highlight the next quarter's focus keywords, I always have this feeling I'm missing something.
Analytics and tracking.
Maybe that's because I'm a growth and digital marketer by trait, I always feel the need to track and measure everything.
But that's the thing, why should SEO (search engine optimisation) be any different? You spend hours researching the top keywords to focus on and create content around.
And for what?
Me building the SEO report...yes I need 5 laptops ;)
We need to be able to see, clearly, how this work pays off.
You: "But Rupert, we already have Google Search Console. What more do you need?"
Sure, Google Search Console (GSC) is great. We can see impressions, clicks, click through rates and average position data in the past 16 months (as of recently it was a 3 month look back window)!
The issue I had with this is that it's hard to see real trends as the data is sorted only by day. So you can't sort for weekly, monthly or yearly trends. And if you work for a B2B business, you'll get serious weekend drop-offs like below.
3 months of Google Search Console reporting
I tried to work with it and spent an inordinate amount of time exporting individual keyword data sets and adding them to a single graph to get a wholesome view on our SEO.
I tried. Got fed up. It was awful.
Too much work little payoff
You (again): "But Rupert, why build your own when there are soooooo many SEO-focused businesses out there with awesome products? Like Ahrefs, Moz, SEMrush, SEOClarity to name a few."
Sure thing.
If you have the cash to spend, go for it. But if you're like many early starters, you need to hack a few things together until you have that cash to burn on a full suite product.
So I decided to build a workaround.
A step-by-step guide to building your SEO report
I'm not sure about you, but I'm a visual person.
I want to be able to open up a report and in a split second understand the SEO performance of my focus keywords. A dashboard that shows the overall health of my keywords and much more.
Below you will find the steps I took to build that dashboard, that has now become the go-to SEO report for our growing team of 20 marketers.
And just to reiterate for the umpteenth time. IT'S FREE!
Step 1. Google Data Studio
First step to building your saucy SEO report is to connect all the right data sources to Google Data Studio.
You will want to connect your Google Analytics and Google Searcon Console data sources to the same data studio project. Don't worry it's easy.
At this stage you're going to want to connect both "Site Impression" and "URL Impression" tables from the search console data source as they provide equally relevant data points.
Step 2. Report Structure
The most logical way for me to structure the report is as follows.
Page 1 - overview page on top performing keywords/queries
Page 2 - seo focus keyword 1
Page 3 - seo focus keyword 2
Page 4 - seo focus keyword 3
Simple. Saucy
Now we have the three data sources connected, it's time to pull some data through.
The first page gives you an overall picture of which keywords are ranking highest, which are driving the most impressions and which are driving the most clicks to your site.
Simply, create a table, pull in query data from search console and add in the metrics you want to see, i.e. clicks, impressions, average position. Be sure to compare these data points by clicking compare to the previous period.
High level. But useful
On my first page, I have three of the same tables just sorted differently:
by average position and impression - wanted to know which keywords were ranked number 1 and how many impressions they were driving
by impression - wanted to know the most visible keywords
by clicks - wanted to know which keywords are driving most clicks
These 3 tables should give you enough of a high-level feel of how your keywords are doing.
Step 3. Deep dive your focus keywords
These are the best pages. It's important to get them right. They are the heart of the report.
They're also the most fun to create.
Before we jump into it, you must ask yourself what you are trying to find out. Which questions are you trying to answer? And how should I present the data to do so?
I came up with these four:
What is the average position over time for the focus keyword?
How much traffic is the keyword driving to our site?
What types of content are people interested in around this keyword?
What are the opportunities for growing traffic in this area even more?
These questions helped guide the layout of the deep dive pages.
Your saucy SEO report that's quick and easy
With this report, the aim was to answer the above questions:
1. What is the average position over time for the focus keyword?
Over the 3 month period, you can see the average position has been improving ever so slightly. The positioning has decreased by ~8% to 5.9%. NB: down is a good thing - down means closer to #1, aka ranked first.
Data source used: Google Search Console
Filter created: "query" contains [enter focus keyword]
2. How much traffic is the keyword driving to our site?
In the same 3 month period, traffic had increased, plateaued and now decreasing. However, the visualisation could be due to the date layout of YYYww.
Meaning, the days are now bundled into weeks and can potentially cutoff some days. That said, we've had +65k unique pageviews to the site from our focus keywords (woop woop).
Data source used: Google Analytics
Filter created: "landing page" contains [specific url]
3. What types of content are people interested in around this keyword?
So I've blanked this out, for privacy reasons, but what you'll see is a big list of all our highest clicked-on landing pages.
I.e. the ones driving the most amount of traffic. Good thing to understand is why are they clicking on these landing pages? Is it the headline? Does that specific landing page tap into a key question your audience is looking for?
Data source used: Google Search Console
Filter created: "query" contains [enter focus keyword]
4. What are the opportunities for growing traffic in this area even more?
This is a great table to create. I pulled in through search console the query data and showed only the keywords ranking between 4-20, based on the seed keyword.
Data source used: Google Search Console
Filter 1 created: showed queries with average positions between 4-20 (i.e. keywords that have room for improvement).
Filter 2 created: query containing [enter focus keyword]
Step 4. put it all together
Now you've got your front page SEO overview and your deep-dive focus keyword pages, put it all together and you have yourself a fully fledged SEO report.
Which doesn't cost $100+ per month. Again, IT'S FREE!
Conclusion
In this essay, we covered a lot.
Talked about the limitations of using platform-only data (using search console only to see performance).
How to connect different data sources into the same data studio sheet.
How to give a good overview of SEO health.
And how to setup deep-dive pages on your focus keywords.
What we didn't cover was design and layout of your reports. Because that's up to you. You have the tools now.
Go create your FREE SEO report and get that next promotion because your boss will notice you just saved them at least $1,200 a year.
Template Giveaway
Best thing to do is connect with me on LinkedIn (I'm currently accepting all connections) and ask for the template there.
.....
As always with my essays, I try to help others through my own learnings. If you got something useful out of my musings then please let me know either with a comment, share or even subscribe on my blog if you're feeling quirky.
I really appreciate it!
You can find more of my writing about growth, strategy and user acquisition on my site Bright Fund. Alternatively you can find my Medium publication, Bright Fund and give it a 👏.