Data prep should not be a one-time exercise

Intelligent organizations realize that data preparation should not be a one-time exercise. Here's the story of one organization that didn't get it.

More than 15 years ago, I hung my own shingle. For several reasons in the early aughts, though, I took full-time positions. For instance, I bought my first home in 2004 – warranting the stability and benefits that come with a proper salary.

I worked briefly at one large retail organization. Let's call it SBM. The company's internal systems and data were, quite frankly, a mess. I interviewed at SBM before taking the job and, to be fair, my potential colleagues and boss openly copped to that fact. The company wanted to fix things, something that (I thought) meshed well with my mind-set, interests and skills.

I took the job.

I soon realized that things were much more toxic than I thought. Case in point: Running biweekly payroll in Canada and all 50 states was a constant struggle and routinely involved punch cards. (No, I'm not kidding.) Moreover, data flowed inconsistently from many different places into multiple systems.

The end result was chaos: Employees were often paid incorrectly. Some received far more vacation and sick pay than owed. (Good luck getting that back.) Other times, stores hired holiday workers as new employees when they had in fact worked for different SBM stores before. This required extensive manual cleanup.

In a way, this was old hat to me. I had seen many of these problems before in spades. Still, on those ERP and CRM consulting projects, my clients in theory envisioned a future in which they could easily handle basic blocking and tackling. By contrast, it was evident to me that SBM just accepted this type of anarchy as the status quo, even though it amazingly ran systems capable of dramatically simplifying things. Put differently, it was able but unwilling to make things better. Scrambling was the cultural norm, and the only question was whether I could live with constantly applying Band-Aids.

Nowhere was SBM's data preparation more lacking than in the "strategic" report I had to cobble together every month. This was the definition of insanity. In a nutshell, I had to extract data from SBM's systems and manually enter key numbers into a mega-spreadsheet.

The process took me three full days – and I knew how to automate and auto-populate databases and spreadsheets. Even worse, because of a chronic stream of retroactive transactions, I still couldn't accurately answer basic business questions. I was dismayed.

Knowing that this made for bad business decisions, I approached my boss and suggested better ways to disseminate information:

Why not retire the spreadsheet from hell and replace it with a number of separate, far more manageable reports?
Better yet, why not let HR and line managers run these reports themselves via self-service? (SBM already possessed that capability and technology.)
Why not move away from static reports? Why not build interactive tools that let managers to some extent fish for themselves?

To make a long story short, my manager said that things wouldn't change on her watch. Either get with the program or move on. I moved on.

(Interesting postscript: I found out a few years later that SBM's CEO resigned after an internal probe. It turns out he had violated internal policies for securities trades. Not that I'm not implying cause and effect here.)

Simon Says

The moral of this little yarn: data preparation should be an ongoing effort. Band-Aids, workarounds and other one-time fixes hamper an organization's ability to make real sense of its data.