Operations • 7 Min Read

Cleaning Up Your CRM Data with AI

Your customer list is likely full of duplicates, bad formatting, and missing fields. Here is the exact workflow to fix it using LLMs without coding.

If you have been in business for more than a year, your CRM is probably a mess. "John Smith" is listed three times. Phone numbers have different formats. Companies are spelled "Inc." and "Incorporated."

Historically, fixing this meant spending weekends in Excel hell or hiring a VA. But with the large context windows of models like Gemini 1.5 Pro or Claude 3.5 Sonnet, we can automate the cleanup logic.

The "Context Window" Revolution

Standard ChatGPT (GPT-4) used to struggle with large CSV files. It would forget rows or truncate data. However, newer models can now ingest massive amounts of data (100k+ tokens) in a single prompt.

This allows us to upload a raw CSV export and ask the AI to act as a Data Hygiene Engineer.

⚠️ Critical Security Warning

Never upload Personally Identifiable Information (PII) like Credit Cards, SSNs, or private medical data to a public LLM. Before using this workflow, anonymize your data (replace Names with IDs) or use "Enterprise Mode" where data training is disabled.

The Cleaning Workflow

Here is the 3-step process to clean a messy list of leads:

1. Preparation

Export your contacts to CSV. Remove columns you don't need (created_at, tags) to save tokens. Keep only the identifiers: First Name, Last Name, Email, Company.

2. The Logic Prompt

We don't just ask the AI to "fix it." We give it a rigid set of rules. Copy this prompt into Claude or Gemini:

SYSTEM_PROMPT.txt COPY

ROLE: You are a Data Hygiene Expert. TASK: Analyze the attached CSV file containing lead data. Your goal is to identify duplicates and standardize formatting. RULES: 1. {Deduping}: Identify duplicates based on fuzzy matching of Email OR (First Name + Last Name). Keep the row with the most complete data. 2. {Formatting}: Capitalize Names (Title Case). Format all phone numbers to E.164 standard (+1...). 3. {Company Names}: Remove legal suffixes like "LLC", "Inc", "Ltd" to standardize company names. OUTPUT: Return a clean CSV format code block ready for export. Do not summarize. Just give me the data.

3. Re-Import

Copy the output code block, save it as a new `.csv` file, and re-import it into your CRM (HubSpot, Salesforce, etc.) using the "Update existing records" feature.

Why this matters

Clean data means higher email deliverability and better personalization. When you trust your data, you can automate your outreach without fear of sending "Hi JANE" (all caps) to a VIP client.