Fulfillment Doctor: The Art of Data Conversion

Jul 18, 2007 8:22 PM  By

Question: We are in the process of planning our file conversion as we implement our new order management system. Our vendor is telling us that they normally don’t write a file conversion program for most files. What’s your recommendation?

Today’s comprehensive order management systems perform integrated functionality for order entry, customer service, order processing, warehousing, marketing, and merchandising. There are literally hundreds of tables and files in these systems that have to be converted or built from scratch manually. These range from promotional tables and shipping tables to the more complex customer files, item masters, and purchase order files.

For smaller businesses, the better approach is to minimize the number of file conversion programs. For larger companies (those with tens of thousands of customers and products) the approach will need to combine manual builds and file conversion programs. Converting years of history often results in significant file integrity problems because the data is not consistent over long periods of time. Needless to say, conversion then takes many more passes through the data, and it may still not be totally corrected.

In addition, conversion programs take time to write and test. Many of the new systems files and tables can be set up faster manually than the time it takes to write programs and convert files. Setting up files has also proven to be a good way to train departmental users in what the new system will require in terms of maintenance. Going through the process gives you familiarity with the new system at a detailed level. If you attempt to convert all files, the users will take much longer to understand what the system requires.

For larger businesses, it may be more compelling to look at programming a larger share of the file conversion. But we would still advise that this should not be taken to extreme.

Here are some guidelines to follow:

  • Don’t underestimate the time that will be required to develop specs, convert program and test files, and use copies of subsets of the live files in training.
  • Avoid trying to convert too much data by machine—too many years back. How much history do you need to convert?
  • Look at using your marketing service bureau as a source of hygienic customer data. Get them involved with the file conversion early to see how they can assist you. If you use them, you’ll end up sending them the files once the conversion programs are tested, several days before the go-live. This will ensure that you’ll have an updated, hygiene customer data file. Merge/purge to eliminate duplicates just before the conversion. Address correction and NCOA would also be performed.
  • Take into account the data file problems that multiple years of data may include—system-created problems, changes in coding of transactions or tables, etc.
  • Consider the amount of time required to make the file conversion during go-live. Obviously, you don’t test with the live data file. Test initially with a copy of selected records from the files that illustrate as many conditions as you can identify. Then, do a conversion volume test to see how long the actual file conversion will take. This is especially crucial with large files (e.g., customer and item master files) being loaded to a relational database. What is the minimum that you need to convert for start-up or go live? Can you convert the remaining after go live?
  • Schedule sufficient time to review data. You can’t look at every record, but you need to sample the converted file enough to know that the file conversion programs are working correctly. Departments should all be involved in reviewing samples in the files they use. If you only review a few accounts, you are taking a high risk.
  • Plan out the final days of the conversion. You will need to begin the file conversion a few days in advance of the go-live date. Most businesses cannot shut down during the file conversion, so you need to figure out how to update the key files during the go-live. How will you continue to process new customer orders and returns, add new products, etc.? You may need to go back and update those files during the go-live.
  • Can you keep your old system operational for some period of time to answer inquiries and compare records? Remember, a high percentage of inquiries and complaints occur in the first 90 to 120 days after the sale or return, and then inquiries drop off quickly. Does all customer data for the previous 10 years need to be on the new system? But for marketing purposes you don’t want to lose customer purchase activity and promotional history.

Build vs. convert

In our consulting practice we look at each company’s file conversion and its file data objectively. But here are some generalizations about the types of files and whether they should be built manually or converted:

  • Files that are typically converted with programs include: customer files, item masters, customer order and return history, inventory files, purchase orders, subset of item master for WMS system, item locations.
  • The majority of files and tables are set up manually by user departments. These include: promotions, source codes, sales tax, shipping and handling, files that govern business rules (system control values that determine the functions of the system), open orders (keying the data gives you experience with order entry and all the order coding), general ledger chart of accounts, merchandise hierarchy (division, department, class), employee files.
  • Accounts receivable files could go either way—build or convert.
  • There are some types of files—like the historical promotions—that aren’t converted. The results may be sent to a data warehouse, a spreadsheet, or a marketing database.

Curt Barry is president of F. Curtis Barry & Co., an operations and fulfillment consulting firm, that uses best practices to recommend and implement order management systems for multichannel businesses. For more information visit www.fcbco.com