Every agency I look at loses time in the same five spots. Reporting rebuilt by hand each month. Briefs rewritten into the same shape. Content checked against guidelines by eye. Onboarding tracked in someone's head. Hours leaking out of the margin. Each one below runs in your browser. The Python beside each demo is what I would actually ship.
All data below is invented. Nothing here calls a model. That is the point, and it is rule one.
The friction. Every month someone exports four CSVs, rebuilds the same slide, and hunts for why one number moved. The build is a join plus a variance check plus one paragraph a human can send.
Four channel exports, already normalised to one schema. Rows are spend, leads and the prior month.
import csv, statistics
def load(path):
with open(path) as f:
return [r for r in csv.DictReader(f)]
def cpl(spend, leads):
return round(spend / leads, 2) if leads else None
def build(rows):
out = []
for r in rows:
now, was = cpl(float(r["spend"]), int(r["leads"])), cpl(float(r["prev_spend"]), int(r["prev_leads"]))
delta = None if not was else round((now - was) / was * 100, 1)
out.append({**r, "cpl": now, "prev_cpl": was, "delta": delta,
"flag": delta is not None and delta > 20})
return out
def paragraph(rows):
worst = max((r for r in rows if r["flag"]), key=lambda r: r["delta"], default=None)
total = sum(float(r["spend"]) for r in rows)
leads = sum(int(r["leads"]) for r in rows)
head = f"Spend of ${total:,.0f} produced {leads} leads at ${total/leads:,.2f} each."
if not worst:
return head + " No channel moved more than 20 percent."
return head + (f" {worst['channel']} needs attention, cost per lead up "
f"{worst['delta']} percent on {worst['prev_cpl']} to {worst['cpl']}.")
The friction. An account manager receives a rambling email and rewrites it into the same six headings every time. The template is deterministic. The model, if it is used at all, fills the gaps and never invents the offer.
import re
FIELDS = ["client", "objective", "audience", "offer", "budget", "deadline", "tone"]
SIGNALS = {
"objective": r"(campaign|launch|awareness|leads|sales|pre-approval)",
"audience": r"(target|audience|first time|buyers|demographic)",
"offer": r"(promise|guarantee|offer|price|rate|free)",
"budget": r"(budget|spend|\$\s?\d|around \d)",
"deadline": r"(launch|deadline|before|by the end|week \d)",
"tone": r"(warm|corporate|bold|playful|serious|tone)",
}
def extract(text):
found, gaps = {}, []
for field, pattern in SIGNALS.items():
m = re.search(pattern, text, re.I)
found[field] = text[max(0, m.start()-40):m.end()+40].strip() if m else None
if not m:
gaps.append(field)
return found, gaps
# The model is optional here and only ever rewrites the quoted line.
# It is not allowed to add a budget or a deadline that was not in the text.
The friction. Every deliverable is eyeballed against a client's rules by someone under time pressure. Rules that can be written can be checked. The model handles tone, and only after the rules pass.
RULES = [
("medical claim", r"clinically proven|treats|cures|heals"),
("absolute promise", r"guaranteed|always works|never fails|100%"),
("unsupported superlative", r"best (product|in the world|on the market)|world.?s best"),
("missing disclaimer", None), # checked separately, required for this client
("hard sell", r"buy now|act now|limited time|hurry"),
]
def check(text, requires_disclaimer=True, disclaimer="T&Cs apply"):
fails = []
for name, pattern in RULES:
if pattern and re.search(pattern, text, re.I):
fails.append(name)
if requires_disclaimer and disclaimer.lower() not in text.lower():
fails.append("missing disclaimer")
return {"passed": not fails, "fails": fails, "words": len(text.split())}
# Tone is the only thing a model is asked about, and it runs after this passes.
# A failed rule is never sent to a model to be argued with.
The friction. Onboarding lives in someone's head and in four email threads, so nobody knows whether the tracking is live until week three. A checklist with owners and dates is unglamorous and it is the fastest win in most agencies.
from datetime import date
STEPS = [
("contract signed", "sales", 0),
("billing set up", "finance", 2),
("access to ad accounts", "account lead", 2),
("analytics installed", "tech", 3),
("brand assets received", "client", 3),
("kickoff held", "account lead", 5),
("reporting template set", "tech", 7),
("first deliverable", "creative", 10),
]
def status(start: date, done: set):
today = date.today()
out = []
for name, owner, due_day in STEPS:
overdue = name not in done and (today - start).days > due_day
out.append({"step": name, "owner": owner, "done": name in done, "overdue": overdue})
return out
# The value is not the checklist. It is that "overdue" is computed rather than remembered.
The friction. Agencies bill hours and pay for hours, and the gap between them is invisible until month end. The build is a join between timesheets and contract rates, and one flag per client.
def effective_rate(hours_total, retainer):
return round(retainer / hours_total, 2) if hours_total else None
def analyse(clients):
out = []
for c in clients:
internal = sum(h for kind, h in c["hours"] if kind != "client")
billable = sum(h for kind, h in c["hours"] if kind == "client")
total = internal + billable
eff = effective_rate(total, c["retainer"])
out.append({**c, "total_hours": total, "internal_hours": internal,
"effective_rate": eff, "flag": eff is not None and eff < c["target"]})
return sorted(out, key=lambda r: r["effective_rate"] or 0)
# The leak is almost never the billable work. It is internal hours nobody
# records against the client but which are paid for out of the retainer.
None of these need a model to work. They need a schema, a rule and a place to put the result. The model earns its place in two of them, drafting the paragraph and judging tone, and in both cases a human approves before anything reaches a client. That is the same pattern as the medical centre build, and it is why the systems survive contact with a busy team.