Combine Google Analytics, Webmaster Tools and Moz Data

combining-google-analytics-webmaster-tools-moz-dataAnalytics Edge makes it easy to combine data from different sources in the same report. This article describes one of our free workbooks that combines data from Google Analytics with Google Webmaster Tools and Moz.

Download the workbook here.

If this is your first Analytics Edge workbook, you must configure all three connectors with Accounts. The Moz connector also requires that you get API credentials, which you enter into the Accounts wizard for that connector.

ga-queryThe workbook contains three Analytics Edge macros to make it easier to understand and modify. The first downloads the top 100 landing pages from Google Analytics. You can customize the query by selecting the macro in the task pane, clicking on the “GA Reports” function and clicking the Edit (pencil) icon.

Select the login account and web property on the Views tab. Change the time period for the report on the Dates tab, and the number of pages on the Sort/Count tab.

Run this macro (press the Play button) and you should see your Google Analytics data in the Top Pages worksheet.

wt-query1You fill in the Webmaster Tools data with the second macro. Again, configure the query by selecting the macro in the task pane, clicking on the “WT webmastertools” function, and clicking the Edit (pencil) icon.

Be sure to enter your website (there is no default setting), and make the date range match what you used for the Google Analytics query. You can optionally limit the query to WEB searches (media setting), or restrict it to your region/country.

Run this macro (press the Play button) and you should see your Google Webmaster Tools data merged into the Top Pages worksheet (the Avg. position, Impressions and Clicks columns).

mz-queryThe third macro gets data from Moz, but it is limited to 10 rows per query (and free accounts are limited to 1 query every 10 seconds). The macro will keep track of what has been updated and can be run multiple times to get data for all your webpage URLs.

This query uses the default account and is automatically configured, so there is nothing to do here but run the macro (press the Play button) several times to fill in the Moz data in the workbook.

The final report shows data from all three sources, automatically aligned and ordered. No fussing with downloads, no formulas, no programming, and you now have plenty of time to interpret the results.

ga-gwt-moz-report2

The Analytics Edge Core Add-in makes it easy. Simple Excel report automation.


How It Works

macroThe magic of this report automation lies in the Analytics Edge macros. These are not Excel macros — they require the Analytics Edge Core Add-in to run. The Add-in manages the macros in a task pane that opens beside your worksheet.

Each macro has a series of functions steps, and each step was created by a function wizard. There is no programming or formulas involved.

The first macro, Refresh Google Analytics, performs the Google Analytics query described above. The Moz query will require a series of canonical URLs, which can be created by Joining the GA ‘Hostname’ with the ‘Page’ — the Column/Join wizard does this.  [Note that wizard functions are referred to by the button/menu names, so Column/Join can be found by clicking the Analytics Edge ribbon button Column, and selecting Join from the popup menu.] The Table/Arrange wizard is then used to force the columns to be in the exact order desired in the report, which create placeholders for the Webmaster Tools and Moz columns that don’t exist yet. The results are then written (File/WriteToWorksheet) to the ‘Top Pages” worksheet where you can see them.

In preparation for tracking Moz requests, all the rows are Table/Filtered out of the data and the header row is written to the worksheet “Pages With No Moz Results”.

macro2 (1)The next macro, Refresh Google Webmaster Tools, starts with the Webmaster Tools query. The Page column contains the whole URL, including the “http://” portion. This is not included in the Google Analytics data, so the Column/Replace wizard is used with a Regular Expression (RegEx) replacement to strip it (and the https variant) out.

The column is named “Page”, and we need it named “Canonical URL” for the Moz query, so the Table/Arrange wizard is used to rename the column. Then Arrange is used again to fix the order. This intermediate table is named “Webmaster Tools” so we can refer to it later.

Switching gears, the File/ReadWorksheet function is used to get the Top Pages worksheet with the Google Analytics results, then the Multiple/Update wizard is used to merge in the data from the saved “Webmaster Tools” table [no more vlookup!]. Then Table/Arrange the columns as we want them and File/WriteToWorksheet back out to the “Top Pages” sheet.

macro3Our last macro, Refresh Moz, gets a little trickier. We load the Top Pages worksheet as in the last macro, but this time with an option to name the table with the worksheet name (“Top Pages”) so it can be referenced later (instead of reloading it). The Moz API can process a request for 10 URLs at once, and we have more than that, so we have to keep track of what has been processed and what hasn’t been. Previously processed rows would have values for External Equity Links, a Moz metric, so we Table/Filter them out.

Moz doesn’t always have results for every URL, and some rows may come back blank, so we will keep a second worksheet with a list of the URLs that had no results, and use the Multiple/Match wizard to remove them. In the first macro, we cleared that worksheet or nay previous results, so the first time through there won’t be any matches.

Using Table/Top function to keep the top 10 rows, we then use Table/Table Name to both assign range names to the columns and to tag the current table as “Pages Queried” so we can reference it later. Using columns as ranges lets us specify the column name [Canonical URL] in the Moz query (the square brackets are used to identify the term as a range name), and all 10 values are fed to the query automatically.

range-name-used

Pro Tip: Most text fields in Analytics Edge wizards can accept [Range Name] entries. Where the function can process a list, then entire column will be used, but if the function can only handle one value, only the first row’s value is used.

The Moz query is run, and the results are tagged with the Table/Table Name “MozResults”. Switching back to the Top Pages table, the Multiple/Update function is used to pull in the MozResults data. Making sure the columns are in the right order with Table/Arrange, we dump the results (File/WriteToWorksheet) back out to the Top Pages worksheet.

One remaining item is to update the other worksheet with any URLs that Moz didn’t have. This is done by switching back to the Pages Queried table, removing matching URLs from the MozResults table to produce a list of URLs not found. These are Multiple/Append-ed with the “Pages With No Moz Results” worksheet, and the combination is written (File/WriteToWorksheet) back to the same worksheet.

On the next cycle of the macro, this list will be removed from the Google Analytics data so  the next group of 10 URLs will be processed.

refresh-allOnce your accounts are set up and your workbook is configured, refreshing it is as simple as running the macros in sequence and repeating the Moz query until all the rows have be populated.

Analytics Edge even makes refreshing the macro in sequence easy — just click the Refresh All button. Sit back and watch the macros run in the task pane. The sequence ends with the Refresh Moz macro, so you just click Play to repeat that one macro until you have all your data.

Simple Excel report automation. Analytics Edge takes all the work out of downloading your data from multiple sources and merging it into a meaningful report. So you can focus on your work, not the tool.