Follow
When I start an analytics project for a new client, I usually get a few laughs when I talk about the need for data cleansing. “We have dirty data? How on earth did we ever survive?”
The truth is that the systems we use to record information often are as imperfect as the people who enter the data and maintain the systems and as such are subject to a variety of data quality problems. Before I build the first graph on a new project, I have to put on my archaeologist hat and figure out how consistent and uniform the data sources available to me are, and then fix any mistakes that might complicate my work.
The quirks in business data that annoy us in our day-to-day operations can make a big impact on the outcomes of analytics projects. If I’m trying to uncover patterns in profitability across product lines over time and somehow we haven’t named the products consistently – or consistently recorded expenses and revenues – then any conclusions I might draw from the data might very well be wrong.
Assessing data quality — and fixing a few common problems — is an essential step in most data analytics projects. Here are a few of the more common data quality problems my team and I tend to encounter.
1. Duplicate Entries and Common Misspellings
Over time, organizations tend to do things such as set up vendors multiple times in our accounting systems. It’s not uncommon for companies to have several dozen entries for “Wal-Mart” in their systems or to add the same contact to Salesforce.com each time a new salesperson meets her.
If systems don’t have very strict controls and manual review processes, it’s easy for things to get out of control quickly. Sometimes that’s OK, but when we want to do things such as calculate lifetime payments to a vendor, we usually have to combine duplicates as best we can.
The good news is there are a number of tools in the marketplace and data mining techniques available to find potential duplicates and correct common misspellings in places such as vendor and customer databases.
2. Misclassified Data and “Rollups”
Our most common analysis task is to try to find categories of “stuff” that are somehow different from other categories of “stuff.” For example, we’re sometimes asked to figure out what’s driving a company’s profitability. That usually requires me to look at sales and cost data and try to find groups of products or expenses that are, on average, different from other groups.
With any luck, the client has a stable product catalog – and everyone there agrees that all of the products have been labeled properly and are all placed in the right categories. Almost always, though, there’s at least one product that’s been placed in the wrong category at some point in the past.
The good news is that these kinds of mistakes are easy to fix in the databases we build during our analytics work, and can often be changed over and over again by analysts without affecting the original computer systems.
3. Numbers That Change Meaning Over Time
It’s not uncommon for us to run across business systems that include data that’s twenty years old and has been moved from one system to the other at least twice – and under the direction of several different managers. When we sit down to do things such as calculating cost trends, it’s essential to make sure that the numbers we need have been recorded consistently over the years, and if not, to correct those numbers in our analysis database.
Of all the types of data quality problems we run into, these are the most difficult to fix and almost always require the involvement of experts who have an idea of how things were recorded over the years and why – and who can make decisions about what should have happened along the way.
DataClear is a Baton Rouge-based data analytics consulting firm. Contact Us for a free 30-minute consultation and discover how your company can profit from data-driven decision making using tools that won’t break your budget.