Content Performance Explorer
Which articles actually sell subscriptions — by section, topic, format, author, and hours spent on the homepage. Editorial asked weekly. Now they don't have to ask.
- Role
- Requirements, analysis, and build
- Context
- RCS MediaGroup
- Period
- 2025 – 2026
- Stack
-
- Python
- FastAPI
- React
- Vite
- BigQuery
- Parquet cache
- Warehouse table article grain
- Columnar cache on disk survives a restart
- In-memory frame async background refresh
- Aggregation per request pure functions over a dataframe
- React filters & drill-down click a bar, get the articles
Objective
Editorial and marketing both wanted the same thing from different angles: which content converts. Not which content gets traffic — that was already visible — but which pieces lead a reader to buy a subscription within a day of reading.
The question came back every week with a different slice: by section, by topic, by format, by author, by single URL.
Approach
Rather than answering slices, I built the slicing. Every dimension became a filter and every filter combination stays interactive, which meant the aggregation had to be fast under arbitrary grouping.
The chart that changed the conversations was the least sophisticated one: a scatter of conversions against hours spent on the homepage, with bubble size as conversions per article. It makes two things visible at a glance — content that converts because it was promoted, and content that converts despite barely being promoted. The second group is the interesting one, and no ranked table had ever surfaced it.
Drill-down matters as much as the aggregate: clicking a bar or a bubble opens the actual articles behind it. A dashboard that cannot answer “which ones?” sends the reader back to asking a human.
Architecture
Warehouse table → local columnar cache → in-memory dataset with asynchronous background refresh → pandas aggregation per request → JSON. The cache layer exists because the dashboard is read-only and slightly stale is fine, while slow is not; a status endpoint lets the frontend show a loading state on cold start instead of hanging.
All filtering and aggregation is pure functions over a dataframe, kept separate from the API layer — the only reason the dimension list could grow without the code turning into a maze of endpoints.
What I owned
From the editorial brief to the working tool: warehouse reads, caching strategy, aggregation logic, and the React frontend with its filters, charts and drill-down popups. Deployment was data engineering’s.
Outcome
The weekly “can you pull the numbers for this section” request stopped. Editorial started using the scatter view in planning meetings, which was not something I designed for and is the outcome I am most pleased about.