Using Google Analytics web analytics service to monitor your web site gives you some excellent metrics to monitor the engagement level of your site visitors. Engagement is typically measured with four distribution reports that explore your visitors’ loyalty, recency, and the duration and depth of visit profiles. Distribution reports can be very difficult to program in many reporting tools, including Excel, particularly when the groupings are arbitrarily determined. This article discusses a Microsoft Excel template, used with Nextanalytics for Excel, which combines all four of these reports, and a few others, inside an easily-refreshed ‘dashboard’ workbook.

How it works

With Nextanalytics for Excel, we directly query your Google Analytics account through their data API. Once the data has been retrieved, it is processed using Nextanalytics script commands, and the results are dropped into worksheets for presentation using Excel’s formatting and charting capabilities. All of this happens transparently, and can be updated with the click of a refresh button. If you manage multiple websites, you can select from any of your Google Analytics profiles using an Excel combobox (a drop-down pick list). Details behind the construction of each of the components displayed can be found in the related posts listed at the end of the article.

Installation and configuration

1. From the attached zip file, extract the four macro files used to build distribution reports, and save them to the ‘macros’ directory of your Nextanalytics for Excel installation. By default, this is located in My Documents\Nextanalytics\macros\.

2. Open the workbook file and navigate to the ‘AccountInfo’ sheet. Enter your Google Analytics account and password in the cells beside the corresponding labels, highlighted here in yellow.

3. In the area labelled ‘Profiles’, enter a column of friendly labels for each of your profiles with a corresponding column of profile IDs. Profile ID’s are NOT the numbers from your tracking code; they are available from the Google Analytics administrative interface by Selecting Analytics Settings and clicking Edit for the Website profiles listed. As an alternative, you can get them from Nextanalytics for Excel’s Google Analytics data connector interface. If you have more than 5 profiles you want to enter, you will need to insert rows and update the Excel INDEX() formula in the cell just under your password. See the Adding interactivity… article for more information.

4. Click the Nextanalytics Refresh button on the ribbon bar.

Related articles:

Adding interactivity: selecting a web profile