ERP Implementation Part 2: Data Migration & Testing Guide

Why Data Migration Is the #1 Risk

The Statistics:

• 35% of ERP failures caused by poor data quality
• 60% of companies underestimate data cleanup effort
• Average data cleanup time: 3-4 months (most plan 2-4 weeks)
• Cost of bad data: $15M/year for average company (Gartner)

What Goes Wrong:

• “Our data is fine” (it never is)
• “We’ll clean it after migration” (too late)
• “We need all 15 years of history” (you don’t)

• Duplicate customer records (can’t invoice)
• Incorrect inventory counts (can’t fulfill orders)
• Mismatched GL accounts (can’t close books)
• Missing required fields (transactions fail)

PHASE 3: Data Migration (Weeks 13-18)

✅ Data Assessment & Cleanup

• Primary system (legacy ERP/accounting)
  • Customer master
  • Vendor master
  • Item master
  • Chart of accounts
  • Open transactions (AR, AP, POs, SOs)
  • Transaction history
• Secondary systems
  • E-commerce platform (Shopify, Magento)
  • CRM (Salesforce, HubSpot)
  • Shipping system (ShipStation)
  • Spreadsheets (pricing, inventory adjustments)
  • Access databases (custom reports)
• Identify “source of truth” for each data type
  • Customers: CRM or ERP?
  • Items: E-commerce or ERP?
  • Inventory: WMS or ERP?

• Duplicate records
  • Customers with same name/address
  • Vendors with same tax ID
  • Items with same description
• Missing required fields
  • Customers without billing address
  • Items without cost or price
  • Vendors without payment terms
• Inconsistent formats
  • Addresses: “St” vs. “Street” vs. “ST”
  • Phone: (555) 123-4567 vs. 555-123-4567 vs. 5551234567
  • Dates: MM/DD/YYYY vs. DD/MM/YYYY
• Inactive/obsolete records
  • Customers with no orders in 3+ years
  • Items not sold in 2+ years
  • Vendors not used in 1+ year

• 8K duplicates (same company, different spellings)
• 12K inactive (no orders in 5+ years)
• 5K missing required fields (tax ID, payment terms)
• Actual clean records: 25K (50% reduction)

• What data to migrate:

• Active customers (ordered in last 2 years)
• Active vendors (used in last 1 year)
• Active items (sold in last 1 year)
• Chart of accounts
• Open transactions (AR, AP, POs, SOs)

• 1-3 years transaction history (for reporting)
• Inactive customers (if might reactivate)
• Inactive items (if might restock)

• 10+ years of history (archive instead)
• Duplicate records
• Test/demo data
• Obsolete items (discontinued, never coming back)
• How much history:

• Open transactions: Yes (AR, AP, POs, SOs)
• Closed transactions: 1-2 years (for reporting)
• Historical GL: 7 years (for tax compliance, but can archive)

• Sales orders: 1 year (for customer history)
• Purchase orders: 1 year (for vendor history)
• Inventory transactions: 1 year (for costing)

• Merge duplicates
  • Customers: Merge “ABC Company” and “ABC Co” and “ABC Company Inc”
  • Vendors: Merge based on tax ID
  • Items: Merge based on manufacturer part number
• Standardize formats
  • Addresses: Use USPS format
  • Phone: (555) 123-4567 format
  • Dates: YYYY-MM-DD format
• Fill missing fields
  • Customer payment terms: Default to “Net 30”
  • Item cost: Use last purchase price
  • Vendor 1099: Yes/No (required for tax)
• Remove obsolete records
  • Archive (don’t delete) inactive customers/vendors/items
  • Keep in legacy system for reference
  • Don’t migrate to new ERP
• Validate critical data
  • Inventory counts: Physical count before migration
  • AR/AP balances: Reconcile to GL
  • Item costs: Verify against recent POs

• Excel (for small datasets, <10K records)
• SQL queries (for large datasets)
• Data cleaning tools (Trifacta, Talend, Informatica)
• Implementation partner’s data migration tool

• 200 orders failed (duplicate customer records)
• 50 items couldn’t ship (missing cost)
• Finance couldn’t close month (AR/AP didn’t balance)

• Chart of accounts mapping

• Customer/vendor mapping
  • Old ID → New ID (if changing ID structure)
  • Old payment terms → New payment terms
  • Old tax codes → New tax codes
