#!/usr/bin/env python3 """Pipedrive Organizations export -> Apify input -> Pipedrive update-import CSV. prepare: read a Pipedrive Organizations export CSV, keep organizations 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 pipedrive_import.csv with the Pipedrive System ID, so the import updates the exact organizations 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: accepts "Organization - ID" or "ID", "Organization - Name" or "Name", "Organization - Website" or "Website", and your custom "Phone" / "Email" fields (with or without the "Organization - " prefix). Override with --phone-col / --email-col. Standard library only. No token 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) 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: for k in (n, "Organization - " + n): if k in row and row[k] is not None: return row[k].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 prepare(export, out="websites.json", all_rows=False): seen, sites, skipped = set(), [], [] for r in read_export(export): oid = col(r, "ID") or col(r, "Name") d = norm(col(r, "Website")) if not d or "." not in d: skipped.append((oid, "no website")); continue if any(d == s or d.endswith("." + s) for s in SKIP): skipped.append((oid, "platform domain " + d)); continue if not all_rows and col(r, PHONE) and col(r, EMAIL): skipped.append((oid, "already has phone and email")); continue if d in seen: skipped.append((oid, "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 oid, why in skipped: print(f" skipped org {oid}: {why}") def merge(export, dataset, out="pipedrive_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 = ["ID", "Name", "Website", PHONE, EMAIL, "Email deliverability", "Contactability score"] n = kept = 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: 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) w.writerow({ "ID": col(r, "ID"), "Name": col(r, "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})") 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: pipedrive_enrich_prep.py prepare export.csv [--all] | " "merge export.csv dataset.json [--phone-col NAME] [--email-col NAME]")