Turn a Small Mailchimp Lead Export into a Reviewable CSV with Python

A technology-filtered website list is a starting point for research. Before it reaches a CRM, someone still needs to inspect the evidence, handle repeat hostnames and decide which records deserve a closer look.

This walkthrough builds two local CSV files from an already downloaded JSON export: candidates for human review and exceptions that need attention. It makes no network calls and sends no messages.

Disclosure: ApiVault Labs publishes this guide and maintains the Mailchimp Leads Database used for the export step. The four-row example below is synthetic, not an Actor run or a customer result.

Start with an explicit, small export

For an agency researching websites associated with Mailchimp, the important distinction is the kind of evidence behind a row. A record can belong to an original named dataset, or it can have a saved technology-token match. Those are different statements. Neither proves a website's current installation.

This input requests up to ten domains with a stored email field and a positive saved Mailchimp token:

{
  "workflow": "export",
  "hasEmail": true,
  "maxItems": 10,
  "outputPreset": "contacts",
  "requireStoredTechnology": true
}

These fields were checked against the published build's input schema on 13 November 2026. This is an input example, not a claim that ten records were returned. Fewer records, or none, can match.

Inspect the Actor's current Pricing tab and the run's resource settings before starting. A small result limit controls the example's size; it is not a total-spend limit. A count workflow is also not automatically free: start and platform usage can still apply.

Download the Dataset as JSON. Keep the run and Dataset IDs in your own work notes so the saved export can be found again.

Preserve uncertainty instead of turning it into a sales claim

The output contract includes Root Domain, Emails, _platformEvidence and _storedTechnologyMatch. This example uses three of those fields:

• Root Domain identifies the website record to review.
• Emails is stored contact text. A populated field does not validate a mailbox.
• _storedTechnologyMatch: true means a saved token matched. It does not establish a live installation, a subscriber list or permission to send a campaign.

The script leaves the contact text intact. It deliberately does not split it into mailboxes or label it deliverable. A row without positive saved evidence goes to the exceptions file.

Build the two queues

Save this as normalize_leads.py. It uses only Python's standard library.

"""Prepare a review queue from an already downloaded JSON export. No network calls."""
import csv
import json
import sys
from pathlib import Path
from urllib.parse import urlsplit


def hostname(value):
    value = str(value or "").strip()
    try:
        parsed = urlsplit(value if "://" in value else "https://" + value)
        if parsed.username or parsed.password:
            return ""
        host = (parsed.hostname or "").lower().rstrip(".")
    except ValueError:
        return ""
    return host[4:] if host.startswith("www.") else host


def spreadsheet_cell(value):
    text = str(value if value is not None else "")
    if text.lstrip().startswith(("=", "+", "-", "@")) or text.startswith(("\t", "\r", "\n")):
        return "'" + text
    return text


def prepare(rows):
    candidates, exceptions, seen = [], [], set()
    for row in rows:
        domain = hostname(row.get("Root Domain"))
        emails = str(row.get("Emails") or "").strip()
        reason = "inspect_website"
        if not domain or "." not in domain:
            reason = "missing_or_invalid_hostname"
        elif row.get("_storedTechnologyMatch") is not True:
            reason = "no_positive_stored_technology_match"
        elif not emails:
            reason = "missing_stored_email"
        elif domain in seen:
            reason = "duplicate_hostname"
        record = {"hostname": domain, "stored_emails": emails, "review_status": reason}
        if reason == "inspect_website":
            seen.add(domain)
            candidates.append(record)
        else:
            exceptions.append(record)
    return candidates, exceptions


def write_csv(path, rows):
    with Path(path).open("w", encoding="utf-8", newline="") as stream:
        writer = csv.DictWriter(stream, fieldnames=["hostname", "stored_emails", "review_status"])
        writer.writeheader()
        writer.writerows({k: spreadsheet_cell(v) for k, v in row.items()} for row in rows)


if __name__ == "__main__":
    rows = json.loads(Path(sys.argv[1]).read_text(encoding="utf-8-sig"))
    if not isinstance(rows, list) or not all(isinstance(row, dict) for row in rows):
        raise SystemExit("Input must be an array of JSON objects")
    candidates, exceptions = prepare(rows)
    write_csv("candidates.csv", candidates)
    write_csv("exceptions.csv", exceptions)
    print(f"Candidates for human review: {len(candidates)}; exceptions: {len(exceptions)}")

The hostname rule is deliberately narrow. It lowercases the hostname, removes a leading www. and drops a trailing dot. It does not calculate registrable domains, merge arbitrary subdomains, or decide whether two websites belong to the same company.

When the normalized hostname appears again, the extra row stays in the exceptions file. That keeps conflicting contact text available for review instead of silently discarding it. Formula-like CSV cells receive a leading apostrophe before they are opened in a spreadsheet.

Check it with synthetic records first

Save the following as sample.json. All hostnames use the reserved .example suffix.

[
  {"Root Domain":"https://WWW.BRIGHT-SHOP.EXAMPLE/catalog","Emails":"[e-mail]","_storedTechnologyMatch":true},
  {"Root Domain":"bright-shop.example","Emails":"[e-mail]","_storedTechnologyMatch":true},
  {"Root Domain":"second-shop.example","Emails":"","_storedTechnologyMatch":true},
  {"Root Domain":"third-shop.example","Emails":"[e-mail]","_storedTechnologyMatch":false}
]

Run:

python normalize_leads.py sample.json

The tested synthetic example produces one candidate and three exceptions. The repeat hostname, missing contact text and absent positive technology match each remain visible. This checks the transformation; it does not measure the quality of a live lead database.

Review before importing

Open each candidate website. Check whether it is still an active business, whether the service you offer is relevant, and whether a suitable business contact route is publicly available. Record the check date and the reason for keeping or excluding the site.

Do not automatically email candidates.csv. Its status is inspect_website, not approved outreach. If you later import selected records, carry the review date, evidence category and unresolved questions into the CRM rather than keeping only a name and email address.

For a fresh source export, Mailchimp Leads Database provides the small-preview input used here. Start with a reviewable sample, then decide whether the observed results fit your workflow.