Spec the Four-Stage Reporting Pipeline
Objective: Given a small team's data sources and reporting cadence, write a build spec for a four-stage automated reporting pipeline, naming the exact tool for each stage and drafting the AI narrative prompt that turns raw numbers into a 'what changed and why' summary.
You're the sole marketer at YETI's DTC growth team. Every Friday you manually export GA4, Meta Ads, and email numbers into a slide, and it eats half your afternoon. You've been asked to spec (not yet build) an automated replacement before the team approves a Zapier/n8n budget line.
Map YETI's three data sources and weekly cadence onto the lesson's four pipeline stages, name the specific tool for each stage, and write the actual AI narrative prompt stage 3 will run.
Before you start
What you'll need
Free path (everything below is enough to finish)
Free, no account friction, works for a single-marketer team
Free tier is enough to iterate on prompt wording
Paid upgrades (optional, faster/deeper)
Handles authentication and scheduled refresh for 100+ sources without custom API code
No access? Manual weekly CSV export into the same Google Sheet tabs until budget is approved
The process
2 steps
Step 01 of 02
The lesson's pipeline has four dominoes: API pulls, a landing zone, an AI narrative step, and auto-delivery. Skipping the narrative step leaves someone staring at raw numbers with no story attached.
YETI's sources are GA4, Meta Ads Manager, and Klaviyo email data, delivered every Friday 8am to a #dtc-weekly Slack channel. Which named tool goes in each of the four stages?
Procedure
- Stage 1 (API pulls): name Supermetrics as the connector pulling GA4, Meta Ads, and Klaviyo on a Friday 6am schedule
- Stage 2 (landing zone): name a Google Sheet tab structure, raw_this_week and archived raw_last_week
- Stage 3 (AI narrative): name an LLM API step reading both tabs and drafting the changed-and-why paragraph
- Stage 4 (delivery): name n8n posting the formatted Slack message to #dtc-weekly at 8am Friday
PIPELINE SPEC, YETI DTC Weekly Report Stage 1, API pulls: Supermetrics, scheduled 6:00am Friday, sources GA4 + Meta Ads Manager + Klaviyo Stage 2, Landing zone: Google Sheet 'DTC Weekly', tabs raw_this_week (overwritten) and raw_last_week (archived copy taken before overwrite) Stage 3, AI narrative: LLM API call at 6:05am, reads both tabs, drafts 2-sentence summary of the two biggest deltas Stage 4, Delivery: n8n formats numbers + narrative, posts to #dtc-weekly at 8:00am Friday
Healthy
Each stage names one specific tool and one specific trigger time, with no stage left as 'TBD.'
Unhealthy
A spec that says 'some kind of dashboard tool' for stage 1 or skips naming stage 3 entirely.
What this means
A build spec that can't be handed to someone else to implement without follow-up questions isn't finished yet.
So what do I do about it?
| Symptom | Action | Effort |
|---|---|---|
| Stage 3 is left blank or vague | Name the exact LLM step and what two things it compares | 5 min |
Step 02 of 02
The worked example's prompt asks the LLM to compare this week to last week, identify the two biggest changes, and explain what happened and a plausible reason why, in plain English.
YETI's raw_this_week tab shows DTC conversion rate up 0.4 points and email click rate down 3 points versus raw_last_week. What exact prompt text does stage 3 need to turn those two numbers into a narrative?
Procedure
- Write the prompt as an instruction, not a description: 'Compare this week's numbers to last week's'
- Name the exact output shape: 'identify the two biggest changes'
- Require a plausible cause, not just the number: 'one sentence on what happened and one plausible reason why'
- Set the tone constraint: 'in plain English,' so it reads like a teammate, not a data table
STAGE 3 PROMPT (literal text to send to the LLM API): 'Compare this week's YETI DTC marketing metrics (raw_this_week) to last week's (raw_last_week). Identify the two biggest changes. For each, write one sentence on what happened and one plausible reason why, in plain English. Do not include changes under 5%.'
Healthy
The prompt names a comparison, a count limit (two), a two-part sentence structure, and a tone.
Unhealthy
A prompt that just says 'summarize this data' and hopes the LLM infers the comparison structure.
What this means
A vague prompt produces a vague narrative; the prompt is doing the actual thinking work here, not the LLM.
So what do I do about it?
| Symptom | Action | Effort |
|---|---|---|
| Narrative output reads like a list of numbers, not a story | Add the explicit 'one sentence what happened, one sentence why' structure to the prompt | 5 min |
Final deliverable
A one-page build spec naming the tool for each of the four pipeline stages, plus the literal stage-3 narrative prompt text.
See a reference example
PIPELINE SPEC, Allbirds Retention Weekly Report Stage 1, API pulls: Supermetrics, scheduled 6:00am Monday, sources GA4 + Klaviyo Stage 2, Landing zone: Google Sheet 'Retention Weekly', tabs raw_this_week and raw_last_week Stage 3, AI narrative prompt: 'Compare this week's Allbirds retention metrics to last week's. Identify the two biggest changes. For each, write one sentence on what happened and one plausible reason why, in plain English.' Stage 4, Delivery: n8n posts to #retention-weekly at 9:00am Monday
Success criteria
You're done when you can:
- Names a specific tool for all four pipeline stages, no stage left vague
- Stage 3 prompt includes a comparison instruction, a count limit, a two-part sentence structure, and a tone constraint