How to build a NetSuite segment mapping file

Introduction

After you have finalized your new segment structure in NetSuite, the next step in the data migration process is to map the legacy segments to the new NetSuite segments. The file that tracks this mapping is called a segment map file. This document is essential because it determines how your historical data will look in NetSuite. In my opinion, this is the most important step in the data migration process.

In this article, I will discuss the following:

• Mapping segments between systems

• How to prepare a segment map file

• Tips & tricks for making this process as efficient as possible

Mapping segments between systems

The segment structure is the core of the organization's financial reporting function. Setting up the segment structure is one of the first implementation tasks. If you are new to segments, check out my article on segment structure basics for an overview.

Different systems treat segments differently. And, each company can use segments uniquely for their own business needs. For example, pre-revenue biotech organizations use QuickBooks's “Customer” field to track “Program” spending. Two segments always map the same way:

• Legacy general ledger (GL) accounts map to NetSuite GL accounts

• Legacy entity names (i.e., customers and vendors) map to NetSuite entity names

Before you map the individual segment values, you must plan how each segment maps between systems. Below are examples from my clients. As you can see, different clients using the same system used segments uniquely.

Once you’ve finalized how segments will map between systems, you are ready to prepare the segment map file.

How to build a NetSuite segment map file

Before starting, create the NetSuite segment values with your implementation partner. See my article on segment structure basics for an overview.

1. Identify the legacy segment values used within each segment

The first step to building a segment map file is to identify the population of unique values within the legacy segment used in the period you’ll be migrating data. For example, if you bring two years of financial history from QuickBooks Online (QBO), you must identify all the GL accounts used over the last two years.

There are two methods for getting this population:

• Run the segment list - for example, you can run the Account list from QBO to determine the full population of GL accounts. The benefit of this approach is that you’ll get the complete population. But, the downside is that if you have a large population of inactive or unused accounts, you’ll be completing extra work in the mapping exercise.

• Run a transaction list for the period - export a transaction list and use Excel’s remove duplicates function to find the population of used segments. The benefit of this approach is that only used segments will be included on the list. The downside is you might need to run several files if you have a large transaction volume. You also need to consider any opening balance sheet accounts that don’t have activity.

This video shows you how to run a transaction list from QuickBooks Online and get the unique population of GL accounts and entity names.

2. Map the legacy segment values to NetSuite segment values

The next step is mapping each legacy segment value to the corresponding NetSuite segment value. The level of effort in this step depends on the changes you are making to your segment structure. For example, if you made no changes to your chart of account structure, everything will be a 1:1 match. This exercise will be easy. However, this exercise will take more effort if you completely revamp your department structure.

Below are two examples. The first screenshot is an example account-to-account map file, and the second screenshot is an example account-to-class map file:

Example account-to-account map file:

Example account-to-class map file:

Remember - you must include every legacy segment value on the map file. But you don’t need to include every NetSuite segment value. Why? You are moving data from the legacy system into NetSuite. New NetSuite GL accounts won’t be in the legacy system. This is ok. But, if you exclude legacy segment values, the historical financials won’t balance when you attempt to load them into NetSuite.

3. Review, Review, Review

Once the segment map file is complete, it is essential to review the map file with all stakeholders. While making changes after data is loaded isn't impossible, it can be challenging. Most of my projects that go over budget go over budget because clients didn’t review their map files.

4. Use the map file to prepare your historical financials

You are ready to prepare and validate the historical financials with your segment map file. See my article on preparing historical financials and open transactions for the next steps.

Tips and Tricks when Mapping Segments

Here are some tips and tricks to make this process easier.

• Every legacy segment value can only be mapped to a single NetSuite segment value; for example, you can map the “1000 Cash” account to a single NetSuite account. You can have multiple legacy segments mapped to a single NetSuite segment value. See the next bullet. ****

• Consolidate segment values in the map file: if you want to merge legacy segment values, map each value to the same NetSuite segment value. In the example below, we are mapping four legacy travel accounts (airfare, car rentals, tolls, and parking) to a single NetSuite account, “Travel and Airfare.”

• Use the NetSuite internal ID: the internal ID is the best way to import data to NetSuite. The internal ID can never change. If the import uses the segment name, the name string must include all parent values. Any time the department name changes, the map file must be updated. See bullet one in this article to display the internal ID field on every list page in NetSuite.

• When possible, make segment changes in the legacy system: an ERP implementation is an opportune time to update the chart of accounts. When feasible, make the updates in the legacy system to ease the tieout process between systems. QuickBooks Online has a merge feature for segment values. I also have an article discussing how to mass edit transactions in QBO.

• Mandatory fields: if a field was not mandatory in the legacy system but is mandatory in NetSuite, consider creating a general or historical NetSuite segment value like “00 - General.” The reason I recommend this instead of making the field not mandatory for historical imports is that once the field is mandatory, NetSuite won’t let you save the record unless every mandatory field is populated.

• Subsidiary considerations: if you have multiple subsidiaries in multiple QBO accounts, you should complete a separate map file for each unique QBO instance. If you have multiple subsidiaries in one system, special considerations should be taken to ensure each segment value is unique. Also, remember that bank and credit card accounts can only be assigned to a single subsidiary.

Closing

Your NetSuite segment map file is essential to a successful NetSuite implementation. This document determines how your historical financials will be loaded into NetSuite. Care and consideration should be taken to ensure this process is done correctly.