In his keynote at the inaugural SEMPhonic XChange Conference, Eric T. Peterson said he was going to be controversial, that he wanted to stimulate the discussion. That he was tired of the ‘me too’ and ‘that’s exactly what I think’ commentary. Fast-forward a bit and Eric goes ahead and lobs a controversial bomb my way…

"Reporting Is Evil"

You might as well say Seeing is evil.

Seeing, after all, is just your eyes reporting received electromagnetic stimuli to your brain.

I can understand the sentiment behind it – I really can. It comes from a frustration with organizations and people who think web analytics is nothing more than regurgitation of data. A frustration with people and organizations that don’t "get it" and can’t "grok it". The frustration comes from web analysts within those organizations (I’ve felt it, believe me), from consultants trying to help companies get to the next level and vendors trying to sell their wares.

The problem is that reporting is NOT evil. It is vital to the web analytics process.

Analysis, web analysis, is a process – or at least it should be.

Analyses, like any process, have outcomes. The outcomes might be changes to the website, changes to advertising creative, changes in SEM strategies, changes to the web analytics implementation itself and many more that I’m not thinking of at the moment. One of the over-arching deliverables of a process is communicating its results. If the results of the web analytics process are not communicated, then no outcomes are possible. How can recommended changes to the website be made if they aren’t communicated and substantiated with an analysis of the data?

Yep, that’s my way of saying that reporting is an expected outcome of the analytic process. A process that doesn’t communicate its results is a failed process. Thus, if we excise reporting from web analytics because it is evil, we are left with a process that can give us no insight because there is no way to extract information from it. What is the point of going through the process at that point? We might as well use ‘Pin the Tail on the Donkey’ in all its blindfolded glory to make a decisions.

There are many definitions of reporting, but in this context I think that there are two that are most important:

1. Reporting is a required outcome of the analytics process
2. Reporting is a communication tool

The frustration that leads people, Eric included, into thinking and saying that reporting is evil derives from organizations treating reporting as the whole analytics process.

So, if you are spending time, money and resources on web analytics and all you are doing is reporting the data, then you probably feel like you’re not getting nearly enough value out of your investment.

Heck, without reporting there is no feedback loop in analytics and then your just running one blind test after another – throwing spaghetti at the wall as it were – until something sticks.

Reporting without analysis is just a regurgitation of facts. Analysis without reporting is impossible because then there is no mechanism for creating the feedback loop within the process.

More on color palettes today…

One of the problems with the previously discussed Excel color tip is that custom colors are not particularly portable. In other words, I can easily set up the custom palette on my computer but sharing that with you is a rigamorole. Instead of the color definitions being embedded in the Excel file, I’d have to give you a template file that you could import the palette from (yech!) every time you want to look at the report with my carefully chosen and designed colors.

This led me to the question of, can I store the color definitions in VBA? The answer is, no surprise, YES! I found the following snippet in a thread over on Mr. Excel.

Private Sub Workbook_Open()
Application.ScreenUpdating = False

ActiveWorkbook.Colors(35) = RGB(0, 115, 106) ‘ Teal
ActiveWorkbook.Colors(36) = RGB(255, 255, 153) ‘ Yellow
ActiveWorkbook.Colors(37) = RGB(52, 99, 175) ‘ Light Blue
ActiveWorkbook.Colors(38) = RGB(244, 154, 193) ‘ Pink
ActiveWorkbook.Colors(40) = RGB(255, 204, 153) ‘ Tan

Application.ScreenUpdating = True
End Sub

Yeah! Now my custom colors will load whenever the workbook is opened. AWESOME. Uh Oh, wait, Excel refers to each of the fifty-six colors by an index number and by the way, the index number has absolutely nothing to do with the color’s position in the palette grid (available from the format menu).

Mr. Excel to the rescue again. In this thread, I found a cool little function that lists our colors 1-56 in color coordinated cells.

Cool Little Function: (oops I had the wrong function in there orginally – sometimes I’m my worst editor)

Sub colors()
For i = 1 To 56
With Cells(i, “A”)
.Interior.ColorIndex = i
.Value = i
.HorizontalAlignment = xlCenter
.Font.Color = vbWhite
.Font.Bold = True
End With
Next i
End Sub

The only missing piece is the original RGB value. So, with a little work in Photoshop, I created the following matrix:

The number in the top right corner of each color block represents the Index number that Excel assigns to that color (see what I mean about it not really being arranged in a particularly useful way? It looks like they messed up a couple times and put some of the numbers in order).

In the lower left hand (ish) corner of each block is its RGB value.

The first five rows represent Excel’s ‘standard color palette’ while the sixth and seventh rows represent the colors for chart lines and fills respectively.

Whew! Now I have a reference map for building out that first macro (I know which colors are which in the Excel palette when I change a particular index’s RGB value) and I have that Macro for making the custom color choices portable.

Are there other better solutions for making custom color palettes (in Excel) portable? Let us know how you’ve gotten around this issue

By the way, I am well aware that this tip is another rehash of information found elsewhere – but why should I have to go through another 2 or 3 hours of searching pain if I need this again and more importantly why should you?

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.

At Emetrics, I presented on the topic of report design. For all two of you who read this blog, weren’t there, and are interested, I thought I’d put up the super-abbreviated version.

These are the basic principles that I apply to every report I ever author.

1

Design for Understanding
Reports, among many other important things are shortcuts. The viewer wants the report to ‘cut to the chase’ so that they don’t have to wade neck-deep through the data. So, I’m always looking to present data, KPIs, analyses, etc. in the most easily digestible way.

2

Design for Impact
Designing for understanding is just a means to an end. Our end goal is to get the report users to take action (the brass ring being the correct action). When designing a report, I am constantly looking for ways to make it impactful – ways to get the users to sit up and take notice of the information and insight it is delivering.

3

Design for Efficiency
The most beautifully crafted and fireworks-generating report will ultimately be doomed to failure if you can’t reproduce it quickly and easily. Because our business is iterative (design, test, measure…repeat) it is likely that the majority of the reports and analyses you produce must be updated on a regular basis. Therefore, if you can’t quickly update the data in the report and get it out, you’ll miss deadlines. If the report is a particularly good one and effectively drives change, then it’s actually hurting your business not to get it done on time.

By the way, these 3 rules tend to be interdependent – meaning that making gains on one often leads to losses in one or both of the others. So, it’s a fine balancing act to adequately meet all 3 rules. However, when it happens, you end up with an effective report design.

It might go without saying, but I’ll say it anyway, when you find something that works – a layout, a component, a color, etc – abuse it. Most of us have more work than we can possibly accomplish so we need shortcuts too.