Note: This post has moved from Leapthree.com to Ayima as part of the 2018 acquisition.
*** Update – This blog post is the original article releasing the first Free Excel Dashboard Template now called ‘Performance Dashboard’. Downloaded thousands of times, this Free Dashboard has become very popular since its release in May 2011 but we have now updated it to fix few bugs and we also provide 2 additional Free Dashboard Templates – click here to check them out ***
Dashboards are a much maligned tool in web analytics. Many can be only politely described as reports, whereas data pukes is a more accurate term. But I believe there is a gap between these reports filled with data and the minimalistic Actionable Dashboards created by Avinash, a gap that can be filled by useful performance dashboards.
Business owners need information to make decisions. Some of them want to view data, not just be told what the data means and what their decision should be. But web analytics tools contain too much data and end up being useless to the business owner as they can’t get the information they need.
A well designed dashboard meets this need (automated so minimal resources are required to update). It contains only required information so that it is basically a simplified web analytics UI. The business owner no longer needs to use the web analytics tool directly, instead the dashboard provides all the information they typically require. Requests can be escalated to an expert user when necessary who can use the web analytics tool to answer advanced business questions.
These Excel Dashboard templates are designed to provide this level of data for business owners. Up to 12 metrics can be included within each dashboard and these metrics can be analysed using the three basic techniques of data analysis:
- Context – each metric is referenced against a comparison period
- Trend – each metric is trended for up to 15 periods
- Segmentation – up to five segments can be applied to the data
My dashboards follow a basic structure and layout that I have evolved over the past five years. They consist of sets of metrics displaying performance for a current period referenced against a comparison number. Each metric can also be viewed visually trended over time. The % change between current performance and the comparison number is highlighted as either good or bad (green/red) if outside of a certain range.
The dashboards print out nicely (very readable) on an A4 page. To enable a potentially huge level of detail to be accessible through the dashboard, drop-down menus are used to select the segment of data or metric for display (macros must be enabled for these to work). These rely on Excel formulas which were originally inspired by Stephane Hamel with an Excel dashboard template he created.
Setting up the Dashboard
The dashboard comes with a Setup Dashboard worksheet where various elements need to be entered. Once this is done, the button is pressed to set-up the dashboard automatically using a macro. Following the completion of the set-up, unnecessary worksheets can be deleted.
Full instructions on the information required to set up the dashboard are included with each file. The elements are:
- Formatting – a set of eight elements that determines which dashboard is used, the appearance of this dashboard and how it is configured for use.
- Metric Groups – the names for up to four groups of metrics.
- Segments – if the dashboard that allows segmentation is chosen, you can enter the names of up to six segments here.
- Metrics – the names of up to three metrics within each metric group along with the formatting options for that metric and whether good performance is a positive or negative change.
Updating the Dashboard
The dashboard relies on the user to find a method of populating the Data Summary worksheet with the metric values for that time period. Assuming the data sources have an API, I recommend setting up a process whereby another worksheet has the data updated each period and each cell in the Data Summary worksheet simply references the appropriate cell in this data extract worksheet.
The same principle applies if there are multiple data sources, simply set up multiple data extract worksheets. If need be, the data can be entered manually although I highly recommend being creative in thinking of a better solution than this.
Don’t forget to enter the date for this data period, whether the day/month or the end date if it is a weekly dashboard. After that, simply press the Update (Dashboard) button and the dashboard is ready to go.
This template contains a fairly standard layout for a dashboard, covering all the basic information that key stakeholders might require. However they might also want to see funnels, top 5/10 tables or indexed data within the dashboard, still in an easy to read and visual style. This is all possible with customised dashboards.
All the basics of the approach used by L3 Analytics are contained within this dashboard template, the style, the formulas and macros. So if you have someone in-house who is good with design and Excel, they should be able to set up a custom dashboard to your specifications.
Don’t waste time and money searching for the information you need, instead have everything you need at your fingertips when you need it.
Update – Downloaded thousands of times, those free dashboards have become very popular since their release in May 2011. We have now updated them to fix few bugs and we also provide 2 additional free templates.
There are two dashboard templates, the first allows segmentation while the second is simpler without a segmentation option. Additionally, a sample copy of the dashboard containing data is provided for demonstration purposes. All are available in Excel 2007 and Excel 97-03 versions: