Lead List Output Formatting
Purpose
This recipe transforms raw lead data into CRM-ready and outreach-tool-ready import files with standardized schemas, platform-specific column mappings, and personalization angles. The output is a validated, deduplicated CSV file that imports directly into Salesforce, HubSpot, Pipedrive, or outreach tools (Instantly, Lemlist, Apollo) without manual field mapping.
Prerequisites
- Raw lead data — CSV or spreadsheet with minimum columns: first_name, last_name, email, company_name
- ICP definition — to filter and tag leads by fit score
- Target CRM credentials — login or API key for the destination platform
- Email verification account — NeverBounce, ZeroBounce, or MillionVerifier (free tier sufficient for <1,000 leads)
Constraints
- CRM field names are case-sensitive in Salesforce. Use exact API names (
FirstName,LastName,Email,Company) — not display labels. [src2] - HubSpot accepts
.csv,.xlsx, or.xlsfiles up to 512MB (paid) or 20MB (free). First row must contain headers matching HubSpot property internal names. [src1] - Email addresses serve as the universal deduplication key across all CRM and outreach platforms. [src6]
- Date fields: Salesforce requires
YYYY-MM-DD. HubSpot acceptsMM/DD/YYYY,YYYY-MM-DD, or Unix timestamp. [src3] - Outreach tools require specific variable names — Instantly:
{{firstName}}, Lemlist:{{firstName}}, Apollo:{{first_name}}. [src4][src5]
Tool Selection Decision
| Path | Tools | Cost | Speed | Output Quality |
|---|---|---|---|---|
| A: Manual Sheets | Google Sheets | $0 | 30 min | Medium |
| B: Sheets + Templates | Google Sheets + formulas | $0 | 20 min | High |
| C: Python Script | Python + pandas | $0 | 15 min | High |
| D: API Pipeline | Python + CRM API | $0 | 10 min | Excellent |
Execution Flow
Step 1: Audit and Clean Raw Data
Duration: 5-10 minutes. Review raw lead data for completeness. Remove rows missing email. Standardize name capitalization to Title Case. Split full names into first_name and last_name if combined. Validate email format with regex pattern. Required: email, first_name, last_name, company_name. Recommended: job_title, linkedin_url, phone (E.164 format).
Verify: Zero rows with empty email. All names in Title Case. No test/spam entries.
Step 2: Verify Emails
Duration: 5-15 minutes. Upload email column to NeverBounce, ZeroBounce, or MillionVerifier. Remove emails scoring below 90% confidence. Mark catch-all domains for manual review. Target: verification rate > 85%.
Verify: Verification rate above 85%. All invalid and unknown results removed.
Step 3: Map Columns to Target CRM Schema
Duration: 5-10 minutes. Rename columns to match target platform's required field names. Salesforce: FirstName, LastName, Email, Company, Title. HubSpot: firstname, lastname, email, company, jobtitle. Outreach tools: map first_name to tool-specific variables.
Verify: All required columns present for target platform. No column name typos. Test with 5-row sample import.
Step 4: Add Personalization Columns
Duration: 5-10 minutes. Add personalization_line (specific reference about the lead), pain_point (mapped from ICP to their role/industry), and value_prop (tailored one-liner). For batch personalization, use conditional formulas or LLM-assisted generation.
Verify: Every lead has at least one non-empty personalization column. No placeholder text remaining.
Step 5: Deduplicate and Generate Final Files
Duration: 5 minutes. Remove duplicates by email. Sort by lead score. Generate three files: CRM import file, outreach tool file, and quality report JSON with stats on completeness, verification rate, and duplicate count.
Verify: File row counts match expected totals. Zero duplicate emails. All date fields in correct format.
Quality Benchmarks
| Quality Metric | Minimum | Good | Excellent |
|---|---|---|---|
| Email verification rate | > 80% | > 90% | > 95% |
| Data completeness | > 60% | > 80% | > 95% |
| Personalization coverage | > 50% | > 80% | 100% |
| CRM import success rate | > 90% | > 95% | 100% |
Error Handling
| Error | Cause | Recovery |
|---|---|---|
| CRM import rejects file | Column headers don't match field names | Rename headers to exact API names from Step 3 mapping |
| Duplicate detection blocks import | Email already exists in CRM | Use "update existing" instead of "create new" option |
| Date format errors | Wrong format for target CRM | Convert all dates to YYYY-MM-DD (Salesforce) or Unix timestamp (HubSpot) |
| Character encoding issues | Non-UTF-8 characters in names | Re-save CSV as UTF-8 with BOM |
| Outreach tool rejects personalization | Variable names don't match tool syntax | Check syntax: Instantly/Lemlist use {{var}}, Apollo uses {{var}} |
Cost Breakdown
| Component | Free | Paid | At Scale |
|---|---|---|---|
| Data cleaning | $0 | $0 | $0 |
| Email verification | 100-1,000 free | $0.003-$0.01/email | $0.001-$0.005/email |
| Personalization enrichment | Manual ($0) | $0.05-$0.15/lead | $0.02-$0.08/lead |
| Total for 500 leads | $0 | $5-$15 | $10-$40 |
Anti-Patterns
Wrong: Importing unverified emails directly into CRM
Unverified emails cause 15-30% bounce rates, damage sender reputation, and risk domain blacklisting. [src2]
Correct: Verify all emails before import
Run every email through a verification service. Remove invalid addresses. Flag catch-all domains for manual review.
Wrong: Using display names as CRM field headers
Headers like "First Name" or "Company" may not match CRM API field names. Salesforce silently maps to wrong fields. [src3]
Correct: Use exact API field names from documentation
Map to internal field names: FirstName (Salesforce), firstname (HubSpot). Test with a 5-row sample import first.
When This Matters
Use this recipe after lead scraping and enrichment is complete and before loading outreach sequences. CRM data quality directly impacts deliverability, personalization effectiveness, and sales team trust in the pipeline.