Using Email Notifications to Fix Bad Numbers
Educators know the impact a phone call can have on the trajectory of a student. Studies have also documented the positive effects school to family communication can have on improving attendance.1 At schools today, one impediment to conducting systematic phone outreach is that contact information can be out of date. Phone numbers change, and oftentimes these changes are not recorded in a school's student information system. With the Google Form from the past two posts Student Contact Log: Rationale and How-To Guide (Part I) and Student Contact Log: How To Guide (Part II) and the Add-on FormMule we can send an automatic email to a designated staff member anytime a bad number is reported. The purpose of sending this kind of alert is to systematically purge bad contact information from the school information system and replace it with accurate numbers, which creates a virtuous cycle. The more bad contacts are found and corrected, the easier it becomes to outreach to families.
Sending Emails from a Form
Below is the spreadsheet that contains the responses from the aforementioned Google Form.
Make sure you have installed the free New Visions Add-on "FormMule" written by Andrew Stillman. To install FormMule either click on the link or in any Google Spreadsheet go to the Add-ons menu, select "Get add-ons..." and then search form "FormMule." Once installed open FormMule by going to the Add-ons menu, selecting "FormMule - Email Merge Utility" and then selecting "Launch."
The first configuration step asks the user to select the sheet that contains email addresses and source data. In this case the sheet name is "Form Responses 1."
This setup allows users to set triggers. Triggers are events that tell FormMule to perform a particular action. There are two types of triggers in this case. A form trigger is the event that occurs whenever a user submits the form. We need to use the form trigger event because we want FormMule to send an email whenever a user submits a form response that flags a bad number. By turning on form triggers, formMule will run every time a new form submission arrives and evaluate whether or not to take further action. In this way, emails will only send when a user flags a phone number as a bad contact.
Time triggers are time based events that tell FormMule to run. For example, one can set FormMule to run every hour. The time trigger is a useful safety net, just in case the form trigger does not fire. In other words, as long as a time trigger is set, if the form trigger malfunctions, the most formMule will wait before re-running is one hour.
In this example I have selected the following options:
- The sheet containing the email addresses and merge source data is "Form Responses 1"
- I'm enabling form triggers
- I'm also enabling time triggers, which I have set to run every hour. Please note, this setting does not mean a new email is sent every hour, rather FormMule evaluates whether or not to send emails each hour. Emails that have already been sent are not re-sent.
Next, I create one email template with the name "Bad Number" to signify that this is the email template I want FormMule to use if a user flags a number as bad. I then set a condition for when I want FormMule to send an email notification, which is whenever the "Report a bad number" checkbox question in my form comes in as a "Yes". These conditions can be finicky so make sure to double check spelling, capitalization and stray white spaces.
Next, use the "Build / preview templates" screen to create an email template that will be sent whenever the condition is met. For the "To:" field of the email, I want to designate the one person in my school responsible for finding correct contact information (e.g. parent_coordinator@myAwesomeSchool.org) Merge tags are the column headers from the source sheet. By clicking a merge tag button, FormMule will load in that merge tag along with a label to wherever the cursor is within this screen. Merge tags in FormMule are conveniently denoted with double carrots, just like in an AutoCrat template. I recommend keeping the template short and sweet by including only the essential information. Another feature worth highlighting is that FormMule accepts HTML in the email body portion of the template builder. With HTML you can do things like bold text, create lists, add tables, and include hyperlinks.
Clicking "Preview this" will replace the merge tags with the data from the selected sheet. This is a great way to proofread the emails before going live.
Clicking "Preview and send all" will show another preview of the emails about to be sent along with a count of the total number of emails FormMule will send. The remaining daily send quota will depend on the type of account being used. Commercial gmail accounts have 100 emails per day whereas free Google Apps for Education accounts have 1500 emails per day.
Clicking "Send now" will fire off the email and the parent coordinator will receive the below email within a few moments. Clicking "Cancel" will close the setup screen, but the settings will be preserved, which means that the next time a form is submitted with a bad contact flag or in the next 60 minutes, all unsent emails will go out.
This concludes the three part series on contact logging. We have looked at
- the purpose and systems design considerations for contact logging
- methods for sharing contact information
- pre-filling forms to make logging easier and more accurate
- automating notifications to a designated staff member in the event a bad number is flagged by another staff member
Schools have used the above system to monitor the types of outreach staff are performing on a regular basis, the students receiving the most outreach, and the frequency with which each staff member performs outreach.
Many of these systems design principles are transferrable to other school-based use cases. The true brilliance of Andrew Stillman's CloudLab tools are their transferability and modularity. Stay tuned for additional build walkthroughs and policy pieces.