generate_sample.py

07-Automation/sample/generate_sample.py

1. #!/usr/bin/env python3
2. """
3. generate_sample.py: build the invented client book this vault ships with.
5. Meridian Display Co. is fictional, and so is every client in this vault. This
6. script invents them: 381 Plinth accounts on the roster (plus one new customer
7. who first orders during the sample weeks), two weeks of Plinth `orders.fetch`
8. page dumps, and two weekly Bitrix deal enumerations with their stage histories.
9. It is seeded, so every run produces the same world.
11. `run_sample.py` then replays two Monday runs of the weekly pipeline against
12. these inputs, and everything under 03-Clients/, 05-Outputs/partner-runs/ and
13. 07-Automation/data/ is the pipeline's own output, not hand-written.
15. The world is built to exercise the parts of the driver that matter:
17. * a Plinth day that truncates silently, paged with overlapping offsets, so
18. the ingest convergence loop has to keep going until a page adds nothing;
19. * cancelled (27) and refunded (28) orders that must not count;
20. * clients who cross a tier band mid-week, and a brand-new customer whose
21. first order qualifies them (has_baseline=False);
22. * an approved merge (one person, two Plinth accounts);
23. * a directly-partnered account whose mailboxes are Rhodium by override and
24. never cold-emailed, plus a staff mailbox on the company domain;
25. * an identity twin (same email, two accounts) where only one twin carries
26. the Not Interested closure;
27. * a shared company mailbox used by two different people;
28. * a role-named account and a messy shipping-label name;
29. * retired Bitrix stages, same-minute workflow re-entries, and a cart that
30. moved into production funnels after converting.
32. Usage:
33. python3 07-Automation/sample/generate_sample.py # writes sample/inputs/
34. python3 07-Automation/sample/generate_sample.py --out DIR
36. Needs openpyxl (the roster workbook is an .xlsx, like the real one).
37. """
39. from __future__ import annotations
41. import argparse
42. import datetime as dt
43. import json
44. import random
45. from pathlib import Path
47. SEED = 20260921
48. HERE = Path(__file__).resolve().parent
50. # Two Monday runs. The roster workbook's totals run through BASELINE_AS_OF.
51. BASELINE_AS_OF = dt.date(2026, 9, 7)
52. RUN_DATES = [dt.date(2026, 9, 14), dt.date(2026, 9, 21)]
53. PULL_WINDOW_DAYS = 8 # matches partner-thresholds.md (one day of overlap)
54. TRUNCATE_AT = 50 # Plinth's silent cap on this sample's busy day
55. BUSY_DAY = dt.date(2026, 9, 17)
57. # Id ranges. Everything is invented; the ranges only keep the kinds apart.
58. ACCOUNT_BASE = 40000
59. ORDER_BASE = 61000
60. DEAL_BASE = 400100
61. CONTACT_BASE = 600000
62. TEST_DEALS = ["400017", "400023"] # must match TEST_DEALS in partner_pipeline/stages.py
64. BANDS = [("Palladium Partner", 2500, 8000), ("Platinum Partner", 8000, 25000),
65. ("Iridium Partner", 25000, 75000), ("Rhodium Partner", 75000, 160000)]
67. FIRST = """Ada Aiden Alma Amara Anika Ansel Arlo Isla Audra Avery Beatrix Bennett Bram
68. Briony Callum Calla Cassius Celia Cyrus Dalia Dario Delphine Denver Edie Elio Elowen
69. Emeric Esme Ezra Fallon Felix Fenna Florian Gemma Greer Hale Harriet Idris Ilse
70. Imogen Isaak Jonah Juniper Kai Kasimir Keira Lark Leander Lena Linus Lucia Mabel
71. Magnus Maren Matteo Mira Nadia Nell Niko Noor Oona Orrin Otto Paloma Perrin Petra
72. Quill Remy Rhea Rowan Saoirse Selma Silas Soren Sunniva Tamsin Tobiah Uma Vesper
73. Wendell Wren Xavi Yara Yusuf Zadie Zev""".split()
74. LAST = """Abernethy Achterberg Alcott Ambrose Ashdown Barrowclough Beaumont Birchall
75. Blackwood Bramley Castellanos Cavendish Clemons Coldwell Crane Dalgleish Delacroix
76. Dunmore Eastwood Ellery Fairbairn Farrow Fenwick Gallardo Garroway Greaves Hadley
77. Halloran Hartigan Hawthorne Holloway Ingleby Iverson Jardine Kettering Kingsley
78. Lachance Larkspur Lindgren Lockhart Maddox Marchetti Merriweather Montague Northcott
79. Oakes Ormsby Pemberton Penhaligon Quimby Radcliffe Ravensworth Redfern Rushworth
80. Saltonstall Sandoval Seaton Sheffield Stanhope Sutcliffe Tamsworth Thornbury Trevelyan
81. Underhill Vance Vandermeer Wainwright Westbrook Whitcombe Winterbourne Yardley
82. Zamora""".split()
83. CO_A = """Amber Anchor Arbor Aster Basin Beacon Bluff Cedar Cinder Cobalt Copperline
84. Crescent Driftwood Ember Fernhill Fieldstone Foxglove Granite Harbor Heron Hollow
85. Juniper Keystone Kestrel Lantern Larch Meadowlark Millrace Northwind Oakmoss Orchard
86. Pinecrest Prairie Quarry Redwing Ridgeback Saltmarsh Sandpiper Silverleaf Skylark
87. Stonebridge Sundial Tidewater Timberline Valley Willowbank""".split()
88. CO_B = ["Events", "Outfitters", "Athletics", "Realty", "Brewing Co.", "Clinic Group",
89. "Foundation", "Expo Services", "Coffee Roasters", "Academy", "Dental",
90. "Motorsports", "Credit Union", "Marketing", "Builders", "Film Festival",
91. "Youth Soccer", "Wellness", "Distillery", "Church", "Robotics", "Farms"]
92. INDUSTRIES = ["events services", "sports & recreation", "real estate", "food & beverages",
93. "health care", "nonprofit", "education", "automotive", "financial services",
94. "marketing & advertising", "construction", "religious institutions",
95. "entertainment", "retail", "manufacturing"]
96. TITLES = ["Marketing Manager", "Events Coordinator", "Owner", "Operations Director",
97. "Brand Manager", "Office Manager", "Community Relations Lead", "Executive Director",
98. "Trade Show Manager", "Facilities Coordinator", ""]
99. OWNERS = ["Matt Guerra", "Hazel Okafor", "Nora Lindqvist"]
102. def iso(d: dt.date) -> str:
103. return d.isoformat()
106. class World:
107. def __init__(self, seed: int = SEED):
108. self.r = random.Random(seed)
109. self.accounts: list[dict] = []
110. self.orders: list[dict] = [] # post-baseline orders only
111. self.next_account = ACCOUNT_BASE
112. self.next_order = ORDER_BASE
113. self.used_names: set[tuple[str, str]] = set()
114. self.used_companies: set[str] = set()
115. self.overrides: list[str] = []
116. self.merges: list[dict] = []
117. self.suppression = {"names": [], "emails": [], "domains": []}
118. self.cases: dict[str, dict] = {}
120. # ── people and companies ──────────────────────────────────────────────
121. def person(self) -> tuple[str, str]:
122. while True:
123. n = (self.r.choice(FIRST), self.r.choice(LAST))
124. if n not in self.used_names:
125. self.used_names.add(n)
126. return n
128. def company(self) -> tuple[str, str]:
129. while True:
130. a, b = self.r.choice(CO_A), self.r.choice(CO_B)
131. name = f"{a} {b}"
132. if name not in self.used_companies:
133. self.used_companies.add(name)
134. slug = (a + b.split()[0]).lower().replace(".", "")
135. return name, f"{slug}.example"
137. def phone(self) -> str:
138. return f"({self.r.randint(201, 989)}) 555-01{self.r.randint(0, 99):02d}"
140. def add_account(self, *, ltv: float, last_order: dt.date, orders: int | None = None,
141. first=None, last=None, company=None, domain=None, email=None,
142. group: str = "", extra_email: str = "", title=None, industry=None) -> dict:
143. if first is None:
144. first, last = self.person()
145. if company is None:
146. company, domain = self.company()
147. if email is None:
148. email = f"{first.lower()}.{last.lower()}@{domain}".replace(" ", "")
149. n = orders if orders is not None else max(1, int(ltv // self.r.uniform(900, 2600)) + 1)
150. span = self.r.randint(120, 1600)
151. if n == 0:
152. last_order = None
153. first_order = (last_order - dt.timedelta(days=span if n > 1 else 0)) if last_order else None
154. repeat = 0.0 if n == 1 else round(ltv * self.r.uniform(0.35, 0.8), 2)
155. acc = {
156. "account_ref": self.next_account, "first_name": first, "last_name": last,
157. "email": email.lower(), "extra_email": extra_email, "company": company,
158. "phone": self.phone(), "title": self.r.choice(TITLES) if title is None else title,
159. "website": f"https://www.{domain}" if domain else "",
160. "industry": industry or self.r.choice(INDUSTRIES),
161. "registered": (first_order or BASELINE_AS_OF) - dt.timedelta(days=self.r.randint(0, 400)),
162. "group": group,
163. "baseline": {"ltv": round(ltv, 2), "orders": n, "repeat": repeat,
164. "first": first_order, "last": last_order},
165. }
166. self.next_account += 1
167. self.accounts.append(acc)
168. return acc
170. def add_order(self, acc: dict, day: dt.date, amount: float, status: int = 5) -> dict:
171. ship = round(amount * 0.08, 2)
172. o = {"order_ref": self.next_order, "account": acc, "day": day,
173. "grand_total": round(amount + ship, 2), "subtotal": round(amount, 2),
174. "shipping_total": ship, "status_code": status,
175. "hour": self.r.randint(8, 18), "minute": self.r.randint(0, 59)}
176. self.next_order += 1
177. self.orders.append(o)
178. return o
180. # ── the book ──────────────────────────────────────────────────────────
181. def build(self):
182. r = self.r
183. run = RUN_DATES[-1]
184. dormant_cut = run - dt.timedelta(days=365)
186. def last_date(dormant: bool) -> dt.date:
187. if dormant:
188. return dormant_cut - dt.timedelta(days=r.randint(20, 900))
189. return BASELINE_AS_OF - dt.timedelta(days=r.randint(3, 330))
191. # Partner book by band, then Retail accounts under the lowest band.
192. for count, bi in [(80, 0), (26, 1), (7, 2), (3, 3)]:
193. tier, lo, hi = BANDS[bi]
194. for _ in range(count):
195. ltv = round(r.uniform(lo, hi if bi < 3 else lo * 1.9), 2)
196. dormant = r.random() < 0.55
197. grp = tier if r.random() < 0.25 else ""
198. if grp and bi > 0 and r.random() < 0.3:
199. grp = BANDS[bi - 1][0] # stale store group: a real mismatch
200. self.add_account(ltv=ltv, last_order=last_date(dormant), group=grp)
201. for _ in range(250):
202. ltv = round(r.uniform(60, 2450), 2)
203. self.add_account(ltv=ltv, last_order=last_date(r.random() < 0.6),
204. group="Retail" if r.random() < 0.3 else "")
206. # Special cases, each named in self.cases for run_sample.py and the docs.
207. # 1. Directly-partnered account: Rhodium by override, never cold-emailed.
208. hr = []
209. for first, last, ltv, n in [("Maren", "Achterberg", 4210.50, 3), ("Soren", "Blackwood", 880.00, 1),
210. ("Lena", "Coldwell", 0.0, 0), ("Callum", "Dunmore", 12650.25, 6),
211. ("Rhea", "Farrow", 1540.00, 2), ("Otto", "Garroway", 610.00, 1)]:
212. self.used_names.add((first, last))
213. a = self.add_account(ltv=ltv, orders=n, last_order=last_date(False), first=first,
214. last=last, company="Harlow Ridge Homes", domain="harlowridge.example",
215. industry="real estate", group="")
216. hr.append(a)
217. self.overrides = [a["email"] for a in hr[:4]] # per-email, not per-domain
218. self.suppression["domains"] = ["harlowridge.example", "meridiandisplay.example"]
219. self.cases["partnered_account"] = {"overridden": self.overrides,
220. "not_overridden": [a["email"] for a in hr[4:]]}
222. # 2. Staff mailbox on the company domain with real spend (event orders).
223. self.used_names.add(("Theo", "Bassett"))
224. staff = self.add_account(ltv=31480.00, orders=14, last_order=dormant_cut - dt.timedelta(days=40),
225. first="Theo", last="Bassett", company="Meridian Display Co.",
226. domain="meridiandisplay.example", industry="printing", title="Operations")
227. self.cases["staff_mailbox"] = staff["email"]
229. # 3. Approved merge: one person, two Plinth accounts after a rebrand.
230. self.used_names.add(("Imogen", "Radcliffe"))
231. surv = self.add_account(ltv=6120.40, orders=5, last_order=last_date(False), first="Imogen",
232. last="Radcliffe", company="Tidewater Foundation", domain="tidewaterfdn.example")
233. absd = self.add_account(ltv=2310.00, orders=2, last_order=dt.date(2024, 11, 2), first="Imogen",
234. last="Radcliffe", company="Tidewater Trust", domain="tidewatertrust.example")
235. self.merges = [{"survivor": str(surv["account_ref"]), "absorbed": [str(absd["account_ref"])],
236. "unit": "person", "name": "Imogen Radcliffe",
237. "reason": "Same person across the Tidewater Trust -> Tidewater Foundation rename: "
238. f"{surv['email']} (live) + {absd['email']} (old domain). Combined "
239. "lifetime crosses into Platinum.",
240. "approved_by": "Matt", "approved": "2026-08-06"}]
241. self.cases["merge"] = {"survivor": surv["account_ref"], "absorbed": absd["account_ref"]}
243. # 4. Identity twin: same person re-registered under a married name, same email.
244. self.used_names.update({("Delphine", "Hartigan"), ("Delphine", "Hartigan-Vance")})
245. tw_a = self.add_account(ltv=5340.00, orders=4, last_order=last_date(False), first="Delphine",
246. last="Hartigan", company="Lantern Film Festival", domain="lanternfilm.example")
247. tw_b = self.add_account(ltv=3120.00, orders=2, last_order=last_date(False), first="Delphine",
248. last="Hartigan-Vance", company="Lantern Film Festival",
249. domain="lanternfilm.example", email=tw_a["email"])
250. self.cases["twin"] = {"with_closure": tw_a["account_ref"], "clean_twin": tw_b["account_ref"],
251. "email": tw_a["email"]}
253. # 5. Shared company mailbox, two different people.
254. for first, last in [("Nell", "Ormsby"), ("Idris", "Seaton")]:
255. self.used_names.add((first, last))
256. self.add_account(ltv=r.uniform(2800, 7000), last_order=last_date(False), first=first,
257. last=last, company="Lakeshore Arts Council", domain="lakeshorearts.example",
258. email="events@lakeshorearts.example")
259. self.cases["shared_mailbox"] = "events@lakeshorearts.example"
261. # 6. Role-named account (dormant, so Winback holds it for review).
262. role = self.add_account(ltv=9870.00, orders=7, last_order=dormant_cut - dt.timedelta(days=200),
263. first="Purchasing", last="Dept", company="Northwind Academy",
264. domain="northwindacademy.example", email="purchasing@northwindacademy.example",
265. title="")
266. self.cases["role_named"] = role["email"]
268. # 7. Messy shipping-label name (the stub gets flagged on creation).
269. messy = self.add_account(ltv=3360.00, orders=2, last_order=last_date(False), first="Booth 214",
270. last="c/o Riverside Expo", company="Riverside Expo Center",
271. domain="riversideexpo.example", email="booth214@riversideexpo.example",
272. title="")
273. self.cases["messy_name"] = messy["email"]
275. # ── post-baseline orders (the weekly pulls) ──
276. partners = [a for a in self.accounts if a["baseline"]["ltv"] >= 2500]
277. # Small accounts well under the Palladium line, so routine orders don't
278. # push them over it (the one planned crossing is added below).
279. retail = [a for a in self.accounts if a["baseline"]["ltv"] < 1600 and a["company"] != "Harlow Ridge Homes"]
281. # Routine orders go to clients already buying this year, and stay clear of
282. # a band edge, so the week's tier movement is only the planned crossings.
283. tops = [lo for _, lo, _ in BANDS]
284. buying = [a for a in partners if a["baseline"]["last"] >= dormant_cut
285. and a["email"] != self.cases["role_named"]]
286. added: dict[int, float] = {}
288. def headroom(a):
289. now = a["baseline"]["ltv"] + added.get(a["account_ref"], 0.0)
290. return min([top - now for top in tops if top > now] or [1e9])
292. def pick():
293. while True:
294. a, amount = ((r.choice(buying), round(r.uniform(150, 1400), 2)) if r.random() < 0.7
295. else (r.choice(retail), round(r.uniform(60, 350), 2)))
296. if amount * 1.08 < headroom(a) - 50:
297. added[a["account_ref"]] = added.get(a["account_ref"], 0.0) + amount * 1.08
298. return a, amount
299. days = [BASELINE_AS_OF + dt.timedelta(days=i) for i in range(0, 15)]
300. for d in days:
301. if d.weekday() >= 5:
302. continue
303. for _ in range(r.randint(3, 7)):
304. acc, amount = pick()
305. self.add_order(acc, d, amount)
306. # The busy day: enough orders that Plinth truncates the response.
307. for _ in range(64):
308. acc, amount = pick()
309. self.add_order(acc, BUSY_DAY, amount)
310. # Cancelled and refunded orders that must not count toward lifetime value.
311. self.add_order(r.choice(partners), dt.date(2026, 9, 10), 2200.00, status=27)
312. self.add_order(r.choice(partners), dt.date(2026, 9, 16), 1450.00, status=28)
314. # Tier crossings mid-week.
315. near = [a for a in self.accounts if 2000 < a["baseline"]["ltv"] < 2450
316. and a["company"] != "Harlow Ridge Homes"]
317. up_retail = max(near, key=lambda a: a["baseline"]["ltv"])
318. self.add_order(up_retail, dt.date(2026, 9, 16), 640.00)
319. pal = [a for a in partners if 7300 < a["baseline"]["ltv"] < 8000]
320. up_pal = pal[0] if pal else partners[0]
321. self.add_order(up_pal, dt.date(2026, 9, 18), 1120.00)
322. # A brand-new customer whose first order qualifies them.
323. self.used_names.add(("Wendell", "Pemberton"))
324. newbie = {"account_ref": self.next_account, "first_name": "Wendell", "last_name": "Pemberton",
325. "email": "wendell@pembertonrobotics.example", "extra_email": "",
326. "company": "Pemberton Robotics", "phone": self.phone(), "title": "Founder",
327. "website": "https://www.pembertonrobotics.example", "industry": "manufacturing",
328. "registered": dt.date(2026, 9, 11), "group": "", "baseline": None}
329. self.next_account += 1
330. self.accounts.append(newbie)
331. self.add_order(newbie, dt.date(2026, 9, 19), 3875.00)
332. self.cases["crossings"] = {"retail_to_palladium": up_retail["account_ref"],
333. "palladium_to_platinum": up_pal["account_ref"],
334. "new_customer": newbie["account_ref"]}
335. return self
338. # ── writers ──────────────────────────────────────────────────────────────────
339. def plinth_order(o: dict) -> dict:
340. a = o["account"]
341. ts = f"{iso(o['day'])} {o['hour']:02d}:{o['minute']:02d}:00"
342. # Plinth sends some numbers as strings and some as numbers; so does the sample.
343. gt = o["grand_total"] if o["order_ref"] % 3 else f"{o['grand_total']:.2f}"
344. return {
345. "order_ref": o["order_ref"], "account_ref": a["account_ref"], "email": a["email"],
346. "first_name": a["first_name"], "last_name": a["last_name"], "company_name": a["company"],
347. "phone": a["phone"], "customer_group": a["group"], "grand_total": gt,
348. "subtotal": o["subtotal"], "shipping_total": o["shipping_total"], "tax_total": 0,
349. "status_code": o["status_code"],
350. "status_label": {27: "Order Cancelled", 28: "Order Refunded"}.get(o["status_code"], "Completed"),
351. "closed_on": ts, "shop": 1, "lines": {"1": {"line_ref": o["order_ref"] * 10 + 1}},
352. "shipments": [],
353. }
356. def write_pages(w: World, out: Path):
357. """One file per (day, offset) API response, as Claude would save them.
358. Days with more orders than TRUNCATE_AT come back cut short, and the next
359. offset overlaps the previous page instead of continuing after it."""
360. pdir = out / "plinth"
361. pdir.mkdir(parents=True, exist_ok=True)
362. by_day: dict[dt.date, list] = {}
363. for o in w.orders:
364. by_day.setdefault(o["day"], []).append(o)
365. first = RUN_DATES[0] - dt.timedelta(days=PULL_WINDOW_DAYS - 1)
366. d = first
367. while d <= RUN_DATES[-1]:
368. rows = sorted(by_day.get(d, []), key=lambda o: o["order_ref"])
369. if len(rows) <= TRUNCATE_AT:
370. pages = [rows]
371. else:
372. pages = [rows[:TRUNCATE_AT], rows[20:20 + TRUNCATE_AT], rows[30:]]
373. for i, page in enumerate(pages):
374. body = ({"order_map": {f"ord_{o['order_ref']}": plinth_order(o) for o in page}}
375. if page else {"notice": "No match"})
376. (pdir / f"{iso(d)}_p{i}.json").write_text(json.dumps(body, indent=1), encoding="utf-8")
377. d += dt.timedelta(days=1)
380. def write_roster(w: World, out: Path):
381. """The partner roster workbook, same tab and column contract as the real one."""
382. from openpyxl import Workbook
383. from openpyxl.styles import Font, PatternFill
384. wb = Workbook()
385. work = wb.active
386. work.title = "2026 Partner Working List"
387. part = wb.create_sheet("Complete Partner Clients List")
388. allacc = wb.create_sheet("All Accounts")
389. date_us = BASELINE_AS_OF.strftime("%m/%d/%y")
390. ph = ["Id", "First Name", "Last Name", "Email", "Additional Email", "Company", "Phone",
391. "Job Title", "Website", "User Group", "Correct Group", "Updated Group Plinth?",
392. "Date Updated Plinth", f"Plinth LTV {date_us}", "Total Spent (Orders)", "Last Order",
393. "Industry", "Registered", "# Orders", "Repeat Revenue (Plinth)", "First Order",
394. "Pipeline(s)", "Comments", "Contacted (Pipeline Outreach)", "Most Recent Contact Date",
395. "First Outreach Date", "Times Contacted (Pipeline Touches)", "Total Pipeline Entries",
396. "Current Pipeline Stage(s)", "Pipeline Owner/Rep"]
397. wh = ["Completed"] + ph[:9] + ["User Type"] + ph[9:13] + ["Social Media", "Total Spent (Orders)",
398. "Industry", "Registered", "# Orders", "Repeat Revenue (Plinth)", "First Order", "Last Order",
399. "Pipeline(s)", "Comments", "Contacted (Pipeline Outreach)", "Most Recent Contact Date",
400. "First Outreach Date", "Times Contacted (Pipeline Touches)", "Total Pipeline Entries",
401. "Current Pipeline Stage(s)", "Pipeline Owner/Rep", "LTV (Plinth)"]
402. head = Font(name="Arial", bold=True, color="FFFFFF")
403. fill = PatternFill("solid", fgColor="1F3864")
404. for ws, hdr in ((part, ph), (work, wh), (allacc, ["ID", "First Name", "Last Name", "Email", "LTV", "RR"])):
405. ws.append(hdr)
406. for c in ws[1]:
407. c.font, c.fill = head, fill
409. def tier_of(ltv):
410. for t, lo, hi in BANDS:
411. if ltv >= lo and (ltv < hi or t == "Rhodium Partner"):
412. return t
413. return ""
415. money, day = "$#,##0.00", "mm/dd/yyyy"
416. body = Font(name="Arial", size=10)
417. for a in w.accounts:
418. b = a["baseline"]
419. if b is None:
420. continue
421. allacc.append([a["account_ref"], a["first_name"], a["last_name"], a["email"], b["ltv"], b["repeat"]])
422. tier = tier_of(b["ltv"])
423. if a["email"] in w.overrides:
424. tier = "Rhodium Partner"
425. if not tier:
426. continue
427. part.append([a["account_ref"], a["first_name"], a["last_name"], a["email"], a["extra_email"],
428. a["company"], a["phone"], a["title"], a["website"], a["group"], tier, "", None,
429. b["ltv"], b["ltv"], b["last"], a["industry"], a["registered"], b["orders"],
430. b["repeat"], b["first"], "", "", "", None, None, None, None, "", ""])
431. for ws in (part, allacc):
432. for row in ws.iter_rows(min_row=2):
433. for c in row:
434. c.font = body
435. h = ws.cell(row=1, column=c.column).value or ""
436. if h.startswith("Plinth LTV") or h in ("Total Spent (Orders)", "Repeat Revenue (Plinth)", "LTV", "RR"):
437. c.number_format = money
438. if h in ("Last Order", "First Order", "Registered", "Date Updated Plinth"):
439. c.number_format = day
440. # The curated working tab: a hand-kept subset with human columns filled in.
441. r = random.Random(SEED + 1)
442. notes = ["Prefers a call before quoting.", "Orders ahead of the spring show circuit.",
443. "Asked about storage for booth kits.", "New marketing lead since spring.",
444. "Uses our design studio for every order.", ""]
445. for a in [x for x in w.accounts if x["baseline"] and x["baseline"]["ltv"] >= 8000][:24]:
446. b = a["baseline"]
447. work.append(["", a["account_ref"], a["first_name"], a["last_name"], a["email"], "",
448. a["company"], a["phone"], a["title"], a["website"], "Customer", a["group"],
449. tier_of(b["ltv"]), "", None, "", b["ltv"], a["industry"], a["registered"],
450. b["orders"], b["repeat"], b["first"], b["last"], "", r.choice(notes),
451. "", None, None, None, None, "", r.choice(OWNERS), b["ltv"]])
452. out.mkdir(parents=True, exist_ok=True)
453. path = out / f"Partner-Clients-Roster_{iso(BASELINE_AS_OF)}.xlsx"
454. wb.save(path)
455. return path
458. # ── Bitrix: two weekly enumerations and the stage histories behind them ─────
459. STAGES = {
460. "24": {"research": "UC_2YNXWE", "tier_email": "UC_6H9C8U", "artwork": "UC_5DTN59",
461. "mockup": "PREPAYMENT_INVOIC", "gift_sent": "FINAL_INVOICE", "won": "WON",
462. "lose": "LOSE", "retired_send": "PREPARATION", "retired_2nd": "UC_CEAGAV",
463. "retired_bulk": "UC_N4JCMW"},
464. "26": {"client": "UC_JUUNES", "activated": "UC_L2JMHY", "first": "PREPARATION",
465. "noreply1": "UC_K9M6LT", "follow": "PREPAYMENT_INVOIC", "precall": "FINAL_INVOICE",
466. "won": "WON", "lose": "LOSE", "survey": "UC_PMLDJW"},
467. "36": {"active": "NEW", "dormant": "UC_LXCMAV", "first": "PREPAYMENT_INVOIC",
468. "second": "EXECUTING", "noreply": "UC_7XQR9H", "won": "WON", "notint": "1"},
469. "16": {"new": "NEW", "first": "PREPARATION", "follow": "PREPAYMENT_INVOIC",
470. "noreply2": "EXECUTING", "won": "WON", "lose": "LOSE"},
471. }
474. def build_deals(w: World):
475. """Returns (week1_deals, week2_deals, histories, contacts). Deal JSON shape is
476. what the SOP's enumeration step saves: deal_id, cat, stage, stage_date, title,
477. owner, created, value. Histories and contacts are what get_stage_history and
478. get_record would return, attached by run_sample.py only for the deals the
479. pull list asks for."""
480. r = random.Random(SEED + 2)
481. run = RUN_DATES[-1]
482. cut = run - dt.timedelta(days=365)
483. by_ref = {a["account_ref"]: a for a in w.accounts}
484. partners = [a for a in w.accounts if a["baseline"] and a["baseline"]["ltv"] >= 2500
485. and a["company"] not in ("Harlow Ridge Homes", "Meridian Display Co.")
486. and a["email"] not in (w.cases["shared_mailbox"], w.cases["role_named"], w.cases["messy_name"])]
487. dormant = [a for a in partners if a["baseline"]["last"] < cut]
488. active = [a for a in partners if a["baseline"]["last"] >= cut]
489. twin_a = by_ref[w.cases["twin"]["with_closure"]]
490. twin_b = by_ref[w.cases["twin"]["clean_twin"]]
491. dormant = [a for a in dormant if a not in (twin_a, twin_b)]
492. active = [a for a in active if a not in (twin_a, twin_b)]
493. r.shuffle(dormant)
494. r.shuffle(active)
496. deals, hist, contacts = [], {}, {}
497. next_id = [DEAL_BASE]
499. def at(d: dt.date, h=None, m=None):
500. return f"{iso(d)} {h if h is not None else r.randint(8, 17):02d}:{m if m is not None else r.randint(0, 59):02d}"
502. def deal(cat, a, path, created, *, title=None, value="", owner=None, deal_id=None):
503. """path: list of (stage_key, date). The deal sits in the last stage."""
504. did = deal_id or str(next_id[0])
505. if not deal_id:
506. next_id[0] += r.randint(3, 40)
507. st = STAGES[cat]
508. h = [{"stage": f"C{cat}:{st[k] if k in st else k}", "at": at(d)} for k, d in path]
509. last_key, last_day = path[-1]
510. stage = f"C{cat}:{st[last_key] if last_key in st else last_key}"
511. deals.append({"deal_id": did, "cat": cat, "stage": stage, "stage_date": at(last_day),
512. "title": title or f"{a['first_name']} {a['last_name']}",
513. "owner": owner or r.choice(OWNERS), "created": at(created, 9, 0), "value": value})
514. hist[did] = h
515. if a is not None:
516. contacts[did] = {"name": f"{a['first_name']} {a['last_name']}", "email": a["email"],
517. "phone": a["phone"]}
518. return did
520. d = dt.date
521. # Winback (26): most sit untouched in the Client bucket; Nora's stage moves are the sends.
522. for a in dormant[:12]:
523. deal("26", a, [("client", d(2026, 8, 6))], d(2026, 8, 6), owner="Nora Lindqvist")
524. for a in dormant[12:15]:
525. deal("26", a, [("client", d(2026, 8, 6)), ("activated", d(2026, 8, 11))], d(2026, 8, 6),
526. owner="Nora Lindqvist")
527. for a in dormant[15:18]:
528. deal("26", a, [("client", d(2026, 8, 6)), ("activated", d(2026, 8, 11)), ("first", d(2026, 8, 13)),
529. ("noreply1", d(2026, 8, 20)), ("follow", d(2026, 8, 24))], d(2026, 8, 6),
530. owner="Nora Lindqvist")
531. a = dormant[18]
532. did = deal("26", a, [("client", d(2026, 8, 6)), ("activated", d(2026, 8, 11)), ("first", d(2026, 8, 12)),
533. ("follow", d(2026, 8, 19)), ("precall", d(2026, 8, 27))], d(2026, 8, 6),
534. owner="Nora Lindqvist")
535. # Same-minute workflow re-entries: one send, not three.
536. hist[did] += [{"stage": "C26:PREPAYMENT_INVOIC", "at": "2026-08-19 10:14"},
537. {"stage": "C26:PREPAYMENT_INVOIC", "at": "2026-08-19 10:15"}]
538. for a in dormant[19:21]:
539. deal("26", a, [("first", d(2026, 2, 10)), ("survey", d(2026, 2, 12)), ("won", d(2026, 3, 30))],
540. d(2026, 2, 9), owner="Hazel Okafor")
541. deal("26", dormant[21], [("first", d(2026, 2, 10)), ("follow", d(2026, 2, 17)), ("lose", d(2026, 4, 1))],
542. d(2026, 2, 9), owner="Hazel Okafor")
543. # Winback already done this dormancy spell: excluded from routing, not re-touched.
544. deal("26", dormant[22], [("client", d(2026, 6, 2)), ("first", d(2026, 6, 4)), ("lose", d(2026, 7, 15))],
545. d(2026, 6, 2), owner="Nora Lindqvist")
547. # Partner Tier Upgrade (24): first touches and tier crossings on active clients.
548. for a in active[:4]:
549. deal("24", a, [("research", d(2026, 7, 20)), ("tier_email", d(2026, 7, 21))], d(2026, 7, 20),
550. owner="Hazel Okafor")
551. deal("24", active[4], [("tier_email", d(2026, 7, 21)), ("artwork", d(2026, 7, 29))], d(2026, 7, 20))
552. deal("24", active[5], [("tier_email", d(2026, 6, 24)), ("mockup", d(2026, 7, 2)),
553. ("gift_sent", d(2026, 7, 14))], d(2026, 6, 23))
554. for a in active[6:8]:
555. # Older deals that moved through stages since deleted from the funnel.
556. deal("24", a, [("retired_send", d(2026, 3, 3)), ("retired_2nd", d(2026, 3, 17)),
557. ("retired_bulk", d(2026, 4, 1)), ("won", d(2026, 4, 20))], d(2026, 3, 2),
558. owner="Hazel Okafor")
559. deal("24", active[8], [("research", d(2026, 8, 12))], d(2026, 8, 12))
560. # The twin with the Not Interested closure (an opt-out for the whole identity).
561. deal("24", twin_a, [("tier_email", d(2026, 6, 24)), ("lose", d(2026, 7, 8))], d(2026, 6, 23),
562. owner="Hazel Okafor")
564. # Quarterly Check-In (36).
565. for a in active[9:13]:
566. deal("36", a, [("active", d(2026, 8, 6))], d(2026, 8, 6), owner="Matt Guerra")
567. # Dual-active: an open tier-upgrade deal parks the check-in in Dormant.
568. for a in active[:2]:
569. deal("36", a, [("active", d(2026, 5, 12)), ("dormant", d(2026, 8, 12))], d(2026, 5, 12),
570. owner="Matt Guerra")
571. for a in active[13:15]:
572. deal("36", a, [("active", d(2026, 7, 27)), ("first", d(2026, 7, 29))], d(2026, 7, 27),
573. owner="Matt Guerra")
574. deal("36", active[15], [("first", d(2026, 7, 29)), ("noreply", d(2026, 8, 5)), ("second", d(2026, 8, 11))],
575. d(2026, 7, 27), owner="Matt Guerra")
576. deal("36", active[16], [("first", d(2026, 4, 14)), ("won", d(2026, 5, 2))], d(2026, 4, 13),
577. owner="Hazel Okafor")
578. deal("36", active[17], [("first", d(2026, 7, 29)), ("notint", d(2026, 8, 3))], d(2026, 7, 27),
579. owner="Matt Guerra")
580. deal("36", active[18], [("first", d(2026, 4, 14)), ("second", d(2026, 4, 28)), ("won", d(2026, 5, 20))],
581. d(2026, 4, 13), owner="Matt Guerra")
583. # Abandoned Carts (16): titles are cart numbers, so contacts come from get_record.
584. retail = [a for a in w.accounts if a["baseline"] and a["baseline"]["ltv"] < 2500
585. and a["company"] != "Harlow Ridge Homes"]
586. for i, a in enumerate(retail[:5]):
587. path = [("new", d(2026, 9, 2 + i))]
588. if i % 2:
589. path.append(("first", d(2026, 9, 3 + i)))
590. deal("16", a, path, d(2026, 9, 2 + i), title=f"Cart - {70100 + i * 7}",
591. value=round(r.uniform(90, 900), 2), owner="Hazel Okafor")
592. # A guest checkout that is not a Plinth account at all.
593. guest = {"first_name": "Selma", "last_name": "Iverson", "email": "selma.iverson@quarrybrewing.example",
594. "phone": "(512) 555-0147"}
595. deal("16", guest, [("new", d(2026, 9, 9))], d(2026, 9, 9), title="Cart - 70177", value=412.50,
596. owner="Hazel Okafor")
597. # A cart that converted and moved on into production funnels (C18/C20 entries
598. # must not count as cart emails even though PREPARATION is a cat-16 send token).
599. conv = retail[6]
600. did = deal("16", conv, [("new", d(2026, 8, 25)), ("first", d(2026, 8, 26)), ("won", d(2026, 8, 28))],
601. d(2026, 8, 25), title="Cart - 70093", value=655.00, owner="Hazel Okafor")
602. hist[did] += [{"stage": "C18:PREPARATION", "at": "2026-08-29 09:12"},
603. {"stage": "C20:PREPARATION", "at": "2026-09-01 11:40"}]
604. for tid in TEST_DEALS:
605. deal("16", None, [("new", d(2026, 8, 20))], d(2026, 8, 20), title=f"TEST cart {tid}",
606. value=1.00, owner="Matt Guerra", deal_id=tid)
608. # Week 1 is the same book one week earlier: deals that moved in week 2 sat one stage back.
609. week2 = [dict(x) for x in deals]
610. week1 = []
611. moved = {}
612. for x in deals:
613. h = sorted(hist[x["deal_id"]], key=lambda e: e["at"])
614. before = [e for e in h if e["at"][:10] <= iso(RUN_DATES[0])]
615. if not before:
616. continue # created in week 2
617. y = dict(x)
618. if before[-1]["stage"] != x["stage"]:
619. y["stage"], y["stage_date"] = before[-1]["stage"], before[-1]["at"]
620. moved[x["deal_id"]] = True
621. week1.append(y)
622. # Two week-2 moves, so the cache has something real to decide about.
623. for x in week2:
624. if x["cat"] == "36" and x["stage"] == "C36:NEW":
625. x["stage"], x["stage_date"] = "C36:PREPAYMENT_INVOIC", "2026-09-16 10:20"
626. hist[x["deal_id"]].append({"stage": "C36:PREPAYMENT_INVOIC", "at": "2026-09-16 10:20"})
627. break
628. for x in week2:
629. if x["cat"] == "26" and x["stage"] == "C26:UC_L2JMHY":
630. x["stage"], x["stage_date"] = "C26:PREPARATION", "2026-09-17 14:05"
631. hist[x["deal_id"]].append({"stage": "C26:PREPARATION", "at": "2026-09-17 14:05"})
632. break
633. return week1, week2, hist, contacts
636. def main():
637. ap = argparse.ArgumentParser(description=__doc__.split("\n\n")[0])
638. ap.add_argument("--out", default=str(HERE / "inputs"))
639. a = ap.parse_args()
640. out = Path(a.out)
641. w = World().build()
642. write_pages(w, out)
643. roster = write_roster(w, out)
644. week1, week2, hist, contacts = build_deals(w)
645. bdir = out / "bitrix"
646. bdir.mkdir(parents=True, exist_ok=True)
647. for day, deals in zip(RUN_DATES, (week1, week2)):
648. (bdir / f"bitrix-deals_{iso(day)}.json").write_text(
649. json.dumps({"deals": deals}, indent=1), encoding="utf-8")
650. (bdir / "stage-histories.json").write_text(json.dumps(hist, indent=1, sort_keys=True), encoding="utf-8")
651. (bdir / "contacts.json").write_text(json.dumps(contacts, indent=1, sort_keys=True), encoding="utf-8")
652. (out / "merge_registry.json").write_text(json.dumps({
653. "_readme": "Approved Plinth account merges. The weekly pipeline folds each absorbed account "
654. "into its survivor at classify time (lifetime metrics), drops absorbed pl-<id> "
655. "chunks from the XML census, and never re-creates them. Keys are "
656. "order_history.json customer keys (the Plinth account id as a string). Add "
657. "entries ONLY with Matt's explicit approval, one LEDGER entry per addition.",
658. "version": 1, "updated": "2026-08-06", "merges": w.merges}, indent=1), encoding="utf-8")
659. (out / "suppression_list.json").write_text(json.dumps({
660. "_readme": "Explicit email opt-outs and never-contact accounts. A client here, or with any "
661. "Not-Interested funnel closure, never routes into any outreach pipeline, "
662. "permanently, until Matt personally reverses it (DECISIONS 2026-08-06). Names "
663. "match on normalized full name; emails on exact lowercase; domains match the "
664. "part after '@', lowercase.",
665. "_domains_note": "domains: whole companies that are never cold-outreached because the "
666. "relationship is handled directly (DECISIONS 2026-08-12). "
667. "harlowridge.example = Harlow Ridge Homes, a directly-partnered builder; "
668. "meridiandisplay.example = our own staff.",
669. **w.suppression}, indent=1), encoding="utf-8")
670. (out / "cases.json").write_text(json.dumps({**w.cases, "tier_overrides": w.overrides},
671. indent=1, default=str), encoding="utf-8")
672. n_orders = len(w.orders)
673. print(f"accounts {len(w.accounts)}, post-baseline orders {n_orders}, "
674. f"deals week1 {len(week1)} / week2 {len(week2)} -> {out}")
675. print(f"roster: {roster.name}")
678. if __name__ == "__main__":
679. main()