Coffee Chat Operations Dashboard

LONG STORY SHORT

LIVE PRODUCT

See it in action

The dashboard shipped in Spring 2025 and is actively used by the MSTI program team. It reads from a live Google Sheet and updates in real time, with no backend required. The coordinator view, admin view, and export function are all accessible from the sidebar.

PROBLEM

The Excel tracker wasn't working

The MSTI coffee chat program ran on a shared Google Sheet maintained by 5 coordinators. Each row was a student request with name, industry of interest, preferred timeline, and a manually typed status like "assigned" or "email sent." When the program was small, this worked. At 106+ requests per semester, it collapsed.

The core problems: coordinators had no way to see who owned what. Status updates were inconsistent. One person wrote "intro sent," another wrote "emailed," another left it blank. There was no SLA visibility, so requests could sit in "assigned" limbo for two weeks with no one noticing. An estimated 8–12 requests per semester were simply dropped. Students who submitted never heard back.

The biggest pain point surfaced in a coordinator interview: "I spend 20 minutes every Monday just figuring out what I'm supposed to be doing." That was the problem worth solving.

Student intake form. Each submission became a row in the shared tracker

Original Google Sheet tracker with 5 coordinators, no ownership column, and no SLA visibility

DISCOVERY

Interviews first, build second

Before touching any tooling, I ran 7 structured stakeholder interviews over two weeks: 5 coordinators and 2 program directors. Each session followed the same script: "Walk me through how you handle a request from the moment it comes in to the moment feedback is collected." I was looking for where time was lost and where ownership broke down.

Findings clustered into three themes: ownership ambiguity (no one knew who was responsible for what), status vocabulary drift (14 unique variations of "intro email sent" across the sheet), and SLA blindness (no deadlines, no escalation path). I synthesized these into a 2-page PRD that scoped the MVP: a read-layer dashboard on top of the existing Google Sheet, with standardized status stages, coordinator-level ownership views, and automated SLA flags.

SOLUTION

Building the dashboard

I rebuilt the system around a single core model: every request moves through four explicit stages, each with a hard SLA deadline. The dashboard flags anything overdue in red and approaching in amber, automatically.

I built it in Streamlit backed by a Google Sheets API data source, no backend rip-and-replace required. Coordinators still edited the sheet; the dashboard read from it and layered intelligence on top using pandas for data normalization across the 14 inconsistent status string variants. Key features: coordinator-level ownership view so each person saw only their queue, a cross-coordinator admin view for the program director, bulk export for end-of-semester reporting, and a summary banner showing live counts of on-track vs. overdue requests.

The biggest build decision: keep the Google Sheet as source of truth rather than migrating to a database. This was a deliberate scope call. Coordinators already lived in the sheet. Forcing a tool switch would have tanked adoption before a single request was tracked. The PRD explicitly scoped this out. The dashboard was a read layer, not a replacement.

Dashboard overview: status pipeline, SLA indicators, and coordinator assignment view

IMPACT

Results and learnings

We launched in Week 3 of Spring 2025. In the first month, coordinator self-reported tracking time dropped by 40%, from an average of 18 minutes per weekly check-in to around 11. Post-launch survey of all 11 MSTI staff showed 91% satisfaction. Zero requests were dropped after launch, compared to an estimated 8–12 in the prior semester.

The hardest lesson: the dashboard was not the hard part. Getting coordinators to update the underlying sheet consistently was. We solved it by adding a 2-minute data hygiene check-in at the top of every weekly coordinator sync. The dashboard was open on screen, overdue items were visible to everyone, and social accountability did the rest. Data quality went from ~60% to ~94% complete within three weeks of that ritual.

Week-over-week request completion rate before and after dashboard launch