Gilligan on Data by Tim Wilson

On Monday, we covered the first chapter of this bedtime tale of dashboard creation: a cutesy approach that made the dashboard into a straight-up reflection of our sales funnel. Last night, we followed that up with the next performance management tracking beast — a scorecard that had lots (too much) detail and too much equality across the various metrics. Tonight’s tale is where we find a happy ending, so snuggle in, kids, and I’ll tell you about…

Version 3 – Hey…Windows Was a Total POS until 3.1…So I’m Not Feeling Too Bad!

(What’s “POS?” Um…go ask your mother. But don’t tell her you heard the term from me!)

As it turned out, versions 1 and 2, combined with some of the process evolution the business had undergone, combined with some data visualization research and experimentation, meant that I was a week’s worth of evenings and a decent chunk of one weekend away from something that actually works:

Some of the keys that make this work:

• Heavy focus on Few’s Tufte-derived “data-pixel ratio” –- asking the question for everything on the dashboard: “If it’s not white space, does it have a real purpose for being on the dashboard?” And, only including elements where the answer is, “Yes.”
• Recognition that all metrics aren’t equal –- I seriously beefed up the most critical, end-of-the-day metrics (almost too much – there’s a plan for the one bar chart to be scaled down in the future once a couple other metrics are available)
• The exact number of what we did six months ago isn’t important -– I added sparklines (with targets when available) so that the only specific number shown is the month-to-date value for the metric; the sparkline shows how the metric has been trending relative to target
• Pro-rating the targets -– it made for formulas that were a bit hairier, but each target line now assumes a linear growth over the course of the month; the target on Day 5 of a 30-day month is 1/6 of the total target for the month
• Simplification of alerts -– instead of red/yellow/green…we went to red/not red; this really makes the trouble spots jump out

Even as I was developing the dashboard, a couple of things clued me in that I was on a good track:

• I saw data that was important…but that was out of whack or out of date; this spawned some investigations that yielded good results
• As I circulated the approach for feedback, I started getting questions about specific peaks/valleys/alerts on the dashboard – people wound up skipping the feedback about the dashboard design itself and jumping right to using the data

It took a couple of weeks to get all of the details ironed out, and I took the opportunity to start a new Access database. The one I had been building on for the past year still works and I still use it, but I’d inadvertently built in clunkiness and overhead along the way. Starting “from scratch” was essentially a minor re-architecting of the platform…but in a way that was quick, clean and manageable.

My Takeaways

Looking back, and telling you this story, has given me a chance to reflect on what the key learnings are from this experience. In some cases, the learning has been a reinforcement what I already knew. In others, they were new (to me) ideas:

• Don’t Stop after Version 1 — obviously, this is a key takeaway from this story, but it’s worth noting. In college, I studied to be an architect, and a problem that I always had over the course of a semester-long design project was that, while some of my peers (many of whom are now successful practicing architects) wound up with designs in the final review that looked radically different from what they started with, I spent most of the semester simply tweaking and tuning whatever I’d come up with in the first version of my design. At the same time, these peers could demonstrate that their core vision for their projects was apparent in all designs, even if it manifested itself very differently from start to finish. This is a useful analogy for dashboard design — don’t treat the dashboard as “done” just because it’s produced and automated, and don’t consider a “win” simply because it delivered value. It’s got to deliver the value you intended, and deliver it well to truly be finished…and then the business can and will evolve, which will drive further modifications.
• Democratizing Data Visualization Is a “Punt” — in both of the first two dashboards, I had a single visualization approach and I applied that to all of the data. This meant that the data was shoe-horned into whatever that paradigm was, regardless of whether it was data that mattered more as a trend vs. data that mattered more as a snapshot, whether it was data that was a leading indicator  vs. data that was a direct reflection of this month’s results, or whether the data was a metric that tied directly to the business plan vs. data that was “interesting” but not necessarily core to our planning. The third iteration finally broke out of this framework, and the results were startlingly positive.
• Be Selective about Detailed Data – especially in the second version of the scorecard, we included too much granularity, which made the report overwhelming. To make it useful, the consumers of the dashboard needed to actually take the data and chart it. One of the worst things a data analyst can do is provide a report that requires additional manipulation to draw any conclusions.
• Targets Matter(!!!) — I’ve mounted various targets-oriented soapboxes in the past, but this experience did nothing if it didn’t shore up that soapbox. The second and third iterations of the dashboard/scorecard included targets for many of the metrics, and this was useful. In some cases, we missed the targets so badly that we had to go back and re-set them. That’s okay. It forced a discussion about whether our assumptions about our business model were valid. We didn’t simply adjust the targets to make them easier to hit — we revisited the underlying business plan based on the realities of our business. This spawned a number of real and needed initiatives.

