If you track your web site with the Google Analytics web analytics service today, it is really easy to create a dashboard in Excel that shows key metrics of your web site — and refresh it daily with a single click! All you need is Nextanalytics for Excel along with your copy of Microsoft Excel 2003 or 2007.

A Basic Daily Dashboard

Let’s have a look at what has happened on your site over the past 7 days. Start by opening Excel and opening the Google Analytics connector.

• Open Excel (2003 version shown here, 2007 is similar)
• From the Nextanalytics menu, select Get Data From Google Analytics…

Login to your Google Analytics account. This is the account you use to access your web analytics information at http://www.google.com/analytics. When you are logged in, you will be presented with a form that lets you choose what information you want to download from Google’s server.

There are plenty of options to pick from here, but let’s keep it simple for now – just pick the Google Profile you want to use from the drop-down at the top left. Everything else has some defaults selected – the Past 7 days of a selection of Dimensions and Metrics for all Segments – so let’s just make the query.

• Click the Fetch Data From Google button

When the data is returned, it is automatically saved to a file on your system, ProcessedGoogleData.csv (in My Documents\Nextanalytics\data) by default. If you wanted to, you could load this file into Excel, but it’s a long transactional report that is cumbersome to analyze.

That is a perfect job for Nextanalytics, so our main form automatically loads with your data already available. Since we detected a date in the data, we assume you will probably want to see trends first, so we ‘pivoted’ by the date and picked one of the other columns for the rows, just to get you started. The resulting ‘pivoted’ data appears in Excel in the background. Let’s look at that form a little closer.

In the ‘Incoming’ list in the upper left are all the columns found in your data file. Columns that are recognized as dates are shown in blue, numbers are shown in green.

If there is a column that has a lot of different text values in it, we will show it in red. If you are trying to make a small summary report, that column probably isn’t one you’d use. Likewise, if a column contains only one text value, it will appear in red as well. No sense showing a column if all the values are the same.

Each of these column names can be dragged to the Columns box, the Rows box, or the Numerics box.

• Drag ‘date‘ to the Columns box if it is not already there
• Drag ‘pageviews‘ to the Numerics box
• Drag ‘country‘ to the Rows box
• Drag anything other than ‘country‘ out of the Rows box back to the Incoming box.

We can also change how the dates are shown across the columns to something more common.

• In the Format on worksheet box, enter ‘MMM dd‘

In the worksheet in the background, you can see the results of each action you have taken. Notice that there is one row for every city, and that the pageviews have been totaled (Sum is selected by default) for each date.

What if we wanted to see the top 20 cities? Well, you could just close the Nextanalytics form and use Excel functionality to answer your question. You would create a column of Sum() formulas, adding the 7 days of pageviews, then copy the formula down all the rows in the worksheet, then sort the list in descending order. That is a lot of manual effort, though, and Nextanalytics has a better way.

To create a Sum or total column, and then sort it descending, we pick these simple analytic functions from the tree list and drag them to the open box on the right.

• Click the ‘Analyze‘ tab
• Expand the Column Summary node
• Drag ‘Column Sum‘ to the box on the right
• Expand the Sort Numerically node
• Drag ‘Sort First Column (Descending)‘ to the right

That’s it! No formulas to copy. There is nothing to fix if more rows appear during a refresh.

Now we have the first material for our dashboard. Since we want more than just a plain worksheet of numbers, and we want to be able to refresh it every day, let’s save it using Visual Basic for Applications (VBA) formulas. This provides us with a lot of flexibility in formatting.

• Click the Options tab
• Click the Save Actions Then Exit… button
• Click the Save as VBA button

(Note: if your security settings do not permit this, a dialog box will pop up with instructions on how to change them)

After the form closes, you may not notice the change to the worksheet, but all of the cells now contain formulas instead of text and numbers. Since they are formulas, we can keep the ones we want, move them around and reformat them as desired. Since we only wanted the top 20, we can copy those rows to Sheet1 where we can format them and change the column titles for City and Sum as desired. This spreadsheet can be saved and viewed every day – the formulas will automatically refresh every time you open it.

We now have a simple Excel report that can be easily refreshed direct from the Google Analytics API service. It only took a couple of minutes and involved no coding. Dashboards usually display multiple metrics and contain charts, though, so let’s continue and add one to our workbook.