• Item mapping
  • Old item code → New item code
  • Old item type → New item type (inventory, non-inventory, service)
  • Old costing method → New costing method (FIFO, average, standard)

✅ Data Migration Execution

• Export to CSV/Excel
  • Customers: All fields (name, address, payment terms, etc.)
  • Vendors: All fields
  • Items: All fields (description, cost, price, quantity, etc.)
  • Open transactions: AR, AP, POs, SOs
  • GL balances: Opening balances for new system
• Validate export completeness
  • Record counts: 25,000 customers exported, 25,000 expected ✓
  • Financial totals: AR = $500K, AP = $300K, Inventory = $1.2M
  • Spot-check: Review 50 random records manually

• Apply mapping rules
  • Old account 4000 → New account 4100
  • Old payment terms “30 days” → New payment terms “Net 30”
• Format correctly
  • Dates: Convert to YYYY-MM-DD
  • Numbers: Remove commas, currency symbols
  • Text: Handle special characters (é, ñ, ü)
• Validate required fields
  • Every customer has billing address ✓
  • Every item has cost and price ✓
  • Every transaction has date and amount ✓

• Test Migration #1 (Week 14)

1. Chart of accounts
2. Customers
3. Vendors
4. Items
5. Opening balances (AR, AP, Inventory)
6. Open transactions (POs, SOs)

• ERP’s import tool (CSV import)
• API (for large datasets)
• Implementation partner’s migration tool
• Validate Test Migration #1

• Customers: 25,000 exported, 24,800 imported → 200 failed, investigate
• Vendors: 5,000 exported, 5,000 imported → ✓
• Items: 100,000 exported, 99,500 imported → 500 failed, investigate

• AR: $500K expected, $498K imported → $2K difference, investigate
• AP: $300K expected, $300K imported → ✓
• Inventory: $1.2M expected, $1.15M imported → $50K difference, investigate

• Customer “ABC Company”: Address correct? Payment terms correct? ✓
• Item “12345”: Description correct? Cost correct? Quantity correct? ✓

• 200 customers failed: Missing required field (tax ID)
• 500 items failed: Invalid item type
• $2K AR difference: Rounding errors
• $50K inventory difference: Costing method mismatch
• Fix issues, refine process
  • Add missing tax IDs
  • Correct item types
  • Adjust rounding rules
  • Recalculate inventory costs
• Test Migration #2 (Week 16)

• Apply fixes from Test Migration #1
• Validate again (record counts, financial totals, spot-checks)
• Goal: 100% success rate, zero discrepancies

• Document final migration process (step-by-step)
• Lock down mapping (no more changes)
• Schedule final migration (go-live weekend)
  

• Fix issues
• Run Test Migration #3
• Don’t proceed to go-live until test migration is perfect

• Test 1: 15% failure rate
• Test 2: 5% failure rate
• Test 3: 1% failure rate
• Test 4: 0% failure rate ✓

• Timing: Weekend or holiday
  • Friday 5 PM: Freeze legacy system (no new transactions)
  • Friday 6 PM – Saturday 12 PM: Data migration (6 hours)
  • Saturday 12 PM – 6 PM: Validation (6 hours)
  • Saturday 6 PM – Sunday 12 PM: User acceptance validation (18 hours)
  • Sunday 12 PM – 6 PM: Final checks, issue resolution (6 hours)
  • Sunday 6 PM: Go/no-go decision

• Data migration lead (runs migration scripts)
• Finance (validates AR, AP, GL balances)
• Operations (validates inventory, open orders)
• IT (technical support, troubleshooting)
• Implementation partner (on-call support)
• Communication plan:
  • Friday 4 PM: Email all users “System freeze at 5 PM, no new transactions”
  • Saturday 12 PM: Email steering committee “Migration complete, validation in progress”
  • Sunday 6 PM: Email all users “Go-live Monday 8 AM” or “Delayed, will notify”
• Rollback plan:
  • If migration fails (data doesn’t balance, critical errors)
  • Revert to legacy system Monday morning
  • Communicate delay to customers/vendors
  • Reschedule go-live (2-4 weeks)

• “We’ll migrate once, on go-live weekend” → No testing = disaster
• Financial totals don’t match after test migration → Don’t proceed
• “We’ll fix discrepancies after go-live” → Too late, will cause chaos

PHASE 4: Testing (Weeks 17-22)

✅ Unit Testing

• Basic transactions (Week 17-18)