Will There Be Another Book in the Series?

Even though I am pleased with where the dashboard is today, the story is not finished. Specifically:

• As I’ve alluded to, there is some missing data here, and there are some process changes in our business that, once completed, will drive some changes to the dashboard; overall, they will make the dashboard more useful
• As much of a fan as I am of our Excel/Access solution…it has its limitations. I’ve said from the beginning that I was doing functional prototyping. It’s built well enough with Access as a poor man’s operational data store and Excel as the data visualization engine that we can use this for a while…but I also view it as being the basis of requirements for an enterprise BI tool (in this regard, it jibes with a parallel initiative that is client-facing for us). Currently, the dashboard gets updated with current data when either the Director of Finance or I check it out of Sharepoint and click a button. It’s not really a web-based dashboard, it doesn’t allow drilling down to detailed data, and it doesn’t have automated “push” capabilities. These are all improvements that I can’t deliver with the current platform.
• I don’t know what I don’t know. Do you see any areas of concern or flaws with the iteration described in this post? Have you seen something like this fail…or can you identify why it would fail in your organization?

I don’t know when this next book will be written, but you’ll read it here first!

I hope you’ve enjoyed this tale. Or, if nothing else, it’s done that which is critical for any good bedtime story: it’s put you to sleep! 

Yesterday, I described my first shot at developing a weekly corporate dashboard for my current company. It was based on the concept of the sales funnel and, while a lot of good came out of the exercise…it was of no use as a corporate performance management tool.

Tonight’s bedtime story will be chapter 2, where the initial beast was slain and a new beast was created in its place. Gather around, kids, and we’ll explore the new and improved beast…

Version 2: A Partner in Crime and a Christmas Tree Scorecard

Several months after the initial dashboard had died an abrupt and appropriate death, we found ourselves backing into looking at monthly trends on a regular basis for a variety of areas of the business. I was involved, as was our Director of Finance. I honestly don’t remember exactly how it happened, but a soft decree hit both of us that we needed to be circulating that data amongst the management team on a weekly basis.

Now, several very positive things had happened by this point that made the task doable:

• We’d rolled into a new year, and the budgeting and planning that led up to the new year led to a business plan with more specific targets being set around key areas of the business
• We had cleaned up our processes — the reality of them rather than simply the theory; they were still far from perfect, but they had moved in the right direction to at least have some level of consistency
• We had achieved greater agreement/buy-in/understanding that there was underlying and necessary complexity in our business, both our business model and our business processes

Although I would still say we failed, we at least failed forward.

As I recall, the Director of Finance took a first cut at the new scorecard, as he was much more in the thick of things when it came to providing the monthly data to the executive team. I then spent a few evenings filling in some holes and doing some formatting and macro work so that we had a one-page scorecard that showed rolling month-to-month results for a number of metrics. These metrics still flowed loosely from the top to the bottom of a marketing and sales funnel:

Some things we did right:

• Our IT organization had been very receptive to my “this is a nuisance”-type requests over the preceding months and had taken a number of steps to make much of the data more accessible to me much more efficiently (my “data update” routine dropped from taking my computer over an hour to complete to taking under 5 minutes); “my” data for the scorecard was still pulled from the same underlying Access database, but it was pulled using a whole new set of queries
• We incorporated a more comprehensive set of metrics -– going beyond simply Sales and Marketing metrics to capture some key Operations data
• We accepted that we needed to pull some data from the ERP system -– the Director of Finance would handle this and had it down to a 5-minute exercise on his end
• Because we had targets for many of the metrics, we were able to use conditional formatting to highlight what was on track and what wasn’t. And, we added a macro that would show/hide the targets  to make it easy to reduce the clutter on the scorecard (although it was still cluttered even with the targets hidden)
• We reported historical data -– the totals for each past month, as well as the color-coding of where that month ended up relative to its target.
• We allowed a few metrics that did not have targets set -– offending my purist sensibilities, and, honestly, this was the least useful data, but it was appropriate to include in some cases.

We even included limited “drilldown” capability — hyperlinks next to different rows in the scorecard (not shown in the image above) that, when clicked, jumped to another worksheet that had more granular detail.

But the scorecard was still a failure.

We found ourselves updating it once a week and pulling it up for review in a management meeting…and increasingly not discussing it at all. As a matter of fact, just how much of an abstract-but-not-useful picture this weekly exercise became really became clear when we got to version 3…and quickly realized how much of the data we had let lapse when it came to updates.

So, what was wrong with it? Several things:

