Six Ways To Reduce Your (Inevitable) Data Errors

What are the three most important qualities of a great data analyst?

1. They know their data inside and out
2. They draw useful conclusions from their data.
3. They deliver accurate information

Robin and I have written many articles addressing quality number one (knowing the data), which provides a solid base for a successful career as a CPG data nalyst. I recently tackled the tougher-to-teach skill of drawing useful conclusions. Now, in this post, I’ll address the third item on the list: delivering accurate information.

Great analysts are relentless about quality control. But that doesn’t mean they don’t make mistakes. They do—guaranteed. I’ve been analyzing data for over 30 years (starting as a research assistant in college), and I still make data errors regularly. The question is, who finds the mistakes? You? Your boss? Your boss’s boss? Your (gulp!) client? It’s always better to catch your own errors. Here are some of the methods I use:

1. Be skeptical and dubious about your own work

Don’t assume your numbers are right. Assume they could be wrong and regularly double check things as you work. This mindset will help you avoid most errors.

2. Be extra suspicous of surprise findings

Sales are up 50 percent this month when they’ve never been up more than 5 percent any month in the past year? Time to double check your calculations and confirm there’s not a data error.

3. Don’t type in numbers

Try to avoid manually inputting numbers at any point in the process. For example, when I’m making a PowerPoint chart, I copy and paste from Excel even if it’s just a couple of numbers. Linking can also help reduce data transfer errors though (in my personal experience) linking can also add to data problems so use linking carefully and sparingly.

4. Don’t move data around manually

Just as manually inputting data can introduce errors, so can manually moving data around. For example, if you pull data from some larger system (Nielsen, IRI, retailer POS, whatever) and then realize the columns aren’t in the right order, don’t reorder them manually. Instead, go back, respecify the data request, and let the ideal column order roll in automatically. By the way, I broke this rule the other day and immediately made a mistake—I moved the data without moving the headings, which mixed up retailer names. Luckily, I realized my error because I followed the next rule:

5. Make a habit of cross checking numbers

It may take you a few minutes to figure out the best way to do this. You might have to add an extra step to the process or pull some extra data. But take the time to do it, consistently. I promise you that extra effort will pay off, eventually, in a big way. Here are a few examples of cross checks I use:

• Compare grand totals from one report to another. This cross check works when you’re looking at the same data set through various lenses. For example, one category report shows brand totals and another shows segment totals. But the grand total should be the same on both reports – compare them and make sure that total really does match. Even if the report or analysis doesn’t include a grand total, create one so you can do the check. You’d be surprised how often this simple exercise uncovers an error!
• Make sure custom subtotals add up. Example: You’ve created two custom time periods that divide up a year of data based on when a new product was introduced. Now, as a cross check, add your two pieces together and compare to the 52 week total that’s undoubtedly available from the original data source. Ensure the numbers match.
• Compare current trends to similar numbers from an earlier period or report. Make sure the differences and/or changes are plausible.
• Compare a few data points to the original data. This is key after doing a lot of data manipulation. To make sure you can do this, clearly label and save a copy of your fresh, pristine data before beginning to make changes. Because I am truly distrustful of myself, this is often not even enough for me and I will go back and pull fresh numbers from the master data source to make sure nothing’s been lost along the way.

6. Proofread one more time, preferably the next day

If you can possibly avoid it, never send off a final analysis without taking a break from it, whether it’s a day or a few hours. Your subconscious will work on it in the meantime, and sometimes you’ll see errors you missed or find new implications.

It’s hard to build these methods into demanding timelines—it requires a lot of discipline—but it’s worth it. I’ve improved on this a lot since my early days—when I would write conclusions the morning of client presentations!

What are your tips and tricks for improving accuracy? We’d love to hear them, as would your fellow readers. Please share in the comment box below.

If you enjoyed this article, subscribe to future posts via email. We won’t share your email address with anyone.