Content Pillar Classifier
A connector can give you every field a platform publishes. It cannot give you a field the platform never had. “What is this video for” is a judgement, not a metric, and no amount of API access produces it.
This runs a Supermetrics query through a classifier and writes the answer back as a column in the same sheet, keyed on video ID so it joins to live metrics. Nothing here is specific to YouTube — it reads whichever text columns you point it at and applies whichever taxonomy you give it.
1 · Connect your sheet
Run your Supermetrics query first, then point this at the sheet it wrote to. The only permission requested is drive.file, which grants access to the one file you pick — not your Drive.
Enrichment as a pipeline stage, not a replacement for one
The prerequisite is a query you already ran. A marketer uses the Supermetrics Google Sheets add-on to pull a channel's videos into a sheet in the ordinary way. This tool starts where that finishes. It does not fetch from YouTube, does not hold platform credentials, and does not duplicate a single metric the connector already delivers — the sheet stays the source of truth and the tool adds one field to it.
Three fields carry the signal. Video title, description and duration. Duration matters more than it looks: it is the only thing separating a forty-second brand clip from a fifty-eight-minute recorded webinar, and its absence is what made the original labels wrong. Tags were dropped after measurement — 88% of rows on the test channel had none. Descriptions are cut at 300 characters, past which they are mostly link boilerplate.
The label is constrained at decode time, not requested in a prompt. The eight pillars are a JSON Schema enum on Claude's structured outputs, which compiles the schema into a decoding grammar — a ninth pillar is unrepresentable rather than merely discouraged. That matters because the label joins to a dashboard on a stable key, where one phantom value silently splits a view count in two. Casing is not guaranteed by that mechanism, so validation normalises it and reports the repairs; the set is a structural guarantee, the casing is not, and saying so precisely is the honest version.
The prompt is ordered, not just descriptive. Pillars are evaluated top-down with an explicit first-match-wins rule, three precedence rules that resolve the boundaries that actually collide in practice, and a per-vendor list of data destinations — because “moving data into Power BI” is a tutorial while “building on Claude” is partner content, and no amount of general instruction gets a model there. Temperature is zero, and batches of 25 share one rubric so classifications are made in the same context rather than 286 independent ones.
Haiku 4.5 was chosen on evidence, not price. It is the cheapest model that supports structured outputs, but a full 286-row run costs about thirteen cents on either it or Sonnet — six cents apart is not a decision. It was selected because the eval harness measured it and it was right, and the harness was built first precisely so that call could be made on a number.
What was tried first
Supermetrics ships this capability. Custom fields support a Transform with AI step, and the intended route was to stay entirely inside the product: concatenate title, duration and description into one custom field, truncate it, then run an AI transformation over the result.
It returned blank for every row. A minimal reproduction — one field, one step, a thirty-character prompt — was still blank, while the concatenation field alone rendered correctly, so custom fields worked and the AI step specifically produced nothing. That was raised rather than worked around.
The more interesting limit was measurable regardless. Transform with AI caps prompt plus input value at 1,500 characters combined; with a 300-character input that leaves roughly 1,200 for the rubric, against 3,120 for the full one. Compressing it means deleting the destination list and most of the precedence detail — the parts doing the work. Running the same model under exactly that constraint scored 68.5% against 100% unconstrained, and cost 65% more, because a per-row prompt is repaid on every row instead of amortised across a batch. The native feature is the right first answer; this is where it runs out.
Two credentials, two parties, neither doing the other's job
The page needs a user's spreadsheet and a model API key. Putting both in one place is the obvious design and the wrong one. The browser holds the visitor's Google token and owns all sheet I/O; a Cloudflare Worker holds the Anthropic key and only ever turns text into labels. The Worker never sees a spreadsheet, the browser never sees the model key, and no service-account credential sits in a secret store waiting to leak.
The scope is drive.file, not spreadsheets. The obvious choice is classified sensitive by Google and triggers an app-verification review measured in weeks; drive.file is non-sensitive and grants access to exactly the file the visitor picks in the Picker — which is also what makes the permission prompt honest rather than alarming.
Key design decisions
- Rows are addressed by video ID, never by row index. The ID column is re-read at write time and each row gets the label belonging to the ID currently in it, so a connector refresh that reorders rows or changes the row count re-aligns instead of shifting every label by one.
- Columns are addressed by header, never by position. The header row is detected rather than assumed — a Supermetrics query can write raw field IDs above the display names — and duplicate headers are surfaced for the user to resolve rather than guessed at.
- Batching lives in the browser, not the Worker. One invocation makes one upstream call, so subrequest and CPU ceilings never apply, a failed batch retries without restarting the run, and progress is real rather than simulated.
- Failures degrade instead of collapsing. A rate limit or upstream error still returns a complete result set of Unclassified rows in the contract's shape, so the sheet always gets a full column that admits which rows it could not label.
- Rows marked `human` are never overwritten. Someone who corrects a label keeps it, along with its timestamp and provenance, on every subsequent run.
- Nothing is stored server-side. The Worker keeps no spreadsheet data, no results and no tokens — only a per-IP row counter, so a public URL cannot quietly spend a personal API budget.
Tech stack
- Supermetrics — Google Sheets add-on — YouTube Public Data connector
- Google Picker API — File selection, and what grants drive.file on that one file
- Google Sheets API v4 — values.get, values:batchUpdate, appendDimension
- Google Identity Services — Popup token flow — no redirect route, so a static export can use it
- Claude Haiku 4.5 — Classification with structured outputs
- Cloudflare Workers + KV — Holds the model key; per-IP row budget
- Next.js + Cloudflare Pages — Static export — no server runtime anywhere in this stack
The reference labels were wrong, and the disagreements are how I found out
The eval harness came before the tool. 286 videos from a real Supermetrics query, an offline scoring script, and a mock provider that injects known faults so the harness could be checked against itself before it was trusted to check anything else.
The first real run scored 70.6% and looked mediocre. It wasn't. Every disagreement clustered at the two ends of the duration distribution: the reference bucket labelled “Thought leadership” held 53 rows, of which 20 ran under sixty seconds and 18 over twenty minutes. That is not one category. The earlier labelling pass had title, description and tags but no video duration, so it could not separate a 53-second brand clip from a 58-minute recorded webinar and put both in the default.
All 286 were then relabelled by hand from title, length, link and description, with no model output in view. Scored against those labels:
| Labeller | Accuracy | n | 95% CI |
|---|---|---|---|
| This classifier — batched, schema-constrained | 100% | 286/286 | 98.7 – 100 |
| The original labels — built without duration | 70.6% | 202/286 | 65.1 – 75.6 |
| Same model under Transform with AI's 1,500-character cap | 68.5% | 196/286 | 62.9 – 73.6 |
Key design decisions
- Reproducibility and accuracy are not the same measurement. Agreement with an existing label set tells you whether a classifier reproduces an earlier run; it says nothing about whether either is right. Conflating them is how a stale reference survives.
- Agreement is not evidence when two labellers share a rubric. 202 rows where the model and the original labels agreed were never examined — an unexamined agreement scores exactly like a correct one, which is the same selection bias that makes unjudged documents look irrelevant in retrieval evaluation.
- Confidence was useless for triage: 0.882 mean on wrong answers against 0.908 on right ones. A 2.6-point gap kills the obvious 'route low-confidence rows to a human' feature before it gets built.
- The 1,500-character cap is Supermetrics' own limit on Transform with AI — prompt plus input value combined — not an arbitrary comparison. Reproducing it with the same model on both sides, one row per call and no schema, isolates what the constraint costs rather than confounding it with whose implementation is better.
- That constrained arm is also 65% more expensive, because a per-row prompt is repaid on every row instead of amortised across a batch of 25.
- A single labelling pass means no inter-rater reliability figure. Forty rows relabelled independently would give one; it is not yet measured, and the number should be quoted with that attached.