Data Migration, How Much & How Far Back?

Setting the Scene

As part of any significant CRM or ERP implementation project there will always be some element of data migration. This could be from a well-established legacy application, a spreadsheet or old-fashioned paper.

The extraction, transformation and load process that forms the cornerstone of all data migration activities will not be addressed here in detail.  We will not be looking at the scenarios that create the need for the migration in the first place e.g. business mergers and acquisitions or a greenfield CRM application implementation. In addition we will not be looking at the sequences that data needs to be migrated and the related dependencies.

Instead, the focus will be on the key items and topics that need to be migrated across to the new application.

What to Migrate

There are nearly always four major data types to be addressed when migrating data, these are:

  • Master Data
  • Transactional Data
  • Reference Data
  • Metadata

Some migrations may focus on master data or transactional data; however, it is most common to have all four types involved.

Master Data

Below is a list of the usual suspects typically found in a line-up of master data.

  • Chart of Accounts
  • Service Items or Inventory Item Lists
  • Customer Records
  • Contact Records
  • Employees
  • Supplier Records
  • Project/Job Records
  • Competitors

There is an argument that records like Line of Business, Departments, Subsidiaries, etc. should be classified as master data instead of metadata.  While others feel they shouldn’t be considered as part of data migration and should fall within the realms of configuration.

For the purposes of this, we’ll leave items such as departments out of the discussion.

There’s not much to consider when migrating low volume data such as Chart of Accounts apart from rationalising the data itself prior to migration. This is normally only necessary when the business has historically used detailed account names for each nominal account.

So instead of having 50 different revenue accounts all denoting different items categories, there will only be one revenue account with the item categories being set as values in a separate field/dimension.

The two master data record types that we’ll focus on are the customer records and the item records. The customer record because it’s a guideline for the other master data types such as contacts, vendors and employees. The Item record because it plays a pivotal role in the transaction data types.

Customer Record

When looking at the customer record type the first question that needs to be answered is; is it necessary or even desirable to migrate all customer records in the legacy CRM or ERP application. It is not surprising to find that the answer is invariably an emphatic “yes…. we need everything”.

The business will reason that the data is required for prior period reporting versus current period, marketing purposes or for statutory reasons.

  • Historically the prior period reporting reason as valid a reason as it is has never sat well with some CaEPs, however in this age of machine learning the more data the better so this reason may well withstand scrutiny.
  • The second reason, which is for marketing purposes is another valid reason that has never really been bought into by most CaEPs because it is not what is observed post implementation. What usually happens is that Marketing is either outsourced or a third-party application such as mail chimp is employed to execute marketing campaigns, and only when a cold lead turns into a hot lead is it pushed to the CRM application.
  • Most countries make it a statutory requirement to hold company information for a prescribed period of time this could be five years seven years or more. Although the focus is normally on transactional data it is safe to say that the requirement extends to supporting information as well.

If the business does decide to only bring across a sub section of customer information the decision needs to be made as to which records to migrate and what criteria to use to determine that.

A good approach is to only have customers that have transacted with the business within a specified time frame can be migrated across. But this needs to be aligned with the transactional data as well.

Because, there is no use in trying to migrate three years of transactional data when there’ll only be one year of customer records available.

Ultimately with customer data decision as to how far back to go will be dictated by the question around transaction data. This same logic also applies to vendor records as well as contact records.

Item Record

Item records are generally easy to address, the items that are required to trade are migrated and the ones that are not traded are left in the legacy application.

However, if the business decides to migrate historic transactional information at line level over an extended period of time it is very likely that these transactions will include items that are no longer being sold. The outcome of this is that it will be necessary to migrate old Item records purely for the purposes of supporting historic information.

The same logic applies project /job records, it may be necessary to migrate archived, dormant or inactive projects into the new system purely to serve as supporting information on transactional data that is being migrated.

Transactional Data

There are various forms of transactional data that can be migrated, below is a list of the usual ones:

  • Sales
    • Opportunities
    • Quotes/Estimates
    • Cash Sales
    • Sales Orders
    • Invoices
    • Return Authorizations
    • Credit Notes
    • Fulfillment’s
  • Purchases
    • Purchase Requisitions
    • Purchase Orders
    • Vendor Bills, Credits, and Return Authorisations
    • Inventory Receipts
    • Credit Card Spend
    • Petty Cash Transactions
    • Employee Expense Reports
  • Cash
    • Deposits
    • Payments (customers, vendors, employees)
    • Refunds
  • Others
    • Journals
    • Time Entry

Approaches to migrating transactional data;

Not to migrate transactional data. This can be achieved in certain scenarios, where the business has easy access to the legacy system that doesn’t slow down business processes. It is somewhat controversial and is rarely taken as an option.

Migrate only open transactions with the most summarised line level detail that is possible.  In the case of invoices, these can be migrated across with one line that details the balance that is due to be paid on that invoice. In the case of sales orders, it is necessary to migrate each line accurately as this will hold the necessary information required for shipping and future invoicing.

Migrate only open transactions with full line level detail, this will include quantity, list price, actual price etc. However, some businesses have additional sub-records per line. For example, a magazine company would invoice a corporate customer with the magazine edition being one line item but the corporate customers subscribers for that magazine are listed as sub records to that line item.

Migrate all transactional data. This is a quality control exercise: in that by the end of the process the sales ledger or general ledger will reconcile with the legacy system.  This approach is very labour intensive and not only does it require migrating all the transactional data, it also requires accurately matching transactions against each other in the new CRM or ERP System. For instance, an invoice was paid by two payments and one credit note. In addition, for businesses that trade internationally it would be necessary to consider historical exchange rates.

Reference and Metadata

There is no golden rule as to what reference data or metadata is and the differences between the two. Below are some examples of reference data and metadata to help illustrate that generally reference data is more complicated than metadata because reference data is usually used in some calculation.

Shipping Matrix Customer Category
Revenue Recognition Schedules Payment Terms
Quantity Pricing Schedules Audit Trail Information
Amortization Schedules Expense Category
Billing Schedules Project Type
Sales Quota’s Notes on Transactions or customers
Financial Budgets Meeting, or phone call records

With reference data it makes sense to only migrate active data that will be used going forward. There are no reasonable arguments to be made in the favour of migrating the shipping matrix calculation table from the year 2005.

On the other hand, with audit logs it’s generally not possible to migrate this across into the system.  Today, most systems do not allow for the modification of audit trail records as they are encrypted and/or secured.

Notes against customers, vendors and transactions are records that have value as they provide additional context for the parent record e.g. “Customer is really happy with the product/service and will be sharing their satisfaction with their 60million twitter followers so long as we provide as good a service as last time”. It does make sense to migrate Notes, the key challenge with Notes is usually volume, especially for B2C businesses.

How Far Back

The question as to how far back a business should look at when migrating data is a consequential one. Generally, businesses prefer to migrate everything they have as fast as possible so they can shut off the legacy system.

The problem with this approach is the data migration process is very labour intensive. Even with automation the amount of human interaction required to assert quality is quite high. Because of this high overhead, data migration can unnecessarily prolong the length of any given project. Therefore, unless the business has a big data strategy coupled with machine learning, it is generally best to only bring across the volume of data required for business as usual activities in the first few months post go-live. After everything is settled down the additional activities are undertaken to migrate the remaining data.

In conclusion, there are quite a few items to consider when deciding what to migrate. Each scenario will differ, giving appropriate time to consider these items will make a positive impact on the overall data migration work stream.

What do you think?  Do you see any glaring omissions from this, any different approaches that can be taken or do you completely disagree?

Let us know ⇓

Leave a Reply

Your email address will not be published.