Case study — competitive email intelligence
Email Intelligence Pipeline
A dedicated inbox subscribed to 226 brand mailing lists, and a pipeline that turns everything landing in it into structured competitive intelligence. A serverless collector running inside Gmail extracts ~18 fields from every email hourly, SQL assembles promotions into lifecycles, a self-grading digest briefs my agents daily, and an improvement agent re-tunes the pipeline's own prompts. It has run unattended since February, and it produced the dataset behind the published email panel report.
in practice
What it's actually like to use
I don't use this system. That's the point of it. A dedicated inbox is subscribed to the mailing lists of 226 DTC and retail brands, and every hour a collector wakes up, reads whatever arrived, and turns each email into a structured record: what kind of email it is, what the offer mechanics are, how deep the discount runs, how much urgency it manufactures, what products it pushes, what tone it takes. Then it marks the email read and goes back to sleep.
What I actually touch is the output. Every morning a digest of yesterday's competitive email activity lands in the knowledge base my agent fleet reads, so when a client asks "what are competitors doing on email," the answer is grounded in what competitors actually did, yesterday. Every Sunday a brand analyst reviews each tracked brand's week against its own rolling baselines and flags behavior changes.
The practical outcome: five months of unattended operation produced a 6,120-email panel across 226 brands, the dataset behind the published report on how DTC brands actually use email.
architecture
How it's put together
The pipeline, inbox to insight
Source
a dedicated inbox subscribed to 226 brand lists
Every marketing email those brands send lands here. The inbox is the sensor.
Collect
a Google Apps Script running inside Gmail
Offer mechanics, discount depth, urgency, tone, products, themes — one structured record per email. Unknown senders get a brand auto-created and flagged for review.
Store
Postgres, with SQL doing the assembly
A SQL function clusters each brand's sale emails into promotion lifecycles: launch, reminder, last chance.
Synthesize
a three-pass daily digest: write, grade, rewrite
Sonnet writes the brief, Opus grades it, Sonnet rewrites to the grader's notes. The result is embedded, shredded into claims, and stored in the knowledge base my agents read.
Improve
an improvement agent that re-tunes the pipeline
Daily health checks, then 0-3 small prompt patches proposed from grader scores and injected at runtime. Patches older than a week that dropped scores get auto-deactivated.
A collector that lives inside Gmail
The ingestion layer is a Google Apps Script running on an hourly trigger inside the inbox itself. No server, no queue, no container: Gmail is the message bus and Apps Script is the compute. It deduplicates by message id, resolves the sender to a brand, calls Claude to extract the record, and marks the email read.
The panel enrolls its own members
Sender resolution cascades: exact alias match, then a fuzzy domain match that strips mail/news subdomains and refuses shared ESP domains, and only accepts a unique hit. Unknown senders get a brand record auto-created from Claude's suggestions, flagged for human review. Subscribing to a new list is the entire onboarding.
Extraction hardened by its own mistakes
The prompt encodes lessons from real failures: a sale must be a concrete, actionable offer (vague "exclusive discount" teasers don't count), discount amounts are percentages only with explicit dollar-value null rules, and a price ceiling ("dresses $150 & under") is not a discount.
Promotion lifecycles assembled in SQL
A Postgres function clusters each brand's sale emails into promotion records with per-email roles: launch, reminder, last chance. 3,151 promotions to date, which is what makes questions like "how long do this brand's sales actually run" answerable.
A digest that grades itself
The daily brief is a three-pass pipeline: Sonnet writes it, Opus grades it against a rubric, Sonnet rewrites it to the grader's notes. The final version is embedded, shredded into typed claims, and stored in the claim-level knowledge base, so the fleet's agents cite yesterday's competitive activity as facts.
It re-tunes its own prompts
A daily improvement agent runs database health checks, reads recent grader scores, and proposes up to three small prompt patches that get injected at runtime. Patches that have been live a week get evaluated against the scores they produced; any patch that dropped quality is deactivated automatically.
engineering highlights
The parts I'm proud of
Production discipline in a free serverless runtime
Apps Script is usually a toy, so the pipeline treats its limits as engineering constraints. Runs stop themselves 60 seconds before the 6-minute execution ceiling and hand the remainder to the next hour. Claude calls retry on 429s and 5xxs with exponential backoff. Failed parses land in a retry queue a separate 6-hour trigger drains. Fatal errors email an alert, and every run reports into the same central cron log as the rest of my infrastructure.
// Apps Script kills runs at 6 minutes — leave with a full minute to spare
var elapsed = (new Date() - startTime) / 1000;
if (elapsed > 300) {
Logger.log('Approaching execution limit. Stopping with ' +
(emails.length - i) + ' remaining.');
break; // the next hourly trigger picks up the rest
}Sender resolution that refuses to guess
Matching an email to a brand sounds trivial until the mail comes from klaviyomail.com. The resolver only fuzzy-matches on domains after stripping mail-subdomain noise, skips every shared ESP domain outright, and accepts a match only when exactly one company fits. Anything ambiguous becomes a new record flagged needs_review instead of a silent wrong attribution.
// Skip ESP/platform domains — shared across many brands
var espDomains = ['shopifyemail.com', 'klaviyomail.com',
'mailchimpapp.com', 'sendgrid.net', 'amazonses.com', /* ... */];
// Strip common sending subdomains (mail.brand.com → brand.com)
var baseDomain = domain.replace(/^(mail|email|e|news|send|marketing)\./i, '');
// Only auto-match if EXACTLY ONE company fits — ambiguity goes to review
if (rows && rows.length === 1) return rows[0];A prompt with scar tissue
The extraction prompt's rules read like a changelog of caught errors, because they are. Early runs recorded "$240 off tires" as a 240% discount and counted "special offer coming soon" as a sale. The fixes are now constraints the model can't drift past, which is why the published report's spot-check found zero hallucinated discount amounts.
- has_sale is TRUE only for a concrete, actionable offer: a stated
percentage, a dollar amount, an actual promo code, or explicit free
shipping/gift. Vague promises and "coming soon" teasers are FALSE.
- discount_amount must ALWAYS be a percentage (0-100). If the discount
is denominated in dollars, set it to null — ALWAYS.
- "Free shipping on orders over $79" → the $79 is a threshold, not a
discount amount. "Dresses $150 & under" is a price ceiling, not a sale.The loop closes: pipeline → knowledge → agents → report
stack
Built with
Google Apps Script (hourly time-driven triggers, Gmail as the message bus) · Claude for per-email extraction and the three-pass digest (Sonnet writer/rewriter, Opus grader) · Supabase Postgres (raw emails, insights, promotion lifecycles via a plpgsql grouping function, weekly brand baselines) · Voyage embeddings into the claim-level knowledge base · node-cron workers on Railway for the digest, snapshots, analyst, and improvement agent · healthchecks.io and a central cron log across all of it.
Part of the same platform as the agent fleet and the knowledge base; tracked brands are public mailing lists, and the mechanisms are shown as built.