• Too much detailed data –- because we had forsaken graphical elements almost entirely, we were able to cram a lot of data into a tabular grid. We found ourselves including some metrics to make the scorecard “complete” simply because we could – for instance, if we included total leads and, as a separate metric, leads who were entirely new to the company, then, for the sake of symmetry, we included the number of leads for the month who were already in our database: new + existing = total. This was redundant and unnecessary
• We treated all of the metrics the same -– everything was represented as a monthly total, be it the number of leads generated, the number of opportunities closed, the amount of revenue booked, or the headcount for the company; we didn’t think about what really made sense – we just presented it all equally
• No pro-rating of the targets –- we had a simple red/yellow/green scheme for the conditional formatting alerts; but, we compared the actuals for each metric to the total targets for the month; this meant that, for the first half of the month, virtually every metric was in the red

Pretty quickly, I saw that version 2 represented some improvements from version 1, but, somehow, wasn’t really any better at helping us assess the business.

At that point, we fell into a pretty common trap of data analysts: once a report has stabilized, we find a way to streamline its production and automate it as much as possible simply to remove the tedium of the creation. I’ve got countless examples from my own experience where a BI or web analytics tool has the ability to automate the creation and e-mailing of reports out. Once it’s automated, the cost to produce it each day/week/month goes virtually to zero, so there is no motivation to go back and ask, “Is this of any real value?” Avinash Kaushik calls this being a “reporting squirrel” (see Rule #3 on his post: Six Rules for Creating A Data-Driven Boss) or a “data puke” (see Filter #1 in his post: Consultants, Analysts: Present Impactful Analysis, Insightful Reports), and it’s one of the worst places to find yourself.

Even though I was semi-aware of what had happened, the truth is that we would likely still be cruising along producing this weekly scorecard save for two things:

• What was acceptable for internal consumption was not acceptable for the reports we provided to our clients. The other almost-full-time analyst in the company and I had embarked on some aggressive self-education when it came to data visualization best practices; we started trolling The Dashboard Spy site, we read some Stephen Few, we poked around in the new visualization features of Excel 2007, and generally started a vigorous internal effort to overhaul the reporting we were providing to our clients (and to ourselves as our own clients)
• The weekly meeting where the managers reviewed the scorecard got replaced with an “as-needed” meeting, with the decision that the scorecard would still be prepared and presented weekly…to the entire company

So, what really happened was that fear of being humiliated internally spurred another hasty revision of the scorecard…and its evolution into more of a dashboard.

And that, kids, will be the subject of tomorrow’s bedtime tale. But, as you snuggle under your comforter and burrow your head into your pillow, think about the approach I’ve described here. Do you use something similar that actually works? If so, why? What problems do you see with this approach? What do you like?

One of my responsibilities when I joined my current company was to institute some level of corporate performance management through the use of KPIs and a scorecard or dashboard. It’s a small company, and it was a fun task. In the end, it took me over a year to get to something that really seems to work. On the one hand, that’s embarrassing. On the other hand, it was a side project that never got a big chunk of my bandwidth. And, like many small companies, we have been fairly dynamic when it comes down to nailing down and articulating the strategies we are using to drive the company.

Looking back, there have been three very distinct versions of the corporate scorecard/dashboard. What drove them, what worked about them, and what didn’t work about them, makes for an interesting story. So gather around, children, and I will regale you with the tale of this sordid adventure. Actually, we don’t have time to go through the whole story tonight, so we’ll hit one chapter a day for the next three days.

If you want to click on your flashlight and pull the covers over your head and do a little extra reading after I turn off the light, Avinash Kaushik has a recent post that was timely for me to read as I worked up this bedtime tale: Consultants, Analysts: Present Impactful Analysis, Insightful Reports. The post has the seven “filters” Avinash developed as he judged a WAA competition, and it’s a bit skewed towards web analytics reporting…but, as usual, it’s pretty easy to extrapolate his thoughts to a broader arena. The first iteration of our corporate dashboard would have gotten hammered by most of his filters. Where we are today (which we’ll get to in due time), isn’t perfect, but it’s much, much better when assessed against these filters.

One key piece of background here is that the technology I’ve had available to me throughout this whole process does not include any of the big “enterprise BI” tools. All three of the iterations were delivered using Excel 2003 and Access 2003, with some hooks into several different backend systems.

That was fine with me for a couple of reasons:

• It allowed me to produce and iterate on the design quickly and independently – I didn’t need to pull in IT resources for drawn-out development work
• It was cheap – I didn’t need to invest in any technology beyond what was already on my computer

So, let’s dive in, shall we?

Version 1: The “Clever” Approach As I Learned the Data and the Business

I rolled out the first iteration of a corporate dashboard within a month of starting the job. I took a lot of what I was told about our strategy and objectives at face value and looked at the exercise as being a way to cut my teeth on the company’s data, as well as a way to show that I could produce.

The dashboard I came up with was based on the sales funnel paradigm. We had clearly defined and deployed stages (or so I thought) in the progression of a prospect from the point of being simply a lead all the way through being an opportunity and becoming revenue. We believed that what we needed to keep an eye on week to week was pretty simple:

• How many people were in each stage
• How many had moved from one stage to another

We had a well-defined…theoretical…sales funnel. We had Marketing feeding leads into that funnel. Sure, the data in our CRM wasn’t perfect, but by reporting off of it, we would drive improvements in the data integrity by highlighting the occasional wart and inconsistency. Right…?

I crafted the report below. Simply put, the numbers in each box represented the number of leads/opportunities at that stage of our funnel, and the number in each arrow between a box represented the number who had moved from one box to another over the prior week.

High fives all around!

Except…

It became apparent almost immediately the the report was next to useless when it came to its intended purpose:

• It turned out, our theoretical funnel really didn’t match reality – our funnel had all sorts of opportunities entering and exiting mid-funnel…and there was generally a reasonable explanation each time that happened.
• There were no targets for any of these numbers – I’d quietly raised this point up front, but was rebuffed with the even-then familiar refrain: “We can’t set a target until we look at the data for a while.” But…no targets were ever set. Partly because…
• “Time” was poorly represented – the arrows represented a snapshot of movement over the prior week…but no trending information was available
• Much of the data didn’t “match” the data in the CRM – while the data was coming from the underlying database tables in the CRM, I had to do some cleanup and massaging to make it truly fit the funnel paradigm. Between that and the fact that I was only refreshing my data once/week, a comparison of a report in the CRM to my weekly report invariably invited questions as to why the numbers were different. I could always explain why, and I was always “right,” and it wasn’t exactly that people didn’t trust my report…but it just made them question the overall point a little bit more.
• I had access to the data in some of our systems…but not all of them; most importantly, our ERP system was not something that had data that was readily accessible either through scheduled report exports or an ODBC connection; and, at the end of the day…that’s where several of our KPIs (in reality…if not named as such) lived; back to my first point, there were theoretical ways to get financial data out of our CRM…but, in practice, there was often a wide gulf between the two.

As I labored to address some of these issues, I wound up with several versions of the report that, tactically, did a decent job…but made the report more confusing.

The sorts of things I tried included:

• Adding arrows and numbers that would conditionally appear/disappear in light gray that showed non-standard entries/exits from the funnel
• Adding information within each box to indicate how it compared to the prior week (still not a “trend,” but at least a week-over-week comparison)
• Adding moving averages for many of the numbers
• Adding a total for the prior 12 weeks for many of the numbers

All told, I had five different iterations on this concept — each time taking feedback as to what it was lacking or where it was confusing and trying to address it.

To no avail.

Even as I look back on the different iterations now, it’s clear that each iteration introduced as many new issues as it addressed existing ones.

Still, some real good had come of the exercise:

• I understood the data and our processes quite well -– tracking down why certain opportunities behaved a certain way gave me a firehose sip of knowledge into our internal sales processes
• With next to zero out-of-pocket technology investment, I’d built a semi-automated process for aggregating and reporting the data –- I had to run a macro in MS Access that took ~1 hour to run (it was pulling data across the Internet from our SaaS CRM) and then do a “Refresh All” in Excel; I still had a little bit of manual work each week, so it took me ~30 minutes each time I produced the report
• I’d built some credibility and trust with IT –- as I dove in to try to understand the data and processes, I was quickly asking intelligent questions and, on occasion, uncovering minor system bugs

Unfortunately, none of these were really the primary intended goal of the dashboard. The report really just wasn’t of much use to anyone. This came to a head one afternoon after I’d been dutifully producing it each week (and scratching my head as to what it was telling me) when the CEO, in a fit of polite but real pique, popped off, “You know…nobody actually looks at this report! It doesn’t tell us anything useful!” To which I replied, “I couldn’t agree more!” And stopped producing it.

A few months passed, and I focused more of my efforts on helping clean up our processes and doing various ad hoc analyses –- using the knowledge and technology I had picked up through the initial dashboard development, most assuredly…but the idea of a dashboard/scorecard migrated to the back burner.

Tomorrow, kiddies, as I tuck you in at night, I’ll tell the tale of Version 2 — a scorecard with targets! As you drift off to sleep though, ponder this version. What would you have done differently? What problems with it do you see? Is there anything that looks like it holds promise?

Wow, has it ever been a while since I got a new post up. Three-part excuse: 1) Vacation that was wayyyy offline, 2) Vacation was proceeded by a crazy week on the road for business, and 3) I’ve been working on a post that’ll turn out to be three separate posts. Stay tuned for that, as it’s my assessment/lessons learned from iterations over the course of a year on a corporate dashboard.

