Mailchimp Audience Export Sample CSV (Dirty + Clean)
A realistic Mailchimp subscriber export (12,000 rows) covering inconsistencies that break ESP migrations and cohort analytics. The Mailchimp quirk: OPTIN_TIME is frequently blank for API-imported contacts (bypassing native double opt-in). This causes pandas to_datetime() and SQL date-parsers to throw NaT or NULL errors. The 'dirty' version includes 4,800 blank OPTIN_TIME values, mixed date formats (MM/DD/YYYY vs YYYY-MM-DD in same column), and emails with trailing spaces ruining engagement calculations. Ideal for ESP migration dry runs (to Klaviyo/ActiveCampaign) and deliverability audits. All processing happens locally — your subscriber list stays private.
Live Interactive Preview
Live Data Preview
See how this messy mailchimp data looks before downloading. All processing happens locally in your browser.
💡 Pro Tip: Pandas just threw a NaT error because Mailchimp mixed MM/DD/YYYY and YYYY-MM-DD. Run this file through Format Cleaner to standardize dates and handle null opt-ins.
⚡ Next Step: Moving to Klaviyo? Prep this audience with Klaviyo Prep — normalize email casing and format dates to Klaviyo's strict ISO 8601 standard before migration.
Data Schema Definition
| Column | Type | Description |
|---|---|---|
| Email Address | string | ⚠️ TRAP: Trailing whitespace causes duplicate subscribers on re-import. Always TRIM + LOWERCASE before migration. |
| First Name | string | FNAME merge tag. May contain HTML entities from signup forms; sanitize before personalization. |
| Last Name | string | LNAME merge tag. Blank for ~20% of API-imported contacts; handle gracefully in templates. |
| MEMBER_RATING | integer | Mailchimp 1-5 star engagement score. Recalculated weekly; stale in static exports. Don't use for real-time segmentation. |
| OPTIN_TIME | timestamp | ⚠️ CRITICAL: NULL for API-imported contacts (~40% of list). Causes NaT in pandas, NULL in SQL. COALESCE with SIGNUP_TIME or exclude. |
| OPTIN_IP | string | IPv4 address at opt-in. NULL when OPTIN_TIME is null. Required for GDPR consent audit trails. |
| CONFIRM_TIME | timestamp | ⚠️ TRAP: Mixed MM/DD/YYYY and YYYY-MM-DD in same column. Auto-detection fails; requires explicit format specification. |
| SUBSCRIPTION_SOURCE | string | Signup origin: form | api | import | admin. Critical for consent compliance; 'import' lacks explicit opt-in proof. |
| LANGUAGE | string | ISO 639-1 code (en, es, fr). Defaults to 'en' if unset; verify before localized campaign sends. |
| VIP | boolean | Manual VIP flag. Not correlated with engagement; segment separately from MEMBER_RATING. |
⚠️ Highlighted rows contain known data traps. Review descriptions before building ETL pipelines.