Every month a raw Excel export arrived from HQ as a giant attachment: inconsistent column names, mixed formats, missing values, and enough PII that we couldn't legally paste it into any external tool. Cleaning it was my job, and it ate one to two hours at every monthly close, plus about fifteen minutes a day once the exports went daily. Several of us were each running a private version of the same cleanup.
We were not unusual. Supermetrics' 2025 Marketing Data Report found marketers handling 230% more data than in 2020, while 56% say they don't have time to analyze it thoroughly. The same report found 32% of marketers look at their reports once a month or less. At that cadence, nobody opens the pipeline often enough to notice it rotting.
The tutorial version of this story ends two sections from now, at “the close went from two hours to five minutes.” All true. But the pipeline turned out to be the easy part. The part that earns its keep every month is the review queue, and almost nobody writes about that part. So here is the longer, honest version.
Version one: a Zapier flow, a schema doc, a custom GPT
My first build was deliberately boring. No engineers to borrow, and compliance read everything. The Zapier flow I built
watched for the inbound report email, pulled the attachment into a protected Drive folder, and
ran a cleaning pass: strip the sensitive columns, normalize values, and rename
fields so that KYC Status, kyc-complete? and
K.Y.C. all became kyc_status before any model saw
a single row.
Then I wrote a schema doc for the custom GPT. Every field, what it meant, what blank meant, which metric depended on which. A typical entry:
kyc_completed_date: the date the account passed verification. It is a
separate field from the signup date. Blank means still in review. Never treat
blank as zero, and never count a blank row as a conversion.
With that in place, one cleaned file produced eight standing reports: the monthly acquisition summary, weekly snapshots, regional and channel breakdowns, and a few internal dashboards, all as CSVs ready for HubSpot or Salesforce. My monthly close dropped from one to two hours to under five minutes. Daily snapshots, which none of us had ever had time to produce, became routine.
Parsing is a solved problem now
When I built v1, a real share of the failures were format failures. The model returned a table with a merged header, or JSON with a stray comma, and the downstream step choked. I kept a small graveyard of prompts that said things like “return ONLY valid JSON.”
That whole failure class is gone. In November 2025 Anthropic shipped Structured Outputs on the Claude Developer Platform: you hand the API a JSON schema, it compiles the schema into a grammar and constrains generation token by token, so the response is guaranteed to be valid JSON in your shape. OpenAI's constrained decoding does the same job and hit 100% schema adherence in their own evals, against under 40% for prompt-only approaches on earlier models. When I rebuilt the pipeline on the API, “the LLM returned broken JSON” stopped being a sentence I said.
But look at what the guarantee covers: format, and only format. A wrong value fits a schema just as well as a right one.
Accuracy has not caught up
Two papers reshaped how I think about this system. A 2025 study of LLM data extraction for systematic reviews found Gemini 1.5 Flash, Gemini 1.5 Pro and Mistral Large 2 agreed with human coders on 71.2%, 72.1% and 62.4% of extracted fields, and that adding human validation of each field measurably lifted accuracy. A newer benchmark on safety data extraction put the best configuration at 84%, with GPT-4o at 81% and Claude 3.7 Sonnet at 79%. None of them cleared the roughly 90% bar you would want before letting a system write to a dashboard unsupervised.
Read those numbers the way an operator has to: even on the best setup, plan
for something like one field in five or six needing a human look. That
matched my logs almost exactly. The model's errors all sat outside what a JSON
validator can see, in the places only a person who knew the data would catch: a blank kyc_completed_date counted as a conversion,
a renamed column mapped onto its most plausible neighbor, a new region
quietly folded into “Other.”
The review queue is the product
The lazy answer is to re-check everything the model produces. That is the old job back, with extra steps. The queue only works if reviewing is cheap, and I made three decisions that kept it cheap.
Confidence flags per field. The extraction schema carries a companion field for every value: was the source ambiguous, missing, or in an unexpected format, and why. I open only the flagged fields. Most months that is a short list I clear with coffee in hand.
Diffs against last month. The workflow flags any metric that moves outside its usual range, whatever the model believed about it. Confident-and-wrong is the dangerous quadrant, and self-reported confidence will never surface it. The diff does.
Arithmetic never goes through the model. The workflow counts rows, totals columns, and reconciles against the raw export itself. If the totals disagree, the file never reaches review; it goes back to the cleaning step.
When HQ changes something upstream, the queue gets long. That is the alarm working. A long queue on the third of the month is how I find out that HQ changed something. It catches the loud changes. One quiet one still got past it, which is the next section.
Corrections should be permanent
The piece I wish I had built first: the master dataset lives in a git repo, and every correction from the review queue lands as a commit. “This region moved from LATAM to NA in the June export” is a one-line change with a date and a reason attached.
Before that, corrections lived in people's memories, and the same questions got re-litigated every close. Did we already fix the duplicate merchant rows? Was March restated? Now the answer is a commit, and next month's run starts from the corrected mappings instead of rediscovering them. The review work compounds instead of evaporating.
What still breaks
Candor is the point of writing this up, so: plausible renames still get through. When HQ renamed a funding column to something that resembled an existing field, the model mapped it confidently and the values stayed close enough that the diff sat under threshold for two closes. A human caught it in a quarterly review. The fix is now a commit, and my only real defense since has been tighter diff windows on the fields HQ touches most.
Slow drift is the other one. A metric that decays a little every month never trips a monthly diff. I now eyeball a six-month trendline for the headline metrics, which is exactly the kind of manual step this project was supposed to remove. I have made peace with that.
The schema guarantees the shape, the queue guards the values, and the repo keeps the corrections. Run two of the three and next month starts from scratch again.
If you build this in 2026
Spend nothing on parsing; structured outputs give you the format guarantee on day one. Put the effort here instead, in this order:
- Write the schema doc before the first prompt. Every failure I hit traced back to an ambiguous field definition.
- Strip PII in the workflow layer. “Please ignore the email column” is not a compliance control. Delete the column before the model enters the story.
- Design the review queue as the product. Confidence flags, monthly diffs, arithmetic checks outside the model. Budget for one field in five being wrong, and make checking it cost minutes.
- Version the dataset. A correction you cannot point to will be made again next quarter, by someone angrier.
The pipeline took a weekend. The review queue took months of small adjustments, and it is the reason the numbers on the dashboard are ones I will defend in a meeting.