Within my church organization, I’ve been asked to help organize and run activities for the youth in our area. The biggest activity is what we call “stake youth conference”, which is basically 3 days with 250 youth, age 14-18. It’s not a small task.
As we approached this year’s youth conference, one of our big challenges is in managing the information for all this. We need information for planning, ordering, assignments, medical, legal, and other needs. Having good information is very helpful, and missing or incorrect information really slows things down. In the last few years, here is how our Stake has managed information for youth conference. We are far from perfect, but perhaps there is something here that could help you. And maybe you can leave a comment that would be helpful for us.
Part of our approach here is to use functionally-effective but cost-effective tools. For much of this, we used products from Google. Part of what I like about the Google Workspace suite (Google Sheets, Docs, Drive, etc), is that each document is hosted online and shared in real-time, instead of needing to email around attachments and having to synchronize edits. The other nice thing about Google Workspace suite is that it versions all the changes, so if somebody deletes a paragraph or cell by accident, it can be recovered via Version History.
Spreadsheets as Databases
Yes, we are using spreadsheets as a database. For the level of info we are tracking and the people involved, this works fine. A couple of the things I was careful about was making sure that the Readers was limited only to the need-to-know, and the Editors was limited only with the need-to-change. For example, the centralized attendance sheet (automatically fed by the attendance form) was editable only by me, and I was just correcting errors.
This took the form of a Google Sheet. We called it the “master sheet”, as it either held data or pointed to where data was held.
We created a spreadsheet with the following tabs:
• Role Assignments for each member of the leadership committee
• Schedule for the 3 days of conferences
• Registration dashboard
• Expenses (planned and actual)
• Volunteer needs (role and quantity)
• Ward focal points (name, email, phone)
• Class topics / teachers / locations
• Service projects: locations, size, liaison, requirements, waivers
• Meals (time, menu, cost)
• Equipment to bring / set up for each venue (shade tents, watercoolers, etc)
• Packing list for youth (sunscreen, water bottles, etc)
We created a website with Google Sites. On this website was links to the registration forms, a high-level overview of the activities (no place names or times listed), and the email address of one of the Stake leaders to answer questions. This was all on one page. We also bought a helpful domain name from GoDaddy that would forward to the Google Site so we could give an easy-to-remember pointer to the youth and parents via the ward leaders. Because we have a Spanish branch in our Stake, we used Google Translate to have a 2nd page that was a Spanish translation of the main page, specifically for the parents in that branch (all the youth are English-proficient). We were being careful not to post any personal youth information or specific times+locations on the website, for privacy reasons and to not enable any lurkers to just show up.
Of course we need to figure out who is planning to attend, and what their characteristics are. The way we chose to do this was with a Google Form. Because the data entered in the form (responses) was pushed into a spreadsheet visible only to the stake leaders (write-only, not readable), we could put the link to the form on our website for the parents to find easily. We did verify that all registrants were actual members.
For the data that we need, we worked to consolidate all of it to a single form. This form was intended to be filled out by parents.
In previous years, we had a separate “Honor Code” form we asked both the youth and their parents to fill out. This year we consolidated down the Honor Code points to a core set, primarily pointing to the FSY booklet, and asked the parents to check the boxes.
Regarding late registration, we didn’t turn anyone away. And yes, there was a good-sized handful of youth that were registering late, even up until the day before conference started. Our intention was that we “want to make room for everyone who wants to attend”, even if it means more work for the leaders. Some were move-ins, some were guests, and some were regular members who simply forgot to register before the publicized deadline. Even though we had a publicized deadline of 6 weeks before the conference, about 1/3 of the final attendee count missed the deadline. The planned registration period ran for 4 weeks.
Early on our Stake decided to open up attendance to friends of youth. There wasn’t a pre-set limit because we didn’t expect a lot, and we would cover the costs of the guests just like they were a member. We felt that the missionary value was worth it. And we encouraged each ward to have at least one non-member friend to attend. So our registration form had a spot for guests to register. After we received a registration from a guest, we would go back to the ward to find out who the member-friend is, so we could make an effort to pair them together during assigned activities. In 2022, we ended up with 10 non-member guests, and want to see more in the future.
Ward Focal Points & Ward Reports
As we collected registration responses automatically into a Google Sheet from the Google Form, we wanted to share the registrants with the ward leaders so they could (a) see who was missing, and (b) that registrants were eligible (age, ward membership, etc). To help do that, we created a Google Sheet for each ward. But this sheet was not editable by the ward, instead it was a single query formula into the main registration responses that showed only those from their ward, and only selected fields (name, age, etc) that were pertinent to registration. Because it was a query and not a copy of data, this means it was live, so as soon as someone new registered, their ward report spreadsheet would get automatically updated. This saved a lot of emails regarding “can you send me the latest list”.
So then we asked each ward for a focal point to review this ward report of their registrants, and specifically to help chase down the youth/parents that weren’t registered yet but that were eligible and likely to attend. Of course there were some folks on family vacations, summer jobs, etc., but we wanted to make sure every eligible youth had the opportunity. Since the ward leaders know their youth the best, they did well in that role. We created a sheet for each ward that would query just their ward’s youth from the master response sheet. It worked well.
We asked the ward leaders to pick a focal point. The focal point should know the youth in the ward, and have good follow-up skills.
Church Permission Form
Since youth conference is a multi-day event that has more activities (and thus risks) than a usual weekly youth night, we use the church headquarter’s full-page Medical and Permission form, and require one to be filled out for each attending youth.
An artist friend has access to the Adobe Suite, so using the PDF editor there we were able to modify the headquarter’s PDF form to fill in the top section with the activity details, and then save that as a new template. This avoids requiring parents to fill in that common part.
In years past, we’ve shared this modified PDF with parents, asked them to print at home, and then fill it in with a pen and give to their ward leaders, that I can then pick up a hardcopy for each youth. It has been difficult to execute this. At my employer they use a tool named HelloSign for managing online signatures of documents. Since they trust it, and it comes from the same parent company as dropbox.com, we decided to use it. Here’s how it works:
Using the modified PDF mentioned above, it gets loaded into their tool as a template using their web site tool. Then for each field that I want the parent to fill out, using their tool I graphically draw a box around the area of the PDF that would otherwise be written on with a pen. Do this for all the fields, and give each field a name. Then for the signature line, draw a special box around that with their tool. Save this template. Now, for each registrant that I receive a registration form for, I go back into their tool, select the template, type in the name and email address of the parent (which we ask for in our Google form), and then hit the send button. The system will sent a link to the parent’s inbox. When the parent clicks on that link, they will see the template with all the fields on the PDF that they can type in to, to provide the medical information. And they can also draw on the screen of their phone (or similar for a PC) to provide their signature. That all gets captured in a document that gets sent to me. If they don’t do this submission, the parent will get a couple email nag notes. I can also go into the tool and manually send the parent a nag note whenever I want. And the tool shows which parents haven’t responded yet.
This gets the church permission form into a totally online format. No hardcopy collection needed, but the softcopy looks like the hardcopy. I ended up printing each softcopy at home, and putting them into a binder that I could give to our nurse, so that we’d know the medical details in case anyone got hurt or sick.
HelloSign has a 30-day free trial so you can see if it works for you. We used the monthly plan at $20/month, and we kept it active from the opening of registration until the end of the conference.
From what I can tell, there may be an option to automatically pre-fill the document-for-signing to the parent with personalized data, though it may take some extra software and a higher-priced subscription tier, and maybe writing some custom code. If that was the case, then it may be possible for the attendance form to ask for those questions in the Google Form, and then the modified PDF could be pre-filled with all the medical and personal data, and needs to be only e-signed by the parent. That would be the perfect solution, but for now we have the decent solution. This year I ended up reading through all the HelloSign submissions and manually copying-and-pasting the allergies + medical needs back into our centralized attendance sheet. The personalization described above would get us out from doing that as well as making it easier for the parents. Something to investigate for next time.
A couple of our third-party partners required their own waivers for their venues/activities. One was a ropes course, and the other was for a service project. They had their own system for collecting these waivers. Luckily, they could be filled out online by parents, AND these partners were able to periodically provide the list of all the waivers they had received from our group. This made it pretty easy to determine which waivers were missing, so the ward focal points could go a chase those down.
There was a lot of chasing down. The parents did pretty good on filling out the attendance form, but each additional form we asked for had lower and lower submission rates. So we learned the hard way to minimize the number of forms.
In the centralized attendance response Google Sheet, we added columns to put a simple “Y” for each waiver that the partner confirmed as received. Anything without a “Y” needed to be chased down by the ward focal points.
There were two groups of signups we asked for. One was for food contributions of snack items (i.e., diced fresh fruit), and the other was for ward leaders to attend shifts as helpers/chaperones. We used a SignupGenius for both. Although the ads on SignupGenius can be annoying, we stayed with their free tier of service.
We did not put the links to the SignupGenius on the website, since it listed the times and locations of the conference. These links to the signups were distributed via email to parents and ward leaders.
We have a tradition of getting a t-shirt for each youth, that has a custom print. For the design, we use the church’s base design, and then add a small customization, such as our stake name and year. Typically a youth is capable of designing that. We’ve established a relationship with a local screen printer who can order the blank shirts (large lead time needed because of supply chain issues) and paint the design on the fabric.
On our attendance form, we ask for the desired t-shirt size. We order a few extra for the Stake Presidency plus late registrants. We ended up paying about $10/shirt.
Special Food Needs
There may be youth with special food needs such as allergies (nuts was a common one) and preferences (vegetarian). I manually copied-and-pasted these non-blank values from the submitted medical forms into our centralized attendance sheet. Then I created another Google sheet that queried for non-empty values to give a count/description to our food helpers.
Given a Google Sheet that serves to collect responses from a Google Form, it is possible to manually append columns on the far right that will co-exist with the cells that are filled in by the form submissions. This is where I put these extra fields: church permission form flag, waiver flag, food needs, medical needs.
Similar to the special food needs, there was a surprising amount of youth with needs that were both physical (i.e., recently broken bones) and mental (i.e., panic attacks, autism). In reading through these, it became apparent that there are a few youth that would not respond as expected to discipline if needed. I also manually copied-and-pasted these from the submitted medical forms into our centralized attendance sheet.
I also printed all the Church Permission & Medical forms, sorted them by ward, and then sorted each ward by name, and put it in a binder. This we would take with us on activities, so if anyone got hurt, we would be able to quickly look up any allergies or issues.
Name Badges / Lanyards
Each year we’ve had a personalized badge and lanyard for each attendee. It made them feel official, it served as a visual proof of check-in, it included on the back generic schedule information, and the front had not only their name but also their personal assignments for classes and service project.
Avery is the company known for printing badges on the perforated card stock, which you can then tear along the lines and put into a badge holder. I like having the large 4×3 inch format instead of the smaller ID badge size 3×2 inch format. Avery uses 4-digit codes for each of their templates, I like 5392.
To support all that card stock, Avery has a pretty good app for doing a “mail merge” for printing out the personalized data on those cards. It is called “Avery Design & Print”, and from their home page can be accessed via the Templates menu. You’ll want to create an account, since it’s a web-based app and you’ll be able to save your work online using that account.
Basically I created a spreadsheet that had the personalized data fields: firstname, lastname, ward, class names and times, and any other info we wanted. Then you export that as a CSV file. Next, go into the Avery Design & Print web tool and start a new project. Do the “Import Data Mail Merge” to upload your CSV file, then you can add the data fields to a sample card. Then add any static text, graphics, and do any formatting and layout and graphics you want. It even supports text rotation and opacity, in addition to font types and sizes. You can also get a PDF preview of what they all look like, and send it to your printer. It’s a surprisingly good web tool, I recommend it.
The first year I bought the Avery card stock and tried to print this mail merge on my home laser printer, the first couple sheets came out OK, then the following ones had problems with the toner coming off the paper in flakes. What I ended up doing was skipping the Avery perforated card stock and just printing on regular plain white everyday paper. However, this loses the perforations for separating the pieces. To get around that, I found a PDF file from Avery that prints a thin line on the whole sheet that shows where the perforations would normally go. So I print all the mail merge badges once, then stick those papers back in the printer’s input paper tray to print again to get the lines on them. Then I borrowed the guillotine paper cutter from the ward library (the one with the big cutting arm on the edge of a hard slab) and simply cut along the lines. Tada!
To complete the package, I created another Avery Design & Print project using the same 5392 template, but instead of having a bunch of personalized data via the Mail Merge, I just use static data so each card looks exactly the same: this is where we put the schedule and other general information. Do the same double-print to get the lines printed for cutting, and insert this in the badge holder facing the opposite side out, so now the badge holder has a front with the personalized info, and a back with the general info.
Regarding the clear vinyl badge holders, I got these ones from Amazon (250 for $40). For the lanyards, it is possible to get ones custom printed, but I found these ones from Amazon in a generic color that worked fine and were much less expensive (50 for $17 = 250 for $85).
I did feel a bit bad for using Avery’s tools without buying anything from them.
So before the conference started, we needed to communicate out to the participants their assignments for the activities when they were split up. Instead of manually sending out 250 emails, since we had the information in spreadsheets, this is what a mail merge would be good for. But not just creating a personalized document that could be saved and printed, but actually emailed. After doing some hunting around I found that OpenOffice could do that. They have a help document that describes how to do it:
Creating Mail Merge Documents From Text/CSV or Spreadsheets. It’s not trivial to set up, but if you can get it working, it is a huge time saver. It creates the email body, and actually sends the email, all in one automated step. So sending to 250 recipients is as easy as sending to 2 recipients. It differs from a regular document-generation only mail-merge in that you also configured your outgoing SMTP server. There are instructions for doing that via Gmail. To the recipient it appears to come from your personal Gmail account.
The challenge here is that when sending communications to the youth, our experience is that the youth don’t look at their email inbox. Sending emails is supported, it’s easy, but most of the youth don’t see it. It’s not uncommon to get an email bounce from a youth saying that their inbox is full. Instead their focus is on social apps and texting. Last year we used Remind to try to send messages to the youth, because it is the app used by our county school system, so the youth are familiar with it and generally have it already installed. However, Remind requires the recipient to manually opt-in, and after repeated requests we got only about half of the youth to join our Remind group. And in the free tier of Remind, outbound messages are limited to 160 characters, and group size is limited to 150 members (we expect ~250 conference attendees). So this year we gave up on Remind, and I went looking for a group-texting system that doesn’t require opt-in.
What I found is Textedly. I tried the free trial, and although it was a bit expensive, it gave us the function I was looking for. The original goal was to both email and send SMS messages to parents and youth each day, describing what was going to happen the next day and what they needed to do to prepare.
In the registration form, we asked for the preferred email address and cell phone number for a parent, and the same for a youth (all the youth had an email address, and almost all had cell numbers). So that gave us our recipient list. With some queries and a little bit of manual effort I was able to get a list of all the email addresses for parents and youth in a single spreadsheet column, and I could copy-and-paste that into my Gmail client, and send a daily email address from my personal Gmail account. (Yes, apparently Gmail will let you send an email to 400+ recipients. The reason I didn’t use the church’s LCR tool for mass emailing is that it doesn’t let us filter out the non-attendees, especially the under-14 youth.) Some additional queries and manual work and I was able to build a separate spreadsheet of our SMS recipients (parents + youth) based on registration data. This sheet included their name, “parent” or “youth” designator, and cell phone number. I was able to upload this sheet as a CSV file to Textedly. Because I had a parent/youth designator on each person, I could select to send a message to all or to just youth or just parents. Sending an outbound message is called a “campaign” in Textedly, probably because most of their customers use it for marketing.
One of things about Textedly is that normally a message (costing 1 credit) is limited to 160 characters. The biggest message you can send is 3×160 characters for 3 credits. However, the nice but somewhat hidden thing is that if you include an image in your message, then it becomes an MMS and you can send a message up to 1600 characters at a cost of 3 credits. I just included a small image of the conference logo, and then was able to include the full information I wanted to send, same full content as went out in the email. 400 recipients at 3 credits for each day’s communication got to be expensive, so I switched to sending text messages only to the youth, but emailed both the youth and parents. When I did that, in the last text to the parents I said “keep an eye on your email inbox”. I ended up spending $56 for a month of service and the message credits included in that tier, and another $20 for additional message credits. All in all, it worked pretty well. It included a STOP handler, and you could see any text replies. They did require our organization name to be at the beginning of the outbound message so it was more clear who it was from, but that ate into our character count. Being able to effectively reach the youth where they are was worthwhile to us. This worked pretty well. Unless I find a better solution, I plan on doing the same next time.
Yup, it’s a boat load of work. And with the right tools it’s still a lot of work. But I can’t imagine how hard it would be without the right tools.
Do you have any suggestions for better ways to do this? If so, please leave a comment!