Cleaning up customer records for better reporting

Why reporting breaks

Most home service companies have years of customer records entered by different people in different ways. The same customer appears three times under slightly different names. One office manager tagged jobs “AC repair,” another “Cooling – repair,” a third left it blank. Lead source was optional, so half the jobs say nothing. Then the owner asks a simple question, such as which marketing channel produced the most profitable customers, and nobody can answer it.

Cleaning up the database is not glamorous, but it is the foundation of every metric that matters: retention, revenue per customer, marketing return, agreement renewals and equipment replacement opportunities. It is also a precondition for switching software, because migrating messy data just moves the mess.

Start with an audit

1. 01

   Export everything

   Customers, locations, jobs, invoices, equipment and tags into spreadsheets. Keep the raw export untouched as a backup.

2. 02

   Count the problems

   Possible duplicates (same phone, email or service address), records missing a phone or address, jobs with no type, jobs with no lead source, and tags used fewer than five times.

3. 03

   Decide what matters

   Prioritize the fields your reports use: customer, service address, job type, lead source, equipment, agreement status.

4. 04

   Set a cut-off

   Customers with no job and no contact in many years may be archived rather than cleaned. Agree the rule with your accountant and any retention requirements.

5. 05

   Assign owners and dates

   One person per cleanup task, with a deadline, and a weekly check on progress.

Example: a fictional HVAC company exports 14,200 customer records and finds 1,900 likely duplicates, 2,300 records with no email, 38% of jobs with no lead source and 61 distinct job-type tags, many of them variants of the same thing. That audit becomes the cleanup plan.

Deduplicate with clear rules

• Match on phone number first, then email, then service address. Normalize formats before matching: digits only for phones, lowercase for emails, standard abbreviations for streets.
• Decide which record survives: usually the one with the most job history. Merge jobs, notes, equipment and agreements into it.
• Do not merge different people at the same address without checking. A landlord and a tenant, or a previous owner, are different customers at one location.
• Review a sample of automatic merges by hand before running them across the whole database.
• After the merge, block new duplicates: require a phone lookup before creating a new customer.

Separate people, places and billing

Home service records work best with three linked objects. The customer is the person or company you deal with. The service location is the property where the work happens. The billing account is who pays. A property manager may have one customer record, forty service locations and one billing account. A homeowner who sells a house should leave the location behind, with its equipment history, for the next owner.

Equipment belongs to the location. Record type, brand, model, serial number, install date, warranty end and service-specific details such as filter size, refrigerant type, opener model or panel rating. Those fields drive maintenance reminders, warranty claims and replacement offers. A technician who arrives knowing the equipment saves a trip; a marketer who knows equipment age can target replacement offers honestly.

Fixed lists for tags, job types and lead sources

Lead source needs particular care. “Google” could mean a paid ad, a maps listing, an organic search result or a local services ad, each with a different cost. Use separate values for each, plus referral, repeat customer, yard sign, vehicle, email, direct mail and other. Make the field required at booking, and have the office ask “How did you hear about us?” on every new call.

Why timestamps and response data matter

Clean records also let you measure how fast you respond. In an audit of US companies' handling of web leads, 37% responded within an hour, 24% took more than 24 hours and 23% never responded^[2], and firms contacting leads within an hour were nearly seven times as likely to qualify them^[2]. You cannot know where you stand unless every lead has a created time, a first-contact time and an outcome. Add those fields, and report them weekly.

Phone intake is a common source of messy records, because details are typed quickly during a call. Some companies use an AI Employee such as Ava to answer overflow and after-hours calls; it produces a consistent summary with name, callback number, service address, ZIP code, trade and the problem in the caller's words, which the office then enters into the job system.

Verify before you change a record

Customer records are a target for fraud. Pew found nine in ten US adults think people often or sometimes pretend to be someone else to steal personal information by phone or online^[3]. Callers who ask to change a billing address, email or payment card, or who ask for a copy of account details, should be verified against what is already on file: the service address, the last job date or a code you set up with the customer.

• Never read back full card numbers or account details over the phone.
• Confirm changes of email or phone by contacting the old contact method too.
• Log who changed each key field and when; most field service software keeps an audit trail if it is turned on.
• Limit exports and admin rights to the people who need them, and remove access the day someone leaves.

Phone numbers deserve special care because so many other fields hang on them. Customers change numbers, families share them and some calls come from work lines. When a known customer calls from an unfamiliar number, add it as a second number with a label rather than overwriting the first. That single habit prevents many of the duplicates that the cleanup was meant to remove.

Consent, do-not-call and privacy

Customer data comes with obligations. Record for each contact whether they agreed to calls, texts and emails, when and how. The National Do Not Call Registry had 259 million active registrations as of September 30, 2025^[1]; scrub marketing call lists against it and against your internal do-not-call list, which should be a field in the record, not a sticky note.

• California's privacy law applies to for-profit businesses with gross annual revenue over $25 million, or that buy, sell or share personal information of 100,000 or more California residents or households, among other tests^[4]. Check whether it and other state privacy laws apply to you.
• Businesses that sell personal information must give California consumers a clear “Do Not Sell or Share My Personal Information” link^[4].
• Delete or archive data you no longer need, on a written schedule, and keep the records your accountant and regulators require.
• Limit who can export the full customer list, and log exports.

Keep it clean

1. Weekly: review new customers created, merge any duplicates, fill missing lead sources from the booking notes.
2. Monthly: run the audit counts again and compare with last month.
3. Quarterly: review tags and job types; retire any that no report uses.
4. On every job: technicians confirm equipment details and update them in the app.
5. Yearly: review consent records, archive inactive customers by your rule, and test a restore from backup.

Clean data pays off immediately in the reports you already run. See cleaning company KPIs and pest and lawn KPIs for metrics that depend on it, and the field service software comparison if a cleanup is the first step of a software switch.