Content Performance Watchdog

Inspiration

What it does

How we built it

Challenges we ran into## Inspiration

Streaming platforms release hundreds of titles a year. When one quietly underperforms, someone has to notice — usually by scanning dashboards and spreadsheets by hand, show by show, region by region. By the time a human catches it, weeks of marketing budget and audience attention are already gone.

We wanted to see if an AI agent could do that first pass automatically — not just flag the outliers, but give an analyst a grounded, useful starting hypothesis for why, so they open their day already knowing where to look.

What it does

Content Performance Watchdog scans a media/streaming catalog and:

1. Detects underperformers — queries viewership data in ClickHouse (via the official mcp-clickhouse MCP server) to find shows falling well below their expected audience.
2. Finds a real comparable — for each underperformer, it runs a second query to find a show in the same genre and region that succeeded, giving the diagnosis something concrete to reason against instead of guessing in a vacuum.
3. Generates a grounded hypothesis — sends both shows to Gemini, which reasons comparatively about the gap, using deliberately hedged language ("may have struggled," "likely benefited") rather than asserting a confirmed cause.
4. Presents it clearly — a searchable, click-to-inspect dashboard ranking every underperformer by shortfall, with each case file showing the issue, the comparative diagnosis, a concrete recommendation, and an explicit confidence note framing the output as a hypothesis for human review, not a verdict.

How we built it

• ClickHouse Cloud stores the viewership dataset (750 synthetic shows, ~16% deliberately underperforming for realistic signal).
• Every single ClickHouse interaction — reads and writes — goes through the official mcp-clickhouse MCP server's run_query tool. This includes the original CREATE DATABASE/CREATE TABLE/INSERT during data loading. No direct SQL client is used anywhere in the codebase; this was a deliberate architectural choice to genuinely satisfy the MCP requirement, not a nominal integration.
• Google Gemini powers the comparative diagnosis step, with a prompt specifically engineered to reason against a real baseline rather than free-associating from genre/region alone.
• Streamlit renders the dashboard, custom-styled to move away from default chart-app aesthetics — a deliberate visual identity ("Ratings Room") built around real broadcast-industry motifs (tally lights, marquee bulbs, screening rooms) rather than generic dashboard tokens.
• The agent pipeline writes results incrementally, after every show, and can resume from where it left off — a design change made after a real mid-run network failure taught us the cost of an all-or-nothing write.

Challenges we ran into

• Gemini's free-tier daily quota (20 requests/day per model) repeatedly blocked full-batch runs. We solved this by switching to a separate model with its own quota bucket, and by re-architecting the pipeline to write and resume incrementally, so a quota wall or network drop never cost us completed work again.
• The ClickHouse MCP server exposes only three tools (list_databases, list_tables, run_query), with no dedicated data-loading tool. All schema creation and data insertion had to be routed through run_query — genuinely MCP end-to-end, but requiring more careful design than a direct SQL client would have.
• A subtle MCP connection-validation issue: the server validates its configured default database at connect time, even before that database exists — solved by bootstrapping against default first, then creating and switching to the target database.
• Getting the UI to feel like a real product, not a generated template took real iteration — moving from default chart-library styling to a custom, restrained visual identity with real typographic hierarchy and a layout that scales cleanly from 5 shows to the full 118.

What we learned

That grounding an LLM's reasoning in an actual comparable data point — rather than asking it to reason from a single row of metadata — meaningfully changes the quality and specificity of its output. And that being explicit about a model's epistemic limits (hypothesis vs. verdict) is not a hedge that weakens a tool — it's what makes an AI-generated recommendation something a real analyst could actually trust and act on.

What's next

• Grounding diagnoses in real signals beyond metadata — marketing spend, release calendar density, competitive titles in-market — rather than genre/region/viewership alone.
• Trend-based detection (catching a show sliding before it fully underperforms), not just point-in-time thresholds.
• A feedback loop where an analyst's real outcome (did the recommendation work?) refines future diagnoses.

Accomplishments that we're proud of

What we learned

What's next for Content Performance Watchdog

Built With

• clickhouse
• faker
• gemini-api
• google-cloud
• google-gemini
• mcp-clickhouse
• model-context-protocol
• python
• python-dotenv
• streamlitmcp