The great NetSuite data migration

This blog was originally published by Tribridge. Since then, Tribridge has become the DXC Eclipse practice within DXC Technology.

As with any enterprise resource planning (ERP) migration, you need to perform a data migration to move your old system’s data into NetSuite. It is essential that this migration be done correctly, or your data may suffer from what is essentially permanent damage.

Data scope strategy

As a NetSuite partner, when we bring up data migration scope, many clients’ initial reaction is along the lines of “let’s bring over everything” or maybe “the last five years should be plenty.” Taken in a purely logical point of view, this makes sense. Why would you want to lose data?

The problem is cost. Every single record adds to the cost of migration. There isn’t a big difference between 10 and 100 records, but there is between 100 and 1,000. It adds up fast. A very loose metric might be to consider that every migrated record is going to cost somewhere between 10 and 50 cents to migrate. Some organizations could have more than a million combined records of varying types.  Moving all of that gets pricey!

Historical data

Do you need to know exactly what items clients bought or do you just need to know the dollar amount they spent on orders? Do you need to migrate all your closed opportunities? Do you need to know what you bought from which vendor, when and for how much?

Maybe you do. Companies that are very product-focused may need to report on historical product sales trends, so they need to know exactly who bought what and when.

However, for many organizations, it’s not relevant – or rather, not relevant enough, to justify the cost. For many, some transactional data, such as monthly accounting figures, are sufficient. You really have to decide whether you are going to actively report on that data or not.

Keep in mind that in many cases, your full legacy data can still be parked somewhere if a need ever arises to access it. In cases of NetSuite-to-NetSuite migrations in particular, it’s worth noting that NetSuite offers “archived instances”. These are read-only NetSuite instances with one or two user logins. Other legacy systems may offer options in the same vein. Worst case, keeping everything in CSV files somewhere may be a sufficient option.

Key steps for a successful migration

For every record type you are migrating, you will want to follow these seven steps:

  1. Map legacy system fields to NetSuite fields
  2. Export data
  3. Massage/modify/convert legacy data to conform to expected NetSuite formats
  4. Perform trial import
  5. Adjust and repeat steps 1 to 5, as needed
  6. Fire for effect
  7. Verify and perform corrections, as needed

Data cleanup

Some pick this time to perform a general data cleanup, including de-duplication and removal of inactive records. This is certainly a worthwhile effort, but keep in mind that you’ll be adding additional time and effort to an already difficult process. Be sure to plan for the impact this is going to have on budget and timeline.

Keep points to keep in mind:

  • Always set an external ID, a field specifically used to track the legacy primary key of the record from your legacy system, on your records. If you do not, then you will have no reliable way of matching imported data to your legacy data. This is vitally important to be able to do when you realize there are mapping errors.
  • Map to internal or external IDs when referencing other NetSuite records, unless it is very simple list data. The effort of running vlookups and/or search and replace to convert the values will be worth it.
  • Verify your data thoroughly. If you are not a vlookup expert, stop what you’re doing and become one. Do not proceed with migration unless you can perfectly explain vlookup to a 5-year-old, and then explain it again to a NASA rocket scientist.

Migrating A/R, A/P and financials

I commonly see accounts where A/R and A/P transactions were migrated and then the full balance sheet was migrated, which includes A/R and A/P balances. Of course, that double-booked A/R and A/P, so what do you do? Post a journal to back out the value of the migrated A/R and A/P, right? Wrong.

This is a very common mistake, but why is it wrong?

It’s specific to NetSuite. Journals allow you to book A/R and A/P without specifying an entity that owns that debt. The problem is that NetSuite will show any impact to A/R or A/P on its aging reports. Any amount posted without an owning entity will show up in the “no customer” section. The only way to stop something from aging is to close it. The only way to close something is to apply payment and it is only possible to apply payment to debits and credits that are tagged to a specific entity.

In other words, if you post A/R or A/P without a name, you will never be able to close off that amount and prevent it from appearing on NetSuite’s aging report. Keep in mind that once the period those bad journals reside in are closed, you can no longer set a name on it. Without a name, you will literally never be able to fix your aging report.

Some will then edit aging reports to hide anything going to “no customer”. That will mask future possible postings to “no customer”. You want to see those because that should be a signal to correct these entries. A/R booked to “no customer” is A/R you aren’t likely to know how to collect.

Instead, follow this recipe:

  1. Import your open A/R and open A/P into NetSuite as transactions. These should, minimally, have the proper date, total amount and the proper customer/vendor they belong to.
  2. Pull the trial balance from NetSuite for the month(s) you are planning on importing opening balances from the legacy system
  3. Pull the legacy system opening balance(s) you mean to import. Get that in Excel.
  4. Subtract from the legacy balance(s) the NetSuite trial balance results.
  5. Now import the modified opening balance(s)

This is the proper way of migrating financial data in NetSuite if you want to avoid long-term problems and junk appearing on your aging reports.


Olivier Gagnon is NetSuite Practice Manager for DXC Eclipse

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: