Tatvic enhances immeria's Excel web analytics dashboard

Some time ago I released "Web Analytics Dashboard Fun in Excel 2007", a free Microsoft Excel 2007 dashboard example I created as a demo, sample and starter kit for web analytics students and professionals. Some of the features are:

• Current vs. previous periods - month to month, year to year
• % Change in form of Indicators via conditional formatting
• Spark lines
• Control limits and Significance
• Different Sections like "General Statistics", "E-commerce" and "Conversions"
• Automation & Customization
• Data Analysis and recommendations.

I released this dashboard primarily as an example for students enrolled in the UBC Award of Achievement program I’m tutoring as well as the Laval University course I’m teaching. I also use it as a demo in the Roadmap to Online Analytics Success workshops. Based on the numerous feedbacks I got since its release, I realized lots of analysts and agencies are using it as a starting point. Some of them had asked for Google Analytics automation and I referred them to Tatvic GA Excel plug-in. With this enhanced version, Tatvic actually just made this job very easy!

Some of the important features are:

1. Trends and % change indicators: the up/down red/yellow indicators are based on 5% variations of month-to-month or year-to-year values. Those should be updated to your own threshold sensibility and set to their according positive/negative significance. For example, increase in visits is usually a positive thing (green/up) but increase in bounce rate is typically a negative thing (red/up).
2. Segmentation, Automation & Customization: once you understand how to add any metric to your dashbord it becomes very easy to tailor this sample dashboard to your own needs. Adding a new segment is more complex but can be done if you look how they are configured in the spreadhseet.
3. Control limits and Significance: Based on any given metric, the spreadsheet will automatically calculate upper/lower control limits and show them beside the sparkline. Furthermore, the sparkline itself use +/- 1.5 Standard Deviation – a statistically valid way to define the control limits range – and make it easier to spot outliers.

Whilst many used this template as a guide and customized it to incorporate clients’ data to present Monthly Dashboard, Tatvic realized that:

1. As an analyst the amount of time it takes to get large volumes of data counts for a lot and
2. Extracting the data from Google Analytics interface and putting in the back end of template was a tedious and time consuming process. Also copying and pasting data in exact cells was prone to human errors.

Tatvic enhancements to the Excel web analytics sample dashboard

With Google’s release of its API and thanks to the Tatvic Google Analytics Excel Plug-in tool which seamlessly integrates in Excel, the sample spreadsheet was enhanced for:

• Predefined Date range features
• Segmentation Availability via Advance Segments
• Time period Granularity
• Edit Functionality
• Filter and Sorting Functionality
• Higher Data Import Capacity (approximately 10k) in one Query

But one of the most important features which need a special mention is the "Refresh" functionality. With a single click all your dashboard data gets updated much quickly and without much hassle!

Here is the step by step process that you can follow to refresh your dashboard periodically with just a single click:

1. Download the enhanced version of the dashboard (for Excel 2007 only)
2. Download and install the Tatvic GA API Excel Plugin
3. Open the dashboard and login with your Google Analytics credentials into Tatvic GA API Plug-in
4. Go to "Background sheet" and replace the profile name from Tatvic.com to desired one. Follow the same method for start and/or End date and/or advance segment.
   
   
5. Click Refresh and see your dashboard getting update within few seconds!

Your dashboard is ready, you can update it on monthly basis or any intervals required. All you have to do is follow the method described above and the data gets updated quickly. Moreover you can customize it with your own KPIs.

Please share comments / feedback / critique / about it.

Do you have any dashboard tricks to share? How do you think this sample dashboard could be improved?