Immediately upon returning from vacation, I had a customer data management experience that highlighted just how bad we are as marketers at successfully tracking our customers and keeping our information on them current. Since we were gone for 10 days, we had our mail stopped. I returned home several days before the rest of my family, which meant I was on hand when the post office dropped off the bin of accumulated mail. As I sorted through it, I wound up with a surprisingly large stack of mail that was not intended for anyone in our house.

The next day — the first day that only a single day’s worth of mail came — I did a little analysis. We got seven pieces of mail (pictured at left). Of those, only three had totally correct information on the address:

• One piece — was for the prior owner of the house; we’ve now owned the house for a year (side note: the prior owners were on the opposite end of the political spectrum from us, and, sorting through the 10 days of mail, the ratio of literature the prior owners received from “their” Presidential candidate outnumbered the literature we received from “ours” by 3:1)
• One piece — included my wife’s middle initial…but it was not the correct middle initial
• Two pieces — were addressed to the wife of the couple we bought our last house from…in another state…five years ago!

We’ve gotten to the point, I think, where we just accept this. The problem is that we’re starting to get too smart for our own good. Sending mail to someone who no longer lives at the address has always happened.

Having a minor data entry issue — mistyping a middle initial — is going to happen any time there is human involvement (we can trace back the fact that I still occasionally get mail for “Jim Wilson”, rather than “Tim Wilson,” to a single phone company screw-up shortly after we got married a decade-and-a-half ago).

