When a chiropractic practice owner came to me via Upwork, they had a clear request: "I don't want to rebuild anything. I just want to see what's happening."
They were already tracking everything — visits, collections, new patients — across Google Sheets. What they lacked was a way to see it all together, in one place, without digging through individual tabs for each doctor or office. This is a problem I see constantly with small and mid-size healthcare practices: the data exists, but the visibility doesn't.
Here's exactly what I built, and how I did it.
The Starting Point: Understanding the Data
Before touching a single chart or dashboard, I reviewed the existing data structure. The practice had records across two offices — Office A and Office B — and four associate doctors: Dr. Sarah Mitchell, Dr. James Patel, Dr. Emily Tran, and Dr. Marcus Lee.
Each row of data captured:
- The date of the visit
- Which office it occurred at
- Which doctor performed it
- The type of visit (Adjustment, Consultation, or Rehab)
- Whether the patient was new
- The collection amount in dollars
Simple, clean, and already consistent — which made this project much more straightforward than most.
Step 1: Structuring the Google Sheet
Rather than altering the raw data tab, I added a dedicated Summary tab on top of it. This is the key principle of this kind of project: you add a layer, you don't replace anything.
The Summary tab contains three tables, all driven by formulas that pull directly from the raw data:
Doctor Summary — Total visits, new patients, total collections, and average collection per visit for each doctor. These are calculated using COUNTIF, COUNTIFS, and SUMIF functions referencing the raw data tab. No manual entry required — when new rows are added to the raw data, the summary updates automatically.
Office Summary — The same metrics rolled up by office, making it easy to compare Office A vs. Office B at a glance.
Monthly Trend Table — Visits, new patients, collections, and average collection per visit broken out by month. This powers the trend charts in Looker Studio.
Step 2: Connecting to Looker Studio
Connecting Google Sheets to Looker Studio takes about 90 seconds. From the Looker Studio homepage, I created a new report, selected Google Sheets as the data source, pointed it at the Raw Data tab, and confirmed the headers were being read correctly.
The key decision here: I used the raw data tab as the primary source, not the summary tab. This gives Looker Studio full flexibility to slice and aggregate the data however we need, rather than being locked into the pre-aggregated summary structure.
Step 3: Building the Dashboard
I organized the dashboard into five pages, each answering a different question the owner might have when they open it in the morning.
Page 1: Executive Summary
Five KPI scorecards at the top of the page: total visits, total collections, new patients, average collection per visit, and new patient rate. A date range control sits in the corner — the owner can filter everything to this week, this month, or any custom range they choose.
This page is designed to answer the question: "How are we doing right now?"
Page 2: Doctor Performance
Bar charts showing each doctor's total visits and total collections, plus a detailed table with a heatmap overlay so the top performer is immediately visible. A dropdown filter lets the owner isolate a single doctor and review their numbers in full.
This page answers: "Which doctors are performing, and where are the gaps?"
Page 3: Office Performance
A donut chart for visit share between offices, a bar chart for collections, and a grouped bar chart breaking down visit types (Adjustment, Consultation, Rehab) by office. This surfaces patterns like one office skewing heavily toward adjustments while the other has more consultations — which often has implications for revenue mix.
Page 4: New Patient Analysis
Dedicated to tracking new patient flow by doctor and by office. New patients are the lifeblood of any practice, so giving this its own page keeps it front-of-mind rather than buried in the doctor performance table.
Page 5: Monthly collections
This is a time series that tracks the collection amount - both daily and cumulative. Helps to understand which days were busy and which days had no collection.
The Results
Across the three-month dataset:
- Office A handled a higher volume of visits, driven largely by Dr. Sarah Mitchell and Dr. Emily Tran.
- Office B showed a slightly higher average collection per visit, suggesting a different patient mix or visit type distribution.
- New patient flow was consistent across both offices month-over-month, with no significant spike or drop — a stable baseline to build from.
- Dr. Sarah Mitchell led on total collections in the dataset, while Dr. Marcus Lee showed the highest new patient rate among Office B doctors.
None of this required rebuilding the sheet. It required reading it better.
What I Delivered to my client
- Raw data tab (unchanged from original structure)
- Summary tab with auto-updating formulas and a written problem statement
- Full 5-page Looker Studio dashboard connected to live Google Sheets data
- Plain-English documentation for every chart, formula, and data connection
- 1 free revision post-delivery and 7 days of follow-up support
Why This Approach Works for Small Healthcare Practices
Most practice owners don't need a new system. They need a window into the system they already have. Building on top of existing Google Sheets — rather than migrating to a new database or analytics platform — keeps the barrier to adoption low, keeps the team's workflow intact, and keeps the owner in control of their own data.
The Looker Studio layer costs nothing to maintain, updates in real time as new data is entered, and requires no technical knowledge to use. That's the point.
If your practice has data scattered across spreadsheets and you're spending more time digging for answers than making decisions — this is the kind of project that pays for itself in the first week.
Want to see examples of dashboards I've built? Reach out and I'll share a live demo.
Top comments (0)