With a new baby, you come to appreciate the small inventions that make a big difference. The Baby Bjorn, the Diaper Genie, the baby monitor: these are simple, well-designed technologies that can save your sanity.

Which got me to thinking: What are the Diaper Genies for data analysts? What are the little tools that solve nagging problems in a light, simple, intuitive way? I put together a starter list below of a few of my favorites. The thing that's amazing about these tools and tricks is that they have changed my usage behaviors. By simply eliminating a few clicks or keystrokes, I work differently and more efficiently.

• MWSnap is a bit of freeware by Mirek Wojtowicz that lets you easily capture images on your screen. Pre-MWSnap, I often found myself using the Windows Ctrl-PrtSc function when making presentations. Then I would have to chop down the full screen capture in an image editing program. This application lets you grab just the part of the screen you need, then copy and paste it right into PowerPoint.
• Keyboard shortcuts in Excel. Keyboarding both saves you time and expands your ability to work effectively with large data sets. There are two kinds of keyboarding that we teach: 1) using the Alt key with letters to navigate the menu structure (e.g. Alt-i-r to insert rows, Alt-e-s-v to paste special). 2) using the Ctrl and Shift keys with the arrows to move around and grab blocks of data. See Chris' keyboarding game. Everyone rolls their eyes when we harp on keyboarding as an essential element to being effective with Excel; I've seen too much value from the skill to care.
• Excel Pivot Tables."What's easy about PivotTables?!," you say. Admittedly, this tool doesn't exactly fall under the category of simple and quick to learn. In fact, we are still looking for an intuitive way to teach Pivot Tables. Here are a few good tutorials I've come across. In my experience, you have to put up with a short and mildly painful learning curve; it's worth the trip. I've seen many analysts who still rely on vlookup functions when a simple Pivot Table would let them manipulate their data far more quickly. We are developing a post that offers our tips and tricks on working with Pivot Tables.
• Voice IM (e.g. Google Talk, Skype). There is a form of conversation that requires less focused discussion than a phone call but more verbal interaction than instant messaging. This is where the voice talk features on many IM clients become valuable. Often Chris and I will open a Google Talk call while we work on something together. Long silences suddenly feel acceptable since it's free.
• Windows Alt-Tab. This key combination gives you the ability to flip between applications and can be a huge time saver. I've found it especially useful when I'm pulling data from application and dropping it into another.
• Faster web browsing with Firefox. Mozilla's Firefox browser touts its security, pop-up blocking, and extensions as differentiators. The features I care about are those that help me manage the information better. Ctrl-f gives a 'find' box in the bottom of your browser where you can start typing instantly. It jumps you right to the part of the page with your word/phrase. Tabbed browsing and the ability to jump between tabs (Ctrl-PgUp or -PgDn), close tabs (Ctrl-w), or open a new tabs (Ctrl-t) lets me work with half a dozen web sites at the same time.
• Google Desktop. The new version has a feature where you can press Ctrl twice and it pops a search box. Not only is this a better tool for finding files than Windows offers, but it can quickly find applications. You may never have to go to that Windows Start button again!

We don't claim to be productivity gurus like Merlin Mann of 43 folders or the folks at Lifehack, but I think you'll appreciate how these tools seem to scratch an itch then mercifully leave you alone. They give you a sense of control that is about the opposite of MS Word deciding how your bullets should look.

A reader wrote to point out a small oversight in last week's introduction of the Excel Chart Cleaner tool.

Installed your chart fixer upper tool...and it seems to have been installed because it shows up checked in my add-ins list. But how do I actually USE it? If I have a sheet of data and make a line chart out of it...where is the button to press to clean it up with your add-in? Maybe I'm too much of a neophyte to be reading your blog and trying this stuff...but maybe you could let people know how to actually use this excellent tool you've provided instead of just installing it.

Thanks so much for putting this stuff out there. I learned more about Excel reading your blog than in several paid training sessions. Worth the price of getting out of bed today!

So, you're one of those sneaky people that actually wants to USE the tool. Hmmm.

Well, here's the secret: the add-in puts a menu item in the Format menu. If you have a chart already selected it will say "Clean this chart..." otherwise, it says, "Clean all charts...". Select the option and you'll get a number of ways to clean and simplify the chart.

Thanks for the kind words. There's a screencast coming on how and why to use the Chart Cleaner tool.

Welcome to the Juice Analytics blog. We've gotten quite a few new visitors from Chris' podcast discussion with Jon Udell. If you are new, thanks for stopping by. Our blog covers a range of topics, from data visualization to Excel and PowerPoint tips and tricks to our business analytics approach. Here's a brief "best of" list to help you get acquainted:

• Excel tools and tips: Fixing Excel charts; Excel as a BI tool; Problems with Excel charts

• Fun with mapping: Google Earth census heatmaps; geo-coding tools in Excel and Python; and our compilation of resources for using Google Earth

