With the Analytics Edge Core Add-in for Excel, you can easily build a multi-website report, quickly and without programming. This article and the attached sample workbook show how easy it can be.
Download the sample workbook: multisite-report.xlsx
Setting Up the Queries
This report is configured by a simple table of values, listing a website label, Google Analytics view id, and the target worksheet for each website. You can add as many websites as you want — there are no limits with Analytics Edge.
The website column is used for your information only — it is not actually used by the macro at all. Enter a label or identifier so you can tell what website it is.
The view id column is where you enter the Google Analytics view id for the query. You can find the view id from the Google Analytics query wizard (shown below)
The worksheet column identifies the name of the worksheet to be used to write the results of the queries. Each website would get its own worksheet in the workbook.
Create a Repeating Macro
The automation in the report is performed by a Repeat Macro. The Repeat Macro reads the values from the table and, for each row, repeats the rest of the macro. To start a Repeat Macro, simply open the wizard (File – New Repeat Macro) and pick where you want to get the data from.
The values in each column are available to the various functions in the rest of your macro simply by entering the column name as if it was an Excel range name (with square brackets around it, like ‘[view id]‘ or ‘[worksheet]‘).
Making Queries
The actual queries are made as you normally would, with one notable exception: instead of selecting a website view, you enter ‘[view id]‘ as a value instead. When Analytics Edge makes the actual query, it will substitute the value from that row of the view id column.
Writing To Worksheets
Once the query is done, you can use other Analytics Edge function wizards to manipulate the data as desired, then write the results to your worksheet for that website. You simply use the column name as if it was an Excel range name (with square brackets around it) instead of entering a specific worksheet name.
In this example, there are multiple queries in each target worksheet, so the top-left cell is picked in one of the worksheets and then the name is changed to ‘[worksheet]‘. Analytics Edge will substitute the value from that row of the worksheet column for each loop of the Repeat Macro — writing each website’s results to a different worksheet. If a worksheet of that name does not exist, a new one would be created. Note: this is case sensitive, and you might get an error if you type the same name with different case (an Excel quirk).
In this example, an option was selected to Transpose the rows and columns of the results, putting the column header to the left of the data. This makes for a slightly better presentation.
One Click and You’re Done!
When you finish building the queries, just click Refresh All and Analytics Edge will cycle through all of your websites, make all the queries, and write all the results to the various worksheets. Development time was just minutes. Add a new website in seconds. This is what Analytics Edge automation can do for you.
Summary and Possibilities
You can see how quickly the queries are created, and how easily new websites can be added (or removed). If you format the report worksheets, you can save yourself more time by copying a fully-formatted sheet and then renaming it for the new website — enter a new row in the table for the new site, and the macro will update the data on the next Refresh.
Note that you don’t have to manually enter everything into a table for the Repeat Macro — it could use the results from a different query for a fully dynamic reporting solution. For example, you could use the Google Analytics connector to get the top 10 landing pages from organic search, and then use a Repeat Macro and the Google Search connector to get the top queries (keywords) used to find each of those pages. Macros are executed in alphabetical order, so just name the macros accordingly.
You also don’t need to write the results to different worksheets — they can be combined into one long list if desired. Simply use the Append To Worksheet function to write the results of each query to the end of a common worksheet. The Repeat Macro has an option that lets you clear that worksheet before the first row is processed, letting the macro always start with a clean sheet. If you need something in each result set to know which query it came from, you can use the Arrange – Insert column function to create a new column in the query results, and set an initial text value with one of the Repeat Macro columns (like ‘[website]‘).
With Analytics Edge, the possibilities are endless. Don’t be afraid to ask for help.