How to Reduce the Risk of Manual Data Prep

Why Manual Processes Aren’t “Good Enough”

While every analyst knows that automation practices lead to better data quality, more accurate reporting, and ultimately the ability for them to focus on more analysis, starting the process to fix or automate data usually brings to light all the dirty laundry that has been tossed aside over the years.

So if the manual processes are working “well enough” and you have great analysts on the job, then the next thought is often “automation can wait.” But this thought process needs to change, and here’s why:

• The biggest challenge with manual data prep is that one or more people must take time from their day to manipulate data to use it in any number of different reports. This also means they are taking time away from analyzing the data and making an informed and timely decision (assuming the data is correct the first in the first place).  This one-two punch costs money on both sides of the equation, because not only are hours wasted on prepping the data, but the opportunity cost of not getting the data in an appropriate time can lead to residual costs down the line.  The former is relatively easy to calculate, but the latter is not.
• If data isn’t widely available and business logic isn’t centralized, teams are reporting on different and conflicting datasets. One department or user might calculate something one way, while another calculates it differently. The lack of a single source of truth leads to distrust in the data and usually leads to more manual manipulation of the data, so users can feel confident making a decision or recommendation. Over time, this typically leads to data silos, and it becomes harder for cross-departmental agreement and collaboration.
• Manual processes inevitably mean inaccuracies or errors in calculations and reporting. Simple mistakes, like a copy and paste mishap or an incorrect VLOOKUP, are compounded over time and can go unnoticed before it’s too late.  Suddenly realizing that the year or quarter is in the yellow or red is never a good conversation to have with the boss.

But the good news is that most, if not all, of these potential risks can be mitigated by automating your data processes as much as possible.

Steps for Automation

• Clean up your data: It might take a little elbow grease up front, but in the long run, it will pay dividends time and time again. Clean data at the source, and put rules and processes in place to ensure data stays that way (as much as possible).  For example, “WI,” “Wi,” “Wisconsin,” “Wisconesn,” and other variations might look accurate to the naked eye, but to report accurately, they all need to be in the same format. Even if just a few values don’t follow suit, it could have adverse effects in reporting.  Define options for fields, ensure numeric fields get numeric values, and make important fields mandatory. Once rules are in place, take the time to clean historical data.  This process might take some time, but you’ll only need to do it once. (Read more about the implications of “bad data” and how to overcome it.)
• Choose the right tools: Throw a rock and you can hit a dozen tools that can help solve these and several other issues with data automation. Which tool is right depends on a lot of different things (see our blog on Software Evaluation & Selection), but the point is, tools can automate cleansing tasks and ensure that inputted data will be in the correct format in the database for accurate reporting.  These tools can perform audit reports to uncover data issues, quickly resolve them, and ensure the same mistake isn’t repeated.
• Centralize the business logic: Similar to automation tools, there are a multitude of different tools and methodologies that can be used to centralize business logic. Whether you invest in a full-blown data warehouse, or use SQL to create the metric, having a centrally stored, single definition of a metric ensures that everyone is using the same numbers in their analysis.  Now, instead of users doing their own analysis, data silos are eliminated, and everyone across the organization is on the same analytical page.  This means no more asterisks next to numbers in cross-departmental meetings and consensus on the metrics that matter the most.
• Empower users to do more analysis. The more user adoption, the more tools and processes can be refined. By giving users the power (and time) to analyze the data instead of taking time to manipulate the data, users can report on the data quicker, adapt to the ever-changing business environment quicker, and ultimately do more with the data.  All the previous steps listed in this article allow for users to do just that, and to do confidently.