The Problem Begins: "Haven't I Seen This Email Before?"
We receive dozens of buyer inquiries every week. We've been manually entering emails, phone numbers, and company names into Excel one by one. One day, a team member muttered, "Wait... didn't we contact this company last week?"
It was an intuition. A gut feeling that something had slipped through the cracks.
Before: The Limits of Manual Validation
This is how we used to do it:
Excel → scroll through manually → "Hmm, is this a duplicate?" → delete by hand → next row → repeat
The result? By the following Friday, duplicates appeared again. You simply can't review all 3,000 rows with your eyes.
The Automation Solution: Claude Code Validation Bot
On our Windows device, we created a simple Python script.
# Buyer data duplicate validation logic
import pandas as pd
from difflib import SequenceMatcher
# 1. Detect complete duplicates
# 2. Email format validation (check @, verify domain)
# 3. Phone number regex validation
# 4. Flag items with 70%+ similarity
What the bot does:
• **Complete Duplicates**: Detects when the same email or phone appears across multiple rows
• **Format Errors**: Catches missing @ symbols, non-numeric phone numbers, etc.
• **Suspicious Duplicates**: Flags similar entries like "john@company.com" and "j.john@company.com"
• **Auto Cleanup**: Preserves the most recent entry and removes older ones
After: What We Discovered in One Run
The results from the first execution were shocking.
| Validation Item | Count Found |
|---------|----------|
| Complete duplicates | 48 |
| Email format errors | 23 |
| Phone format errors | 15 |
| Suspicious duplicates (70%+ match) | 14 |
| **Total Cleaned** | **100** |
We had missed 100 entries. We also discovered we'd been sending auto-replies to incorrect email addresses for three weeks straight.
Actual Results
Before: 3,000 rows with 100 duplicate/error records mixed throughout
After: 2,900 rows of "trustworthy" buyer data
The most dramatic change was email delivery rate. Previously, we received an average of 15-20 "undeliverable" bounce messages per week. After automation, that dropped to 3-5.
The Patterns the Bot Revealed
When analyzing the validation results, we discovered several patterns:
1. **Time-Based Error Rates**: Data entered at night had significantly higher error rates (fatigue factor)
2. **Regional Format Issues**: Buyers from certain countries made nearly identical formatting mistakes
3. **Repeat Contact Cycles**: The same buyers re-inquired at 2-3 week intervals
Using this information, we improved our input form and added automatic duplicate-warning features.
Now
Every Monday at 10 a.m., the bot automatically runs validation. If any errors slip through, the team gets an alert immediately. Manual validation time is essentially zero.
That question, "Haven't I seen this email before?" is now handled by the bot.