Seeing an outbreak in the sewer before it hits the clinic
Wastewater surveillance program | SQL + Power BI, normalized signals and trend analysis
Locations, organizations, site names, and numbers on this page are invented. Virus and marker names are standard public health terms, so those stay.
The Problem
The lab results came back as spreadsheets. Each row had a genetic marker count for a sample taken somewhere in the sewer system, and on its own that number did not tell you much. Heavy rain or grey water getting into the lines dilutes a sample, so the count drops even when nothing in the community actually changed. A dry week does the opposite and pushes the count up. That made the files hard to use, because nobody could open one and say plainly which areas were getting worse and which were fine.
The Solution
The fix was to measure the dilution instead of guessing at it. Every sample is also tested for PMMoV, the pepper mild mottle virus, which shows up in human waste at a fairly steady rate whether people are sick or not. That makes it a good reference for how watered down a sample is. Dividing each virus count by that sample's PMMoV level takes most of the rain and flow effects out of the numbers, so results from different sites and different days could finally be compared side by side.
In a second round of the project I expanded the panel beyond COVID to the other viruses the lab was already testing for, so one report covered the full respiratory and gastrointestinal picture instead of a single virus.
I also added trend analysis, which ended up being the most useful part. A single reading tells you the level on one day, but not which direction things are moving. The report takes the 14 days ending on whatever date you select, compares them with the 14 days before that, and color codes each site by how much it rose or fell. Move the date and the whole map recolors, so you could spot a problem a week or two before it showed up anywhere else.
Try the trend map
This is a small working version of the same report, built on invented data for an invented region. Pick a virus, move the date slider, and click a site to see its history. Switch over to raw counts and you can see how much the rain effect was hiding.
Every trend below is the 14 days ending on this date, compared with the 14 days before it.
| Site | Trend | Level |
|---|---|---|
Willowbrook Communities | +2.15 | 718 |
Maple Ridge Care | +1.47 | 721 |
Northfield College | +1.33 | 4,359 |
State Prairie University | +1.04 | 4,769 |
Granite Valley DPH | +1.03 | 368 |
Lakewind Utilities | +1.01 | 1,097 |
North Ridge County Health | +0.94 | 480 |
Harbor Bluff Water | +0.93 | 450 |
State Prairie University | +0.84 | 1,633 |
Northfield College | +0.74 | 2,268 |
Maple Ridge Care | +0.64 | 288 |
North Ridge County Health | +0.51 | 9,759 |
North Ridge County Health | +0.47 | 1,360 |
Willowbrook Communities | +0.24 | 4,009 |
Harbor Bluff Water | +0.04 | 4,518 |
Willowbrook Communities | -0.18 | 1,412 |
Lakewind Utilities | -0.20 | 2,096 |
Cedar Valley DPH | -0.22 | 318 |
Northfield College | -0.25 | 1,165 |
Silver Creek Utilities | -0.36 | 1,886 |
Silver Creek Utilities | -0.36 | 930 |
Maple Ridge Care | -0.60 | 651 |
Harbor Bluff Water | -0.65 | 1,250 |
Cedar Valley DPH | -0.72 | 1,556 |
Lakewind Utilities | -0.79 | 747 |
State Prairie University | -0.90 | 300 |
Granite Valley DPH | -1.13 | 189 |
Cedar Valley DPH | -1.22 | 385 |
Illustrative data only. 28 invented sites across 10 invented organizations, generated in code. Virus and marker names are standard public health terms.
The Results
Cities, counties, universities, and nursing homes could open the map in the morning and see right away where cases were likely to rise. A university could watch the trend for its own residence halls. A county could tell whether an increase was limited to one neighborhood or spread across the whole area. Nursing homes could time their visitor precautions to the local numbers instead of the news. The sampling was already happening before I got involved. The report just turned it into something people could act on the same day they looked at it.