By Hugh O. Stewart 

Transactional data mapping is one of the most critical steps during an accounting software application migration. When moving from legacy accounting software to modern accounting systems like Sage Intacct, you can’t just export data into a CSV, rearrange a few columns, and then import it into Sage Intacct. It’s not like moving data from an old iPhone to the latest version, or tweaking your Christmas Card address spreadsheet to use Minted instead of Vistaprint.  

There are massive gaps between modern, cloud-based accounting systems like Sage Intacct and legacy accounting systems such as QuickBooks, Great Plains, MIP, and Xero. Sage Intacct uses different data formats, has more complex hierarchical structures, and has more advanced technological capabilities than tools like QuickBooks.  

Transactional data mapping is how you bridge the gap. A comprehensive data mapping exercise will help you identify all the hierarchical structures the new software configuration needs to have in place to receive the legacy data with all of its contextual details.  

What is Transactional Data Mapping in Accounting? 

Data mapping is a step in any kind of data migration process. If you’re migrating from Hubspot to Salesforce, you have to map out the structures Salesforce will need to have in place to hold all of your customer data from Hubspot. Data mapping can also deal with transferring settings, user accounts, or automations. But for the scope of this article, we’re only going to talk about transactional data mapping for accounting data migrations.  

In the context of a historical accounting data migration, transactional data mapping primarily involves the combined processing of two types of data:  

1. Historical Transactions: the business activities that affect a company’s financial position (purchases, sales, bills, adjustments, etc.)  
2. Master Data: non-transactional information that is often shared between business units, locations, departments, etc., that makes transactional data meaningful. It’s all the descriptors that you care about in a transaction, such as Vendor ID (who is sending this bill?), Item (what is the bill for?), and Project (what gig does this bill belong to)?  

In advanced ERP systems like Sage Intacct, master data comes with complex hierarchical structures that must be set up before you can enter transactions that reference those master data elements.  

This is where transactional data mapping comes in.  

Transactional data mapping is the process of looking at all of the relevant transactional data that you wish to migrate and cross-referencing it against the master data (and quasi-master data, if applicable) from your legacy system and determining all the necessary counterparts that will need to be built within the new system.  

How Do You Create a Data Map?  

A data map is a de-duplicated list of all the master data headers cross-referenced from a list of validated transactions for the desired migration period, in a single spreadsheet. Our data mapping process has five steps:  

• Extract the relevant transactional history for the migration 
• Validate the extracted data  
• De-duplicate all the present master data from the validated extracted history  
• Cross-reference master data records found in extracted transactions with the master data lists in the legacy system 
• Define the mapping strategy for data elements to be created in the target system 
• Complete the map 

Let’s dig into each step in detail.  

Step 1: Extract the Relevant Transactional History for the Migration 

The traditional approach to creating a data map during a migration to Sage Intacct is to simply extract the active master data lists (vendors, chart of accounts, customers) from the legacy system and plot them all in a spreadsheet.  

On the surface, this approach may seem sound because it will capture all of the customers, vendors, employees, items, classes and accounts the company is using right now. And those are probably the most important records to migrate to the new system.   

However, this approach inevitably leads to errors when you go to import historical transactions into Sage Intacct. Why? Because active master data lists don’t always contain all of the vendors, items, or projects that are referenced in the transactional data.  

Master data lists are living databases that can be edited at any time. Vendors, items, or projects can be deleted if the finance team decides they are no longer needed — because a project ended, an item is no longer being sold by a vendor, the company is no longer doing business with a vendor, or a hundred other reasons. When a vendor is removed from the list, future transactions can no longer reference it, but historical transactions will still reference it.  

This is why active master data lists are an unreliable source of all the relevant master data that a company needs to plan for when configuring their new system. The raw transaction data is the only reliable source. And at Platform Transition, we go straight to the source.  

To build a transactional data map for a customer, we extract all the transactions they want to migrate with all of their details — whether they live in one Quickbooks file or if they’re spread between multiple legacy systems — and we harvest all the master data elements that are included in those transactions. 