• Create sales order → Add items → Calculate tax → Calculate shipping → Save
• Expected: Order total = $100 + $8 tax + $10 shipping = $118 ✓

• Create PO → Add items → Set vendor → Save
• Expected: PO created, vendor notified ✓

• Receive PO → Scan items → Update quantity → Post
• Expected: Inventory increased, PO marked received ✓

• Pick items → Pack → Generate shipping label → Ship
• Expected: Inventory decreased, order marked shipped ✓

• Create invoice from sales order → Calculate totals → Post
• Expected: AR increased, revenue recognized ✓

• Receive payment → Apply to invoice → Post
• Expected: AR decreased, cash increased ✓
• Calculations (Week 18)

• Order to California: 9.5% tax ✓
• Order to Oregon: 0% tax (no sales tax) ✓
• Tax-exempt customer: 0% tax ✓

• 10% discount on $100 order = $90 ✓
• Volume discount: 100+ units = 15% off ✓

• Ground shipping: $10 ✓
• Expedited: $25 ✓
• Free shipping over $100 ✓

• FIFO: First in, first out ✓
• Average cost: Recalculates on each receipt ✓
• Workflows (Week 18-19)

• PO under $5K: Auto-approved ✓
• PO $5K-$25K: Manager approval required ✓
• PO over $25K: Director approval required ✓
• Email notifications sent ✓

• Expense under $500: Auto-approved ✓
• Expense over $500: Manager approval ✓
• Integrations (Week 19)

• Order placed on Shopify → Flows to NetSuite within 5 minutes ✓
• Customer data syncs ✓
• Inventory syncs ✓

• Order ready to ship → Generate FedEx label ✓
• Tracking number updates order ✓
• Customer receives tracking email ✓

• Credit card payment → Authorizes in Stripe ✓
• Payment posts to NetSuite AR ✓
• Reports (Week 19-20)

• Profit & Loss: Revenue, COGS, expenses ✓
• Balance Sheet: Assets, liabilities, equity ✓
• Cash Flow: Operating, investing, financing ✓

• Inventory by location ✓
• Sales by customer ✓
• Purchasing by vendor ✓
• Open orders ✓

• Daily sales dashboard ✓
• Inventory reorder report ✓
• Vendor performance scorecard ✓

• Pass/fail for each test
• Screenshots of successful transactions
• Log of any issues found

• Skipping unit testing, jumping to UAT → Will find issues too late
• “It mostly works” → Not good enough, must be 100%
• Not documenting test results → Can’t prove readiness

✅ User Acceptance Testing (UAT)

1. Customer calls, wants to place order
2. Sales rep creates sales order in ERP
3. Adds 3 items, applies 10% discount
4. Calculates tax and shipping
5. Saves order, sends confirmation to customer
6. Expected result: Order total correct, customer receives email ✓

1. Order appears in pick queue
2. Picker scans items, confirms quantities
3. Delivers to packing station
4. Packer generates shipping label
5. Packs order, scans label, marks shipped
6. Expected result: Inventory decreased, customer receives tracking ✓

1. Order shipped, ready to invoice
2. Finance creates invoice from sales order
3. Invoice posts to AR
4. Customer pays via credit card
5. Payment applied to invoice
6. Expected result: AR balance correct, payment reconciled ✓

1. Item low in stock, reorder needed
2. Purchasing creates PO
3. PO routed for approval
4. Manager approves
5. PO sent to vendor
6. Expected result: PO in vendor’s system, confirmation received ✓

1. Vendor shipment arrives
2. Warehouse receives against PO
3. Scans items, confirms quantities
4. Posts receipt
5. Items available for sale
6. Expected result: Inventory increased, PO marked received ✓

1. Customer wants to return item
2. CS creates return authorization
3. Customer ships item back
4. Warehouse receives return
5. Finance issues credit memo
6. Expected result: Inventory increased, AR decreased ✓

1. Received wrong item from vendor
2. Purchasing creates vendor return
3. Warehouse ships back to vendor
4. Finance receives vendor credit
5. Expected result: Inventory decreased, AP decreased ✓

1. Run month-end checklist
2. Reconcile AR, AP, inventory
3. Post adjustments
4. Generate financial statements
5. Close period
6. Expected result: Books balanced, period locked ✓

