SEO Reporting Template for Agencies using Google Search Console Data
Learn How to Build your Own SEO Reporting Dashboard
→ Effective “No-Cost” SEO Reporting Template for Agencies
How Does My Site Rank in Google?
As search marketers, our clients want to know how their sites are performing in Google. They want to know how often their web pages appear in Google’s organic search results and the keywords that triggered this result.
How can we provide the best view into Google’s organic traffic to our clients?
As a digital agency focused on driving organic search traffic to our B2B clients’ sites, the challenge of providing actionable dashboards in the world of “Not Provided Keyword Data” has made finding an alternate reporting platform essential.
How Do You Solve The Challenge of SEO Reporting?
After years of testing a variety of reporting platforms, testing APIs to gather the data from Google,we’ve developed a reporting template that is so simple to create that I can’t believe we kept it under wraps all this time.
Search engine optimization is the science of influencing the position that a web page receives for a search query (keyword phrase). What better way to measure that data than going directly to Google?
What is Google’s Search Console?
Google Webmaster Tools (GWMTs) – now renamed Google Search Console – provides visibility into your website’s online presence – for free!
With Google’s Search console data, you can:
- Establish a baseline before site optimization
- Monitor changes in organic traffic
- Optimize underperforming pages
- Improve the status of low-performing keywords
- Evaluate content and keyword themes that produce low CTRs
- You can also use Google’s Search Console data to make important technical decisions about your site structure and to conduct marketing analysis in combination with other Google tools such as Analytics, Trends and AdWords.
This post describes a framework for collecting, manipulating and reporting using that data.
- CLICKS: The number of times a person clicked your site’s URL in the unpaid results for a particular query.
- IMPRESSIONS: The total number of searches that returned URLs from your site for a web search.
- QUERY : The number of organic clicks your URLs received, divided by the total number searches that returned pages from your site.
- AVG. CTR : The click-through-rate measures the percentage of clicks to impressions.
- AVG. POSITION : Average top position of your listing (or multiple listings) for each query.
Why do you need this?
- 90-Days of Data – Then it Vanishes: Google gives – then it takes it away. The value of Google Webmaster Tools is that it provides you with historical data but unfortunately, the data disappears from the tool after 90 days. There is no evidence that Google will change this any time soon.
- Search Ranking Reports are a Waste of Time: Search ranking reports don’t cut it any longer for a reporting platform – and they haven’t for years. Read Jill Whalen of High Rankings excellent post on the topic.
- Too Many Tools – All Providing Different Results: There are many free and paid tools that report organic search traffic for your website. After testing many of them over the years, and seeing wide variances in the data, we opted to use Google’s own reporting platform to offer more credibility to our clients.
Creat Your Own SEO Dashboard Template
Set Up and Gain Access to Google Webmaster Tools
This post is about “reporting” using Google’s Search Console data. If your website is not taking advantage of this rich source of data, follow these setup and verification instructions to get started.
Crushing it with a Template for Actionable Reporting
- This Excel file shows a completed organic search analytics dashboard that maps to the instructions below.
- After downloading our template, log in to your Google Search Console and select the website you wish to track and keep your browser open beside the Excel file.
- Start your own Excel Dashboard and follow the instructions for replicating the downloaded Excel file.
Instructions for Excel Tab #1 | Creating the Monthly Organic Results Overview
The first tab is a month-over-month overview of the most critical data; impressions, clicks, CTR, average ranking positions, inbound links and data on selected filtered views from GWMTs.
- Navigate to Search Traffic> Search Analytics.
- Make the selections as shown in the screen capture below. The filtering capabilities are not robust but you can make up for that later by using conditional formatting.
- Your data from the view shown below can now be copied into your own Excel template. It’s a manual process of “copy and paste”.
- I typically take a screen shot for the monthly tab so that later if there is a question about data integrity, I have the screen shot to back up our reports. (time: 5 seconds)
QUICK TIP: If you move out of the Search Analytics tab within Search Console to any other section shown in the left hand column, the date range will revert to last 30 days instead of your selection. It is easier to pull all the reports from the Search Analytics section at one time before moving to other sections found in the left-hand column.
How to Track Important Types of Content or Keywords by Brand vs Non-Brand
You can use the Search Console “queries” and “pages” filters to monitor specific areas of your site, and for selected keywords types such as brand versus non-brand.
What is a brand keyword and why should I care?
When a visitor goes to Google and searches for you by your company or product names – we assume that they already know your company from your other marketing efforts. In fact, they may be customers who are using Google to locate your website rather than typing in the full URL which they may not remember. We refer to these queries as brand keyword searches.
Non-Brand are keyword searches where the visitor isn’t indicating advance knowledge of your company. It may be the first visit to your site. We separate out Brand from non-Brand because these groups indicate a different level of engagement in the sales funnel.
To search by non-brand, use the filter set to “Queries not containing” and insert you’re your company name.
How to Track Important Keyword Types for Brand vs Non-Brand
For all our clients we have additional rows in the Excel Overview worksheet that use the Queries tab as the filter, and we filter for the client’s name (or a part of it) and call that “Brand” and then run another filter excluding the client’s name and call that “Non-Brand”. It’s not perfect but it’s the closest you’ll get in GWMTs.
In our report use the Pages data to unlock the organic performance for important content sections such as blogs, resource center and/or PDF content.
In your report you will likely include sections from Products & Services or other highly relevant areas where you can use unique text from within the URL to create the filter.
To generate the data shown on rows 29 to 42 of our Dashboard Overview tab, change the toggle from “Devices” to “Pages” and filter for content you are interested in tracking over time.
GRAPHS – Make’em Once and Be Done!
- The beauty of this dashboard is the ease in creating beautiful graphs for the client that take absolutely no time to generate after initial set-up. This allows us to see – in an instant – when there are big trend changes. Clients love the graphs – especially when the data is positive!
MONTH-OVER-MONTH % CALCULATION
- Using an Excel calculation, we show month-over-month percentages changes. This makes it super easy to email clients when they organic clicks go up by 25%!
Within Search Analytics you can only compare data for the previous 90-day period but once you gather the data in Excel, you can compare much broader time periods.
Track other Available Data That Impacts Organic Search Rankings
- Inbound links are an important ranking factor and should be tracked over time as it may answer questions about why organic rankings are improving or declining over time.
To locate this data, select Search Traffic>Links to Your Site. (see screen shot)
No one but Google knows for sure whether this number is 100% perfect but we like to track the number as a way of being alerted to other site indexing problems.
- The number of domains pointing to a site is important because you could have 1,000 inbound links but that might only represent 100 domains. Google is using the quality of the domains pointing to your site as a ranking factor.
To locate the number of “domains” pointing to your site following these steps:
- Click on “Links to your Site”.
- Under “Who links the most”, click on “More »”
- See screen shot for the location of the domain count.
PAGES IN GOOGLE’S SEARCH INDEX
- that are often incomplete or out of data, Google tells us the total URLs available to appear in search results.
Google gathers this data by crawling your XML sitemap along with locating other URLs it might discover by other means. It’s an important metric to watch.
To access, select Google Index> Index Status.
It’s a good idea to take a snapshot of this result to document any big swings in data. You can see that we keep screen shots of important data stored within the monthly tabs.
Instructions for Excel Tab #2 | Creating the Monthly Worksheets
Each month we create a separate worksheet within Excel to collect and store Google’s Search Console data. In our example Excel file downloaded earlier, we are tracking all web pages, keywords and ungated PDFs, that drive organic activity during the previous month including impressions, clicks, click-through-rates and average position.
Step #1: Download the current month’s Pages and Query data and copy/paste Pages into your Excel file.
Step #2: Within this monthly Excel worksheet, use Excel’s conditional formatting to highlight important data (see screenshot below to see how to set up conditional formatting).
- Create a condition using one color to highlight a selected content type such as all blogs. Do this by filtering on a word in the file name.
- Create a condition using another color to highlight all PDFs
- Sort the data set by Impressions to see the pages that are doing well in terms of impressions but not for clicks. This provides a starting point for further SEO optimization efforts.
- Create a condition to highlight non-brand keywords. While not apparent in our agency data, what happens for large well-known companies, is that they have row upon row of searches for their name appearing at the top. The trick is to use conditional formatting to change the text color for branded phrases to a light grey color so that it becomes instantly obvious what the non-brand phrases are.
- You can run these reports with other filters. For example, you could add an additional filter for Queries to compare desktop to mobile if that was an important metric.
- The elements that you pull out of the Search Analytics data will likely change for each of your clients. Keep a separate tab in the Excel file called your Cheat Sheet to explain what you’ve done.
QUICK TIP: When it’s time to create the subsequent month’s data, copy the priior month to a new worksheet. Then when you download the new month’s data, you can follow this Excel command: Edit/Paste Special “Values” to paste the new data over the old data. In this way, you won’t have to set up conditional formatting each month.
This post focused on creating a search dashboard. But Search Console can uncover a wide variety of issues that can restrict your site’s search visibility and provide recommendations and tools to dive into those problems including:
- Duplicate content
- Duplicate or missing title and descriptions.
Indexing and XML Sitemap Issues
- Pages/URLs not being indexed
- Sitemap and Robots.txt issues
- Server problems: 404s and other types of error codes.
- Mobile usability issues.