Juice Analytics

Update (March 2012): Max Rice has generously updated the Excel Geocoding Tool. It is shared on Github here.

I’m happy to announce a few small revisions to our Excel geocoding tool. The tool takes a list of addresses and will look up the latitude and longitude of those addresses. The addresses can then be exported as a Google Earth map.

A user pointed out that the tool wasn’t looking up zip+4 codes properly in Yahoo and this problem is fixed along. Also, I’m sorry to report that Yahoo has lowered their limit of free geocodes to 5,000 per day from 50,000 per day.

Geocoding Tool v3.1.xls

Sometimes the best ideas are the simplest ones. Sparklines are little, word-like graphics. A sparkline can shows a single time-series or the occurance of events. The idea is that as you can pick up the gist of the data in the flick of an eye. This lets you say things like:

The New Jersey Nets have been streaky all year while the Boston Celtics have been the picture of consistency–consistent mediocrity.

A note on interpretation: green upward whiskers are wins, red down whiskers are losses. So how can ordinary business folks make these things? Until now, sparklines have been the domain of programmers and graphic artists.

Thankfully, Bissantz, a German company, had an elegant idea. The created a set of special sparkline fonts and an easy to use tool that you can use to build sparklines in Excel using their fonts. The tool looks like this.

Sample sparklines look like this:

It works in Excel and it really is fun and easy.

If you want to learn more about sparklines and see some beautiful examples; the canonical page for sparkline theory and discussion is here. Edward Tufte provides a chapter on sparklines from his newest book followed by a back an forth discussion with practitioners in the field. Lots of great examples!

Steve Few in his blog on the Business Intelligence Network says:

“Most business intelligence software vendors don’t understand data visualization. Even their basic charting functionality is embarrassing. They ought to be experts in this field, because it is critical to effective business intelligence. They’ll only become experts when their customers stop asking to be entertained with flash and dazzle and begin to demand effective visual analysis and communication functionality that is firmly rooted in an understanding of how people see and think.”

We agreed. I pulled up demo dashboards from a couple of the Google featured advertisers for ’executive dashboard’. Here’s the kind of chart-junky stuff they show off:

Steve isn’t done with these folks. In his white paper called Visual and Interactive Analytics: Fulfilling the Promise of Business Intelligence, he takes the industry to task for its immaturity:

“The BI industry has helped us build huge warehouses of data that we can now access at lightening speeds, but most of us look on with mouths agape, feeling more overwhelmed than enlightened…BI is still a fledgling industry, awkwardly struggling with good intentions to mature beyond adolescence”

That doesn’t stop the enterprise BI vendors from promising the moon. Check out the banner on the top of the MicroStrategy web site:

“All your data” and “better decisions everyday by everyone.” “Imagine”, they say. Yes, you best keep on imagining. How can promises like that not fail to deliver? The fallacy of this approach is wrong on so many levels:

• It assumes you know what specific data you need at every part of your organization to make smarter decisions. It is rare (impossible?) to understand your business that well.
• It emphasizes reporting and scorecards over analysis.
• It suggests having access to more data is better. In most situations, the problem is understanding what the data is saying that is the core problem.
• It puts tools ahead of people.

We are talking with a growing number of business intelligence practitioners that recognize that this industry hasn’t yet cracked the code on how to make value from the data. Hopefully we can help move it forward.

I like the bumper sticker that goes: “Never forget that you are unique, like everyone else.” Most of our clients believe they suffer from the ugliest pile of unmanageable numbers possible. Guess again; you’re probably no worse off than the next guy.

In an attempt to ease your fears of being alone with your data troubles, we’ve put together a list of common data-related issues we see in our client work:

1. No unique identifier. Faced with numerous enterprise systems and any number of customers and employees entering data, many organizations are unable to maintain unique customer identifiers. With unique identifiers, you can match customers across their interactions with the organization; without them, it becomes very hard to get a full view of the customer experience.
2. Blocked by the reporting front-end. Many enterprise systems (CRM, ERP, etc.) do you the “favor” of bolting on a reporting engine. Set up correctly, these tools can be modestly satisfying in their ability to spit out metrics and support basic slicing-and-dicing analysis. However, when you start to ask complex questions or want to dig into the raw data, you find that your reporting engine is more of a door than a window.
3. Too many reports. A big stack of dashboards, key performance indicators, and success metrics is piling up on your desk—and yet you don’t feel like you understand the state of the business. As we pointed out here, too many metrics can mean you don’t fully understanding business drivers, but want to create that facade.
4. Inconsistent data definitions. What does “customers” mean? For marketing, it is the number of people brought in the door. Operations only counts the number of active users. This leads to any number of  unproductive conversations trying to explain discrepancies in the numbers.
5. Messy, unstructured data. Data is rarely arrives in an easy to use form.  Sometimes it is spread across tables in Excel (or worse, PDFs).   Dimensions and measures are poorly labeled and not defined.
6. Access. Getting to the right data can mean a well-formed and informed request to the IT group. When IT and business folks struggle to communicate, data stays locked away. Our friend Dratz writes a great blog that offers nice perspective for business folk like me.