• Better presentations: PowerPoint pile o' ideas; presentation checklist; an approach to developing well-structured presentations

• Visualizing data: Dynamic data; Sparklines; Lessons from Tufte

• Analytics in theory: Misguided BI theories; Success metrics; Common data issues; What is analytics?

• Analytics in practice: Jiffy Lube; Zillow; Budget Rent A Car; Top blogs for data analysis types

• Juice Analytics: If you want to learn more about us, check out our consulting approach and case studies.

Thanks for visiting; we love to hear from our readers so send us an e-mail or leave a comment.

Andy Hayler from Kalido is a great read for a no-nonsense perspective on business intelligence. He wrote recently.

"Meanwhile, a survey of 1,000 UK business managers at companies with over 250 staff, published by ICS, indicates a widespread need for better BI systems. The study found that over three quarters of respondents were forced to make decisions 'blind' due to late or insufficient business information". By contrast, this is entirely believable, though not for the reason that the article gave. The critical issue is that you can have as many pretty reporting tools and dashboards as you like, but you need accurate and timely information to feed those systems coming from a data warehouse (unless you are one of the few brave souls using EII). The problem is that most data warehouses are entirely unable to keep up with the pace of business change (reorganisations, acquisitions etc) and so are constantly out of date. Consider a data warehouse with just ten source systems. A major change in one of its sources will impact the warehouse schema, and may take three months to fix the schema, the load routines and the reports that are impacted by the change (this is a pretty typical figure in my experience at Shell).

A major change of this type does not happen every day, but is almost certain to happen once a year to each of these source systems, maybe twice. There are then ten sets of separate changes, each taking three months worth of changes needed to the warehouse every year. Even assuming that the changes are neatly spread over the year and that you have plenty of programming resources to fix the changes, so you can do these in parallel, you still have 15 months of change to fit into 12 months; basically the warehouse can never catch up. You may well have more than 10 sources for your data warehouse, so the problem could be even worse than this. This is indeed what happens in reality: the data warehouse is usually out of date, so armies of Excel jockeys in finance get the answers via email and have to manually number-crunch for anything really critical while the warehouse lumbers on with out of date information. This situation is not the fault of the BI tools—it is the fault of the data warehouses that feed the BI tools. Until companies admit that the status quo is failing and start abandoning custom-build warehouses this problem will persist. It is like with treating alcoholism: the first step is admitting that there is a problem.

We've long been skeptical of top-down analytics that try to centralize a businesses information and knowledge. Andy's post highlights one problem with this approach - change happens. Here's another problem - you don't know what you don't know when you build a warehouse.

In other news, I've cut my six degrees of separation to Bill Gates down to two. More later.

It's always better to suggest a solution than criticise. Recently, we've slung stones at Excel's default charts. The default colors are bad, and some of the built-in charts are "what were the smoking?" ugly.

Edward Tufte and others provide principles for making good infographics, but beating Excel's rusty butterknife into an explanatory sword is hard. People who want to make nice looking charts waste time fixing them up. People who don't care about making nice looking charts inflict those charts on others.

We have a solution. The "Clean Charts" tool turns hard-to-read Excel default charts into Tufte-compliant wonderwerks in a single click. Here's what it does:

• Removes "chart-junk" (the contrast-reducing light grey background on most Excel charts, extraneous lines)
• Formats the axes with easy to read numeric formats (22000 becomes "22k")
• Changes series colors to an optimally chosen set that are designed for maximum contrast and readability
• Removes 3D from the chart. 3D charts introduce distortions that make it hard for people to understand your numbers.
• Fixes axis scaling problems.
• Fixes font and marker sizes to make them readable if you have resized your chart

To try Clean Charts and install it, download both these files into the same directory. Then open the Clean Charts Installer.xls file with macros turned on. Follow the instructions inside the installer.

CleanCharts.zip

To turn macros on, go to Tools, Macro, Security. Select Medium security level. Close the workbook and re-open it. On re-opening, when Excel gives the security warning that asks if you want to enable macros, choose "Enable Macros".

The add-in puts a menu item in the Format menu. If you have a chart already selected it will say “Clean this chart…” otherwise, it says, “Clean all charts…”. Select the option and you’ll get a number of ways to clean and simplify the chart.

This project is offered under the MIT License.

I attribute my intolerance for graphics ugliness (see our post on Excel colors) to being the son of an artist/photographer. Growing up, this meant kitchen sinks full of oil paint brushes and new works slowly taking shape in the studio.

His works are figurative and whimsical at times. Or in his words: "The iconography I use centers around people because that's my greatest interest: how people think and feel as individuals, how we interact with each other intimately and as a society." An original mash-up artist.

My dad has just launched Potato Hill Artworks, an online store to sell fine-art limited edition prints of his oil paintings. Check it out if you're looking for something unique. A few of my favorites:

• Madman at the Barbeque
• That's Entertainment
• Our Son, the Astronaut
• The Choice

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.