Cleaning and importing customer and equipment history

Step 1: Inventory every source

Before cleaning anything, list every place customer or equipment data lives. In a typical HVAC shop that includes:

• the old dispatch or field-service software;
• accounting software contacts and invoices;
• a maintenance agreement spreadsheet;
• comfort advisors' own quote files or email folders;
• a shared contacts list on the office phone;
• paper agreement forms or job folders.

For each source, record the row count, the date range, what each column means and who knows it best. This list becomes the checklist for reconciliation at the end.

Step 2: Settle the structure first

Decide where each piece of data will go before you move any of it. That means the new CRM's customer, site, equipment and job records, and their fields, should already be agreed. The fields and stages guide covers how to design them.

Then build a mapping sheet: one row per source column, with the destination field and any transformation. Columns that don't map anywhere are either dropped by agreement or combined into a single "legacy notes" field. Writing this down stops the import turning into a series of quick decisions nobody remembers.

Step 3: Clean addresses and phone numbers

Addresses and phones are the keys you'll match on, so they get cleaned first.

Addresses:

• split unit numbers into their own field, so "12-345 Main St" and "345 Main St Unit 12" become the same site;
• pick one style for street types and directions, such as "Street" or "St", "North" or "N", and apply it everywhere;
• format postal codes consistently, with capital letters and a single space (A1A 1A1);
• keep the province as a two-letter code;
• watch rural routes and lot numbers, which often don't follow urban patterns.

Phone numbers:

• strip spaces, dashes, dots and brackets, then store in one consistent format;
• separate extensions into their own field;
• label each number (mobile, home, work) where you can tell, because texting only works on mobiles.

Clean phone numbers matter twice: once for duplicate matching, and again when call tracking tries to match incoming calls to existing customers.

Step 4: Match duplicates on several signals

No single field reliably identifies a customer. Use several at once and score the matches.

A practical rule set:

1. Two strong signals agree: merge automatically.
2. One strong signal plus a medium one: merge, but log it.
3. Anything less: send it to a review list.

Expect special cases. A landlord's phone appears on twelve sites, which is correct and shouldn't be merged into one site. A family with a shared email may be two separate customers. Spouses with different surnames at one address are usually one customer with two contacts.

Step 5: Parse equipment notes into fields

Years of equipment history often live in notes such as "replaced HX 2016, 80% furnace, attic". Turning those into fields is what makes replacement and reminder lists possible.

Work through it in passes:

1. Find the equipment type using keywords (furnace, boiler, combi, heat pump, AC, tank, tankless, HRV).
2. Find years (four-digit numbers between a sensible range), and mark them as estimated unless the source is a rating plate or permit.
3. Find brands from a list of the brands you install and service.
4. Find model and serial numbers where they follow a recognisable pattern.
5. Leave the original note attached, so nothing is lost if the parsing was wrong.

Anything ambiguous goes on a short list for a technician or service manager to review. Even partial results help: knowing that a site has a gas furnace from roughly 2009 is far more useful than a blank record.

Step 6: Test-load a sample

Load a sample first, often a few hundred customers chosen to include the awkward cases: landlords, multi-site owners, commercial accounts, customers with long histories and customers with none.

Then have someone in the office spot-check twenty or thirty records against the old system. Check that:

• each customer has the right sites;
• equipment sits at the right site;
• agreement start dates and terms came across;
• phone numbers and emails are in the right fields;
• notes are readable.

Fix the mapping, rerun the sample and only then load everything.

Step 7: Load, reconcile and freeze

After the full load, compare counts against the inventory from Step 1: customers, sites, equipment, agreements and jobs. Small differences should be explainable by merged duplicates or agreed exclusions. Large ones mean something went wrong.

Then freeze the old system. Make it read-only if you can, so nobody keeps entering work in two places. Set a cut-over date and move any records created after it in one final batch.

Privacy while you work

Customer records are personal information. According to the Office of the Privacy Commissioner of Canada, PIPEDA's fair information principles include limiting collection, limiting use, disclosure and retention, accuracy and safeguards. The OPC's summary of privacy laws in Canada notes that Alberta, BC and Quebec have private-sector laws considered substantially similar to PIPEDA, which generally apply to businesses operating within those provinces.

For an import, that translates into practical habits:

• move exports through secure, shared storage, not personal inboxes or texts;
• give the project only the access it needs;
• delete working copies when the project ends;
• use the cleanup to correct outdated details, which also serves the accuracy principle.

This is general information, not legal advice. Confirm your obligations with your own advisor.

Common mistakes

• loading data before the structure is agreed;
• merging every record that shares a phone number;
• dropping old equipment notes because they are messy;
• skipping the sample and discovering a mapping error after 20,000 rows are in;
• letting staff keep using the old system after cut-over.

For help with an import, see the data import service. Once records are clean, the maintenance reminders service can put agreement dates to work. Questions? Contact us.