• Sales team tests order entry (Scenarios 1, 6)
• Warehouse team tests fulfillment (Scenarios 2, 5, 7)
• Finance team tests invoicing, payments, close (Scenarios 3, 8)
• Purchasing team tests PO creation (Scenarios 4, 7)
• Customer service tests returns (Scenario 6)

• Receives test script (step-by-step instructions)
• Logs into test environment (not production!)
• Executes scenarios
• Documents results (pass/fail, issues found)

• Week 21: Full UAT

• All testers execute their scenarios
• Log issues in issue tracker (Jira, Excel, etc.)
• Implementation partner available for support

• Critical: Blocks core workflow, must fix before go-live
• High: Impacts workflow, should fix before go-live
• Medium: Minor inconvenience, can fix after go-live
• Low: Cosmetic, can fix after go-live

• Dev team fixes critical and high issues
• Testers retest fixed issues
• Sign-off on resolved issues

• All critical issues resolved ✓
• All high issues resolved or have workarounds ✓
• Medium/low issues documented for post-go-live ✓

• Concurrent user testing
  • 10 users: Response time <2 seconds ✓
  • 25 users: Response time <3 seconds ✓
  • 50 users: Response time <5 seconds ✓
  • 100 users: Response time <10 seconds ✓
• Peak transaction volume
  • Simulate Black Friday: 2,500 orders in 8 hours
  • System handles load without crashing ✓
  • No failed transactions ✓
• Integration performance
  • E-commerce orders: 100 orders/hour sync to ERP ✓
  • Shipping labels: 50 labels/hour generate ✓
  • No API timeouts ✓

• Optimize database queries
• Increase server resources
• Adjust integration timing (batch vs. real-time)
• Retest until performance acceptable

• Department champions sign off:
  • Finance: “System ready for go-live” ✓
  • Operations: “System ready for go-live” ✓
  • Sales: “System ready for go-live” ✓
  • IT: “System ready for go-live” ✓
• Steering committee approval:
  • Review UAT results
  • Review open issues (all critical/high resolved)
  • Review performance test results
  • Decision: Proceed to go-live ✓

• IT doing all testing (users must test!) → Users won’t know how to use system
• Skipping performance testing → System crashes on day 1
• Going live with open critical issues → Guaranteed problems
  

Common Data Migration & Testing Mistakes

Mistake 1: “Our Data Is Fine”

• 60% of companies have 20%+ duplicate records
• 40% have 10%+ missing required fields
• 80% have inconsistent formats

• Run data quality assessment (3-4 weeks)
• Allocate 6-8 weeks for cleanup
• Validate before AND after migration

Mistake 2: Migrating Too Much History

• Migrating 15 years of transactions slows system
• 90% of that data never accessed
• Increases migration time 3-5x

• Migrate 1-2 years of closed transactions
• Archive older data (keep in legacy system for reference)
• Focus on operational data, not historical

Mistake 3: No Test Migrations

• “We’ll migrate once, on go-live weekend”
• Day 1: Data doesn’t balance, orders fail, chaos

• Run 2-3 test migrations
• Fix issues after each test
• Don’t proceed until test migration is perfect

Mistake 4: Users Don’t Test

• IT tests everything, says “it works”
• Day 1: Users don’t know how to use system

• Actual end users must perform UAT
• Test realistic scenarios (not just “create order”)
• Users sign off that system is ready

Real-World Example: Automotive E-Commerce Data Migration

• 50K customers (25K after cleanup)
• 100K SKUs (80K after cleanup)
• 15 years history (migrated 2 years)
• $2M AR, $1.5M AP, $5M inventory

• Week 1-4: Data assessment
• Week 5-10: Data cleanup (longer than planned!)
• Week 11-12: Test migration #1 (15% failure rate)
• Week 13-14: Fix issues, test migration #2 (3% failure rate)
• Week 15-16: Fix issues, test migration #3 (0% failure rate)
• Week 17-20: UAT (found 12 issues, all resolved)
• Week 21: Performance testing (passed)
• Week 22: Go-live weekend

• Friday 5 PM: Freeze legacy system
• Saturday 8 AM – 2 PM: Data migration (6 hours)
• Saturday 2 PM – 8 PM: Validation (all balanced!)
• Sunday: User acceptance validation
• Monday 8 AM: Go-live ✓

• Zero data discrepancies
• Zero failed orders day 1
• Smooth go-live

About TopSource Global

What’s Next?

• Train users effectively
• Execute smooth go-live
• Provide hypercare support
• Measure success