Long-term reporting with Google Search Console data is fraught with challenges. This article discusses some of the unique problems time can create, and how Analytics Edge products can save you time while solving them.
Google Search Console annual reporting challenges:
- getting all the data
- merging http/https or subdomains into a single report
- getting and removing branded search terms
- removing off-topic high-impression queries
- blending page/URL changes over the year
1 Getting All The Data
Now that Google Search Console keeps 16 months of historical data, there is lots of data available, but the web interface doesn’t provide any ability to download it. From the web interface, you are limited to 1000 rows of data and one dimension. There are a variety of API-based solutions that will get more (5000 rows), but few will go as far as Analytics Edge.
The Analytics Edge connector for Google Search will get all the data available, making multiple API queries as required to get it, even if it is over 80,000 rows of data. The connector can also get it in weekly or monthly buckets, something Search Console itself does not do.
Some people have discovered that you can often get more detailed data when you iterate over a series of filtered queries than you can with one big unfiltered query. Analytics Edge makes this type of iteration easy using Repeat Macros.
2 Merging http/https Or Subdomains
If you have multiple websites defined in Search Console, either due to an http-to-https conversion or because you track subdomains separately, chances are that you will be faced with merging separate data downloads together.
For http-to-https conversions, you need to strip the protocol portion from the pages, then merge duplicate rows of clicks and impressions. The process is a little more complicated if you want to blend the CTR and Avg Position columns as well, since you need to calculate weighted averages of those metrics. These steps can be easily automated using the Analytics Edge Core Add-in.
avg position = (sum of positions1 + sum of positions2) / (impressions1 + impressions2) where (sum of positions1) = avg.position1 * impressions1
3 Getting and Removing Branded Search Terms
Most search engine optimization analysis is about non-branded search, so you will need some ability to identify and filter out those search terms. Google Search Console only provides a simple filter which most people will find inadequate for excluding multiple branded terms and variants.
The Analytics Edge Core Add-in provides a robust regex-capable filter function, allowing you to use any number of expressions.
4 Removing Off-Topic High-Impression Queries
Nothing messes up search results data more than suddenly ranking for a high-volume, off-topic phrase. This usually happens when Google makes an algorithm change, and one of your pages suddenly starts ranking for a new unrelated phrase, sometimes for just a few days. Google has also expanded the amount of data it tracks, so you tend to see more poorly-positioned, unrelated searches (position 30 and worse). The problem is that more than a few impressions with a very poor position can cause your (weighted) average position to shift. It becomes a measure of quality of the search engine rather than optimization of your website for search.
Excluding these queries involves two steps: identifying them, and then removing them from the results. With the Analytics Edge Core Add-in, you have the tools to do that quickly. Write the detailed results to a temporary worksheet, identify the combinations that you want eliminated, and copy them to an ‘exclude’ worksheet. In your analysis macro, add a Match function to remove rows that match the ‘exclude’ worksheet values.
5 Blending Page/URL Changes Over The Year
Things change, and even though you may redirect visitors to new page locations, the historical data doesn’t change. This means your reporting will show the old URL with traffic that drops off, and a new URL with traffic that picks up in mid-year.
You will need the ability to map old page URLs to new page URLs, and blend the results. This is easily solved with a lookup worksheet, mapping old to new URLs. Analytics Edge can automate the lookup and the blending of old to new. That way the results would be combined into a single row, using the new URL.