Stop Guessing Your Sendout Staffing. Let Your TAT Data Decide.

The Staffing Problem Nobody Measures Correctly

Most lab staffing decisions are made from gut feel, shift history, and the institutional memory of whoever's been there longest. And they're not entirely wrong — experienced lab staff build a real intuition for when things get busy. But intuition has a ceiling. It can't tell you whether 9 AM or 10 AM is actually busier across two years of data. It can't tell you whether Tuesday looks like Monday or Friday. It definitely can't tell you whether your quiet window is 1 AM–4 AM or 2 AM–5 AM.

Your LIS already has the answer. Every sendout order that arrives in-lab has a timestamp. That timestamp, aggregated across months of data, is a precise map of exactly when the volume hits — by hour, by day of week, by season. The problem isn't that the data doesn't exist. The problem is that nobody builds the analysis.

This notebook does exactly that. You connect it to a Google Sheet containing your in-lab arrival timestamps, and it outputs an interactive HTML report with box plots, a day-of-week heatmap, a median arrival curve, and a staffing guidance table — all derived from the data, not from assumptions. The report is self-contained: one HTML file you can email to your lab director, your operations team, or anyone who's ever wondered "when exactly is it busiest around here?"

What "Turnaround Time" Actually Measures

TAT is typically measured as the interval between specimen collection (or receipt) and result reporting. But turnaround time risk isn't uniform across the day — it concentrates where volume concentrates. A 6-hour TAT target that's trivially achievable at 3 AM becomes a real operational constraint when 40% of your daily sendout volume arrives between 9 AM and noon.

Understanding your in-lab arrival pattern is the first step in TAT management that most labs skip. They measure TAT in aggregate, see a number, and try to optimize the process. But aggregate TAT hides the hours where TAT degrades most — which are almost always the same hours where arrivals spike.

The notebook surfaces exactly where those TAT-risk windows are. If you want to go further and connect actual result times, the same framework extends naturally — but this analysis is valuable on its own as a volume-driven staffing baseline.

The Data You Need

The minimum viable dataset is almost insultingly simple: a spreadsheet with a datetime column representing when each sendout order arrived in-lab. That's it. Pull it from your LIS, paste it in Google Sheets, share the link with the notebook.

inlab_date,order_id
2024-01-03 09:14:22,ORD-000001
2024-01-03 09:47:05,ORD-000002
2024-01-03 14:03:18,ORD-000003
2024-01-04 08:55:44,ORD-000004
...

The notebook auto-detects columns named anything like inlab_date, inlab_dts, arrival_date, received_dts, or date. If your column name is something else entirely, it'll fall back to the first column. Additional columns — department, test name, priority level — don't hurt anything, they're just ignored unless you want to extend the analysis.

Step 1 — Connect Google Sheets to Colab

Open the notebook in Google Colab, paste your Google Sheet URL into Cell 2, and you're connected. The sheet needs to be shared as "Anyone with the link can view" — the notebook converts your URL to a direct CSV export endpoint automatically.

1. Export your sendout arrival data from your LIS

   Pull at minimum 90 days — 6–12 months gives you seasonal patterns and enough volume for reliable hourly distributions.

2. Paste into Google Sheets, share the link

   File → Share → Change to "Anyone with the link" → Copy link. That's the URL you'll paste into Cell 2 of the notebook.

3. Open the notebook in Colab and paste the URL

   Change GOOGLE_SHEET_URL = "" to your URL. Everything else runs automatically.

4. Runtime → Run All

   The notebook parses the timestamps, computes hourly distributions, builds all charts, and generates the HTML report. Takes about 15 seconds on a typical dataset.

# ── CONFIGURATION ─────────────────────────────────────────────
# Paste your Google Sheet URL here
# Leave empty "" to use the built-in sample dataset
GOOGLE_SHEET_URL = "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/edit"

REPORT_TITLE     = "Sendout In-Lab Arrivals — Hourly Staffing Analysis"
OUTPUT_FILENAME  = "sendout_tat_staffing_report.html"

# Optional: filter to a specific date range
DATE_START = None  # e.g. '2024-01-01'
DATE_END   = None  # e.g. '2024-12-31'

Step 2 — What the Notebook Computes

Once the data loads, the notebook builds four core analytical layers before writing a single line of HTML. These layers are what drive the staffing guidance table in the final report — they're not decorative charts, they're the actual model.

# Daily volume per hour — the core data structure
hourly_daily = (
    df.groupby(['date', 'hour']).size()
      .unstack(fill_value=0)
      .reindex(columns=range(24), fill_value=0)
)

# Percentile distributions per hour
box_data = [hourly_daily[h].values.tolist() for h in range(24)]
meds     = [float(np.median(d))            for d in box_data]
q1s      = [float(np.percentile(d, 25))   for d in box_data]
q3s      = [float(np.percentile(d, 75))   for d in box_data]
p5s      = [float(np.percentile(d,  5))   for d in box_data]
p95s     = [float(np.percentile(d, 95))   for d in box_data]

# Peak zone: hours above 75th percentile of medians
peak_thresh   = np.percentile(meds, 75)
peak_zone_hrs = [h for h in range(24) if meds[h] >= peak_thresh]

# Quiet window: lowest consecutive 3-hour block
quiet_start = min(range(22), key=lambda h: meds[h]+meds[h+1]+meds[h+2])

Step 3 — The Interactive HTML Report

The output is a fully self-contained HTML file — one file, no dependencies, no login, no BI license. Open it in a browser, zoom, hover over cells, filter by scrolling. Every chart is interactive: hover any bar in the distribution chart and you get the exact percentile values for that hour. Hover any cell in the heatmap and you get the average arrival count for that day-hour combination.

The report also includes a Day × Hour heatmap — the single most useful view for planning shift coverage. It shows average arrivals per slot across every combination of day of week and hour of day. Mondays look different than Thursdays. Saturday nights look nothing like Monday mornings. The heatmap makes that visible in one glance.

Finally, there's a staffing guidance table that classifies every hour of the day into Peak, Moderate, or Low zones and writes a recommended action for each — not as static text, but derived from your actual data. If your peak zone is 7 AM–11 AM based on your data, that's what the table reflects.

Step 4 — Download and Share

After the notebook finishes, Cell 7 triggers an automatic download of the HTML report to your local machine. Open it in any browser. Send it in an email. Put it on a shared drive. It works anywhere without any software — because it's just a file.

There's also an optional inline Colab preview in Cell 8 if you want to inspect the report before downloading. On most datasets this renders cleanly — if the preview is cut off, just open the downloaded file directly in Chrome or Edge.

try:
    from google.colab import files
    files.download(OUTPUT_FILENAME)
    print("📥 Download triggered — check your browser downloads.")
except ImportError:
    print(f"ℹ️  Not running in Colab. Saved locally as: {OUTPUT_FILENAME}")

What You Get

How This Connects to TAT Improvement

Reducing lab turnaround time is ultimately a queuing problem. You have arrival rates, service rates, and staffing levels — and TAT degrades when arrivals outpace service capacity. This analysis gives you the arrival side of that equation in precise, data-driven terms. You finally know exactly when volume hits, how reliably it hits, and which days are reliably outliers.

The next logical step is layering in your actual result timestamps to measure TAT directly and show how it correlates with arrival peaks. That's a natural extension of this analysis — and a future notebook in this series. But this analysis is the foundation. You can't manage what you haven't measured, and most labs have never formally measured their arrival pattern at all.

If you're dealing with TAT complaints, staffing disputes, or courier schedule optimization, start here. The answer is probably already in your LIS — you just need the analysis to surface it.