CineSignal

Campaign Impact needs one input: when the marketing beat happened
Click any number: the exact SQL, 8,192 rows scanned, 367ms
Point it at any title, search resolves through a real ClickHouse query, live
The agent runs live, every step is a real MCP query to ClickHouse Cloud
Verdict: IN LINE. Every stat cites the query behind it
Platoon (1986) resolves with zero ambiguity
Public Fan Explorer, no agent or LLM in this path
Actual attention vs. a counterfactual baseline, 48-title cohort, 81.2 percentile
CineSignal: evidence-backed campaign impact memos over real Wikipedia attention data

Inspiration

A marketing VP stares at a slide of engagement percentages and can't say whether the trailer moved a single ticket. Social listening vendors sell a sampled, unauditable guess for that answer. Meanwhile hundreds of millions of people vote with their attention on Wikipedia every day: a free, absolute count, per day, per language, already validated in a peer-reviewed study (Mestyán, Yasseri & Kertész, PLoS ONE, 2013) as a box office predictor. No one in that marketing workflow can query it.

We wanted to treat fan attention like market tick data, measuring marketing cause and effect the way a finance desk measures abnormal returns around an earnings call, rather than trying to predict the box office outright.

What it does

CineSignal runs three playbooks against a real attention warehouse: Wikimedia pageviews joined to the IMDb catalog through Wikidata, in ClickHouse Cloud.

Title Pulse checks a title's attention health: trend, percentile rank against a same-genre cohort, anomaly days, 28-day momentum.

Campaign Impact is the flagship. Point it at a marketing beat (a trailer drop, casting news, a release date announcement) and it runs a real event study: abnormal attention lift against a baseline-scaled counterfactual cohort, spike decay half-life, spillover to cast and crew pages. Run live against Platoon's 1986 awards-season campaign, it came back with a 12,418-view abnormal lift, an 81.2 percentile rank against a 48-title cohort, and a spike that decayed by half in a single day.

Launch Window ranks candidate release weekends in a target quarter by competition density and a five year seasonal genre demand curve.

Every run produces a decision memo: prose with [qN] citation markers. Click a citation and an evidence drawer opens, showing the exact SQL, the raw rows, and the query's elapsed time and rows scanned. A validator rejects any memo that states a number it can't trace back to an actual query result. The numbers are never just "the model said so."

How we built it

The warehouse sits on one join spine. An entities table, resolved from Wikidata via SPARQL, carries IMDb tconst/nconst ids. It joins against the raw IMDb catalog and a pageviews_daily table loaded from the Wikimedia per-article pageviews REST API. A materialized view, entity_attention_daily, is what every playbook actually reads.

We call this the determinism boundary. Every playbook's SQL lives in a versioned YAML template with typed, literal-escaped {{param}} placeholders. The model never writes or sees raw SQL. It only chooses parameter values, through the resolver, and prose, through the composer. The SQL behind any number in the memo is therefore fixed and inspectable, which is the whole reason a citation is worth anything.

The agent tree runs on Google ADK against Vertex AI Gemini. An EntityResolver (Flash) disambiguates search candidates. An Interpreter (Pro) turns deterministically computed findings into ranked talking points. A MemoComposer (Pro) writes the cited prose. A NumberValidator checks every citation against the query results, and it's deliberately not a model at all.

Every playbook query, and the resolver's search query, goes through the official mcp-clickhouse server over stdio rather than a raw ClickHouse connection. We verified that path live, end to end, before building anything on top of it.

The frontend is React, Vite, and ECharts, with Server-Sent Events streaming the live step timeline while a playbook runs. It ships as one Cloud Run container. FastAPI serves both the SPA and the API. mcp-clickhouse runs as a subprocess at runtime. ClickHouse credentials come from Secret Manager, and a dedicated least-privilege service account handles Vertex AI auth.

Challenges we ran into

The model IDs from the docs didn't work, and this was the challenge with the widest lesson. The -latest aliases (gemini-flash-latest, gemini-pro-latest) 404'd on Vertex AI's Model Garden, the exact aliases the ADK docs recommend for "pin whatever's current." Some newer models listed by client.models.list() 404'd too, on generate_content, for this project and region. Neither the documentation nor the model catalog could be trusted; only a real API call could. We now verify every model ID that way before it ships.

The Wikidata Query Service kept timing out. The literal SPARQL query for "all films" (wdt:P31/wdt:P279*, a transitive class path) failed regardless of batch size, and a combined query joining sitelinks, release dates, and genres threw a Blazegraph StackOverflowError. We split it into two stages: a cheap non-transitive paginated scan, then small targeted lookups per page.

rows_scanned looked broken. We were reading it from system.query_log, which flushes asynchronously, so the value was landing as 0 in the UI. Reading it synchronously off clickhouse-connect's HTTP response summary instead fixed it.

Deep links 404'd in production, because StaticFiles(html=True) only serves exact file matches. Refreshing on /memo/:id returned a 404 until we added an explicit SPA fallback route.

One more thing surfaced while recording the demo, and it is worth stating plainly: the cohort-candidate query uses LIMIT without an ORDER BY, so ClickHouse is free to return a different arbitrary subset per run. Two runs with identical inputs produced 48 titles at the 81.2nd percentile and 46 titles at the 78.3rd. The SQL and the citations are reproducible; the cohort membership is not yet, and a one-line ORDER BY is the fix.

Two more bugs surfaced during final live testing, not a staged demo. A title with no comparable cohort candidates was producing an empty percentile instead of a clear result. The Fan Explorer page was rendering genre tags as individual characters instead of words. Both got fixed and redeployed before submission.

Accomplishments that we're proud of

A memo that cites its own SQL, and lets you click through to the real rows and scan stats behind every number. We verified this live, not just in a demo script: search, resolve, run a playbook, watch a real event study chart render, click a citation, see the actual query, the actual rows, the actual elapsed_ms and rows scanned. The agent tree itself, resolver through validator, worked correctly against real production infrastructure on its first end-to-end run. The two bugs in the Challenges section above were UI and data-edge-case bugs, found during the live testing that came after.

What we learned

Determinism is a design decision, not a hope. The most fragile-sounding piece of the spec, "the model never writes SQL," turned out to be what made the rest of the system trustworthy. The spec's own sketch labeled the validation step "flash," asking one model to grade whether another model hallucinated. We call that the model grading its own exam, and it's exactly the kind of judgment a model is bad at making about itself. Because every number traces to a fixed, versioned query template, the NumberValidator gets to skip that exam entirely: it's a plain set-membership check. That's the design decision every citation in the memo is standing on.

What's next for CineSignal

Scale ingestion past the current floor toward the full multi-year, multi-language target. The pipeline is checkpointed and resumable, and it's already running unattended in the background (currently 195,659 entities, 10.4M attention-days indexed).

Build the stretch playbook from the spec: Localization Priority, ranking per-language over-index and under-index against global share, for dubbing and territory prioritization.

Run a studio pilot: nightly memos across a whole release slate, not one title at a time.

A marketing number is only as trustworthy as the query a person can click through to verify it. CineSignal proved that this weekend, against real production infrastructure, not a mockup.