Even more Web Analytics Dashboards Fun in Excel 2007!
Update 2010-05-07: If you want to use this dashboard with Google Analytics data, check out "Tatvic enhances immeria's Excel web analytics dashboard".
Attention Internet Explorer users (MSIE)! When clicking on the link to download the .xlsx template, MSIE wrongly try to save it as a .zip file, which it's not. Make sure to rename the file with the proper .xlsx extension (or better yet, switch to Firefox!)A couple of weeks ago I published "Web Analytics Dashboards: fun with Excel 2007", it got on Stumbled and became one of the most visited article. A while back I also published Box plots and whisker plots in Excel 2007 and it consistently receives a lot of views.
So here's an update of Web Analytics Dashboard Fun with Excel 2007!What is it?
I'm glad to release "Web Analytics Dashboard Fun" (Microsoft Excel 2007, 85KB .xlsx), a free Microsoft Excel 2007 template for web analytics professionals. It can be used for learning web analytics, Excel in general or as the basis for more elaborate dashboards. The goal is to use only Excel 2007 features and ease customization of the template and update of the data.Why Excel?
As a teacher of web analytics and a web analyst myself, I noticed people often struggle to define KPIs and get going with dashboards. My clients use different web analytics tools, be it Omniture, Google Analytics, Coremetrics, WebTrends, Xiti or others. Although some of the tools offer very advanced dashboarding and analysis capabilities, there are a number of reasons why you would want to use Excel:- Control the information being distributed
- Publish the information in a format people are used to (either Excel or PDF)
- Avoid granting access to the web analytics tool itself
- Include other sources of data
- Ease "month to month" and "year to year" analysis
- Ease analysis of major segments (such as two countries, two sites, etc.)
- Include the analyst comments and evaluation
Get Web Analytics Dashbaord Fun!
Based on the feedback I got, and some further tweaking to make the dashboard more generic and easier to modify for your needs, I'm releasing "Web Analytics Dashboard Fun with Excel 2007 v1.1". To get started there are just a few steps:- >>> Get the template <<< (Microsoft Excel 2007, 85KB .xlsx)
(Get the Excel 2003 version - with some features disabled)
- Right-click on the S1 and S2 radio button and "Edit Text" to your licking
- Change "Success Event 1" through "Success Event 3" in all worksheets
- Update the data in WebDataS1 and WebDataS2
- Voilà!
Feedback, comments and contribution
One of the goal of immeria.net is to "make it easy to do web analytics" by providing educational ressources, documented processes and tools that are both easy to use and non-specific to a vendor ("product agnostic"). You are free to use this template, modify it and even redistribute it as long as its not for profit. Please send me your feedback and enhancements so I can continue to offer more enhancements and additional tools.
Web Analytics Dashboard Fun by immeria.net is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 2.5 Canada License.
40 comments:
I'm having a though time opening the template in Excel.
Which file is the main file?
Sorry about that, I just modified my post to make it more obvious :)
Would the Sheet also work with Excel 2003? I tried opening it with the Microsoft Converter but had no luck. Maybe you could save it as 2003?
Markus: No, Excel 2003 won't cut it, I'm using many features that only works in Excel 2007 like table references and the conditional formatting.
Stephanie, Thanks a ton... Always still wrapping my head around ways of organizing the multitude of data available :)
Does the UBC tech courses are going through dashboard release?
The UBC courses doesn't cover dashboards creation in this way. Although there are some elements of good dashboard design covered, there is little time for actually hands on experience.
Hi,
it looks great but am unable to open the file... could you please explain (I am blonde...)?
thanks!
WaRfliS: look at the big blue box at the top of the post where it says "Attention IE users", this might help.
Stéphane, this is just awesome. I'm working to tweak it for higher education. I'm giddy with excitement. Seriously. I know, I need to get out more. :)
Thanks.
Shelby
This S/S looks great but my question is "how can i pull the data from webtrends to automatically fill the fields?"
David: each WA solution as it's own way of exposing their data through an API, or some of them have an Excel client tool. However, this is a bit complex to explain in a blog post :)
Would it work as a Google Doc spreadsheet?
Would it work as a Google Doc spreadsheet?
Mark: No, absolutely not. Advanced spreadsheets are still not possible in Google Doc.
is unique visitors sum of new and returning visitors? as per your spreadsheet?
Rehul: yes, Total Unique Visitors = New + Returning
As per google analytics....they don't use that relation to identify unique visitors? can you explain why is that so?
Rahul: new + repeat should = unique visitors... But I also noticed this isn't the case in GA... Why? Simply because GA gives the number of visits from new and repeat visitors instead of the number of visitors! (check the report metrics carefully)
Hi
I love this dashboard however unfortunately I do not have Excel 2007. While I know some of the features are not in Excel 2003, I was wondering if you could save it down to 2003? Any chance of that
Elaine: I have just added an Excel 2003 version (look just under the Excel 2007 one) but there are certainly a number of things that won't work as expected.
Hi
Still playing with this on/off, I have a good start. Part I am not understanding is on the right column, I like the standard deviation display. Would you be able to try and simplify or explain the formula?
=CONCATENATE(TEXT(OFFSET(Lookup!$A$2:$U$2,3,MATCH(Dashboard!$A7,Lookup!$A$2:$U$2,0)-1,1,1),"#,##0"),CHAR(10),TEXT(OFFSET(Lookup!$A$2:$U$2,2,MATCH(Dashboard!$A7,Lookup!$A$2:$U$2,0)-1,1,1),"#,##0"),CHAR(10),TEXT(OFFSET(Lookup!$A$2:$U$2,1,MATCH(Dashboard!$A7,Lookup!$A$2:$U$2,0)-1,1,1),"#,##0"))
I have never used some of these functions...or is there a good resource for me to read and learn.
Thank you
Elaine
Hi
Would you be able to simplify or explain the formula used for the standard deviation on the right? Or is there a reference I can use to read and learn about this? I have never used some of these functions.
=CONCATENATE(TEXT(OFFSET(Lookup!$A$2:$U$2,3,MATCH(Dashboard!$A7,Lookup!$A$2:$U$2,0)-1,1,1),"#,##0"),CHAR(10),TEXT(OFFSET(Lookup!$A$2:$U$2,2,MATCH(Dashboard!$A7,Lookup!$A$2:$U$2,0)-1,1,1),"#,##0"),CHAR(10),TEXT(OFFSET(Lookup!$A$2:$U$2,1,MATCH(Dashboard!$A7,Lookup!$A$2:$U$2,0)-1,1,1),"#,##0"))
Thanks
Elaine
Hello,
Thank you for posting the 2003 version. I downloaded it and all of the cells have #REF! in them. I tried changing the the data in WebDataS1 and WebDataS2 but it has no effect. How can I fix?
How do I import my data into this spreadsheet. Please help
Busca: Look into the other tabs, where the raw data is. Copy & Paste maybe? Or learn how to use data feeds from your web analytics vendor. In either case, using and developing such dashboard requires a fair level of Excel experience...
The spreadsheet looks nice but if it's supposed to be intuitive how to tell it to get info from my GA account, well, I'm afraid it's not obvious to me.
I see the dashboard, S1, S2, and look up, I can right click to change the names, etc. but how do I associate this spreadsheet with my GA info? Or am I missing the point? Thank you
The spreadsheet is not intended to be specific to any tool (ex. Google Analytics). It is up to you to find a way to integrate the data from your tool of choice.
Hint for GA: you can export as CSV directly from GA reports, and there are a number of Excel plugins that will hook you up directly to the GA API (example: Tatvic GA Excel Plug-in, Excellent Analytics)
I am trying to open the 2003 Version.
Template_Dashboard_080811.xls
It bombs out looking for a css file.
Any thoughts as to what I am doing wrong?
Oh my God. If I can get this to work I will have died and gone to heaven! It's a thing of beauty!
Nikki Rae
Great tool! Thanks for sharing Stephane. I'm having trouble getting this to work on Excel 2008 for Mac. Has anyone else tried and succeeded or failed?
To those who asked for integration with the Google Analytics API.
I have made an version integrated with the Google Analytics API. It is however modified a bit since it's made for Analyzing videos tracked with Google Analytics Event Tracking.
Anyway, my solution can be rebuilt to other needs as well (as I have done with Stéphane's splendid dashboard).
Hi
I would need a 3rd dimension (and datasheet).
Not sure how to bring that in a radiobutton function and link the look-up. Anyone out there who tried this? thanks in advance!
NextAnalytics is another program that creates Excel dashboards for web analytics. It works with Excel 2003 as well as 2007!
It`s designed to work with Google Analytics. It`s at least worth looking at since it`s just a 2 meg download and comes free for 30 days.
There`s a really good article about it at analyticsedge.com.
Here`s a link to it.
http://www.analyticsedge.com/2010/05/excel-based-website-visitor-dashboard
Hi Stephane, I would suggest that the conditional formatting for the bounce rate should show an UP arrow if the bounce rate decreases. Otherwise, it might be thought that it's better to have a higher bounce rate rather than a lower one.
Also, is there a reason why the control limits are set at +/-1x standard deviation rather than +/-3x standard deviation as noted on http://en.wikipedia.org/wiki/Control_limits?
Kamal: Yes! Absolutely, the bounce rate up/down arrows should be reversed. That's something I have fixed for the next version of this dashboard (and a bunch of other little things!).
Also, the control limits should be +/- 1.5 times standard deviation, for a total of 3. If you use 3 on each side, you have 99.73% of the data set - basically no outliers (well... 0.27% of the data). By using +/- 1 or 1.5 you will more easily spot outliers.
Note, also, that +/- 3 standard deviations, or Sigma, relates to the notion of SixSigma.
Hi, I love your article! It's EXACTLY what I was looking for. Sadly though, the computers at my office run on MS Office 2003 and the link for your 2003 Excel file link is down. If you could fix it that would excellent!
Thanks!
Hi
I am trying to expand the range of WebDataS1 so that i can include a couplf of extra colunms but i cannot edit the range or insert any new columns within the range as the range is locked??
Can anybody help with this? How can i add an extra couple of columns to the range?
Anonymous: The link to the Excel 2003 version is now fixed.
MissyLemmo - use the choose function instead of if to get more segments on your sheet ... hope this helps
Post a Comment