Success of a business can be made by the quality of its data. Think about it – your data tells the story of your potential to grow, sell, or change. Bad data can start a business down a vicious cycle. When you have bad data in your system it results in invalid reports. Invalid reports cause stakeholders to make decisions based on incorrect data. Poor decisions cost your business money or lost time or worse. With good data, you can make decisions with confidence.
Like any company, the critical data you need doesn’t automatically come in one place – it’s likely spread across multiple systems – some used by sales, others by accounting, perhaps others by your team out in the field. Businesses are wise to integrate those systems so they have the data they need, where they need. But, a key to avoiding bad data starts at the beginning – with a successful integration. When preparing to integrate data, you need to ask yourself if it will be successful simply if it’s imported or if it’s the actual data you want. Bad data does not just mean the data will not import, sometimes bad data can be successfully imported.
Where does bad data come from?
Bad data can come from numerous places. In my experience, a key culprit is simple user error – as simple as typos. We’re only human, there’s no way we’re going to be error-free 100% of the time. I also see businesses whose source data does not match their destination requirements. If you do not take the time to match them up, then there will be issues. Finally, key systems can be out of synch – either from not integrating them or with troubled integrations.
So, do you need to prevent or deal with bad data? Follow my 5 integration tips:
- Ensure your destination has a built-in validation. If it doesn’t, create your own validations.
For example, if your sales team is creating quotes in an online customer relationship system, they could be quoting an item that does not exist in the ERP inventory system. Without data validation, the order could be created to sell an item you do not have to sell. This same scenario could have a quote for a prospective customer, and if the order is sent to ERP, then that customer may not exist yet. Now that order may never show up on reports or windows to be processed and now your “new” customer becomes a “former customer”.
Without data validations, you truly get “Garbage in, Garbage out” syndrome. Even if you KNOW the data is accurate when you first create the integration, so there is no need for a validation to be created, there is no way to ensure future data imports do not bring in bad data. The “I know my data” is an excuse to not build validations, do not use that excuse.
- Ensure your transactions post, not just successfully create.
Without the validations mentioned above, the sales order would be created with the missing item or the missing customer. With this missing data, if we change that order to an invoice we would get an error about bad data.
With integrations, some systems will default data such as distribution accounts, etc. If you also import these distributions, then you need to ensure the distributions are not doubled up. Some of this defaulted data exists on secondary windows, where you open the main transaction window, then need to open child windows to see all data. Verify those windows have all the correct information as well.
- Have a test environment that is an exact copy of your production environment.
Outdated test data can cause “successful” integrations in test but fail in production. “I don’t often test, but when I do it is in production.” No one should ever say this, ever! However, many people do and then wonder how their system gets messed up. By having a separate test environment, you ensure the integrations do not accidentally go into live data. This is the preferred methodology.
This does not need to be a full separate test environment – it can be a test database, company, or organization where the production data is restored on a regular basis. Maintaining a full separate test environment can cause additional costs to maintain, whereas a test location is easier and just as affective.
- Data validations, data validations data validations.
Tracking down error messages given by a data validation is much easier than trying to track down bad data from a “Cannot Post” message. By having or creating data validations, you can stop the bad data from being imported and get helpful messages like “Customer AABBXX Does Not Exist” to know exactly why an error is happening instead of a “Cannot save record” kind of message. So, make sure the data validation error messages are meaningful and informative. The more data validations you build up front ensure bad data cannot be imported. Most of the data validations are found via testing your integration with a lot of data.
- Testing is key.
The key you need to remember is that you can never test data too much. Start with 5 to 10 records, then move to around 100 records, then grow again. By starting smaller you can more easily find the bad data. By testing larger data sets you cover more bad data scenarios.
The more testing you do up front, the fewer issues you run into once you move this into a live production area. It is easier to verify and delete data in a test environment than to fix data you have updated in production. How much more stress do you have if you change every single record you imported at 4:00 pm and need to restore a backup from the night before? TEST! TEST! TEST!
Bad data can cause headaches for any size business. The good news is that doesn’t have to be your company’s destiny. By following the tips I mentioned, you can set your company up to have good data and make great decisions from it.
