#!/usr/bin/env python3 """HubSpot companies export -> Apify input -> HubSpot re-import CSV. prepare: read a HubSpot companies export CSV, keep companies with a domain that are missing a phone (or all with --all), dedupe, skip platform domains, write websites.json ({"websites": [...]}) for shephouse/website-contact-enricher. merge: read the export + the Actor's dataset JSON, write hubspot_import.csv keyed by Record ID. Existing Phone Number values are never overwritten. 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"} def norm(d): d = (d or "").strip().lower() d = re.sub(r"^https?://", "", d).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 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): d = norm(col(r, "Company Domain Name", "Website URL")) if not d or "." not in d: skipped.append((col(r, "Record ID"), "no domain")); continue if any(d == s or d.endswith("." + s) for s in SKIP): skipped.append((col(r, "Record ID"), "platform domain " + d)); continue if not all_rows and col(r, "Phone Number"): skipped.append((col(r, "Record ID"), "already has phone")); continue if d in seen: skipped.append((col(r, "Record ID"), "duplicate " + d)); continue seen.add(d); sites.append(d) json.dump({"websites": sites, "maxPagesPerDomain": 6, "defaultCountry": "US", "delaySecs": 1}, open(out, "w"), indent=2) print(f"{len(sites)} domains -> {out}") for rid, why in skipped: print(f" skipped Record ID {rid}: {why}") def merge(export, dataset, out="hubspot_import.csv"): by = {} for it in json.load(open(dataset, encoding="utf-8")): if it.get("domain"): by[norm(it["domain"])] = it fields = ["Record ID", "Company Domain Name", "Phone Number", "Public contact email", "Email deliverability", "Contactability score"] n = 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): d = norm(col(r, "Company Domain Name", "Website URL")) it = by.get(d) if not it: continue phones = [p.get("e164") for p in it.get("phones") or [] if p.get("e164")] emails = [e for e in it.get("emails") or [] if not re.match(r"(no-?reply|donotreply)@", e, re.I)] status = (it.get("deliverability") or {}).get("status", "UNKNOWN") w.writerow({ "Record ID": col(r, "Record ID"), "Company Domain Name": d, "Phone Number": col(r, "Phone Number") or (phones[0] if phones else ""), "Public contact email": 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}") 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: hubspot_enrich_prep.py prepare export.csv [--all] | merge export.csv dataset.json")