7. Data shmata. Sometimes all the good analysis falls on deaf ears. “Some people in an enterprise apparently aren’t really looking for a single version of the truth. It can be easier for them to work with common assumptions and ’dance with numbers’ during management meetings”, says Bill Hostmann of Gartner.
8. The data warehouse is late to the party. This has to be our favorite. While working for AOL, I watched as on two separate occasions as expensive data warehouses were delivered just as the business changed direction.

Do any of these sound familiar? We’d love to hear your stories of data trouble. Or leave us a comment if you’re interested in our strategies for tackling these problems.

A few other resources on data and business intelligence troubles:

• The Open Source Analytics blog discusses data marts, data warehouses and the related philosophical debates.
• Gartner’s take on “BI’s seven fatal flaws”
• SearchDataManagement.com has a host of articles about enterprise and customer data integration

Sometimes success metrics can create unexpected, misguided, and counter-productive behaviors. I heard a great anecdote recently from a client, Celia. She is a former marketing head at an airline, so she knows of what she speaks:

The other week Celia was rushing to catch a United Airlines flight in Pittsburgh. She arrived late to the gate and found the United employees were intent on closing the door to the airplane ten minutes in advance of the flight. She had to argue to get herself on the flight. At United, it seems, success is measured by the percentage of flights that push-back from the gate before the scheduled time. These employees were perfectly willing to slam the door in Celia’s face rather than face having to fill out paperwork and other repercussions tied to missing the success targets.

No doubt push-back time was considered something employees could control and reasonably correlated with on-time arrivals. In addition, push-back time is straightforward to measure — unlike the seething anger of a customer like Celia.

Last night while watching a cooking show I came across a great example of how to lie with numbers. The show was describing how a vast quantity of a particular candy bar were produced each year. As a point of fact, if those candy bars where placed end-to-end they would reach 12,000 miles, or over “two times around the moon.”

Technically true, but misleading. As my sister quickly pointed out, this moon wrapping means the candy bars would go twice around the surface of the moon, not twice from the earth to the moon. This creates a verbal lie-factor of nearly 99%, which may be a record.

Analytics truism: everyone wants a dashboard (a.k.a. key performance indicators (a.k.a KPIs), success metrics, scorecards). Managers want a barometer of performance, a hammer to use on their subordinates, and a straightforward quantification of their business. Below are a few of the guidelines we use when we take on this task:

1. Actionable metrics. Ask yourself: what would I do if the metric is out of line? Do I have the levers that can impact it? Measures that track final outcomes like revenue or total customers don’t give you much time to react or guidance about what to do next.

2. Less than five. When I first started at AOL, a friend of mine pointed to the dozens of reports flying around the organization and remarked (I paraphrase): “This many ’important’ metrics just indicates that nobody really understands this business.” If you struggle to boil down, you should spend more time defining success and understanding the factors that drive performance.

3. Simplicity over comprehensiveness. We don’t agree with Thomas Davenport’s call for more proprietary metrics:

You know you compete on analytics when…You not only are expert at number crunching but also invent proprietary metrics for use in key business processes.

In our experience, you’re better off if you choose metrics that can be understood outside your corner of the world. One common trap we’ve seen is a desire to create a single comprehensive metric; this metric is often an index that combines a number of factors into an overall measure of performance. The result: numbers that are meaningless without a lot of context and difficulty in interpreting deltas.

4. Presentation matters. Your dashboard should be easy to understand and provide enough data to give your audience context. I’ve seen many dashboards that stubbornly show only the current state of a metric and the change from the previous week. Why so stingy with historical data? At Juice, we always show trending and try to give users a means to “cut” the data – by business line, customer type, month, etc. Check out our template for creating a success metric dashboard (more info below).

5. Evolve to goals. Metrics without goals can be a waste. Unfortunately, getting people to agree to specific targets can be painful. After all, goals start us down a slippery slope toward clear accountability. Here’s what I’ve found works: start by focusing your energy on getting people to buy-in to the success metrics. Get clarity on definitions, show trending, and incorporate them into the organization’s vernacular. Be patient: one day someone will raise their hand in a meeting and ask if there are targets for the metrics. Pretend to act surprised by the cleverness of this suggestion.

The success metrics template makes it easy to quickly put together a top-line report for your organization. The ’data’ worksheet gives you a place to put in your weekly (or daily, monthly, etc.) metrics. Add in dimensions where appropriate. Saving and re-opening the spreadsheet will refresh the pivot tables. Shoot me an e-mail if you have questions.

The Dashboard Spy has a great blog about business dashboards.

