CASE FILE 10/13CLASS: SAMPLE BUILDDATA: REALISTIC, NOT CLIENTTYPE: Data cleaning & migration

Sample build: realistic data, built to show how I'd handle this work. Not client work.

Sample buildData cleaning & migration

Contact data cleanup: three messy exports to one clean CRM import

1,500 messy contact rows reduced to 1,234 clean records, with every change logged

Sample build · realistic data, built to show how I'd handle this work · not client work · every figure is computed from the files in the appendix

1,500rows in
1,234clean contacts out
9,439changes logged
10/10validation checks pass

01Summary

1,500 contact rows from 3 sources are cleaned into 1,234 import-ready records.
240 duplicates are merged by rule and 9,439 changes are logged cell by cell.
10 of 10 validation checks pass, and the row count reconciles to the input.

240 duplicates are merged by rule and 9,439 changes are logged cell by cell.
Key point, computed from the sample files

02The scenario

In this scenario the same person appears in several lists with different spellings, phone formats and state names, and junk rows and test entries are mixed in. Importing as-is would create duplicates and bad data in the CRM.

Exhibit 1: Current-state process map: 4 manual steps across 3 roles

Source: sample files

03Issues in the data

Exhibit 2: Issues in the scenario, counted from the sample data

3exports with different columns and formats

240duplicate people across sources

266rows removed or merged in total

9contacts with no email

Source: sample files

04How I approached it

A cleaning script that standardises names, phones (+61 format), states, postcodes, company suffixes and lifecycle stages.
Duplicate matching with three rules, merging 240 groups while keeping same-name people at different companies apart.
A change log with the before and after value and the rule used for every change.
A validation report workbook with summary, checks, merge log, exceptions and field mapping.

Exhibit 3: Target design for the scenario

Source: my design and the sample files

05How this applies to your business

Assess and designMap the current process, size the gaps from your own data, agree the design and a written scope.
Build and testBuild in your own tools with the checks built in, then run real cases end to end.
Document and hand overSOPs, a walkthrough and a handover, so your team runs it without me.

Exhibit 4: Responsibilities (R responsible, A accountable, C consulted, I informed)

Activity	Business owner	Specialist (me)	Your team
Agree field rules	A	R	C
Run the cleanup	I	R	I
Review merges	A	C	R
Import to the CRM	A	C	R

Source: sample plan

06Controls and checks

Exhibit 5: Control framework: 10 checks, status read from the files

Check	What it catches	Status
Rows reconcile: output + duplicates merged + junk removed = input	Listed on the Checks tab	PASS
No duplicate emails in the import	Listed on the Checks tab	PASS
Every contact has first + last name	Listed on the Checks tab	PASS
All emails contain one @	Listed on the Checks tab	PASS
All phone numbers start +61	Listed on the Checks tab	PASS
All states are standard abbreviations	Listed on the Checks tab	PASS
All postcodes are 4 characters	Listed on the Checks tab	PASS
All lifecycle stages valid HubSpot values	Listed on the Checks tab	PASS
Company names end in 'Pty Ltd'	Listed on the Checks tab	PASS
Every merged record traced in Merge Log	Listed on the Checks tab	PASS

Source: sample files (Checks / Controls tab, test output or run log)

07What the files show

Exhibit 6: What the sample files show

1,500rows in
1,234clean contacts out
9,439changes logged
10/10validation checks pass

Source: sample files, measured by script. Sample data, not a client result.

Rows reconcile: 1,234 out plus 266 removed (duplicates merged and junk rows) equals 1,500 in.
0 duplicate emails remain in the import; 9 contacts have no email and are kept.
1,234 of 1,234 phone fields are in +61 format or blank.
13 distinct cleaning rules appear in the change log.

08Risks and how the build handles them

Exhibit 7: Risks and mitigations

Risk	Mitigation built in
Two different people merged	Merge only on documented match rules; merge log shows every pair
Data lost in cleaning	Rows out + merged + removed = rows in
Bad values reach the CRM	Validation checks run before import

Source: sample design

09Appendix: files

A. Screenshots of the deliverable

Exhibit 8: Before and after: raw contact rows with mixed phone formats, casing and state names, and the merged clean contacts they became

Source: sample files

Exhibit 9: Validation report checks tab: row reconciliation and nine data quality checks, all passing

Source: sample files

Exhibit 10: Merge log tab showing which raw records were merged and by which match rule

Source: sample files

Exhibit 11: Code excerpt: clean_contacts.py, from line 34

def clean_phone(v, state, rid, field):
    d = re.sub(r"\D", "", v or "")
    if not d: return ""
    if d.startswith("61"): d = "0" + d[2:]
    if len(d) == 8 and state in AREA:
        d = "0" + AREA[state] + d; exceptions.append([rid, field, v, "Area code missing: inferred 0" + AREA[state] + " from state " + state, "Info"])
    if len(d) != 10 or not d.startswith("0"):
        exceptions.append([rid, field, v, "Phone number could not be parsed: cleared", "Fix at source"]); return ""
    return f"+61 {d[1:4]} {d[4:7]} {d[7:]}" if d.startswith("04") else f"+61 {d[1]} {d[2:6]} {d[6:]}"

Source: clean_contacts.py

B. Downloadable files

File	What it is	Size
contacts_raw_combined.csv	Input: three messy contact lists combined (before)	250.3 KB
contacts_clean_import.csv	Output: clean, de-duplicated import file (after)	231.3 KB
change_log.csv	Every field change with before, after and rule	690.5 KB
Cleanup-Validation-Report.xlsx	Validation report: summary, checks, merge log, exceptions, field mapping	598 KB
clean_contacts.py	The cleaning script (Python, pandas)	10.7 KB

Sample build: realistic data, built to show how I'd handle this work. Names are placeholders and no client data is used.

Have a similar problem in your business?

Hire me on Upwork (opens in a new tab)