Stephane Hamel of Immeria created a sample dashboard in Excel (link) that took off virally, becoming a popular starting point for others (link) (link) to create their own solutions. I applaud his work and his creativity. In the comments to his blog, though, a number of people found the complexity daunting. I am a strong believer in simplicity (and the amazing capabilities of Nextanalytics for Excel), so I took his idea and created a slimmed-down version specifically for Google Analytics users. Using Nextanalytics to download my Google Analytics data and to do some simple math, I created a greatly simplified version that uses basic cell references and formulas along with the usual Excel charts and formatting. Hope you like it!

The things I loved about Stephane’s design was the simple, clean look, his use of Excel’s icons to show increase/decrease over previous periods, as well as an innovative use of the error bars to create the shaded backgrounds on the trend charts. I got rid of most of the formulas by displaying only the most recently completed month as the “Current Month” (the calculations for that are at the top of the “common_actions” sheet) and selecting the previous month and previous year’s month from there. In this sample, I also only have one block of metrics – the usual site metrics — but adding more would be a simple matter (see below).

On the right side, rather than showing the average +/- 1.5 standard deviations (which appears as the shaded area in the charts), I chose to display the real upper and lower limits of the data along with the median. The median is the value where half of the results are above, the other half below, and it is not as affected by the sudden spikes or dips sometimes encountered in web traffic. I found I wanted to know how high the spike went, how low the dip was, and what the middle of the pack (literally) was.

To use the dashboard, simply download and open the spreadsheet file (Excel 2003 and 2007 versions included) and enter your Google Analytics account information. You’ll need the profile ID number for your web site, but you can find that simply by logging in to the Google Analytics data connection – we’ll show you all your profiles and their ID numbers. After you have entered that into the AccountInfo sheet, click on the Nextanalytics Refresh menu item, and watch your website data appear.

If you’re interested in how it works, have a look at the “common_actions” sheet. Nextanalytics executes those script commands and drops the results onto the “data” sheet. The “Visitors Scorecard” is built from there using simple cell references. The script starts with a query of common visitor metrics to the Google Analytics API and saves it to a file, then Nextanalytics imports that file and joins (concatenates) the year and month columns together. That result is held in memory and given a name (“PageCaption”) called “visitor metrics”.

A second query is made to get the unique pageviews metric (the Google Analytics data API doesn’t allow “uniquePageviews” and “visitors” in the same query). Again, the year and month are concatenated, and then the “uniquePageviews” column is copied to the end of the in-memory “visitor metrics” results. The year-month columns are used to make sure the numbers get copied to the same-labelled rows.

A series of math calculations are performed, creating new columns for the calculated metrics like “Unique Page/Visit”, and then a custom sort is performed to keep only the columns of interest. Since it is more common to have dates across the columns instead of down the rows, I swapped the axes and then named that result as “metrics and rates” in memory.

For the final step, I used a summary command (“AddAggregates”) to calculate a lot of popular statistical measures across each row, including average and standard deviation but I specifically keep the maximum, median and minimum values. As before, I copy those columns to the results in memory and then wrote the whole thing out to the “data” sheet.

To build another panel of KPI’s, you can easily change the queries to retrieve other metrics (Nextanalytics will send your query direct to the Google API and return the results to a file), and modify the calculations accordingly. Use different names for the in-memory page captions and, at the end, write the results to a different sheet (e.g. “data2”).

Nextanalytics for Excel is licensed permanently per PC. There are no subscription fees, no limits to the number of profiles, and your queries are sent direct to Google. You can use our interactive application to build the query or hand-craft your own. Everything can be customized by modifying the script in Excel.