The data model
This interactive report is built using Microsoft Power BI, and data from the New York Times’ Github-hosted coronavirus dataset. The report’s data updates twice a day, at 10pm and 10am. We originally built this just to use internally, but our hope is that you’ll find value in it as well.
Our Report adds a modeling layer on top of the New York Times’ raw data. Their datasets track daily reports of the cumulative cases and fatalities in every county in the US. We used those totals to derive a model that includes:
- County-by-county daily new cases and daily new fatalities
- A 2 week moving average of daily new cases and daily fatalities
- The growth rate of new cases and fatalities in a 2 week period relative to the previous two weeks.
Video: Introducing the COVID-19 Data Analyzer
Glenn Burnside walks through this custom-made COVID-19 Data Analyzer, built using Power BI.
Growth rate analysis
We built this report to highlight what we believe are the two most important metrics to monitor for any given geography – the growth rate in cases, and the growth rate in fatalities. These primary drivers are informed by the federal government’s phased re-entry guidelines.
Those guidelines specifically call for a downward trajectory of cases in a 14-day window as the primary criteria for phased re-opening. But with so much variation in new cases and fatality counts from day-to-day, it can be challenging to understand what the “trajectory” is over a 2 week period.
Establishing the 2 week period growth rate is the best model we’ve seen for identifying if a particular geographic region, down to the county level, is achieving the federal guidelines for “Downward trajectory”. If the growth rate is negative, that means that in the most recent two week period, the average new cases or average fatalities were lower than in the previous two week. Likewise, if the % growth is positive, that means the most recent two weeks demonstrated an upward trajectory of new cases or fatalities.
Being able to visualize how these two numbers are changing over time, as well as being able to identify which states and counties are or aren’t consistently trending down, has helped us to make more informed decisions about how and when to best re-open our physical offices.We’re also able to help our distributed team members around the country to understand what’s happening in their parts of the country, so they can make more informed decisions about their own health and well-being.
Turning data into action
While we aren’t advocating reopening, we want leaders to see the facts and have the tools to make decisions that are the best for their employees. Our top priority in making decisions is to limit the risk of exposure to COVID-19 for all our employees. Using the COVID-19 Data Analyzer, we put together a toolkit with gating criteria and a phased re-entry plan to guide our decision-making and communication to our team.
Our gating criteria for reentry plans
These are the gating criteria we’ve applied to our company’s strategy, and you may find them useful for your business too. These are the criteria that, to us, indicate a trend towards being able to open up for on-site operations.
Within the most recent 14 day period, these criteria should be met:
- New Daily Coronavirus cases are declining
- AND Positive Tests as a % of total tests
- AND Testing Volume is flat or increasing
- AND Flu-like illnesses reported is declining
- AND Covid-like symptomatic cases is declining
- AND Hospitals can treat all patients without crisis care
- AND At-risk Healthcare workers have robust testing place
- AND We have reliable access to materials necessary for incremental disinfecting and cleaning workstations and points of transmission (e.g. – can we get hand sanitizer and disinfecting wipes in enough volume to support having people in the office)
- AND We can account for the additional operating costs associated with re-opening