What was really interesting, though, was what happened when companes tried to address the first issue — identifying when people moved — and generated the last issue. In my wildly-not-statistically-valid anecdote of a single day, trying to “fix” the first issue generated twice the misdirected mail.

What I’m Sure Happened

We bought a house in Austin five years ago from the couple who built it and lived in it for 25 years; in that time, they had gotten embedded into countless systems with that address. We continued to receive a steady stream of mail for them for the entire time we lived in that house, and it was not all junk mail by any means. To make things a little tougher on the senders, we shared the same last name with the prior owners. At some point while we owned that house, some of those companies undoubtedly implemented some sort of customer data integration system that, undoubtedly, hooked into some external data sources to try to sniff out when their customers moved. The problem? Much of their data was already outdated…and they didn’t have a way to identify which data that was.

So, when a “relocation” was picked up — our sale of our house in Austin and the purchase of our house in Ohio — all of the identified “residents” of the Austin house were “moved” with us.

The key takeaways from me for this — and both are really of the “keep in mind” variety — are:

1. Your customer data is always much dirtier than most people in your company assume it to be. A key role for the data analyst is to have a more realistic understanding and be the voice of reason when it comes to requested analysis projects or the planning of marketing campaigns that rely on the data being cleaner than it actually is
2. The only real “solution” to this issue immediately dives into 1984-like paranoia — a single (or just a handful) of universal “profiles” that the customer maintains and that other systems can reference so their data stays current. OpenID is a move in this direction…but sidesteps the paranoia by being simply an identifier (OpenID itself doesn’t store any information about you — your name, social security number, address, friends, etc.). The issue almost seems intractable — any movement towards a universal identifier equates to twice as much ratcheting up of privacy concerns

It’s not pretty, is it?

I’ll put my standard big, fat, hairy disclaimer here that this blog is not about Excel tips. There are lots of resources for that. As a matter of fact, the Contextures blog is one that I stumbled across after Debra commented on my last Excel tip.

Nevertheless, here’s a handy one that requires no customization of Excel, but that I guarantee you’ll be hooked on if you start using it: <F4>

<F4> and <Ctrl>-Y do the same thing, actually, and they work in MS Word, too. What do they do? Pretty simple:

Repeat the Last Action Taken

That’s it. If you’ve just formatted a cell or set of cells with a new border and background color, then you can click on a cell and press <F4> and it’ll apply the same formatting to the new cell. And then do it again! In this case, it does the same thing as the Format Painter…but does it faster (with limitations, as described below).

If you’ve just inserted a row and you want to insert another row lower down on the spreadsheet, highlight the next row and press <F4>.

As you can imagine (if you stop and try to imagine it…and I recognize it’s got to be a pretty bleak day of creativity for this to bubble up as worthy of your imagination), this is particularly handy when doing some oddball work on non-contiguous cells.

This is handier than you might think. And, it does have it’s limitations. The main one is that it only repeats the immediately preceding action. In the Format Painter example above, <F4> is no use if you have a cell already formatted as you want and you want to copy that format to other cells. That’s what the Format Painter is for.

