When building a dashboard for web reporting, people often want a quick summary or overview on the first sheet. In the overview, they’d like to see a few key traffic metrics as well as an indication of the trends over the period. This is easy to do with Nextanalytics and some simple Excel charting.

The common measures to be displayed include the total visits, total pageviews, the ratio of pageviews/visits, the bounce rate (bounces/visits), the average time on the site (timeOnSite/visits) and the percentage new visits (newVisits/visits). In addition, we want to see the trends for each of these measures over the time period.
To use the template, simply enter your Google Analytics account, password and profile on the AccountInfo worksheet, switch to the Dashboard worksheet and click the Nextanalytics Refresh menu item.

Behind the Scenes

Nextanalytics for Excel automates the entire process using a series of script commands, listed on the ‘config_actions’ worksheet. Whenever the Refresh item is clicked, all the script commands are executed and the data is updated from Google. Normally the script commands are automatically recorded by the Nextanalytics for Excel user interface, but it is possible to manually adjust them and even to replace them with calculated Excel formulas. The first command in the list has been modified in this way, using simple Excel formula to reference the account, password and profile from the AccountInfo sheet.

The template was created using two of the ‘advanced’ script commands available with Nextanalytics. After making the necessary Google Analytics query itself, the first advanced command, ImportData, allows us to load all 5 metrics at once into a simple table, just like they came from the query. [Editor’s note: the next release will provide this capability within the user interface] Then we calculate a total row and save the results to a worksheet.

The second advanced command, Compare, performs a quick ratio of all the columns by the ‘visits’ column, and this gives us all of the calculated measures we needed. Again, we save the results to a worksheet so they can be easily referenced on our Dashboard.

To build the dashboard, we make simple references to the appropriate cells from the Total or Per Visit worksheets and format as desired. To build the small trend charts, simply create Excel line charts referencing the appropriate data ranges from our calculated worksheets, then turn off all the titles, legends, axis labels, and gridlines and size the charts to the space desired.

If other charts or analysis results are to be added to the dashboard, more scripts can simply be moved to a common sheet where they will be executed in order. This allows you to create individual charts or analysis tables independently, and then easily ‘merge’ them into a common dashboard.

And there we have it – a very simple approach to adding key measures to our dashboard, and it provides tremendous flexibility for customization.