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 .xls files 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 accepts MM/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.

Lead List Output Formatting