There seems to be a school of thought that Google Webmaster Tool data is useless and inaccurate. General bugginess, delayed reports, showing average position in search results, and rounding numbers has led to many disregarding the search query report in frustration.
When John Mueller was asked during a Webmaster Hangout why rounding clicks was necessary, if there could ever be an advanced option showing actual numbers, and how many people would have to sign a petition for them to reconsider, he disappointingly said they just thought it’d be better if the numbers were “bucketed”.
While we can see how they might need to round impressions to help compensate for automated queries, clicks seem to be correlated enough with visits to make a conservative estimate of (not provided) data. This is my hypothesis. I need help experimenting, but here is my methodology.
The key is understanding how Google decided to round, or bucket clicks. After consolidating data across several sites this list was generated, showing how Google decided to bucket search query clicks.
Step 1 – Generate custom Google Analytics and Google Webmaster Tool search query reports.
In essence, Visits from a custom Google Analytics report need to be inserted into the downloadable Google Webmaster Tool search query report. Begin by creating two reports in your browser.
The GA Custom Report contains:
- Metric – Visits
- Dimension – Keyword
- Filters – include Google (Source), include organic (Medium), and exclude Mobile
- Also be aware of the date range!
Google Webmaster Tools search query report contains:
- Second dimension – Google Property
- Filter – Web
- Date range?
In this example, search query data was exported from a Google Analytics account that was linked with a Google Webmaster Tools account. This same functionality still exists in the report located in GWT.
Once the dates are set it’s time to export. Here’s a useful tip for downloading Google Analytics reports… at the bottom right of the page take note of the number of rows, then change to show 25.
Once the page loads, look at the right most part of the URL in the address bar of the browser and find table.rowCount.
Change 25 to the number of rows previously noted and press enter. Once the page loads you should be able to export the data in its entirety.
Step 2 – Consolidate Reports, Include Bucketed Numbers, and Analyze Data
Now take Visits from the Google Analytics custom report and insert them into the Google Webmaster Tools search query report. I do this using VLOOKUP. If you don’t know how to use VLOOKUP I suggest learning, but also suggest reading on as you will still understand the concept of how to theoretically regain some (not provided) data.
In the image below you can see that in addition to adding GA Visits from the custom Google Analytics report to the spreadsheet, columns for Next Lowest Bucket and Next Lowest Bucket – (minus) Visits were added.
The idea here is to leverage how Google is “bucketing” Clicks in Google Webmaster Tools to find which keywords are getting thrown into the (not provided) keyword referral group in Google Analytics. Clicks = Visits.
Data provided in Google Webmaster Tools contains Clicks associated with the searched term used, where the same Visits are lumped into (not provided) in Google Analytics.
So, looking at the screenshot above, we know that the keyword how to wash toms is getting bucketed by Google at 250 Clicks and the Next Lowest Bucket number is 200. That means if Google is using normal rounding rules they have recorded at least 226 Clicks while GA Visits are only showing 162. The difference of these two numbers are (not provided) Visits that can be associated with the keyword how to wash toms.
To be conservative (and for a quick/easy look) I chose to subtract Next Lowest Bucket from GA Visits, which resulted in a difference of 38 unaccounted Clicks… or (not provided) Visits! Applying this concept across the rest of the applicable keywords resulted in 26 percent of (not provided) keyword data found.
This percentage was raised to 39 percent after taking into consideration normal rounding rules. For example instead of 38 (not provided) Visits for how to wash toms shown above, adding the difference of Clicks and the Next Lowest Bucket (50) divided by two (25) to 38, resulted in 63 (not provided) Visits total that can be associated with how to wash toms.
Things to note about the spreadsheet above:
- You can access my list of the bucket numbers here.
- Keywords that return no Visits in Google Analytics, but result in Clicks in Google Webmaster Tools could also be considered (not provided) Visits.
- The results of Next Lowest Bucket – Visits column is filtered by anything greater than 0.
While there is a chance that the number of Clicks Google records is inflated due to factors like automated querying of search results, only a very small number of instances where GA Visits did not line up with Google’s rounding rules for Clicks were found during this research.