Step 2: Validate the Extracted Data 

After we extract the transaction data, we perform a rigorous validation to prove that the data we’ve pulled is complete and unchanged from when it was inside the legacy system. Additionally, we prove that the extracted data matches the summary reports that the client will ultimately use to validate the results of the migration at the end of the exercise. The traditional approach to data mapping skips both of these validation steps, which leads to many downstream errors during data migrations.

For a historical subledger conversion scope, here’s how that might look with a single vendor. Inside the legacy system, each vendor will have a final balance, representing the sum of all the bills, payments, and adjustments throughout the history of the vendor’s relationship with the company. We will verify that our extracted transactional data — which includes the details of every bill, payment, and adjustment between our customer and that vendor — adds up to that same balance. If it doesn’t, we work with our customer to identify whether the error lives in the transactional data or in the legacy system’s reporting features, and we solve the problem before we move on with the mapping exercise.  

Step 3: De-Duplicate the Extracted Master Data Records 

De-duplication is a critical step during transactional data mapping. 

When we pull master data out of the historical transactions, instead of just extracting the active master data lists from the legacy system, we invariably end up with duplicate entries. Think about it: a single vendor might be referenced in more than a thousand transactions over a two-year period. So, we have to de-duplicate the resulting list.   

After removing all duplicate records from this list, we are left with a single spreadsheet that contains all the master data elements that have ever been referenced in any transaction going back as many years as our customer has asked us to go. We call this the Scrub List. 

Step 4: Cross-Reference Master Data Records in the Scrub List with the Master Data Lists in the Legacy System

Now we are ready to compare the master data records in our Scrub List with the master data lists in the legacy system. There are a few goals here. First, to prevent errors when the historical transaction data is migrated into the target system, we need to ensure that every master data element in the Scrub List will have a corresponding master data element in the target system. Any vendors on our Scrub List that have been deactivated in the legacy system won’t automatically get migrated to the target system. We have to find the deactivated vendor information within the legacy system (or recreate it from scratch, if necessary) and add it to the list of master data elements to be created in the target system. This way, every record that has been identified in the Scrub List will have a full master data record available to be set up in the target system with whatever new ID is chosen for it. 

Second, we also want to avoid migrating master data elements that exist in the legacy system but are no longer needed. A legacy system might have 800 active vendors, but only 500 of them have been referenced in transactions from the migration period. Migrating the remaining 300 vendors would only create clutter in the target system. 

From here, we are ready to present this Scrub List to the customer and engage them in the strategic part of the data mapping process. 

Step 5: Define Your Mapping Strategy

Data mapping isn’t just a complex copy-and-paste job. Once you’ve taken stock of all the master data records that exist in your legacy system for the transactional data that you wish to migrate, you have to make strategic choices about which master data elements to leave behind, which to bring over to the target system, and where to put them all.  

Let’s take the example of a Vendor ID. Some of the available options include:  

• Map the legacy Vendor ID to a brand new target Vendor ID 

 If a vendor doesn’t yet exist in the target system, you can create a new Vendor ID.  

• Map the legacy Vendor ID to an already existing target Vendor ID 

If a vendor already exists in the target system, you can point the legacy vendor ID to its corresponding target ID to merge the two records in the target system. This happens frequently when a company is merging multiple Quickbooks instances.   

In their legacy setup, they have to maintain separate vendor lists for each entity, and they often have many duplicates between those vendor lists. With Sage Intacct, they have the option of sharing Vendor Lists between entities (although they don’t have to if it doesn’t make sense for the way they run their business).  

• Map the Vendor ID to a “historical” dimension ID 

If you know you’ll never do business with a particular vendor again and you don’t want them to use up space in the target system (which can sometimes increase your costs, depending on the pricing structure of the target system), you can plan to move the legacy Vendor ID to a catch-all historical dimension like V-99999 and move the legacy vendor name into a non-reportable field such as a memo field.  

