Let Your Reports Feed Themselves

Newsletter sent 2015-08-25.

Let your reports feed themselves

Most multi-source reports simply display metrics from various sources side-by-side, and that is a perfectly good way to display unrelated pieces of information, but think of the possibilities if you were to use the data from one source to feed queries from another source. The Keywords for Top Landing Pages report does just that.



Say Goodbye to “(not provided)”

In late 2011, Google Analytics stopped providing keyword data for visits from their search engine, creating the dreaded “(not provided)” keywords. People were directed to Google Webmaster Tools (now called Google Search Console) to see what pages appeared in search and what keywords were used. The problem was that getting data out of Webmaster Tools was cumbersome as there was no real API.

With the release of the new Search Analytics query capability in the Google Search Console API, Google has finally opened the door to filtered queries, so you can now get all the keywords used for a specific page on your website.


Get Top Landing Pages -> Get Keywords For Those Pages

The new report uses a Google Analytics connector query to get the top Landing Pages for your website. It also grabs the Hostname value — your web server name. The two pieces combine to form the URL of the page, and that can be fed to the Search Analytics query (Webmaster Tools connector), allowing you to see the keywords, clicks, impressions and average positions of that page in Google’s search results. Extraordinary search engine optimization value!

If you have the Analytics Edge Core Add-in, you could do all this in a repeat macro, looping through all your pages.

With the Analytics Edge Basic Add-in it is still possible; the trick is to realize that queries are refreshed in order, from the top of the worksheet to the bottom, left-to-right. Place the Landing Pages query in column A, and you can use the results to feed Search Analytics queries in columns to the right using simple Excel cell references — just like in the provided report.


Countless Possibilities

This is just one example of using data from one query to feed another; Analytics Edge makes it easy to do. Take a look at your reports and see if there are opportunities to get new insights from the data you already have, simply by feeding one query with data from another. Need help? Just ask.



Mike Sullivan
Founder, Analytics Edge