A sudden change in web site traffic appears and you have to explain it to your boss. Here is an example of a quick analysis using Nextanalytics for Excel with Google Analytics that not only gets to the information you need quickly, but it gives you repeatable presentation material with very little extra effort.

To start, we need to grab our web site traffic by date and country, easily obtained direct from the internet by selecting the dimensions and metrics in the Google Analytics data connector. In this case, let’s grab the past 28 days of data using the floating time range so our analysis can be refreshed and updated with just a click.

When we fetch the data from Google, it automatically loads into the worksheet, trended by date. Notice that Nextanalytics for Excel automatically recognized the dates even though the date format from Google would not be recognized by Excel. We can leave it displayed like it is (20100331), but it would look a lot nicer with just the month and day components, so we pick that format from the dropdown. We also choose to show all the periods instead of just the last 7; we know we loaded only 28 days, so there is no danger of creating a massive spreadsheet with too many columns. For our measures on the worksheet, we want to see the sum of pageviews – an easy selection and our data appears for us.

Since we are interested in the top countries over the period, not just one of the days in the range, we will need to total all the days. This could be done with Excel formulas, but we’d have to copy the formulas down all the rows, and if the data refreshes with more countries the new rows, then we would have to check and copy more formulas. It is much easier to use one of the many Nextanalytics script commands to automate the process.

We can add more script commands, and they will be executed in order, so let’s do a little more analysis so our results are presentation-ready. Since we wanted the top countries, with the largest number of pageviews over the period, let’s sort the new column in a descending order. Now we can easily see the top countries, plus we still have their daily pageview totals.

While this is great for looking at details, there are a lot of countries with small volumes that are just a distraction in our report. It would be nice if we could just see the top 5 countries and group the rest into ‘Other’ for our report. With just a few more analytic script selections, we can easilyget that. Enabling the Advanced Set of Script Commands, an extensive list of commands is now available. We can select a range of rows and total them into an ‘Other’ row, inserted at the top. The number of rows we want to keep is part of the script command, and we can easily change it from 10 (the default) to the 5 that we want in this report. Clicking the Add button copies our modified command to the list to be executed. Many of the script commands can be modified in this way, providing tremendous flexibility.

At this point we are thinking about presentation, and having a total column in the data makes a mess of charts, with one large number followed by a bunch of small ones. Also, it is more common to see the ‘Other’ row at the bottom of a table or chart, so let’s move it down. While we would handle this in Excel’s charting tool, selecting data ranges and series accordingly, we can make the charting task easier by making those changes in the script. We select the total column (the first column) and remove it, and then select the ‘Other’ row (the first row) and move it to the bottom (last) – that was a lot faster and easier than messing with the chart data dialog boxes.

We’re ready for presentation now – we just need a chart and a little formatting. Since we want to be able to refresh this information over time, let’s save the analysis first. We can refresh the data just like it appears, as text and numbers in cells on a worksheet, but that worksheet will be cleared and repopulated every time – destroying any formatting or charting we apply to it. We could set up another sheet with simple formulas pointing to this dynamic sheet, and apply formatting and position a chart there.  This is a common technique for making a ‘dashboard’ type of workbook, where a formatted summary sheet references detailed data on other sheets full of data.

Nextanalytics for Excel has an alternative method, though. We could automatically populate the sheet with function calls that will dynamically update with the latest information – triggering an automatic refresh when you open the workbook. Using this approach, we can format the cells and move the around as desired. All page layout aspects are unaffected by the refresh, so we end up with a dynamic report that can include multiple tables and charts.

For this example, we add a chart above the table and format the table using Excel’s Conditional Formatting. Now we can immediately see that each of our ‘top’ countries had their peaks and valleys over the time period. Some were generally higher than the rest, but others peaked for only a few days…something we’ll have to explain to the boss.

It’s a good thing this report only took a few minutes to produce, and it is refreshable. We have plenty of time to perform a detailed analysis of those spikes in traffic. And for next week’s report, this worksheet will automatically refresh so we can spend our time looking into web visitor behavior and not moving data around and formatting cells.