#!/usr/bin/env python3 """Salesforce Accounts export -> Apify input -> Data Import Wizard update CSV. prepare: read a Salesforce Accounts report/export CSV, keep accounts that have a Website and are missing a Phone or Email (or all with --all), dedupe, skip social/directory domains, write websites.json for shephouse/website-contact-enricher. merge: read the export + the Actor's dataset JSON, write salesforce_import.csv keyed by the 18-character Account ID (15-character IDs are converted), so "Update existing records / Match Account by: Salesforce.com ID" hits the exact accounts you exported. Existing Phone / Email values are never overwritten; no-reply addresses are dropped; email is left blank when the domain has no MX. Column names: "Account ID" (or "Id"), "Account Name" (or "Name"), "Website", "Phone", and your custom email field (default "Email"; override with --email-col, --phone-col). Standard library only. No token or login is read or stored by this script. """ import csv, json, re, sys SKIP = {"facebook.com", "instagram.com", "linkedin.com", "linktr.ee", "yelp.com", "google.com", "x.com", "twitter.com", "wixsite.com", "business.site"} NOREPLY = re.compile(r"^(no-?reply|do-?not-?reply|mailer-daemon)@", re.I) ALPHA = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345" def id18(i): """Convert a 15-char case-sensitive Salesforce ID to the 18-char case-safe form.""" i = (i or "").strip() if len(i) != 15: return i suffix = "" for chunk in (i[0:5], i[5:10], i[10:15]): bits = sum(1 << n for n, c in enumerate(chunk) if "A" <= c <= "Z") suffix += ALPHA[bits] return i + suffix def norm(d): d = (d or "").strip().lower() d = re.sub(r"^https?://", "", d).split("/")[0].split("?")[0].split(":")[0] return d[4:] if d.startswith("www.") else d def col(row, *names): for n in names: if n in row and row[n] is not None: return row[n].strip() return "" def opt(flag, default): return sys.argv[sys.argv.index(flag) + 1] if flag in sys.argv else default PHONE, EMAIL = opt("--phone-col", "Phone"), opt("--email-col", "Email") def read_export(path): with open(path, newline="", encoding="utf-8-sig") as f: return list(csv.DictReader(f)) def aid(r): return col(r, "Account ID", "Id", "ID") def prepare(export, out="websites.json", all_rows=False): seen, sites, skipped = set(), [], [] for r in read_export(export): who = aid(r) or col(r, "Account Name", "Name") d = norm(col(r, "Website")) if not aid(r): skipped.append((who, "no Account ID column value (add it to the report)")); continue if not d or "." not in d: skipped.append((who, "no website")); continue if any(d == s or d.endswith("." + s) for s in SKIP): skipped.append((who, "platform domain " + d)); continue if not all_rows and col(r, PHONE) and col(r, EMAIL): skipped.append((who, "already has phone and email")); continue if d in seen: skipped.append((who, "duplicate " + d)); continue seen.add(d); sites.append(d) with open(out, "w") as f: json.dump({"websites": sites, "maxPagesPerDomain": 6, "defaultCountry": "US", "delaySecs": 1}, f, indent=2) print(f"{len(sites)} websites -> {out}") for who, why in skipped: print(f" skipped account {who}: {why}") def merge(export, dataset, out="salesforce_import.csv"): by = {} with open(dataset, encoding="utf-8") as f: for it in json.load(f): if it.get("domain"): by[norm(it["domain"])] = it fields = ["Account ID", "Account Name", "Website", PHONE, EMAIL, "Email Deliverability", "Contactability Score"] n = kept = conv = 0 with open(out, "w", newline="", encoding="utf-8") as f: w = csv.DictWriter(f, fieldnames=fields); w.writeheader() for r in read_export(export): it = by.get(norm(col(r, "Website"))) if not it or not aid(r): continue phones = [p.get("e164") for p in it.get("phones") or [] if p.get("e164")] status = (it.get("deliverability") or {}).get("status", "UNKNOWN") emails = [e for e in it.get("emails") or [] if not NOREPLY.match(e)] old_p, old_e = col(r, PHONE), col(r, EMAIL) kept += bool(old_p) + bool(old_e) conv += len(aid(r)) == 15 w.writerow({ "Account ID": id18(aid(r)), "Account Name": col(r, "Account Name", "Name"), "Website": col(r, "Website"), PHONE: old_p or (phones[0] if phones else ""), EMAIL: old_e or (emails[0] if emails and status != "NO_MX" else ""), "Email Deliverability": status, "Contactability Score": it.get("contactabilityScore", 0), }); n += 1 print(f"{n} rows -> {out} (existing values kept: {kept}, 15-char IDs converted: {conv})") if __name__ == "__main__": if len(sys.argv) >= 3 and sys.argv[1] == "prepare": prepare(sys.argv[2], all_rows="--all" in sys.argv) elif len(sys.argv) >= 4 and sys.argv[1] == "merge": merge(sys.argv[2], sys.argv[3]) else: sys.exit("usage: salesforce_enrich_prep.py prepare export.csv [--all] | " "merge export.csv dataset.json [--phone-col NAME] [--email-col NAME]")