Sampreeth Avvari
Resume
Projects

Live demo

Cowork Dashboard. One set of numbers two clinics can trust.

A two-clinic operations dashboard built on weekly board exports instead of a live API, joining patients to leads through the CRM's native connect column. Six tabs share one metrics module, so two views structurally cannot disagree on the same number.

Data pipeline · Operations truth 6 steps · synthetic data

Walk through it, six steps

Step 1 of 6

Three files, once a week

Ops exports the leads board and both clinics' patient boards to Excel and drops them in one Drive folder. That is the entire data engineering ask of the team, which is why it actually happens every week.

Weekly exports folderSynthetic data
Leads.xlsxweek 32
  • CRM leads board export
Patients-ClinicA.xlsxweek 32
  • patient board, clinic A
Patients-ClinicB.xlsxweek 32
  • patient board, clinic B
Step 1 of 6

Every name, number, transcript and document in this tour is synthetic. The mechanisms, thresholds and architecture are real. The production system and its data stay private.

The problem.

Leadership needed one trustworthy view of the funnel: leads to scheduled to intakes to consults to accepted treatment, across two clinics. The tempting build was a live API dashboard; the honest one was a weekly export pipeline the team actually trusts, with lead-patient linkage done by the CRM's own connect column instead of fuzzy name matching.

  • Patient-lead linkage ~99% via the CRM connect column, not name matching
  • Weekly workflow 3 min drop three exports, hit refresh
  • Views 6 tabs one shared metrics module underneath
  • Verification Excel diff a harness checks metrics against ground truth

How it fits together.

4 layers, left to right

  1. Ingest

    • Weekly Excel exports into one Drive folder
    • Drive API converts xlsx to sheets
    • Header-name-based parser, order-proof

  2. Model

    • Raw tabs + sync state in one spreadsheet
    • Lead-patient join via CRM connect column
    • One shared metrics module for every view

  3. Present

    • Apps Script web app, Workspace-restricted
    • 6 tabs: scorecards, history, sources, trends, playground, weekly
    • Chart.js with prior-year overlays

  4. Trust

    • Data quality panel with fix instructions
    • Python harness diffs metrics vs raw Excel
    • 3-minute weekly workflow, 10-minute setup

Where it landed.

Leadership reads one set of numbers that agree with themselves and with the raw exports, and the weekly ritual is light enough that it has survived contact with reality. The interesting engineering decision was restraint: swapping a brittle live-API design for a verifiable weekly pipeline raised trust more than any feature could. Clinic names and all figures shown are synthetic stand-ins.

Concepts

Single source of truthRecord linkageBoring tech on purposeGround-truth verificationHeader-name parsing

Stack

Google Apps ScriptDrive APIChart.jsMonday.com exportsPython verification harness
Read the case study All demos