2021 Business Impact Award Nomination: Barnard College: Support College with Covid-19 Test Submissions and Results

[Karen]

Describe your business and technical use case(s) and the challenges the business had.

General business need:

To support the College in its need to immediately react based on COVID test submissions and results, which were provided by students, faculty and staff. Actions required included email generation and notification & data collection for reports and archival.

Use cases:

1. Use case 1: Positivity Report
   Integration to notify the clinical advisor and contact tracers when a new CoVerified user has entered a positive symptom report. It will ultimately aid in the intake of potential COVID-19 patients into our robust Testing and Contact Tracing programs, further improving the health and safety of the Barnard community.
   Barnard College’s Pandemic Response Team (PRT) needs to retrieve Covid-19 data of persons who resulted with positive tests from the CoVerified on an hourly basis. This data is accessed, processed, and stored in an Excel spreadsheet for analysis and reporting by the PRT. If users with positive results are found, their data is emailed to the specified PRT members, and to the individuals identified. (“Push”-like notifications being sent to the clinical advisor and contact tracers each time a CoVerified user enters a positive symptom report.)

2. Use case 2: Testing Non-Compliance
   Integration to notify the clinical advisor, contact tracers, and students when relevant data related to non-compliance is retrieved from the CoVerified web service as described in business requirements below:

   • Check for “new testing non-compliance instances” once per day, by checking whose status in CoVerified has changed to include “restricted” and “test_missing” parameters.
   • Send a predefined html email to each email address in the list, notifying each user that they have gone into what is called “Testing Non-Compliance”.
   • Add each instance of noncompliance to an archive database, which notes the date and demographic information of each “non-compliance” instance.
   • Archive a daily snapshot of the CoVerified database for any future reporting or troubleshooting purposes.
   • Count the number of non-compliance instances for each unique email address to track users who have gone into non-compliance multiple times.

3. Use case 3: New York City Covid-19 Data by ZIP
   Enable the retrieval of data, processing, and report generation. These reports are utilized by the BC’s Pandemic Response Team in ongoing efforts to help the wider Barnard community to successfully deal with the Convid-19 pandemic.
   Barnard College’s Pandemic Response Team (PRT) needs to retrieve publicly available Covid-19 data from the NYC Department of Health github repository on a daily basis. This data is accessed, processed, and stored in an Excel spreadsheet for analysis and reporting by the PRT.

4. Use case 4: New York State Regional Covid-19 Data
   Barnard College’s Pandemic Response Team (PRT) needs to retrieve publicly available Covid-19 data from the NY State Department of Health dataset on a daily basis. This data is accessed, processed, and stored in an Excel spreadsheet for analysis by the PRT.

   • Retrieve publicly available Covid-19 data from the NYS Department of Health public dataset by sending specific queries to the SODA API such as for all NY State Counties and for all NYC Counties: Queens, New York, Kings, Bronx, Richmond.

Challenges:

• initial method required a manual execution of Python scripts every hour
• undocumented vendor-provided PHP application to retrieve data
• distribution to an external provider email
• continual changes to user requirement involved changing Python source code and availability of Python development skills

Describe your strategy and execution to solve the business challenge. Include details on how SnapLogic played a role in the strategy and execution, including Snaps and other SnapLogic products/features.

Strategic goals:

1. To completely eliminate reliance on Python scripts used by the Pandemic Response Team (PRT) because:
   • The PRT relied on poorly written Python code
   • The scripts relied on these dependencies:
     A. Vendor provided PHP application to retrieve Barnard’s Covid-19 data
     B. External Emailing system to send alerts
2. To replace the manual Jupiter Notebooks Python execution with SL’s robust and versatile task scheduling
3. To build integration solutions adaptable to changing user requirements

Execution:

1. Thoroughly analyze the logic in the python scripts
2. Ensure all functionality can be implemented using SnapLogic pipeline(s)
3. Eliminate external dependencies by:
   • Retrieving Barnard’s Covid-19 data from our vendor CoVerified using RESTful Web Services
     (Vendor’s php application no longer needed.)
   • Sending email alerts using “Email Sender” snap and on-premises email server.
     (The external email service no longer needed.)

Who was and how were they involved in building out the solution? (Please include the # of FTEs, any partners or SnapLogic professional services who were involved on the implementation)

Barnard Development: Two SnapLogic developers, Marrah Arenas and Agron Bauta SnapLogic professional services: Kory Knickerbocker

What were the business results after executing the strategy?

Manual work to generate hourly data collection was completely eliminated, able to implement modifications to data requirements quickly, notifications distributed to contract tracers and the College’s Pandemic Response Team which allowed for immediate action in order to minimize exposure of COVID across on-campus constituents

What was the ROI you gained from executing the strategy? Include any additional measurable metrics (ie. productivity improvement, speed improvement, % reduced manual inefficiencies, etc.) *

Allowed the Pandemic Response Team more time* to primarily deal with issues in their area of expertise, to address Covid-19 exposure and protection across campus constituents and ensure the health of Barnard community.

Barnard’s Covid-19 data can be accessed, processed, and stored for future analysis and reporting by the PRT.

Integration workflow can send email alerts to the specified PRT members, such as clinical advisors and contact tracers and to the individuals affected

Anything else you would like to add?

The development of these integrations was a learning process. Plan to develop patterns so future development can take advantage of common logic.