Similar to the Queries for Top Pages report, this article details the steps to create a custom report grabbing the top 5 queries for a selected set of pages [Requires the Analytics Edge Core Add-in and the Google Search Connector].
There is a single macro in the workbook [DownloadQueriesForTopPages.xlsx].
The repeat macro reads the pages from the Top Pages worksheet, splits it by the ‘/’ character to extract the web server, then issues a Google Search Console query to get the queries for the specific page on that server. After the download, the results are cut off at the top 5, then a page column is inserted and the results are appended to the Queries worksheet.
Each step is described in more detail below.
REPEAT MACRO Top Pages
Starting the macro with a Repeat Macro wizard, we pick the Top Pages worksheet to process, 1 row at a time. This means we will process the entire macro for every row in the Top Pages worksheet. The values from each column are available to Analytics Edge wizards as ‘range names’ — meaning you can enter the column name in square brackets (like ‘[page]‘) instead of entering a real value (you will see this later).
We also choose to clear the Queries worksheet before the first iteration. Note that the last step in the macro is to append to the Queries worksheet. This lets us start with an empty Queries worksheet to which we will append the results of each pass through the macro.
As you can see, the first row of the Top Pages worksheet has been loaded into memory, and the ‘page‘ column contains the full page URL. To make the next steps easier, we’ll split this column by the ‘/‘ character to make it possible to get the web server portion of the page URL — ‘help.analyticsedge.com‘ in this case.
WT Search Analytics
With the page URL and server now available in separate columns (‘page‘ and ‘page-3‘), we can make the query to get the Google Search data. Using the default account (‘*‘) and the web server from the URL (as a range name, with the square brackets around the column name ‘[page-3]‘), we get the ‘query‘ dimension, sorted by clicks/impressions/ctr/position.
On the Filters tab, we elect to filter by the page URL using the range name (‘[page]‘).
Note: On the Dates tab, I have left it with the default Last 28 days — you can change that if you want.
Keep top 5
Since the original requirement was only the top 5 queries for each page, we need to get rid of the rest, so we add a keep Top 5 rows step to our macro (using the Table > Top/Bottom wizard).
Arrange Insert column
So we can tell which queries were for which pages, we use the Arrange > Insert wizard to put a new column into the results and set the initial text value to be the ‘[page]‘ range name we have used. This will create a new column and populate it with the page URL being processed in each loop through the macro.
Append to Worksheet Queries
Now that we have the results we wanted for that page, we append the results to the Queries worksheet. On each pass through the macro, each page’s queries will be appended to the results, giving us a single worksheet with the top 5 queries for each of our pages listed on the Top Pages sheet.
Note: when you finish the macro, it will only show the first page’s results in the Queries worksheet. Run the macro to process all of the rows.