With Analytics Edge and the connector for Google Search, you can quickly create SEO analysis reports with tremendous insight potential; way beyond what you can do in Google Search Console itself! Done in minutes with no formulas or programming !Here’s a quick example you could do with the Analytics Edge Add-in for Excel or the independant Analytics Edge Desktop App.
Average Positions Trend for Top Page-Query Combinations
There are some search queries that bring lots of traffic to various pages on your site, and this quick report lets you see if any of them start to climb or drop in the rankings. Use it to gauge the success of your SEO efforts, or to easily see the impact of new search ranking algorithms.
The report starts with a quick “multi-query” using the Google Search connector. For the fields , split by 7-Day Periods, with a Pivot (group by metric). Then select the dimensions ‘page‘ and ‘query‘, plus the metrics ‘position‘ and ‘impressions‘. Select the preset date range of ‘Last 90 days‘.
You will end up with a lot of combinations of pages and queries, many of which get very few impressions, so we will want to sort and/or filter them.
Because we have 12 weekly columns of data, it would help to get a total for the whole 12 weeks first, then sort adn filter on that column. This is easily accomplished with the Add-in’s Table > Total function, summing the last 12 columns. I chose to remove the weekly impressions columns themselves (the ‘summarized columns’) since the report is focussed on the positions.
Once we have the Total impressions column, it is a simple matter to use a Sort function to reorder the rows, and Filter function to get rid of the combinations with few impressions. Optionally, you could use the Top function to keep the top 10, 25 or however many rows you prefer. Then a Write to Worksheet function finishes off the macro and your report is done!
Refresh the report weekly for the latest insights! Click…done.
Bonus Step: Formatting in Excel
As an extra step, I tend to use Excel’s custom formatting for visualization of trends. Since we are looking at search engine rankings, page 1 and 2 are where your focus needs to be, so I use a reverse 3-color rule, with red for higher numbers and green for smaller ones. I edit the rule and change the defaults so numbers 9 and lower are green (page 1 search results), numbers 19 and lower are white (page 2 search results), and numbers over 29 are red (page 3 results will shade to red). You can adjust the numbers to your preferences.
The final result now clearly shows page-query combinations that are improving or degrading in rankings, plus it makes new ‘hot’ pages very visible. If you sort the report by page, you can see how new search terms are picking up existing pages and supporting or replacing older terms.
Variations
Track a specific list of page-query combinations. Create a second worksheet with the page-query columns that contains the desired values, then instead of Filtering, use the Match function to keep matching values from your reference worksheet [no need for weird lookup formulas].
Highlight hot content and queries. You can also report on page+query combinations that have an increasing number of impressions in the past month (‘hot content’). Instead of filtering by the total impressions, use one Total function to get a sum of the last 4 weeks (do not remove the summarized columns), then use a second Total function to get the sum of the last 12 weeks. Now use Calculate to create a new column with the ratio (4 week total/12 week total) and filter on the result (greater than 1). You will also want to Filter on the 12-week Impressions to remove the long tail searches with few impressions overall. Hot combinations would have a decent number of impressions and a high ratio, so Sort accordingly.
Summary
Analytics Edge lets you work this quickly, creating unique and insightful exploration reports, without stopping to do a search on how to build a formula. Stay in the flow — get your report done fast.
Download now – 30 days free and fully functional.