PPE Email Automation Project - Atlanta Beats COVID @ Accenture
Role | Front-End Developer
Duration | 5 months
Project Contributions | Email Automation Google Form
Tools Used | Google App Script (Javascript), Google Sheets, Google Forms
Background
Atlanta Beats COVID (ABC) is a non-profit composed of various makerspaces around Atlanta, including: The Maker Station, Decatur Makers, Roswell Firelabs, and Geekspace Gwinnett. These folks stepped up to help produce and distribute Personal Protective Equipment (PPE) locally during a nationwide shortage of PPE for healthcare workers, school officials, families, and others in need.
ABC 123’s:
70,000+ PPE delivered
235 organizations helped
250+ volunteers
25+ sponsors
Understanding Our Users
As this initiative kept progressing, I had more and more questions and assumptions. For example, understanding how long it takes for a user's request, from start (filling out the Google form) to finish (PPE is in the requestor's hands), to be completed was critical to developing the logic behind the emails that were automatically sent out. As long as a user requested 30 or less faceshields, orders could be fulfilled next day. However, for a request of around 10 reusable gowns or earsavers, it could take a couple of weeks.
This also depended on the amount of donations ABC received, both monetary and phsyically (3D-printing supplies) or even ready-to-wear PPE donations, but many times the supply was difficult to create metrics for. There was also the edge case of a user request for PPE, but then once notified that the PPE is ready for pickup, the requestor never picked up the PPE, which creates a surplus in the supply chain. While this is nice for the overall supply of ABC's PPE, it wasn't a good metric to rely on.
We soon discovered that this was a supply and demand problem - not necessarily a user experience problem we were trying to solve.
The Solution
Atlanta Beats COVID was already using the Google Suite to help with PPE inventory tracking as well as a means of gathering requests from healthcare workers, school officials, families, and other people in need of PPE. I used the results from the Google Form to create a customized script in Google App Script, which is Google's version of Javascript, to validate when PPE was ready for pickup.
There was a lot of tricky guess and check work with different edge cases, but once the Atlanta Beats COVID team finalized the Google Form, we were able to add validations and certain logic around what types of PPE were offered, the quantity a specific user could request when filling out the form, and the expected delivery date. My favorite part about this whole experience is that I was able to see first-hand what all the different pain points an ABC volunteer went through to fulfill various requests of people in need of PPE.
After a couple of months, the script was ready to go live in ABC's "production environment" - aka, their website. Once an order was fulfilled, an ABC volunteer would be able to visit the script I wrote via the Google Form results, validate that the order was ready for pickup, then click one button that automatically sent a requestor an email that their PPE was ready for pickup.
Takeaways
Overnight, our small group of hobby makers learned PPE manufacturing, production, and supply chain. Understanding the importance of inventory tracking and how our different "users" needed different types of PPE was critical to creating an email automation process that was both efficient and didn't require a lot of iteration during a small timeframe.
Atlanta Beats COVID fulfilled their mission and have since closed their doors.
For a demo, reach out to Maddy.