And, another limitation is that it doesn’t work with every possible action. For instance, if you type a value into a cell and then want that same value in another cell…<F4> doesn’t work. You’ll have to copy and paste. It becomes pretty intuitive in a hurry as to where it works and where it doesn’t.

Happy repetition!

Yesterday, I wrote about my beef with the popular cliché that “ROI for social media is Return on Influence.” This latest take was prompted by Connie Bensen’s ROI of a Community Manager post that has some great thoughts when it comes to measuring the value of social media.

As I put in my last post, quantifying the results of your social media investment is a worthwhile endeavor. Mapping those results to business value can be tricky, but it’s important to make the effort. As I implied yesterday, a Darwinian Take on Business says that the key decisionmakers are probably pretty sharp about the business they’re helping to run. They’re probably not sitting back and making every decision based on a simplistic ROI calculation. Talk to them about the business when you’re talking about social media.

Connie’s post has a pretty great point to start with this exercise. And, at the risk of exhibiting excruciatingly poor form blogging-wise, I’m just going to repeat it here. This is Connie’s list of the ways that investing in social media can provide value to the company. The investment can:

• Humanize the company by providing a voice
• Nurture the community & encourage growth
• Communicate directly with the customers
• Connect customers to appropriate internal departments
• Ensure that messaging will connect
• Build brand awareness through word of mouth
• Lower market research costs
• Add more points in the purchase cycle
• Provide support to customers that have fallen thru the cracks
• More satisfied customers because they’ve been involved with product development
• Shorten length of product development cycle
• Build public relations for brand with influentials in the industry
• Identify strengths & weaknesses of competitors
• Collaborate & partner with related organizations
• Provide industry trends to the executive level

Which of these resonate the most with you as something that your company values highly or that your company is struggling to do effectively? How do you know that? Are there anecdotes that are widely circulated? Are there metrics that get shared regularly to either illustrate how important the area is to the company…or how much of an uphill battle the company is facing?

Start there. Don’t jump from what you come up with on that front to “…and here’s what we’re going to measure.” Start there and then develop a social media strategy (read more of Connie’s blog…and Jeremiah Owyang’s…and Chris Brogan’s…and Geoff Livingston’s…and others for tips on that). From that strategy, you can then develop your measures — the way you’re going to assess the value of your social media efforts.

Photo courtesy of cambodia4kidsorg

I’ve taken a run at this before…but my assertion that the emperor has no clothes didn’t stick. Either that, or the dozens of people who read this blog simply agree with me in principle, but don’t really think it’s worth the effort to raise a stink.

Regardless, I’m not quite ready to let it go. And I do think this is important. Connie Bensen’s recent post (cross-posted on the Marketing 2.0 blog) on the subject had me cheering…and crying…at the same time!

Maybe it’s because I’ve had the good fortune to know and work with some incredibly sharp CFO-types in my day. Most notably, for my entire eight years at National Instruments, the CFO (not necessarily his official title the whole time, but that was his role) was Alex Davern — a diminutively statured, prematurely white-haired Irishman who arguably knows the company’s business and market as well or better than anyone else in the company. He is a numbers guy by training…who gets that numbers are a tool, a darn important tool, but not the be-all end-all.

I had to sit down with — or stand up in front of — Alex on several occasions and pushinitiatives that had a hefty price tag for which I was a champion or at least a key stakeholder — a web content management system, a web analytics tool, and a customer data integration initiative. I never had to pitch a social media initiative to Alex, and I don’t know exactly how I would have done it. But, I seriously doubt that I would have pitched that “ROI is Return on Influence when it comes to social media.” I can feel the pain in my legs as I write this, just imagining myself being taken down at the knees by his Irish brogue.

Here’s the deal. Let’s back up to ROI as return on investment. Return. On. Investment. It’s a formula:

Both numbers have the same unit of measure — let’s go with US dollars — so that the end result is a straight-up ratio. Measured as a percentage. This is a bit of an oversimplification, and there are scads of ways to actually calculate ROI. A pretty common one is to use “net income” as the Return, and “book value of assets” as the Investment. With me so far? You acquired the assets along the way, and they have some worth (let’s not go down the path of that you might have spent more…or less…to acquire them than their “book value”). The return is how much money they made for you.

Now, let’s look at ROI as “Return on Influence” (I’ll skip “Return on Interaction” here — I can get plenty verbose without a repetitive example):

Hmmm… The construct starts to break down on several fronts. First off, you’re going to have a hard time measuring both of these in like units. That’s sorta’ the point of all of the debate on ROI — “influence” is hard to quantify. But, that’s not actually the main beef I have on this front. At the end of the day, your return is still “what value did we garner from our social media efforts?” Maybe that isn’t measured in direct monetary terms. But, really, is this whole discussion about mapping the level of Influence to some Return, or, rather, is it about assessing the Influence that you garner from some Investment? A more appropriate (conceptual) formula would be:

