Or, boy I am I oblivious.

Eric Peterson lobbed this my way a week ago and I just noticed it a few days ago (I’ve been hard at work on a response).

His basic question, if I may restate it, is “What do I think about using a ‘tachometer‘ to display KPI data?”

The short answer is, I love to do this - when it’s practical.

If you work in Excel, it is often not practical because the level of initial work and maintenence needed to operate a custom chart element like this violates the efficiency rule (remember, that in order for reports to be truly effective, they need to be easily repeatable so that they can be delivered efficiently and on time). In other words, I’m lazy and it is, generally speaking, too much work for not enough return.

That being said, I’ve spent the past couple of days approximating the dashboard that Eric refers to (via Dashboard Spy and courtesy of Subaru) in Excel, to see if it could be done - with the proper wherewithal.

Here’s a screen grab of the finished product:

Ok, so it can be done.

Here’s a list of resources you’ll need to pull it off:

• Andy Pope’s Gauge Work Around
• John Peltier’s Speedometer Work Around
• Apps Pro X/Y Chart Labeler
• Excel Camera Tool

Scared yet?

Ok, here’s my file for the above picture, you’re welcome to try it out (no commercial use, please), but I make no warranty as to the accuracy or stability of it.

Long story short, custom dashboard elements like the above are cool, sexy and can effectively communicate important information such as KPIs, but hopefully you have a better platform than Excel to create them in (this is where something like Xcelsius becomes helpful).

It’s not that mysterious…

I was talking with some folks the other day and started discussing the importance of separating (or abstracting) the data in a report from the presentation of data in a report and someone asked for an example.

First, let me describe what I mean by abstracting the data from the presentation. Since I’m a web guy, I’ll give you an example via web publishing. Take this blog for instance, it is run on wordpress and at a grossly understated level, wordpress does for blogs what I meant in terms of report design.

All of the data for this site, this text, images and file locations, etc. are stored in a database (mysql) and then there are templates that query the database for the information that they need to present the data (information) in the way that I have designed it to be presented. In this situation the website data is abstracted from the presentation of said data. Contrast this with a more traditional (ok, antiquated) site that is hand-crafted, page by page, and all of the data is hard-coded into the presentation - which one do you think is more efficient?

Similarly, you can design a report where all of the data is hard-coded into the part/piece/sheet that users are supposed to look at or the data can be stored in a different location and called (or queried) into the presentation template.

There are a couple of big benefits to approaching report design this way.

1. Structuring data for manipulation and collation is often counter-productive to understanding. In other words, setting up the data so that I can easily run calculations and transforms on it is pretty much setting it up as that classic table of raw data that nobody but it’s mother can love and use. And since report design is all about providing understanding, it’s often very useful to keep the data in a seperate location from its presentation
2. Hard-coded data is hard to update. Since reports aren’t truly effective unless you can produce them easily and efficiently on schedule, hard-coding the data into the report presentation always makes that harder. So, if the data is called or queried into the presentation layer and we get our data from a refreshable query we have solved the deliverability issue - it’s now easy to produce the report on time.

Putting this strategy into an Excel context

This is very simple. In most cases, I’ll have a worksheet called (imaginatively enough) ‘Data’ where I store all of the data that I need for the report and then I’ll have one or more worksheets that provide the presentation of the data (e.g. the information and insight that the data provides).

The simplest way to call or reference the data in the data sheet is a simple reference to the datum’s location in the data sheet. There are at least two easy ways to do this.

1. Copy & Paste: Copy the cell in the data worksheet that has the datum I want and then using Paste Special –> Paste Link paste it into my presentation worksheet
2. Write the formula. The above task essentially writes a formula that says ‘this cell equals that cell’. So, I can write the formula in myself. So, let’s say that the datum I want is in cell A1 in the data worksheet. Back on the presentation worksheet in cell B2 I can write the following formula:

=Data!A1

Now, whatever datum is in Data A1 will always show up in Presentation B2

That’s the easy way, but also the most inflexible. I’m going to save detailed explanations and examples for other posts, but if you want to read ahead, using functions like:

• vlookup
• lookup
• hlookup
• offset
• indirect
• named ranges (not a function per se, but used in combination with functions and charts - we can start to get super fancy)

and maybe a couple of others provide you with a great deal of flexibility and you can create quite dynamic and robust report interfaces using them.

So a new analytics product — Crazy Egg — launched earlier this week and true to form, I’m testing it out. It’s a click-event based app that will show a standard overlay, a click ‘heat map’ or just a simple list of links and number of clicks.

The problem (for me) seems to be that no one is clicking on anything on my site (crazy egg has measured just 3 clicks in the last 21 hours).
So, come one, go ahead and click through to my sight and click on a bunch of stuff so I can see how it works! (or doesn’t work, maybe you all are clicking like crazy but it ain’t workin’)

If you’re using it too, let me know what you think by leaving a comment about what you think so far.

Yeah!

I’ve been waiting for someone to do this.

I logged into Statcounter this morning and there’s a new feature - Recent Visitor Map which displays the location of your most recent visitors on a Google Map.

SWEET! Thanks Statcounter!

Google Analytics - it’s free but the most well developed. I can run relatively complex analysis and filtering (or segmentation).

Statcounter - it’s real time, the only one I have that is. Good basic package, don’t know about the premium service. It’s also free and the first one I added so it has the best historical data.

Blogbeat - I even broke down and bought a year’s subscription for a whopping $24 dollars. Why? I really, really like the city cloud and am waiting for Blogbeat to release an API/widget for it.

Feedburner - How else will I know who is reading the blog via a reader subscription?

MapSurface - Just plain fun: Hit ‘alt-x’ to see.

Performancing Metrics - Checkin’ it out but I’ll probably drop it, as it doesn’t seem to be giving me any value where each of the above gives me some incremental value.

Still waiting on Measure Map though, requested an invitation months and months ago.

Why do I bring this up? Well, today is Day 100 for this blog so I’m thinking of publishing a ‘First 100 Days’ analysis and if I do, I will be pulling data from all of them (with the exception of Performancing).