This is part of a series showing how easily you can build a daily web dashboard using Nextanalytics for Excel with the new Google Analytics connector. In this article, I want to show how to add a new item to an existing workbook (our Daily web dashboard). Along the way, you will see how easy it is to handle odd date formats, and how to make a top 10 chart that includes an ‘other’ group for items not in the top 10 list.

Add a Top 10 Chart to our Dashboard

Building on the previous example, we can extend our Daily Web Dashboard and gain a lot more insight into our web traffic. The Google Analytics API query used in the last example had 7 different dimensions in it, but we only used the date and city columns. Let’s explore one of the others – the source.

• From the Nextanalytics menu, select Load Data File…
• Open the ProcessedGoogleData.csv file created in the last example

You may notice that something is different this time – the date column has been classified in green as (numbers) instead of in blue as (dates). Drag the date (numbers) entry from the Incoming box to the Rows box and you will see them in the worksheet in the background. This is because Google uses a date format that Excel normally does not recognize (e.g. 20100315). This challenge is very common when working with data from other systems, and Nextanalytics has a simple way to deal with it.

To deal with unrecognized date formats, we enter a format string that matches the data – in this case it is a 4-digit year followed by a 2-digit month and a 2-digit day. The Date Help button provides a few hints as well as web references you can use to understand the date format codes.b

If the format you entered matches the data, the list of fields will have updated, showing the date entry in blue as (dates), and the worksheet will be showing a trend by date in the background showing the 3-character months with 2-digit days. Nextanalytics makes it easy to read in almost any date format and determine how you want it displayed.

Let’s create a chart showing the daily pageviews from the top 10 sources, and show the pageviews of all the remaining sources grouped together as ‘other’. To do this in a query or Excel formulas would be really complicated. With Nextanalytics, we make the process simple.he Numerics boxDrag ‘date&’ to the Columns box

Now we have the rows and columns of interest, so let’s create a total column and sort it descending.ght

Now we want to group everything not in the first 10 into an ‘other’ row.e Show Advanced Click the Show Advancedcheckbox; you should see new commands available in the list

Notice in the worksheet in the background how there is now an ’other’ row at the top of the results, and we are only seeing the top 10 sources. We could make a chart from this table, selecting the ranges for the data series accordingly, but let’s do a couple of extra steps to make that process easier – remove the total column we created and move the ‘other’ row to the bottom where it is usually shown in charts. This would allow us to select the whole range and simply make a quick chart.

• From the Select node, double-click Select First Column
• From the Select node, double-click Remove Selected (notice that the Sum column is gone)
• From the Select node, double-click Select First Row
• Expand the Move node, and double-click Move Selected Rows to Last

Let’s put this table of number on a new worksheet titled Top Sources.

• Enter ‘Top Sources’ into the box at the bottom of the form (Save automatically into: …worksheet)
• Uncheck, then re-check the checkbox beside it and a new worksheet should appear

Finally, let’s save the actions so we can refresh this table later. In the previous example, a tab titled ProcessedGoogleData_actions was created with all our script lines, and we want to append these new actions to that list.button

It is now a simple matter to add a new chart to our dashboard, using the data from the Top Sources sheet. To refresh it, select the Nextanalytics menu Refresh.

We now have a simple Excel dashboard with a couple of different metrics, and it can be easily refreshed direct from the Google Analytics API service with a simple click. It only took a few minutes and involved no coding. You have also seen the extent of the analytics available, and the ease of use they offer. Additional charts and tables can be added in a similar way.