← back to Rentv Sheet Enrich Refine
enrich Layer 1: website-from-email-domain engine + dry-run (1281 websites derivable $0)
f280a732ed6eec303146e4fa2cc21beeafddefc2 · 2026-08-13 09:20:03 -0700 · Steve Abrams
Files touched
A __pycache__/lib.cpython-314.pycA enrich.py
Diff
commit f280a732ed6eec303146e4fa2cc21beeafddefc2
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Aug 13 09:20:03 2026 -0700
enrich Layer 1: website-from-email-domain engine + dry-run (1281 websites derivable $0)
---
__pycache__/lib.cpython-314.pyc | Bin 0 -> 6024 bytes
enrich.py | 117 ++++++++++++++++++++++++++++++++++++++++
2 files changed, 117 insertions(+)
diff --git a/__pycache__/lib.cpython-314.pyc b/__pycache__/lib.cpython-314.pyc
new file mode 100644
index 0000000..4319f88
Binary files /dev/null and b/__pycache__/lib.cpython-314.pyc differ
diff --git a/enrich.py b/enrich.py
new file mode 100644
index 0000000..ae83374
--- /dev/null
+++ b/enrich.py
@@ -0,0 +1,117 @@
+#!/usr/bin/env python3
+"""
+enrich.py — RENTV CRE-sheet enrichment engine.
+
+LAYER 1 (this file, $0, deterministic): derive each company's Website from its
+existing corporate email domain, and add a Website/LinkedIn/Marketing-VP column set.
+Only EMPTY target cells are filled; existing data is never overwritten. Every filled
+cell is stamped light-green by lib.batch_fill.
+
+Modes:
+ --dry-run read local data/tab_<gid>.csv, report projected fills, write NOTHING
+ --apply read the LIVE tab via the Sheets API and write fills (needs connect.py)
+
+LinkedIn + Marketing/VP contact enrichment is LAYER 2 (research) — added next,
+after Layer 1 is verified on the live sheet.
+"""
+import sys, os, csv, re
+
+sys.path.insert(0, os.path.dirname(__file__))
+import lib
+
+FREEMAIL = {"gmail.com","hotmail.com","yahoo.com","outlook.com","aol.com","icloud.com",
+ "me.com","comcast.net","att.net","sbcglobal.net","live.com","msn.com","ymail.com",
+ "protonmail.com","mac.com","verizon.net","cox.net"}
+
+# Per-tab config keyed by gid (=sheetId). Column indices are 0-based.
+# header_row : row index holding column titles (0-based)
+# data_start : first data row (0-based)
+# company/email : source column indices
+# new_website/new_linkedin/new_mvp : destination column indices for the added set
+TABS = {
+ 1536880271: dict(name="Sponsor & Speaker Companies",
+ header_row=0, data_start=2, company=2, email=6,
+ new_website=10, new_linkedin=11, new_mvp=12),
+ 2087021739: dict(name="Strong Prospects",
+ header_row=0, data_start=1, company=3, email=7,
+ new_website=9, new_linkedin=10, new_mvp=11),
+ 1929460437: dict(name="Event Sponsors",
+ header_row=0, data_start=1, company=2, email=6,
+ new_website=8, new_linkedin=9, new_mvp=10),
+}
+
+def cell(row, i):
+ return row[i].strip() if i < len(row) else ""
+
+def domain_from_emails(emailcell):
+ """Pick the first corporate (non-freemail) domain from a possibly multi-email cell."""
+ for m in re.findall(r'[\w.+-]+@([\w-]+\.[\w.-]+)', emailcell or ""):
+ d = m.lower().strip(".")
+ if d not in FREEMAIL:
+ return d
+ return ""
+
+def plan_tab(gid, rows):
+ """Return (fills, stats). fills = list of {row0,col0,value}."""
+ cfg = TABS[gid]
+ fills, stats = [], dict(rows=0, has_email=0, website_fill=0, freemail_only=0, no_email=0)
+ # header cells for the new columns (only if currently blank)
+ hr = cfg["header_row"]
+ hdr = rows[hr] if hr < len(rows) else []
+ for col, label in [(cfg["new_website"],"Website"),(cfg["new_linkedin"],"LinkedIn"),
+ (cfg["new_mvp"],"Marketing/VP Contact")]:
+ if cell(hdr, col) == "":
+ fills.append({"row0": hr, "col0": col, "value": label})
+ for r in range(cfg["data_start"], len(rows)):
+ row = rows[r]
+ comp = cell(row, cfg["company"])
+ if not comp:
+ continue
+ stats["rows"] += 1
+ emailc = cell(row, cfg["email"])
+ # only fill Website if that new cell is currently empty
+ if cell(row, cfg["new_website"]) != "":
+ continue
+ dom = domain_from_emails(emailc)
+ if dom:
+ stats["has_email"] += 1; stats["website_fill"] += 1
+ fills.append({"row0": r, "col0": cfg["new_website"], "value": dom})
+ elif emailc:
+ stats["freemail_only"] += 1 # has email but only free-mail -> needs Layer 2
+ else:
+ stats["no_email"] += 1 # no email -> needs Layer 2
+ return fills, stats
+
+def load_csv(gid):
+ path = os.path.join(os.path.dirname(__file__), "data", f"tab_{gid}.csv")
+ with open(path, newline="", encoding="utf-8", errors="ignore") as f:
+ return list(csv.reader(f))
+
+def main():
+ mode = "--dry-run" if "--apply" not in sys.argv else "--apply"
+ tok = lib.access_token() if mode == "--apply" else None
+ meta = lib.get_meta(tok) if tok else None
+ title_by_gid = {}
+ if meta:
+ for s in meta["sheets"]:
+ title_by_gid[s["properties"]["sheetId"]] = s["properties"]["title"]
+
+ for gid, cfg in TABS.items():
+ if mode == "--apply":
+ rows = lib.read_tab(tok, title_by_gid[gid])
+ else:
+ rows = load_csv(gid)
+ fills, stats = plan_tab(gid, rows)
+ data_fills = [f for f in fills if f["value"] not in ("Website","LinkedIn","Marketing/VP Contact")]
+ print(f"\n=== {cfg['name']} (gid={gid}) ===")
+ print(f" companies: {stats['rows']} | website derivable now: {stats['website_fill']}"
+ f" | freemail-only (Layer2): {stats['freemail_only']} | no-email (Layer2): {stats['no_email']}")
+ print(f" cells to fill this run: {len(fills)} ({len(data_fills)} websites + headers)")
+ for f in data_fills[:5]:
+ print(f" -> R{f['row0']+1}C{lib.col_letter(f['col0'])}: {f['value']}")
+ if mode == "--apply":
+ res = lib.batch_fill(tok, gid, fills)
+ print(f" APPLIED: {res.get('totalUpdatedCells','?')} cells written (light-green).")
+
+if __name__ == "__main__":
+ main()
← 915689c scaffold: Sheets API connect + stdlib read/write lib with li
·
back to Rentv Sheet Enrich Refine
·
Layer 1 LIVE: websites + 4 new cols (Website/LinkedIn Co/Lin 3661344 →