Year-over-year (YOY) comparisons are a great way to see recurring changes in traffic correlated to certain times of year, but if the analytics data comes up short of a year, it’s game over.
This article will walk you through how to use Google Webmaster Tool search queries to supplement date comparison analytics reports. The end result will be the ability to understand if a rise in site visits is due to higher search volume or a bump in ranking position.
Download A Weeks Worth of Google Webmaster Tool Data
It’s important to remember the main limitation of what we have available in Google Webmaster Tools. We’re limited to top 2,000 keywords a day, if your site has more than this try verifying as many subfolders as possible. URLs that are structured like a subfolder will work.
- Create a new workbook to consolidate data and note some numbers as we go.
Create two tabs, name the left-most tab Dashboard and the right-most tab Raw Data.
In the dashboard, create these column and row headers.
- To get all data available, download full reports per day.
From Google Webmaster Tool Site Dashboard -> Search Traffic -> Search Queries.
1. Begin by setting the dates to the first day of your analysis, in this case it’s March 1.
2-3. Change rows from default 25 to 10.
4. Take the total number of keywords and insert into the grid.s parameter. Note this number in the Excel Dashboard tab. It’s also possible to just switch to 500 rows here, but in case you have more this is a catch all solution.
5. Download this table to CSV.
6. Repeat previous steps for each day for the first week of March (or your custom date range).
Consolidate Data Into Single Workbook
At this point we should have seven files of Google Webmaster Tool Search Query data and our Number of Keywords column filled out in the Dashboard tab of our Excel spreadsheet.
- Consolidate all Google Webmaster Tool data into the Raw Data tab and add the applicable date to each row (the default GWT download has the dates).
- Finish filling out the Dashboard tab by filtering the Raw Data tab by date and using the simple =SUM formula to add up Impressions and Clicks.
- Once data is filled out highlight, then choose a standard line graph to see trended Google Webmaster Tool search query data.
Tip: Use this as a starting point to find out why, even though there was demand and Google thought your site was relevant enough to have your site presented in search results, there was no correlation in clicks. For example, find out which keywords of the 428 on March 6 didn’t show up on March 7 (above). Look at the search results to see what URL is associated with that keyword and optimize appropriately.
Categorize to Get More Granular Insight
We can see in the image above that while there’s a correlation in the number of Keywords and Impressions, we don’t see a similar rise in Clicks. Perhaps pulling in ranking information would garner insight showing poor positioning for additional keywords causing the spike.
For the purpose of this article, let’s focus on applying this method to keyword sets. While looking through the keywords people are using to get to the site, it’s noticeable that there are a significant number of users looking to read how-to articles. Let’s say hypothetically we want to put more effort into catering to that style of writing, it would make sense to break that out in the Dashboard as well.
- In the Raw Data tab, filter for a specific type of search behavior and run numbers using the Dashboard template.
Tip: Using Excel’s wildcards and advanced filterswill help easily group keywords.
Looking at the same numbers for “how to” specific queries, we can see that all is fairly steady. While other categorize may be more volatile, how-to articles are steadfast.
This is the most basic way to do what many enterprise tools are charging thousands of dollars for. It makes sense that this process would be valuable to automate, but we all have access to the same data and it’s absolutely possible to garner the same insights with a little effort.