By Hugh O. Stewart  

If you’re planning an accounting software migration, you’ve probably gotten mixed advice about data cleanup.  

Some of the data cleanup tasks finance leaders are tempted to perform before a migration include:  

• Tidying up your active and inactive master lists to reflect your current operations  
• Archiving outdated master data to minimize the volume of data you’ll need to migrate 
• Fixing or reclassifying individual transactions that were entered with errors (e.g. changing dates, reclassifying accounts, or adding dimensions, etc.) 

It’s tempting to approach data migration the way you’d approach moving houses. On the surface, it makes sense to declutter your data as much as you can before migrating to a new platform.  

But accounting data isn’t a garage full of old boxes. Cleaning, archiving, or removing the wrong records can easily turn into wasted effort, duplicated work, or worse, structural decisions that undermine your reporting capabilities in the new ERP.   

After completing more than 2,800 accounting data migrations from legacy platforms into Sage Intacct, we have developed best practices for when to perform each type of data cleanup in order to avoid delays, maintain confidence in the accuracy of your data, and improve overall data quality and consistency. 

But before we share those best practices, let’s make sure we’re on the same page about what causes accounting data to become “dirty” in the first place.  

What Does Dirty Data Mean in Accounting?  

IBM defines dirty data as “information that is inaccurate, invalid, incomplete or inconsistent, making it unreliable for business use.” Sounds simple enough, but in practice, each company has a different threshold for what levels of inconsistencies or incompleteness make data “unreliable for business use.”  

You probably have a wish list of data tidying projects you’d love to take on if you had the time, but unless your data is so corrupted that you can’t run your essential reports, your data is likely clean enough to migrate.  

In general, dirty data breaks down into two categories: minor hygiene issues and critical quality issues. 

Minor data hygiene issues include:  

• Active master lists that haven’t been updated to reflect current vendor relationships, project lists, or item availability  
• Duplicate master data records (like Blue Elephant Cafe and The Blue Elephant Cafe) where both vendor records exist but should be merged into one 
• Transactions that were entered with inconsistent dimensions 
• Inconsistent application of revenue recognition rules around month-end or year-end  

Most companies deal with some of these issues on a daily basis and have workarounds to deal with them. They cause headaches and small variations in reporting accuracy, but they don’t force everything to come to a halt.  

Critical data quality issues include:  

• Master data with no context: You might have the name of a company in your vendor list, but no other information (address, tax ID, phone number, etc.) 
• Voided transactions  
• Exported transactions that no longer sort data into appropriate columns, or are missing transaction lines or include corrupted information 
• Corrupted databases that cause different balances to show up in reports that should match each other 

These issues must be addressed as soon as they are discovered. However, here’s the kicker: most companies have no idea their data has these problems until they begin extracting it from their legacy system in preparation for a migration.  

Data Clean Up Best Practices 

So, how should you approach data cleanup? Which issues should you address before, during, and after a migration? Here’s our approach:  

Avoid Tackling Data Hygiene Issues Prior to Migration  

At Platform Transition, we don’t recommend that our clients do any data cleanup on their own before the start of a data migration project. There are several reasons for this. 

First, many issues related to your data structure can be more efficiently addressed during our data mapping process, so enriching transactions one at a time with missing dimensions or adjusting accounts in your legacy system is an inefficient use of time.  

Second, there’s a risk that you’ll remove data that doesn’t appear important but is critical to completing your migration. This is especially risky during accounting migrations for construction companies who want to migrate their subledgers. Let’s say you have eight years of history in QuickBooks, but you only want to migrate the last three years of your history. If you archive your first five years of history, you might remove some of the master data totals associated with projects that are still relevant to your migration period.  

Lastly, while it’s true that data corruption issues must be addressed before a migration can proceed, we don’t recommend that our clients try to identify these or address them on their own. In most cases, these issues only surface after we extract all of your transactional data from your legacy system and validate that data against the corresponding reports in your legacy system.  If we find any issues, we guide you through steps to identify and fix them before we can move forward with the migration.  

Work with a Migration Expert to Enrich Data During the Mapping Process 

Many data hygiene issues can be addressed during the data mapping step of a migration. Two issues that we frequently address are Master Data De-Duplication and Scaled Reclassification.  

Master Data De-Duplication  

It’s common for accounting teams to end up with duplicate master data records for vendors, customers, or items, especially if they operate multiple entities in individual instances of a legacy system (like QuickBooks), where they will be combining the master data and transaction records from multiple databases into a single instance of Sage Intacct with multiple entities.  

To address this, we offer The Duplicate Suppression System™, a service that helps customers create clean, non-duplicated master records in Sage Intacct. This service uses a proprietary methodology to identify potential duplicates within your data by comparing names, addresses, Tax IDs, and more.  

Scaled Reclassification  

For many companies, the ability to redesign and simplify their chart of accounts and structure their data more effectively is one of the main reasons for migrating to Sage Intacct. We help our clients achieve these goals during the data mapping step.   

We can create new dimensional relationships that allow us to delete or merge obsolete segments, tags, or GL account numbers. For example, if you have five separate Deferred Revenue accounts, each representing a different department, you could merge all of those into one Deferred Revenue account and use the Department dimension in Sage Intacct to track each department separately.  

Delay Transaction-Level Fixes Until After the Migration is Complete  

For many of our clients, our data mapping process addresses their biggest data clean-up concerns. However, some clients still have a wish list of transactions they’d like to change in some way, whether that’s moving them to a different account, changing the date, or enriching them with additional dimensionality. Generally, those clean-up efforts should wait until after the migration is complete. Doing them before or during the migration can violate your validation standard, which jeopardizes your ability to judge the success of the migration as a whole.  

Here’s an example of a common transaction-level fix that you might be tempted to make while the migration is ongoing. Let’s say your legacy software automatically adds today’s date to any transactions that you manually enter. On Feb. 11, you post a transaction to the GL without adjusting the date. Then you begin a migration project. 

Later on, you realize that you meant to backdate that transaction to Jan. 29. Let’s imagine, your migration validation standard is designed around Jan 31. If you’re in the middle of a data migration, adjusting the date on that transaction in the legacy system would violate your validation standard based on a cutoff date of Jan 31.

If your migration campaign period runs from Feb. 1 to the present, changing the date on that one transaction will change how the transactions sum together in the legacy report — but that change won’t be reflected in the target system report (where the already extracted data has been processed and loaded), making the migration appear to have an error. A similar problem will occur if you change a transaction located in the active migration period in the target system before the migration is complete.  

Before Migration, Focus on Data Strategy, Not Hygiene 

Instead of addressing data hygiene issues before a migration, a better use of your time is to think deeply about what you wish you could have reported on in your old system. If you could change anything about the reports you’re getting, what would you change? What insights were you not able to get with your existing data structures? What were your most time-consuming workarounds? How could a different data structure streamline those workflows? 

This strategic exercise will enable you to provide clear priorities to your implementation team, which will help them configure your target system as efficiently as possible to support your reporting needs. 

At Platform Transition, we help accounting teams migrate their historical transaction data from a variety of legacy systems into Sage Intacct. Our 10-step data migration process includes built-in quality assurance steps that identify and correct data quality issues before the data enters your new ERP.  

Schedule a meeting or request a quote to start planning your accounting data migration today.