1. Audit existing 'source' fields and pick a canonical set
Spend an hour exporting the source fields you use today: HubSpot contact property (and any marketing/source properties), Xero contact notes or custom fields, and the spreadsheets your team uses. Keep small samples if datasets are large — a few hundred recent records will reveal the common values and the odd one-offs.
List the distinct values and group obvious duplicates (eg. “web”, “website”, “website form”) and noisy ones (misspellings, free‑text notes). Decide the level of detail you need — for many small teams a short list like: Website, Referral, Trade Show, Phone, Email, Accounting Import (Xero) is enough.
Record that canonical list in a single place (a small spreadsheet or a Google Sheet) and mark which system will be the source of truth for reporting (usually HubSpot for marketing/sales, Xero for billing). That decision keeps future merges consistent.
2. Create a simple mapping table and implement lightweight transforms
- Build one mapping table with these columns: source_system, raw_value, canonical_value, example_record, comments. Keep it to one sheet so anyone can edit and see the mappings.
- For spreadsheet imports use built‑in find/replace or a helper column with a formula (IF/LOOKUP) that outputs the canonical value before you save a CSV.
- In HubSpot: turn the contact source into a controlled property (dropdown) if possible, then use import mappings or workflows to set the canonical value on import. For Xero, map the canonical value into a custom field or a consistent notes tag during import.
- If you repeat the same fixes, a tiny script (Google Apps Script, Python or even an Airtable automations script) that reads the raw value and writes the canonical value will save time; keep it simple and version the mapping table so the script just references that single source.
3. Run a safe clean, validate and assign ongoing ownership
Start with backups: export current HubSpot and Xero data and keep dated copies. Do a small test import (50–200 records) into HubSpot and Xero as needed, check that the canonical values appear as expected and that no other properties are accidentally overwritten.
Validate by spot‑checking a handful of records, running the report or filter you need, and comparing totals with the pre‑clean export. Look for unexpected growth in an ‘Unknown’ bucket — that flags missed raw values to add to the mapping table.
Finally, assign a single owner (an ops person or admin) to maintain the mapping table and run a weekly quick check for new raw values for the first month. If you want practical help to set up the mapping and a safe test plan, Optira can assist with a short workshop and a tidy mapping sheet.