In short: Python Pandas can clean any messy CRM export in under 100 lines of code. Dedupe on email, standardize phone numbers, validate email format, and flag stale contacts. The script pays back in 90 days by preventing data decay, and it runs free on any laptop or Google Colab notebook.
Key Takeaways
- Dirty CRM data costs U.S. businesses $3.1 trillion annually per IBM research
- The Gartner benchmark pegs the cost per organization at $12.9 million per year
- Contact data decays at 30% per year without active maintenance
- Marianne Gatlabayan built one Pandas script that dedupes, standardizes, and validates in under 100 lines
- Recurring cleanup on a 90-day cadence prevents the compound decay problem
Why dirty CRM data is a $3.1 trillion problem
IBM estimates that bad data costs U.S. businesses $3.1 trillion a year. The Gartner benchmark most consultants cite pegs the cost per organization at $12.9 million annually. These sound like enterprise problems, but small businesses feel them in different ways: lost deals from wrong contact info, wasted ad spend on invalid emails, sales reps calling numbers that ring nowhere.
At the tactical level, the damage looks smaller and hurts more. Companies lose an average of 16 sales deals per quarter due to bad data. Workers burn 13 hours a week hunting for basic CRM information that should have been logged accurately the first time. Contact data decays at 30% per year without maintenance, which compounds relentlessly. A 10,000-contact CRM loses 3,000 accurate records per year without active cleaning.
Python Pandas is the free tool that fixes most of it. Marianne Gatlabayan walks through the exact steps on a messy sample CRM export below. The full script runs on any laptop in under 30 seconds against a 10,000-row export.
What do you need before you start cleaning CRM data?
Python 3.10 or newer. If you have never installed Python, download it from python.org and pick the latest stable version. The installer handles path setup on Windows and Mac.
Pandas installed via pip install pandas. Run this in a terminal after Python is installed. If pip is not recognized, close and reopen the terminal so the path update takes effect. The install downloads about 50MB and finishes in a minute on any modern connection.
A CSV export from your CRM. Any CRM. HubSpot exports from Contacts > Actions > Export. Salesforce exports through Data Loader or reports. Pipedrive exports from Filters > Export. A spreadsheet dumped as a CSV works fine.
A code editor. VS Code works well and is free. Jupyter notebooks work if you prefer running code cell by cell. Google Colab lets you run everything in the browser without installing anything, which is the fastest path for someone who has never used Python before. That is the entire stack.
How do you load a CRM export into Pandas?
Start with two lines. This is the smallest possible working Pandas script.
import pandas as pd
df = pd.read_csv('crm_export.csv')
print(df.head())
df.head() shows the first five rows. This immediate check catches encoding problems, header misalignment, and wrong file paths before you do any real work. Confirm the columns you expect are there and the encoding is not garbled.
If you see garbled characters like é where an é should be, add encoding='latin-1' or encoding='utf-8-sig' to the read_csv call. HubSpot exports use UTF-8 with BOM, which needs encoding='utf-8-sig'. Salesforce exports vary by regional setting. When in doubt, try each encoding option until the characters read correctly.
The df variable holds the entire CRM export in memory as a DataFrame, which is Pandas' name for a table. Every subsequent operation transforms this DataFrame. The original CSV file stays untouched on disk until you explicitly save changes back.
How do you diagnose the damage before cleaning?
Before cleaning anything, measure the damage. Running a diagnostic pass first gives you numbers to report and a baseline to measure improvement against.
print(f"Total rows: {len(df)}")
print(f"Duplicate rows: {df.duplicated().sum()}")
print(f"Missing values per column:\n{df.isnull().sum()}")
This tells you what percentage of your data is unusable. Most CRM exports come back with 20 to 30% duplicate rows and missing values in 40% of the "optional" fields. If your export shows better numbers than this, you have unusually clean data. If it shows worse, the cleanup will produce dramatic before-and-after visuals for whoever asked for the audit.
Save this diagnostic output to a text file for your report. open('audit.txt', 'w').write(f"...") writes a plain text file. The audit output plus the final quality report at the end of the script becomes the deliverable when someone asks what the cleanup accomplished.
How do you remove CRM duplicates the right way?
The naive approach removes only exact matches, which almost never solves the real duplicate problem.
df = df.drop_duplicates()
That only catches rows where every column matches. Real duplicates rarely look identical. "John Smith" and "JOHN SMITH" and "john smith" are the same person to a human but three rows to Pandas. A contact who signed up twice with different capitalization survives the naive dedupe untouched.
The better approach normalizes first, then dedupes:
df['email_clean'] = df['email'].str.lower().str.strip()
df = df.drop_duplicates(subset='email_clean', keep='first')
Email is the strongest deduplication key because most CRMs enforce a single email per contact anyway. The str.lower() collapses case variations. The str.strip() removes leading and trailing whitespace that trips up otherwise identical emails. The keep='first' argument keeps the earliest record and drops later duplicates, which usually preserves the record with the most history.
For contacts with different emails at the same company, dedupe by a combination of name and company instead: df.drop_duplicates(subset=['first_name', 'last_name', 'company'], keep='first'). Choose the dedupe key based on what "duplicate" means in your business context.
How do you standardize phone numbers in Pandas?
Phone data comes in ten formats. (555) 123-4567, 555-123-4567, 5551234567, +1 555 123 4567, and combinations of all four. When a sales rep searches for a lead by phone, they only find it if the format matches exactly.
Normalize to a single format with a small helper function:
def clean_phone(phone):
if pd.isnull(phone):
return None
digits = ''.join(c for c in str(phone) if c.isdigit())
if len(digits) == 10:
return f"({digits[:3]}) {digits[3:6]}-{digits[6:]}"
elif len(digits) == 11 and digits[0] == '1':
return f"({digits[1:4]}) {digits[4:7]}-{digits[7:]}"
return None
df['phone_clean'] = df['phone'].apply(clean_phone)
The function strips every non-digit character with the generator expression on line 4. It then reformats based on the digit count. Ten digits becomes (555) 123-4567. Eleven digits starting with 1 becomes the same format with the country code implied.
Every valid U.S. phone number now looks the same. Anything that could not be parsed comes back as None so you can flag it in a separate list. For international coverage, extend the function with country-specific logic or install the phonenumbers library, which handles global formats with lookup tables maintained by Google.
How do you validate email formats with Pandas and regex?
Not every string that looks like an email actually is one. john@ is missing a domain. john@example is missing a TLD. john smith@example.com has an invalid character. Regex catches the obvious garbage before it goes back into the CRM.
import re
email_pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
df['email_valid'] = df['email_clean'].apply(
lambda x: bool(re.match(email_pattern, str(x))) if pd.notnull(x) else False
)
invalid_count = (~df['email_valid']).sum()
print(f"Invalid emails found: {invalid_count}")
The pattern matches standard user@domain.tld addresses with a minimum two-character TLD. The ^ and $ anchors require the entire cell to be a valid email, not just contain one somewhere. This catches records where someone accidentally pasted a full name or a URL into the email field.
You now have a boolean column flagging every bad email. Filter or export those separately for manual review. Bad emails rarely need to be deleted; they need to be sent back to the owner for correction. A trade show list with 200 typos becomes valuable again once someone spends an hour fixing them.
How do you fill in missing first and last names?
CRM records often have a full name but no first or last name split, or vice versa. Reporting and personalization suffer when the name fields are inconsistent.
df['first_name'] = df['first_name'].fillna(
df['full_name'].str.split().str[0]
)
df['last_name'] = df['last_name'].fillna(
df['full_name'].str.split().str[-1]
)
That fills the gaps by parsing the full name column when the split columns are empty. fillna replaces missing values only, so existing first and last names stay untouched. str.split() breaks the full name on whitespace, and str[0] and str[-1] grab the first and last tokens.
The approach breaks on names with more than two words. "Mary Beth Johnson" becomes first="Mary", last="Johnson", losing "Beth". For most CRM use cases this is acceptable. For names with cultural patterns like "de la Cruz" or "van der Berg", write a more sophisticated parser or accept the compromise. Marianne Gatlabayan chooses the simple version and flags edge cases for manual review.
How do you deduplicate company name variants like Google and Google Inc?
"Google", "Google Inc", "Google Inc.", and "GOOGLE" are one company. To Pandas they are four separate accounts. Every sales report that groups by company overstates the account count.
Basic normalization catches most of it:
def clean_company(name):
if pd.isnull(name):
return None
name = str(name).strip().title()
for suffix in [' Inc', ' Inc.', ' Llc', ' Llc.', ' Ltd', ' Ltd.', ' Corp', ' Corp.']:
if name.endswith(suffix):
name = name[:-len(suffix)].strip()
return name
df['company_clean'] = df['company'].apply(clean_company)
The function strips whitespace, applies title case, then removes common corporate suffixes. Add more suffixes as your data needs them. Common additions include " Company", " Co", " Group", " Holdings", " GmbH", " SA", and " AB" for international coverage.
For fuzzy matching beyond that (Google vs Googel, Facebook vs Facebok), install rapidfuzz and use its ratio scoring. rapidfuzz.fuzz.ratio("Google", "Googel") returns 91, and any pair above 85 is usually the same company. That is a longer topic and belongs in a follow-up post because it opens up decisions about auto-merging versus flagging for review.
How do you fix inconsistent date formats?
CRM date columns arrive as strings in whatever format the exporter chose. 9/24/2026, 24-09-2026, 2026-09-24T00:00:00Z. Filtering or sorting on these string values produces wrong results because Pandas treats them alphabetically.
Convert to real dates first.
df['created_date'] = pd.to_datetime(df['created_date'], errors='coerce')
df['last_activity'] = pd.to_datetime(df['last_activity'], errors='coerce')
errors='coerce' turns unparseable dates into NaT (Not a Time) instead of crashing the script. Now you can filter and sort on those columns properly. Records with unparseable dates get NaT values, which you can filter to find and fix.
Flag stale contacts:
from datetime import datetime, timedelta
cutoff = datetime.now() - timedelta(days=90)
df['is_stale'] = df['last_activity'] < cutoff
Now every contact whose last activity was more than 90 days ago gets marked True in a new column. Use this flag to trigger re-engagement campaigns, downgrade lead scores, or exclude stale contacts from active nurture flows.
How do you export the cleaned CRM file and audit report?
Two files come out of the pipeline. The cleaned CRM export ready to re-import. A quality report you send to whoever asked for the cleanup.
df.to_csv('crm_cleaned.csv', index=False)
quality_report = pd.DataFrame({
'metric': ['Total rows', 'Duplicates removed', 'Invalid emails', 'Stale contacts'],
'count': [len(df), duplicate_count, invalid_count, df['is_stale'].sum()]
})
quality_report.to_csv('data_quality_report.csv', index=False)
The index=False argument prevents Pandas from writing its internal row index as an extra column, which would break re-import into the CRM. The quality report gives leadership a one-page summary of what changed.
For a more polished deliverable, add before-and-after counts. Store the initial diagnostic numbers at the start of the script, then compare against the final state. duplicates_before = df.duplicated().sum() before the dedupe step, then duplicates_after = df.duplicated().sum() after, and report the difference.
What do you do with the bad records?
Never delete duplicates or invalid records without saving them first. Keep an audit trail so you can restore anything the script wrongly flagged and so you have evidence of the cleanup for whoever asks.
bad_records = df[~df['email_valid']]
bad_records.to_csv('records_needing_review.csv', index=False)
Send that file back to the CRM owner. Bad records almost always have a story. A trade show list that was uploaded raw. A form that broke last quarter. Someone learning your team's process and pasting a full name into the email field.
Save duplicates separately too. dupes = df[df.duplicated(subset='email_clean', keep=False)] returns every row that participates in a duplicate cluster, including the row you kept. Export that file so the CRM owner can verify which record you chose to keep. Marianne Gatlabayan always sends both files with the cleaned export so the client sees the full picture.
How often should you rerun the cleaning script?
A one-off cleanup lasts about 90 days before decay eats the gains back. Schedule this script on a cron job or GitHub Actions runner. Manual reruns get forgotten within a month.
Best practice from data quality research: re-verify B2B contact data on a rolling 90-day cadence. That is the industry benchmark, and Pandas plus a scheduled job is the free version of what most enrichment tools charge $500 to $5,000 per month for. Set the script to email the quality report on completion so someone actually reads the results.
For a truly hands-off setup, wrap the script in a Docker container and deploy to a free cloud runner like GitHub Actions. Add secrets for the CRM API credentials. The script pulls fresh data from the CRM, cleans it, writes the results back via API, and emails the report, all without any manual step. This is the version Marianne Gatlabayan runs for the franchise brands she supports.
Frequently Asked Questions
Do I need to know Python to use this script?
Basic familiarity helps. If you can read the code above and understand what each section does, you can adapt it. Google Colab lets you run it in a browser without installing anything, which makes the first attempt frictionless.
Will this script work on any CRM export?
Any CSV export works. The column names in the script (email, phone, company) may need adjustment to match your CRM's export format. Rename the column references at the top of the script and everything else adapts.
Can I run this on a Mac or Windows?
Yes. Python and Pandas work on both. Install Python from python.org, then run pip install pandas in the terminal. Linux works too and is often the target when the script gets scheduled on a cloud runner.
Does the script actually change my CRM?
No. The script reads the export, produces a cleaned file, and leaves the original CRM untouched. You import the cleaned file back into your CRM manually. This safety gap is intentional. You always have the original export to fall back to if the cleanup goes wrong.
How long does the script take to run?
On a 10,000-row CRM export, the script runs in under 10 seconds on any modern laptop. On a 100,000-row export, expect around a minute. Beyond that, consider moving the workload to a real database, which is when the data pipeline guide becomes relevant.
What to read next
- Regex Patterns for Non-Programmers for cleaning fields directly in Google Sheets
- The Beginner's Guide to Automating Recurring Reports with Google Apps Script for scheduling this script as an automated job
- Setting Up Your First Data Pipeline for building an ETL flow around the cleanup
About the Author: Marianne Gatlabayan is a Systems Strategist and Industrial Engineer focused on optimizing operations, automating workflows, and driving business growth. With over a decade of experience in analytics, reporting, and process improvement, she helps organizations build scalable systems that turn complexity into measurable results. In her free time, Marianne enjoys discovering and sipping her favorite coffee bean of the week. Connect on LinkedIn.