But, IOI, as pleasantly symmetrical as it is, really doesn’t get us very far, does it? So, let’s go back to Alex as a proxy for the Finance-oriented decision-makers in your company. You have two options when making your case for social media investment:

• The Cutesy Option — waltz in with an opening that, frankly, is a bit patronizing: “What you have to understand about ROI when it comes to social media is that ROI is really Return on Influence rather than Return on Investment”
• The Value Option — know your business (chances are the Finance person does); know your company’s strategy; know the challenges your company is facing; frame your pitch in those terms

Obviously, I’m a proponent of the second. I don’t really have a problem with starting the discussion with, “Trying to do an ROI calculation on a social media investment is, at best, extremely difficult and, at worst, not possible. But, there is real value to the business, and that’s what I’m going to talk about with you. And, I’ll talk about how we can quantify that value and the results we think we can achieve.”

Connie’s post has a great list to work from for that case. But…more on that in my next post.

Oh, yeah. the picture at the beginning of this post. And the title. Susan Powter, people! Stop the insanity!!!

For a variety of reasons, I find myself using Excel (2003) through remote desktop fairly regularly these days, and I just haven’t gotten around to setting up some of the basics that I’ve got set up on my main system. The big one is my setup of <Ctrl>-<Shift>-<V> as Paste Special»Values.There are scads of “Excel Tips”-type sites and blogs, and I’m not putting myself out there as an expert. Really, just looking to share one of my handy favorites with my readers, who are mostly somewhere within a standard deviation or two of my Excel skill level and might find this useful.

Backing up just a little bit. Paste Special is really, really nice to have when you need it. Specifically, pasting values, formats, formulas…and occasionally Transpose. But…mostly (for me) pasting values. When I’ve got a well-formatted table of data and need to move some data around, it’s just annoying to need to then go and fix the formatting. So, pasting formulas only or values only avoids all that. The problem is that the fastest way to do this is:

1. Copy the cell(s) you want to relocate (<Ctrl>-<C>)
2. Right-click anon the cell in the new location
3. Select Paste Special
4. Select Values
5. Click OK

All in all, not too painful…unless you find yourself needing to do it two or three times in a row (between separate workbooks, for instance).

This got annoying enough to me a several years ago that I recorded a macro and dropped it in Personal.xls so that I’d have a faster way to do this. It’s now the first thing I set up on any new computer I get.

The Result: After copying cells (this doesn’t work with cutting data), simply click on the cell where you want the values pasted and press <Ctrl>-<Shift>-<V>. That’s it.

How to Set It Up

This may look like a real hassle. It really isn’t (those four years as a technical writer tend to make my procedure writing a bit…er…detailed). But, it’s a one-time setup, and it really isn’t that bad.

If you’ve read this far and aren’t thinking, “MAN! That would be HANDY!” then just bail now. Otherwise, read on:

1. Launch Excel 2003
2. Select Window»Unhide
3. Select Personal.xls
4. Select Tools»Macros»Visual Basic Editor. This should bring up the VBA editor
5. Select Insert»Module
6. Copy and paste the following into the window:Sub PasteSpecial_Values()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
7. Click on the X to close the Visual Basic Editor (you don’t need to save anything yet). You should be back on the Personal.xls workbook
8. Select Tools»Macro»Macros
9. Select PasteSpecial_Values
10. Click Options
11. Click in the Shortcut key box
12. Press <Shift>-<V>
13. Click OK
14. Click the X to close the Macros window
15. Press <Ctrl>-<S> to save Personal.xls
16. Select Window»Hide to hide Personal.xls
17. Close Excel. If you are prompted to save Personal.xls, do so.

You should be set. Let me know if you give it a shot and find it useful (and if you hit any bumps in implementing it).

I’ve written about baseball before, and I’ll do it again. My local paper, The Columbus Dispatch, had a Sunday Sports cover page two weekends ago titled Going Deeper - Baseball traditionalists make way for a new kind of statistician, one who looks beyond batting averages and homers and praises players’ EqA and VORP. The article caught my eye for several reasons:

• Lance Berkman was pictured embedded in the article — hey, I’ll always be a Texan no matter where I live, and “The Big Puma” has been one of the real feel-good stories for the Astros for the past few years (I’ll overlook that he played his college ball at Rice, the non-conference arch nemesis of my beloved Longhorns)
• The graphic above the article featured five stats…of which I only recognized one (OPS)
• The article is written around the Cleveland Indians, who have one of the worst records in major league baseball this year

