Quick Answer
Data validation before Odoo import requires a 16-point checklist across 4 phases: (1) Source Data Audit (duplicates, missing fields, format issues), (2) Data Cleansing (standardize, dedupe, fix relationships), (3) Post-Import Validation (record counts, relationships, reports), (4) Pre-Production Checklist (revenue totals, critical reports, backups). Investment: $8,800 upfront saves $24,400+ vs post-go-live fixes. Most brands discover 8-15% of their data has critical issues.
PHASE 1: Source Data Audit (Before You Touch Odoo)
1. Count Your Duplicates
Use Excel's COUNTIF to find duplicate emails, phone numbers, and SKUs.
Target: Less than 2% duplication rate. If you're above 5%, you have a serious problem.
Real example: A brand had 8,470 customers but should have had 8,630. Investigation revealed 160 customers with duplicate emails were deduplicated during import without notification.
2. Check for Missing Mandatory Fields
Products: Every product must have SKU, name, price, and category.
Customers: Every customer must have email, name, and country.
Orders: Every order must have customer reference, date, and line items.
Filter for blanks in these columns. How many records are incomplete?
3. Verify Phone and Email Formats
Emails: Filter for entries that don't contain "@" or have spaces.
Phone numbers: Standardize format (e.g., +1-XXX-XXX-XXXX). Mixed formats cause import failures.
4. Check Address Standardization
Look for variations: "USA" vs "United States" vs "US" — Odoo needs consistency.
Verify country codes match Odoo's expected format.
Check for missing zip codes or postal codes (common issue).
PHASE 2: Data Cleansing Checklist
5. Standardize Text Fields
Product names: Remove leading/trailing spaces. Fix inconsistent capitalization.
Categories: "T-Shirts" vs "Tshirts" vs "T Shirts" — pick one and use it everywhere.
Customer names: Verify proper capitalization. "JOHN DOE" should be "John Doe".
6. Merge Duplicates (Don't Just Delete)
Don't delete duplicates blindly. You may lose order history.
Process: Identify the "master" record → merge data from duplicates → preserve all orders → then delete the duplicate shell.
Tool tip: Use OpenRefine (free) for bulk deduplication. It's better than Excel for this.
7. Fix Date Formats
Odoo expects dates in YYYY-MM-DD format.
Check for: "12/31/2024" vs "31/12/2024" vs "2024-12-31" — inconsistency causes import errors.
Watch for Excel date formatting issues (dates stored as numbers).
8. Validate Relationships
Order → Customer: Does every order have a valid customer ID that exists in your customer list?
Order Line → Product: Does every order line reference a valid SKU?
If an order references customer ID 12345, but customer 12345 doesn't exist, Odoo will choke.
PHASE 3: Post-Import Validation
9. Count Records After Import
Compare record counts in old system vs Odoo after import.
Products: Old system: _____ → Odoo: _____ (should match exactly)
Customers: Old system: _____ → Odoo: _____ (should match exactly)
Orders: Old system: _____ → Odoo: _____ (should match exactly)
If numbers don't match, don't proceed. Investigate why records were dropped.
10. Test Key Business Processes
Create a test sales order. Does the customer lookup work? Do prices pull correctly?
Process the order through fulfillment. Does inventory deduct properly?
Generate a simple sales report. Verify totals match your old system.
Run inventory reports. Verify stock levels are correct.
11. Check for Data Type Mismatches
Verify numeric fields (prices, quantities, weights) imported as numbers, not text. Excel often converts these to text, breaking calculations.
Check date fields: did they import as actual dates or text strings?
Currency fields: if dealing with multiple currencies, verify the currency code was imported (USD, EUR, etc.).
Test: Try to add 1 + 1 on a quantity field that imported as text. It will fail or give wrong results.
12. Spot-Check High-Risk Records
Manually verify 10-15 "high-value" records:
- Your top 5 customers (by lifetime value)
- Your top 5 products (by revenue)
- Your largest orders
- Sample orders from different time periods
For each, verify: All fields present, relationships valid (order → customer → address all correct), revenue/totals match original system.
PHASE 4: Pre-Production Checklist (24 Hours Before Go-Live)
13. Final Data Count Reconciliation
Count records in old system: _____ customers, _____ products, _____ orders
Count records in Odoo after full import: _____ customers, _____ products, _____ orders
These numbers should match exactly. If not, investigate why.
14. Validate Revenue Totals
Sum of all historical orders in old system: $___________
Sum of all historical orders in Odoo: $___________
These should match exactly (within $0.01 due to rounding).
If they don't match, you have a data integrity issue. Don't go live.
15. Verify Critical Reports
Run your top 3 business reports in both old system and Odoo. Verify totals match.
- Monthly Revenue by Product Category
- Customer Aging Report
- Inventory Valuation Report
16. Backup Everything
- Full backup of old system database
- Full backup of Odoo test database (before and after test import)
- Export of critical data as CSV (acts as safety net)
- Documented rollback plan (if Odoo import fails, what do we do?)
The Cost Comparison: Validation Now vs. Fixing Later
Scenario A: Validate Before Import (3 weeks)
Scenario B: Skip Validation, Fix Post-Go-Live
Scenario A Saves You $24,400+
And that's conservative. We've seen post-import data fixes exceed $180,000 for large implementations.
The 4-Week Process We Use
| Week | Phase | Activities |
|---|---|---|
| Week 1 | Extract & Audit | Export all master data from old system, run validation checklist, document every issue found |
| Week 2 | Cleanse & Deduplicate | Fix formatting, missing data, duplicates; create mapping files; standardize all text and date fields |
| Week 3 | Test Import | Import to Odoo sandbox, run Odoo's validation engine, spot-check 50+ critical records, fix remaining issues |
| Week 4 | Final Validation | Run full production import, reconcile record counts, validate revenue totals, run critical business reports, flip the switch |
What You Should Do Right Now
This Week:
- Extract your product master data from your current system
- Open it in Excel. Scan the first 100 rows looking for the issues in this checklist
- Count duplicates using COUNTIF. How many do you find?
- Check for blank mandatory fields. How many products are missing prices? Missing categories?
This is eye-opening. Most brands discover 8-15% of their data has critical issues.
Next Week:
- Don't start your Odoo implementation yet
- Run through Phases 1-2 of this checklist
- Set aside $8,000-$15,000 for data cleansing (it's worth it)
- Schedule your implementation to start 4 weeks from now, not 2 weeks
The Reality: Brands that validate data first go live on time and under budget. Brands that skip it burn $30,000-$150,000 in rework and delay their payoff by 3-6 months.
Stop Assuming Your Data is Clean. It Isn't.
Most teams discover $30,000-$80,000 in hidden rework costs lurking in their data. Knowing that upfront changes everything.
Free Data Validation Audit
We'll scan your master data (products, customers, orders), identify critical issues using our 16-point checklist, calculate the cost of bad data specific to your volume, and recommend the exact cleansing path forward.
Don't wait until go-live to find out your data is broken.