Vaccination Volunteers

Sample MySQL database table containing information from Google form, ordered by state for assignment

Inspiration

We noticed that old folks we knew were having trouble figuring out if they were eligible for the COVID-19 vaccine, and even then had to navigate a complicated registration process online. Furthermore, most resources for determining eligibility and signing up are only in English, a further challenge to people who can't speak or read English. Therefore, we decided to create a streamlined process to address all of these challenges.

What it does

Our solution has two main components. The first is a robust reminder apparatus which pulls data on each state's vaccine eligibility criteria and generates a robocall to be sent out to everyone on our calling list in that state.

The second aspect of our solution is a website and training program where volunteers can sign up to help old folks navigate the vaccination process. After signing up, the volunteers are assigned several states they must learn the registration process of before receiving calls.

How we built it

Robocalls

The robocalls were generated by a python script that scraped data from the New York Times’ page with current vaccination requirements before creating an automated message for each state in English and Spanish. We used dialmycalls.com to send out robocalls because they offered a free trial. The service successfully sent batches of state-specific robocalls to phone numbers in our database of the same state.

Website

The website was made using Bootstrap Studio. The website is three pages: “home”, “about us”, and the sample training page “module.” There is also a sign up feature on every page that leads to a GoogleForms. It also includes the motivation for VaccinationVolunteers.

Phone Number Database

The results from the Google Forms CSV populated with the information from the volunteers’ responses were then loaded into a MySQL database table. Each response on the Google Form corresponds to a field in the MySQL database table. The table was then sorted by states to provide information on how many volunteers are currently available per state and it also corresponds to the specific training module that the volunteers receive based on state.

Volunteer Form & Assignment

After the volunteer fills out the google form, they are automatically sent an email with a link to the training module and an email stating which state they are assigned to. The state assignment was created by python. The script would take the .csv file from the google form and create an array that appended all the states where the volunteers stated they currently live. Ideally, we would want to assign someone to their own state. However, we also wanted to make sure that every state had enough volunteers. To ensure this, we assigned several brackets that consisted of states within their own region (Northeast, TriState, EastNorthCentral, etc). The program would calculate the state in each bracket with the least amount of volunteers and assign the volunteer to that state. After the assigned state was calculated, the code implemented yagmail to send an email automatically to the volunteer with their state.

What's next for Vaccination Volunteers

Next, we hope to work with local governments to bolster their COVID-19 vaccination effort. We hope to facilitate this by accessing the large databases of phone numbers that local governments use to distribute similar information during emergencies. Simultaneously, we will develop training modules for every state’s registration process and grow our volunteer base to fit every language. We also have created a Firebase google sign in and we hope to use this so that volunteers can sign in to their account and change their time slot if they wish. Finally, we hope to set up a routed 1-800 helpline for people to call if they want to speak with a volunteer. While this feature was cost-prohibitive during the hackathon, it is critical for effective service.