If you have multiple websites that you manage, and struggle to keep an eye on all of them at once, here’s how to create a quick report to compare their day-to-day traffic at a glance. It is made possible with the Google Analytics Pro connector and the Analytics Edge Core add-in for Excel.
Start with a couple of predefined worksheets; one called Queries (you can use any name) and the other called Results (you can use any name).
If you haven’t yet done so, create an account entry using the Accounts wizard. Follow the prompts to login and grant access to the Analytics Edge application.
Create a New Bulk Query
Open the Bulk Queries wizard. Select the Queries worksheet to hold your query configuration data. Note that this worksheet should not be used for any other purpose, and it can be hidden is desired.
Click the +New Query button to create a new bulk query. The wizard will load account summary information for your default login.
Defining the Query
The wizard will present you with a number of tabs to configure your query. Start by assigning a unique name for the query. Use something meaningful but not too long. Then select a Location for the query results — click the button to select a worksheet cell, or just enter the location using the format of WorksheetName!A1.
Using the Account / Property / View selectors, click the Add > button to add each of the web site views you want to the list on the right side.
For this particular report, we want to display the total number of sessions per week for multiple websites, so select the Week of Year dimension and the Sessions metric. If you want daily, then select the Date dimension instead; monthly, select Month of Year instead.
In the Pivot by selector, select your period dimension — Week of Year in this case.
Picking the Date Range
To select the date range for the query, click the Dates tab. Analytics Edge lets you select dates in a wide number of combinations and methods. In this case, we will select a duration of 13 weeks ending Last Week. That way, every time we refresh the query it will go back 13 weeks from today’s date; we don’t have to adjust the start and ending dates.
Note that if you prefer weeks starting Monday, use the dimension ISO Week of ISO Year and ending date of Last ISOweek. For months, select a duration of a number of months, ending Last Month. For daily reports, you can query any number of days, ending Yesterday or Today (or any other option).
If your date range is long and will encompass a lot more than 500,000 sessions in your web properties, you can avoid the dreaded Google Analytics data sampling errors by selecting the option to “minimize sampling” on the Options tab. Caution: This will issue one API query for each week (day/month) for each website in your query. If you are trying to get 52 weeks of data for 20 websites, that will be 52 x 20 = 1,040 queries which could take a little while. Analytics Edge will display a query progress dialog during this time.
Displaying the Results
The results of the queries will be written to the worksheet location selected for the query; in this case, the Results worksheet. You can use simple Excel conditional formatting to provide some quick visualization of trends in the data, or create an Excel multi-line chart. The choice is up to you.
Next week, just refresh the query for a quick overview of your websites health. Analytics Edge makes is simple.