As discussed in a companion blog article, while trying to create an annual report for my own websites, I discovered that Google Search data is full of ‘noise’ — data that actually makes it harder to make useful observations about trends. This article includes the workbook I used.
This article assumes you are familiar with Analytics Edge macros. The macros are not described in detail, and there has been no attempt to make the workbook work for everyone — it MUST be edited to suit your account, website, and brand filter expression.
REQUIRED: Analytics Edge Add-in with Google Search connector.
REQUIRED: The workbook includes the macros I used for my websites, but you will need to modify them to suit your website analysis.
The workbook: GoogleSearch-AnnualAnalysis-1-0.xlsx
[*** read the rest of this article before trying to run/refresh ***]
The Analytics Edge Macros: Overview
1 get Raw Queries – gets the raw data from the Google Search connector for one or more websites into the ‘Raw Queries’ worksheet.
2 remove http, brand, off-topic – uses the Raw Queries worksheet data, filters out branded and off-topic queries, and strips http:// and https:// prefixes from all page entries. Saves to the Filtered Queries worksheet.
3 rename moved pages – uses the Filtered Queries data, changing page entries for pages that have changed URLs during the year (as listed on the Moved Pages worksheet). Writes the results back to the Filtered Queries worksheet.
3a combine duplicate rows – combines rows in the Filtered Queries worksheet for moved page entries. Writes the results back to the Filtered Queries worksheet.
4 keep only the best position – filters out queries that placed at positions 10 lower than the best position for that page. Writes the results back to the Filtered Queries worksheet.
4a update Impressions by Page-Query – populates the Impressions by Page-Query with the best-position queries for each page, pivoted by date. This can be used to identify off-topic search queries that are better than on-topic queries for the page.
5 create summary – working from the Filtered Queries data, populates the Summary worksheet.
Modifications Required
Macro 1 get Raw Queries:
- edit the >>WT Search Analytics function, and select your account and website.
- if you have an http and https website, use the second query to get the companion website
- if you do NOT have more than one website to process, DELETE the second query and the Append to Worksheet command.
Macro 2 remove http, brand, off-topic :
- edit the Filter function, and change the filter expression to exclude your brand.
Off Topics Queries Worksheet
- to remove off-topic queries from the Filtered Queries worksheet, enter each page and query combination here. See the companion blog article for an explanation.
Moved Pages Worksheet
- if pages have changed URLs during the year, enter the URL each page Moved From and Moved To.
Notes About The Macros
The macros in this workbook were provided as an example of what I did — you may want to change the way they work to suit the unique characteristics of your website.
If you need help modifying the macros, please contact support…we’ll be happy to help.