Start by deciding which source is the truth for what
Suppose, as an example, a building-supply distributor keeps customers and open orders in its main system. Prices are in a spreadsheet the sales manager updates each month. Orders from before the current system exist only in a database backup. What suppliers actually charged is on their PDF invoices.
Some facts appear in more than one place, and the copies disagree. A customer's address in the backup is five years old. A price in the spreadsheet differs from the price on last month's invoice. Before anything is combined, write down which source wins for each kind of fact.
| Kind of fact | Source that wins | Why |
|---|---|---|
| Customer contact details | Main system | Staff update it every day |
| Current selling prices | Price spreadsheet | The sales manager maintains it |
| Orders before the current system | Database backup | It is the only record |
| What a supplier charged | Supplier invoice PDFs | The invoice is the legal record |
With that table agreed, a conflict is settled by a rule the business chose, not by whoever happens to run the import.
Step 1: Extract each source as it is
Take a copy of every source and leave it unchanged. Do not tidy the spreadsheet by hand first, and do not delete the odd rows in the backup. Hand cleanup cannot be repeated, and nobody can later tell what was changed or why. Keep the originals so every record in the new system can be traced back to the file it came from.
PDFs and odd file layouts are where this step usually stalls, because each supplier formats its invoices differently. When a file arrives in a layout the agents have not seen, Buffaly writes a new reader for it and uses it in the same session. We consider this key to how Buffaly works. The Buffaly article Buffaly Builds Its Own Tools reports an analysis of 1.2 million messages and over 380,000 tool calls in which 70% of newly created tools were used in the same session they were written.
Step 2: Map each source to one data model
The data model describes the things the business deals with and how they relate: customers, products, orders and invoices, and links such as which invoice belongs to which order. It is written in the form the data should take, not the form any one source happens to use. What an ontology is, explained for business owners covers the idea.
Each source then gets its own mapping to that model. The spreadsheet column headed "Cust No" maps to the customer's account number. A table in the backup maps to orders. The total on a supplier PDF maps to the invoice amount. The mappings are kept separate from each other, so fixing how one supplier's invoices are read cannot break the spreadsheet import.
Step 3: A person reviews conflicts, and every conflict goes on a list
The table from the first section settles most conflicts automatically. The rest go to a person. Each one is written on an exceptions list with the record, the sources that disagree, what differed, the decision and who made it. The list is the evidence that the combined data can be trusted, and it shows which mapping needs fixing.
Our own migration of a flooring wholesaler off Zoho worked this way. Every field brought across, 293,986 of them, was compared with a backup of the old data until there were zero differences, and 43 exceptions were traced back to source documents. For 35 orders the correct date was missing from the old records, so it was taken from the time the original order email was received. How a flooring wholesaler left 15 years of Zoho patches in one month tells the full story.
Step 4: Run reports and workflows on the model, not the sources
Once the data sits in one model, reports and workflows read from the model. A sales report no longer opens the spreadsheet, queries the backup and searches the PDF folder. It asks one question of one set of data.
This is what makes the work pay off. The flooring wholesaler now runs its own custom workflow, custom business rules and custom reporting, all supported in CommerceServer. It never had that in 15 years on Zoho. When a new source turns up later, it needs one new mapping. The reports do not change.
The method also carries over to the next job. When a sister company of the wholesaler migrated next, what we had learned moving the first company became the tools for the second: the import method and the checks run before and after each import. The second migration was the easy one describes it.
The same four steps across nearly three dozen systems
This method was not invented for small businesses. Intelligence Factory's published case study on integrating EHR systems reports normalization work across nearly three dozen electronic health record systems, as reported in the original case. The inputs included APIs, databases, CSV and Excel files, PDFs, insurance payment files, notes and scanned documents.
The case names the same four steps: extract, map, review and reuse. Each source was mapped to one internal model, people reviewed the initial mappings, and the mappings for each source were kept separate from the rules that use the data for eligibility, billing and reporting. A small business has fewer sources, but the work has the same shape.
Check your own sources this week
You can do the first step without any software:
- List every place your business data lives: the main system, spreadsheets, database backups, PDFs, shared folders and email.
- For each kind of fact, such as customer details, prices and order history, write down which source wins and who keeps it current.
- Pick one customer and look them up in every source. Note each difference you find. That is the start of your exceptions list.
- Ask anyone quoting you a migration how each source will be mapped, who reviews conflicts and whether reports will run on one model or still reach into the old files.
Send us your list of sources
If your data is spread across spreadsheets, backups and PDFs, send us the list you made above. We will tell you how each source would map into CommerceServer and what the review would involve. Tell us about your current system, or read how full service works.