Stephen Few, Principal at Perceptual Edge is taking his data visualization courses public! In the past, Stephen has offered his courses to companies and institutions that could scare up enough people to fill the class but now he’s bringing his expertise to the masses.

The workshop will be held in San Francisco from June 5, 2007 - June 7, 2007. Each day dedicated to a different aspect of data visualization.

Day 1: Table and Graph Design for Effective Communication

Day 2: Dashboard Design for at-a-Glance Monitoring

Day 3: Visual Data Analysis for Discovery and Understanding

Download this PDF for a full workshop description and pricing information.

I’ve personally been excited about this workshop since I saw the announcement on Stephen’s blog a few days back and am figuring out how to get there.

If you sign up for all three courses, you’ll receive a 10% discount.

Finally, if you’re a Web Analytics Association member, you can get an additional 10% - look for information in the next newsletter or keep tabs on the Discounts page at the WAA website (members only).

Stephen Few’s 2007 West Coast Business Intelligence Workshop

Avinash posted today on “Leveraging Statistical Control Limits” in web analytics as a way to cut out the noise in your KPIs and therefore being able to focus on the true outliers - data points that are truly worthy and in need of your valuable brain power.

In his post, Avinash challenged his readers to provide an Excel solution since there are no OOTB (Out Of The Box) solutions for introducing constants, statistical or otherwise, into charts - Excel tries to box us into using just vertical and horizontal axes scale controls.

Well, I can’t pass up a challenge like that right?

What do you think of this?

So what’s going on here?

1. Named Ranges - Named ranges are your friend. In this case, I’ve got named ranges for each important series of data, especially my key metric - page views per visit. That way, I can insert a new row of data and my constants (Standard Deviation, Mean, Upper Control Limit, and Lower Control Limit) will update automatically. BTW, the chart uses the named ranges as well so that it will update automatically every time a new row of data is added.
2. The chart is a combination of lines and stacked bars. The stacked bars create the gray biorhythm (normal variation) area. Line charts are used for everything else.
3. For the top 5 outliers (either upper or lower) I’m dynamically adding a data label so that I know, at a glance, the specific values for them.
4. I’ve eliminated all horizontal lines other than my own constants because they are just distracting

You can download the spreadsheet and play with it if you want.

A couple of caveats:

• Named ranges won’t do the dishes for you. In other words, when you insert a new row, you’ll need to copy down the formulae from the previous row
• Excel doesn’t always do a good job of placing data labels so the outlier labels may need to be massaged after an update
• My spreadsheet only uses 1 standard deviation. While 3 might be a six-sigma best practice, three standard deviations would have put the lower control at 0.1 and common sense tells me that it shouldn’t be possible to have an average page views per visit value lower than 1.

I’ve deliberately not gone into great detail on the inner-mechanics of the spreadsheet (not that their profound or anything) because that would make for an impossibly long post - if you have a question please post it in the comments or email me directly (civy at instantcognition.com).

It’s been 43 days since my last post.

I’ve been busy getting/starting a new job.

New jobs are great, but unlike some, a new job and its associated learning curve drive every little thought from my head.

I’m still in information overload but I’m trying to dry-shift the brain in blogging-gear.

I have been paying attention though. Jeremiah, who has been traversing the far east, has left Hitachi for PodTech.net. Jeremiah, through no fault of his own, was one of the bloggers I studied before I launched mine back in March ’06.

There’s been a bit of a kerfuffle stirred up by privacy advocates over behavioral targeting. There is a discussion about it going on over at the Web Analytics Yahoo! Group.

Scoble asked for some new KPIs around engagement, which just drives me nuts because engagement is not a new topic and not limited to “Web 2.0” – we’ve all (probably) been working on engagement a long time and there are KPIs available – I guess either they’re not special enough or “Web 2.0ers” don’t pay attention to web analytics *sigh*.

On a related note, Eric Peterson has kicked off his Web 2.0 Measurement Working Group to start ferreting out if there are any special measures for new media and what technical requirements need to be met in order to support them.

Scoble and Jeremiah talk about “Content Malls”, maybe I’m old school but isn’t that a suburban way of saying ‘Web Ring’?

Finally, the guys over at Juice Analytics report that marketing guru Seth Godin challenges visualization master Edward Tufte on Minard’s chart. That’s just silly.

I just watched Seth’s Preso on Google Video and he definitely doesn’t think that the Minard chart is a good chart – for the ‘lay’ person. I still disagree, the fundamental points of the chart are easily grasped, but since he pays Tufte a back-handed compliment I guess I’ll let it slide. (By the way, it’s an entertaining presentation, no surprise there, but the Tufte bit is in the last section at around minute 17 or so.

Hopefully I’ll have something useful to write about soon – any thoughts or questions that I might address? Let me know.

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?

Ok, so it’s more like what I’ve been keeping my eye on and this is a desperate attempt to make sure that I actually write something this week, but still here are the things that have held my interest.

• Manoj Jasra over at Web Analytics World had four guest posters this week, Avinash Kaushik, Jennifer Laycock, Gord Hotchkiss and Rand Fishkin. They all cover different topics and each one is worth the read.
• Robbin Steif has an excellent post on what web analysts do all day at her Lunametrics blog, so if your bosses, peers, family, or friends have a difficult time understanding what you do - point them to Robbin.
• Both Stephen Few and the guys over at Juice Analytics pointed out this new tool for creating sparklines and bullet charts (among other things) in Excel, so I’ve been playing with it. By the way, it’s called Microcharts by BonaVista Systems
• Of course, the Hewlett Packard BoD fiasco has been absolutely fascinating (and horrifying) - Scoble is keeping tabs on it: Day 1, Day 2, Day 3, Day 4
• Mom’s back to blogging, this time from Egypt where they’re on a 2 year teaching stint - check out the new masthead for the blog which is the view from her classroom!