Retention Autopsy

An agent that finds where viewers abandon a title, explains which scene lost them, and drafts the promo cut and campaign fix — powered by Gemini and ClickHouse.

Comment

Inspiration

A streaming platform can tell you, to the second, that people stop watching a title. What it cannot tell you is why.

Answering that question means an analyst writing ad-hoc queries against playback telemetry, cross-referencing timestamps against a scene list, and then checking whether the people who left were ever the right audience in the first place. It is most of a day of work, per title. Most titles never get looked at, so the drop-off keeps happening and nobody knows what caused it.

The data to answer it is already sitting in the warehouse. That gap — the answer is present but nobody has a day to spare — is what this project attacks.

What it does

Retention Autopsy runs the whole investigation on one button press, and shows its working while it does.

Given a catalogue of titles, the agent:

  1. Locates the steepest sustained drop-off across every title, comparing proportional decline between consecutive 30-second buckets rather than raw viewer counts.
  2. Explains it by joining the drop-off window to scene metadata, naming the scenes on screen and their pacing scores.
  3. Benchmarks it against other titles in the same genre, so a normal genre pattern is not mistaken for a problem.
  4. Segments the viewers who abandoned by acquisition campaign, separating a content problem from an audience-mismatch problem.
  5. Acts — finds the three highest-completion-rate segments to cut a promo from, writes a campaign recommendation, and persists both back into ClickHouse as a queued production job.

On our seeded catalogue it independently identifies "The Salt Line" losing roughly 57% of its remaining audience starting around the 15-minute mark, during two consecutive slow-paced scenes tagged flashback and exposition with pacing scores of 0.163 and 0.113, against a thriller-genre average drop of about 11% at the same point. Nothing in the prompt tells it where to look.

The output is an action, not a chat answer.

How we built it

The agent is built with the Google Agent Development Kit and runs on Gemini 2.5 Pro through Vertex AI. It reaches ClickHouse exclusively through the official ClickHouse MCP server (mcp-clickhouse), so the model discovers the schema and writes its own SQL rather than calling hardcoded queries. The one thing it does not do through MCP is write, because that server is read-only by design — the write-back is a separate ADK FunctionTool using clickhouse-connect.

The interface is a FastAPI service that streams every step of the run to the browser over Server-Sent Events: the tool being called, the SQL being sent, the rows coming back. The whole thing deploys as a single Cloud Run service with a plain HTML front end, no build step.

The dataset is synthetic streaming telemetry generated by a seed script — around 63,000 per-viewer playback heartbeats across four fictional titles, plus scene-level metadata and campaign attribution, with realistic attrition curves and one deliberately planted anomaly.

Challenges we ran into

The agent invented an entire analysis. On the first run it produced a confident, well-formatted report about a title called "Starlight Odyssey" with plausible-looking numbers — none of which existed in our database. The MCP server had failed to start, ADK's graceful error handling swallowed the failure, and the agent was left with zero tools and no indication anything was wrong, so it answered from imagination. The lesson was to stop trusting fluent output and assert on the plumbing instead: we now verify the tool list loads before running anything, and the run is only considered complete when a job row actually appears in ClickHouse. A demo that looks right and is entirely fabricated is worse than one that fails loudly.

A dependency conflict with no shared solution. ADK requires mcp<2; mcp-clickhouse is built on FastMCP and requires mcp>=2. Pinning either one breaks the other, and both need to run in the same process tree. The fix was to stop trying to reconcile them: the MCP server runs in its own isolated environment, launched as a subprocess, so each package gets the version it needs and they never meet.

The agent kept finding the wrong drop. Early runs identified the 30-second mark, which is just people sampling, and later the tail end of the film where losing three viewers out of five reads as a 60% collapse. Both are statistically real and completely useless. Fixing it was a prompting problem, not a code one — instructing it to ignore the first 60 seconds, to look at sustained decline across consecutive buckets rather than single dips, and to report where a decline begins rather than where it bottoms out.

Promo segments from the opening credits. Asked for the highest-retention segments, the agent returned 0-30s, 30-60s and 60-90s — trivially correct, since everyone is still watching at the start, and useless as promo material. Switching the instruction from raw retention to completion rate among viewers who reached each segment produced sensible mid-film picks.

Accomplishments that we're proud of

The agent reconstructs a planted anomaly it was never told about, using SQL it writes itself, and ends by filing a work order rather than printing a paragraph. Watching the tool calls stream into the page in real time is the part that makes it feel less like a chatbot and more like an instrument.

What we learned

How much of an agent's quality lives in the instruction rather than the model. Every real improvement in this project came from being more specific about what "sharpest drop" and "best segment" actually mean — the same model with a vague brief produces confident nonsense, and with a precise one produces something an analyst would sign off.

Also: tool schemas are prompts. The docstring on the write-back tool is what the model reads to decide when and how to call it, and tightening that docstring changed behaviour more than any amount of rewording elsewhere.

What's next for Retention Autopsy

Wiring it to live playback streams rather than a seeded table, running it on a schedule across a whole catalogue instead of one title at a time, and letting the promo cut list feed straight into an editor's timeline rather than sitting in a queue.

Built With

Share this project:

Updates