This choice makes sense for teams who want to bring over the transactions from 10 years ago, but only want to recreate all of the master data elements from the past five years. The context around their oldest transactions is still available, if they need it, but they don’t bog down their new system with irrelevant master data.  

• Leave the Vendor ID behind completely 

The last option is to not recreate a particular master data element in the new system at all. This option isn’t practical for all types of master data, but it can be helpful with some dimensions or records that you no longer want to track. If you decide not to migrate that master data element into the new system, the analogous transaction in the legacy system won’t have any trace of that master data element. For example, if you remove a Vendor ID, the migrated transaction will just show up as a credit or a debit with no additional context to tell who it was from.   

Each type of master data has its own matrix of decisions to make, and each decision helps you build a Sage Intacct architecture that actually works for your business. 

I’ve seen customers spend as little as a day and as long as three months on the strategic portion of a data mapping exercise. When you actually look at all the master data types and structures you were using in the legacy system, you may realize that your Sage Intacct configuration needs to be modified to accommodate critical types of data you forgot about in their early scoping meetings with your implementation team.  

But it’s far better to discover the need for those missing structures during the data mapping exercise than it is to discover them via thousands of errors during the import step, or during your first monthly close when you’re unable to produce critical reports. 

Step 6: Complete the Data Map According to Your Mapping Strategy.  

Once the strategic decisions are made, filling out the actual data mapping document is pretty straightforward. It requires no programming skills, and much of it can be done quickly and efficiently with Excel functions such as XLOOKUP. 

Benefits of Transactional Data Mapping  

The transactional data mapping process is a critical step in a historical accounting data migration that provides many downstream benefits.  

Enable an Error-Free Migration of Historical Data  

Without a comprehensive data mapping exercise like I’ve described here, you should expect to encounter hundreds, if not thousands, of errors when you try to load your legacy data into your target system. Properly mapping your data beforehand can lead to an error-free migration.  

Unify Your Master Data Across All Entities 

A data map brings all your master data from all of your separate instances of Quickbooks (or other legacy system) into one master data list. When it’s all in one list, it’s possible to perform an additional de-duplication process to identify spelling inconsistencies that have erroneously resulted in multiple entries for the same vendor or customer. We have a proprietary process for doing this at scale called The Duplicate Suppression System^™.  

Plan Your Configuration of Sage Intacct to Properly Handle All Your Data 

In the traditional approach to data migration, the legacy data is forced to fit into whatever structure the implementation team has set up, instead of the implementation being designed to fit the data. In many cases, this results in the accounting team losing the ability to run certain reports they depend on and having to find workarounds to produce those reports in the new system. 

When data mapping is done early enough in an implementation, the go-forward architecture can be designed to fit all the data the customer wants to bring over from their legacy system, and thus support all of the accounting team’s reporting needs.  

Prevent the Need to Re-Implement Sage Intacct Because of Configuration Errors  

When the mismatch between the structures available in the Sage Intacct architecture and the structures needed in the legacy data are severe enough, the software may be completely incapable of producing the reports you need. You might have to start over and re-implement Sage Intacct (at great expense) to build the necessary structures. A comprehensive data mapping exercise helps ensure that your finance team has all the info they need to make the right decisions about their go-forward architecture and greatly reduces the chances of a re-implementation being necessary.   

Hire An Experienced Data Migration Partner to Guide You Through Data Mapping 

To map legacy accounting data accurately, you need a deep understanding of the business processes behind that data and an intimate knowledge of how both the legacy and target systems handle each data type. 

Platform Transition brings that full picture. Our team has decades of experience with Sage Intacct because we use it to run our own operations and we previously served as outsourced CFOs before focusing exclusively on data migration. We know accounting — especially construction accounting — inside and out, and we know Sage Intacct at a level few teams can match. 

Our mission is to revolutionize data migration and free businesses from the constraints of outdated systems. We offer flat-fee services that minimize the risk to our customers and incentivize us to achieve a perfect migration on the first try.  

Schedule a meeting with us or request a quote to learn more about our approach to transactional data mapping for data migration projects.