No Leftovers Left Behind
By Team 15: Cassidy Sullivan, Michael Nguyen, Natalie Baher, Nabhya Harjai, & Teigan Owen
The Leftovers Foundation is a registered non-profit that focuses on reducing food waste by redirecting edible food to service agencies and charitable organizations. Although they started in Calgary, they have now expanded to Edmonton and Winnipeg. Their food donations mainly come from businesses in their local communities and they rely on volunteers for the majority of donation pick-ups.
The COVID-19 pandemic exacerbated three issues that Leftovers has been facing:
- Leftovers needs more efficient ways of managing their inventory. At the beginning of the pandemic when much of the restaurant and education sectors shut down. Leftovers was inundated with donations of food originally meant to be used in culinary schools and eateries. https://calgary.ctvnews.ca/calgary-not-for-profit-going-bananas-trying-to-off-load-pallets-of-fruit-1.5329093
- Leftovers needs to attract more volunteers and make managing them more efficient. As outlined by Mitchell and Clark (2019), the challenge of constantly recruiting new volunteers can be directly addressed by further bolstering volunteer engagement. Leftovers is growing at a very rapid pace but will need more volunteers to continue this trajectory all the while keeping volunteer coordination costs at a minimum to improve productivity.
- Leftovers needs to attract more donations and highlight high-demand items. Bekkers and Wiepking (2010) outline that “awareness of need” is one of the most important forces that drive charitable giving. As a result, increasing donor awareness of specific high-demand items can serve as a call to action to increase the chances of food-based donations.
Leftovers being a non-profit organization we challenged ourselves to find innovative, low-maintenance, and low-cost or free solutions to these problems. Although we created our own mock-up website to demonstrate these technological solutions; they can be very easily implemented into Leftover’s existing website.
Executive Summary
We created our website using Wix, then used created a userform similar to the one currently on Leftovers website that collects information from food donors about what they are donating and when they want a volunteer to pick up the food. We then imported that data into Google Sheets using Wix Automations and Zapier. From there, the data goes through Google Data Studio to create an updated-in-real-time public visual display that we embedded on the website. We also embedded a copy of the Google Sheet on the website behind a password-protected wall for use by volunteers to see when and where their pick-ups are. We also tied in the donor form to an email system that notifies Leftover’s staff and volunteers upon new donor sign-ups, and sends thank you and bi-weekly update emails to donors. Finally, we also set up a Zap to tweet out thank yous to new donors from our Twitter bot.
Here is a link to our presentation video and website demo for a look at how the tools work together:
Tool 1: Website Creation with Wix
The first step in the process was to create a website that mimics the functionality of the current Leftover’s website, but where we could also implement our technological solutions. There are many options on the market such as Wix, Squarespace, Weebly, and Site123. All four offer free options, but the premium plans for each offer increased design and utility features. All four are also designed to be easy to use for novice website creators.
Our team chose Wix because of our previous experience using the platform, and the ease of automation integration with Zapier and Wix’s built-in Automations. One of our team members had a premium subscription and domain from a different project, and we bought an additional upgrade to the Ascend business plan to access increased Customer Relationship Management features. These features would be free to implement on Leftover’s current website, however, as our purchase only removed the limit on the number of questions we can have on the donation form and increased the number of Wix automations we can use.
The most important pages on our website are the “Donate” and “Volunteer” pages which contain the userforms and charts that are integrated into our automations.
Please scan the QR code below or click on the link if you’d like to look through our website:
To maintain a consistent and connected user experience we opted to use Wix Forms to create the donation userforms directly on the website. This also mimics the experience of the current Leftovers website. You could also use third-party external services such as Google Forms or Typeform; they both work similarly and can also integrate with Zapier in the second step.
Tool 2: Exporting responses from the Donation userform to Google Sheets using Zapier
One of the benefits of using Wix is that Wix Automations integrates with Zapier very easily. We created a Wix Automation that connects to Zapier upon receiving a completed donation form.
We created a Google Sheet with columns that mirrored the questions in the form. We then used Zapier to link the form submission from Wix to our Google Sheet and assigned the corresponding Input Field from Wix to the correct field on the Google Sheet.
We embedded the Google Sheet with the userform data onto the website so volunteers could easily and quickly access the donation pick-up information. This was also beneficial because the sheet updated in real-time and would help to connect volunteers directly with the organization instead of reducing efficiency by having to go through a middleman coordinator.
To prevent unauthorized access and tampering with the information, we implemented a password-protected portal that volunteers will have access to; for the purposes of the test website, the password is “volunteer”.
For an extra layer of added security, we have protected specific areas of the sheets from editing by volunteers. Volunteers are allowed to go in and add their name to pick-ups that they are accepting, however, they will not be able to edit any other part of the sheet.
Tool 3: Data aggregation on Google Sheets
The third step was to aggregate the data into useful metrics so we could further use that data on Google Data Studio. We first needed to aggregate the entries of the different types of food and sum their weights together. For example, if two different companies were to donate Cheese, we would want the two entries to sum together to give us the total weight of cheese donations we received.
To achieve this we used a sum-if function where:
=SUMIF([Range you are checking for the Search Term], [Search Term], [Sum Range])
So in this example, the Search Term is “Muffins” highlighted above in purple. The SUMIF function will then search column G, highlighted in orange below, for a corresponding match to the Search Term. If it finds any matches it will sum the corresponding value in column H, highlighted in blue.
Once we have an aggregated list of food types and their summed weights, we can then set up a function to check how much food we have against a preset scale of what constitutes low supply to high supply.
To do this we used a vertical-lookup function where:
=VLOOKUP([Search Term],[Lookup Range],[Which column of Lookup Range should be reported on match],[Exact match True/False])
So for our example below, we used the “Sum weight per week” on the Weekly Food Weight sheet that we calculated earlier using the SUMIF function as the Search Term. We then set the Lookup Range to the table titled “Supply Level (lbs)” highlighted in purple. The VLOOKUP will respond with “Low” if the sum weight per week is between 0 and 49 pounds, “Adequate” if the weight is between 50 and 79 pounds, and “Excess” if it is over 80 pounds. This can be changed to any number that suits the needs of the organization.
We then created a second VLOOKUP to map a “Need Level” to the supply level we calculated in the previous step; this is functionally just the opposite of the Supply Level. Although we could have just mapped a Need Level without the first Supply Level calculation; this makes it a lot easier for organization staff to set their own weight thresholds for supplies as they can just edit the value directly in the “Supply Level” table.
We also used conditional formatting to colour code the Need column:
Format cells if [Qualifier] [Input]
This provides a quick visual way of identifying which foods are in demand and which we have enough supply of.
Tool 4: Creating visuals in Google Data Studio and embedding them on the website
It was very convenient to use Google Data Studio to import the data from the Google Sheet. First, you’ll want to add data to the chart and set up a table. Click “Add data” in the toolbar at the top and connect it with your data source; for us that will be the “Leftover Food Donations — Calgary” from Google Sheets.
After linking a data source, you will want to add a chart; you can just go ahead and add a graph at this point but we find that adding a table will make it easier for you to understand what you need to do to create a chart.
When you click on a chart such as a table or a graph a sidebar on the right will open up with some input data options. If you have experience using Tableau or Pivot tables in Excel then Google Data Studio will be very easy to navigate. If not, fret not, as for your tables and charts you will be wanting to add data into the “Dimensions” header — this is equivalent to the X and Y axis in Excel. Google Data Studio will automatically figure on what axis to put what type of data for you. You can use the “Metric” header for data if you want to compare across two types of data such as to calculate a percent difference, etc. We put a“Record Count” in there which just tells us how times we have that type of food in our donation sheet.
In the data toolbar you also have a header labeled “Sort” which is useful to organize or present data in a specific way.
For example, for the graph of sum weight per week and the type of food, we used the Sort function to organize the food from most weight to least weight.
We also used the “Sort” on the Need chart highlighted in green and red above the graph. We sorted it so that “High” need items would always be at the top (Ascending).
One last useful tip for Google Data Studios is using the “Filter” function, it is found near the bottom of the Data toolbar on the right. Sometimes you get “null” rows or values in your sheets; you can easily exclude these results using the filter in Google Data Studio.
Filter [Action] [Dimension] [Qualifier]
The last step is to prepare your visual for embedding; you can do this by going up to the top toolbar on the right and beside the “Share” button there is a little dropdown arrow. If you click that you will see an option to “Embed report”. Clicking on that will give you two options to embed; a URL or a snippet of HTML code that you can insert into your site. You’ll also have to make sure the “Enable embedding” checkbox is checked off.
Tool 5: Creating a Twitter Bot with Zapier to thank donors
For this part I will assume that you know how to sign-up for a Twitter and/or already have a Twitter account, however, if you need to sign-up for one you can click the link above and sign-up using your email address or phone number.
The first step is to create a Zap that will link the data from the donations Google Sheet to Twitter so that we can send out a message like this:
On Zapier, we will link information from the “Leftovers Food Donations — Calgary” sheet to our Twitter account. We set the trigger event to “New Spreadsheet Row” as we want a tweet to go out every time we get a new donation.
We then link the action to create a Tweet in Twitter. We format the tweet how we want and make sure to include an input from the Google Sheet with the donor’s business name in the tweet.
The final step is to make sure the Twitter bot is working by completing the test steps in Zapier but also just going in a submitting a few forms and seeing if the Tweets come out correctly.
Tool 6: Creating automated emails using Zapier
We have a few types of automated emails on the website; we have ones that thank donors, others that thank email subscribers, and one that sends a message out to current donors every two weeks with information on what types of food are currently in demand.
We will demonstrate the creation of the latter type of email as the steps to create the thank you emails are similar.
The first step is linking to the donations Google Sheet like with the Twitter Bot, however, this time we want the trigger to be a new or updated spreadsheet row. We also set the trigger column to column C, “Need”, as we only want it to trigger if there is a High need present.
To ensure that it only triggers if there is a High need, we used the Filter action by Zapier to check the Need column to see if it contains the text “High”. If it contains a cell that says “High” then it will proceed through the Zap, if not the Zap will stop at this point until the next time there is a new or updated spreadsheet row and it will try again. The reason we do this is that we do not want to send out emails if we do not have a high need.
We also used the Delay action in Zapier to prevent too many emails from being sent out. As configured the Zap will function every time there is an update to the Google Sheet; basically every time a donation userform is submitted on the website. We set the delay to every fourteen days; the Zap will trigger and proceed through the filter every time there is an update, however, it will only allow one signal through the Delay function every fourteen days.
Finally, we connected the trigger to send an email to the donor emails on the donations Google Sheet. We also included data inputs from the Google Sheet on the types of food with a high need level.
Conclusion
The implementation of these tools provided an inexpensive method to streamline various processes for the LeftOvers Foundation. This recommendation can be scaled up and improved to better manage the needs of the organization.
References
Bekkers, R., & Wiepking, P. (2010). A literature review of empirical studies of philanthropy. Nonprofit and Voluntary Sector Quarterly, 40(5), 924–973. https://doi.org/10.1177/0899764010380927
Google Data Studio — Unlock the power of your data with interactive dashboards and beautiful reports that inspire smarter business decisions.. (2021). Retrieved from https://datastudio.google.com/overview
Google Sheets — Create, edit, and collaborate wherever you are. For free. (2021). Retrieved from https://www.google.com/sheets/about/
Mitchell, S., & Clark, M. (2020). Rethinking non-profit brands through a volunteer lens: Time for B2V. Journal of Marketing Management, 1–24. https://doi.org/10.1080/0267257x.2020.1818804
Twitter— What’s happening and what people are talking about right now. (2021). Retrieved from https://twitter.com/
Wix — Create a Website You’re Proud Of. (2021). Retrieved from https://www.wix.com/
Zapier—Connect your apps and automate workflows. (2021). Retrieved from https://zapier.com/