With my wife and kids out of town, I got to head to the local bagel shop and actually read beyond the front page of the paper, and the article was interesting. The kicker remains that the article leads off by talking about two members of the Indians front office: Eric Wedge is a traditional, up-through-the-ranks-as-a-player baseball guy; Keith Woolner has two degrees from MIT, a master’s degree from Stanford, and a ton of experience working for software companies. The article treats these two men as the ying and yang of modern baseball, pointing out that both men have experience and knowledge that’s useful to their boss, Indians GM Mark Shapiro.

The problem? The Indians stink this year.

Nonetheless, there’s a great quote in the article from Wedge:

“What I think people get in trouble with is when they go all feel or all numbers. You have to put it all together and look at everything, then make your best decision. You can’t have an ego about it.”

The same holds true in business — if your strategy is simply “analyze the data,” you don’t really have a strategy. You’ve got to use your experience, your assessment of where your market is heading as the world changes, some real clarity about what you are and are not good at, an understanding of your competitors (who they are and where they’re stronger than you are), and then lay out your strategy. And stick to it. The data? It’s important! Use it while exploring different strategies to test a hypotheis here and there, and even to model different scenarios and how things will play out depending on different assumptions about the future. But, don’t sit back and wait for the data to set your strategy for you.

Once you’ve set your strategy, you need to break that down into the tactics that you are going to employ. And the success/failure of those tactics need to be measured so that you are continuosly improving your operations. But don’t get caught up in thinking that the data is the start, the middle, and the end. If it was, we’d all just go out and buy SAS and let the numbers set our course.

So, what about the goofy acronyms in the title of this post? Well:

• VORP (Value Over Replacement Player) — a statistic that looks to compare how much more a player is worth than a base-level, attainable big leaguer playing the same position (Berkman had the highest VORP at the time of the article)
• EqA (Equivalent Average) — think of this as Batting Average 2.0, but it takes into account different leagues and ballparks to try to make the measure as equitable as possible
• FIP (Fielding Independent Pitching) — this is sort of ERA 2.0, but it tries to assess everything that a pitcher is solely responsible for, rather than simply earned runs

The fact is, these are good metrics, even if they start to bend the “it has to be easy to understand” rule. In baseball, there have been a lot of people looking at a lot of data over a long period. My guess is that there were many fans and professionals who realized the shortcomings of batting average and ERA, and it was only a matter of time before someone started tuning these metrics and looking for new ones to fill in the gaps.

At the end of the day, the Indians stink. And it’s a game. And there are countless variables at play that will never be fully captured and analyzable (the same holds true in business). Mark Shapiro will continue to have to make countless decisions based on his instincts, with data as merely one important input. Maybe they won’t stink next season.

And…it’s the monthly installment of “Don’t These People Know that Wednesday Comes After Tuesday?” Also known as “Web Analytics Wednesday (on Tuesday) in Columbus.” This month’s event was graciously sponsored by Coremetrics.

We had a record turnout (um…by one), with participants from Victoria’s Secret, DSW, ECNext, ForeSee (all the way from Motor City!), Lightbulb Interactive, Highlights (current and former), Resource Interactive (current and soon-to-be former), Nationwide (former and soon-to-be-again), Franklin University, and, of course, Bulldog Solutions.

This month’s topic was “Social Media Tools for Web Analysts.” As usual, the presentation/handout was quick, and the more interesting part of the evening was the various side discussions that the discussion spawned. Several active Twitter users were in attendance: @bigbryc (who, apparently, I inadvertently “outed” as a Twitter user to some of his co-workers after last month’s WAW), @reubenyau, and @tgwilson (me).

The discussion centered around the various social media tools/sites that have web analyst-oriented activity. Presented from the perspective of…me, so by no means all-encompassing, and not really intended to be. We (mostly) steered clear of “social media measurement,” and we definitely steered clear of “leveraging social media as a marketing tool for your company.” The list of sites/tools and how/where I’ve seen them being used by the web analyst community is available in this Excel 2003 spreadsheet. I’ve tagged the sites/tools that, personally, I am a regular user of, as well as some of the sites/tools that I am likely to become a regular user of in the near future (or really ought to be a more regular user of) — print/print preview to see the two footnote indicators and what they mean.

It’s not comprehensive…and, yet, it’s longer than it really ought to be. I picked up a tip on Google Notebook, so I need to check that out.

I can’t figure out exactly how to work a couple of notes into this post, so I’ll just drop them in as non sequiturs:

• Scott Zakrajsek was temporarily possessed by evil aliens recently. In reality, he always has and always will think that Coremetrics is the greatest web analytics tool on the planet
• The soon-to-be-traditional Monish Datta direct reference so he can pop up on his friends’/co-workers’ Google Alerts…

As always, it was great to see the regular faces, great to see a few new faces, and we missed some of the regular faces.