One of our current projects it to design or re-design the user interface for the next generation of one of our client’s products. While talking with the designers and engineers, you wonder why the people with the deepest understanding of the product aren’t always the most qualified to design the interface.

The biggest challenge of designing the UI of a complex system is that as you become comfortable and knowledgeable of a design, the harder it is for you to accurately assess how simple and intuitive it is. How do you account for this? By constantly bring in fresh eyes at all aspects of the design process. The longer you’ve been toiling over a feature, the less likely that you yourself notice a glaring design flaw.

Lovely article in the New York Times business section on Sunday. Rite-Solutions, a software company, has built an internal marketplace to allow everyone in the company to invest in good ideas.

At Rite-Solutions, the architecture of participation is both businesslike and playful. Fifty-five stocks are listed on the company’s internal market, which is called Mutual Fun. Each stock comes with a detailed description — called an expect-us, as opposed to a prospectus — and begins trading at a price of $10. Every employee gets $10,000 in “opinion money” to allocate among the offerings, and employees signal their enthusiasm by investing in a stock and, better yet, volunteering to work on the project. Volunteers share in the proceeds, in the form of real money, if the stock becomes a product or delivers savings.

This intrigues me both for its democracy and for the discipline of requiring people with ideas to write a clear, easy-to-read description of their project.

“We’re the founders, but we’re far from the smartest people here,” Mr. Lavoie, the chief executive, said during an interview at Rite-Solutions’ headquarters outside Newport, R.I. “At most companies, especially technology companies, the most brilliant insights tend to come from people other than senior management. So we created a marketplace to harvest collective genius.”

Great ideas are equally possible coming from the bottom up as the top down. In particular, those “bottom” people have a lot of hands on experience with the way things actually work.

“There’s nothing wrong with experience,” said Mr. Marino, the company’s president. “The problem is when experience gets in the way of innovation. As founders, the one thing we know is that we don’t know all the answers.”

I’ve worked at companies where a senior executive is vigorously pursuing an idea that most people know is doomed. No one person is willing to share the bad news, but what if he or she had to confront a slumping share price for their “expect-us”?

Funny thing happened to me last week on my way to an oil change – my car’s engine was destroyed.

It all happened in a blink: I stopped by Jiffy Lube on my way to the bakery and swung up to the garage entrance, first in line. If you’ve ever had an oil change, you’ve probably experienced the old “preventative maintenance” up-sell: a technician pulls you out of the waiting room, gives you a grim, disappointed look, then explains the various parts of your car that are in severe need of service. In the past, I’ve been good at standing up to these automotive authority figures. I’d mumble “no thanks, maybe next time,” not daring to look into the eyes which so clearly said: Don’t you care about your own safety? This time, however, I broke down and gave the go ahead for an engine flush. I was assured that any sane car owner would have this procedure done every 15k miles; here I am at 80k without my first flush.

I knew something was wrong when I saw them pushing my car out of the garage half an hour later. I was assured it was no problem; they just needed to dry off my spark plugs. Two hours later I was calling for a ride.

All of which would have been a small inconvenience if I hadn’t gotten a call the next morning letting me know they would need to replace my engine. Clearly something had gone terribly wrong with that engine flush.

I should say: I have little reason to gripe about Jiffy Lube. They are covering the engine replacement and a rental car. That said, there are a few lessons Jiffy Lube management might take from this situation:

• The edge cases matter. A while back we wrote (here and here) about analysis of anomalies and the opportunity for learning. One point that applies in this case: Collectively, outlier customers provide a service: they stress test the product and highlight unrealized strengths or weaknesses. In its desire to relentlessly upsell, Jiffy Lube has extended its service outside its comfort zone to a point of weakness.
• Data can make you smarter. I had an interesting conversation with the outside mechanic that is installing the replacement engine. He said I was lucky. My engine has a known problem with high levels of sludge build-up. He has seen other instances where an engine can be so full of sludge that an engine flush is incapable of breaking through the muck (like clogged arteries, I imagined) and the result is ruin. I get a refurbished engine with 80% new parts in place of an engine that was like the heart of an overweight cholesteral-holic. Maybe Jiffy Lube shouldn’t be indiscriminantly upselling every customer. It wouldn’t be difficult to build some filters into their system for high risk maintenance.
• Communicate with unhappy customers. Most companies would benefit from a simple alarm system for catching and responding to customers with particularly bad experiences. Something to appease them before they tell all their friends, family, and co-workers about their crappy experience (heck, they might even blog about it). All I ask as a customer is: a) recognize that you have created an inconvenience for me; b) convey that this isn’t a status quo situation; and c) assure me that you will make me whole. Jiffy Lube wasn’t effective in communicating any of these. They had an odd nonchalance that suggested this happens all the time, no single point of contact to speak to, and no apology for the inconvenience.