The People’s Summit 2017 asked Good Good Work to develop a ticketing system that could circumvent third-party websites such as Eventbrite while making the event more inclusive and diverse. While helping them save a pretty large sum of money, we also saved them a lot of time by automating parts of the system they didn’t think they could.
There were three main parts to this system:
- Applications system – We needed to create a step in the application process that would involve partner organizations first, before moving accepted applications on to the registration process, starting with the gateway.
- Registration Gateway – Once an applicant was approved, the website needed a way to verify their acceptance before letting them buy a ticket. We also needed to be sure that the applicant’s data—such as a registration code—hadn’t already been used to buy a ticket before.
- Sales system – Once an accepted applicant was through the gateway, we needed a way for them to purchase their ticket.
Step 1: The Application System
The first step in the process was to develop an application system. To do so, we created a Google Form for each partner organization, giving their staff access to it. Google Form results flowed into a Google Spreadsheet—called the form results spreadsheet—that had an additional column for marking entries as accepted. Columns can be added to a form results spreadsheet without impacting the attached form.
We knew organizers and staff would be familiar with spreadsheets, which is why we used such a tool. Additionally, we used data validation and protected cells to make errors even less likely.
This system was not in the original scope. As we were working closely with organizers and talking to them about the system in a holistic way, we learned about their plans to do much of the following by hand. Alerted to the inefficiency of their plan, we developed systems that saved countless staff and volunteer hours.
Once these forms were prepared, we set to work on the master spreadsheet. This Google Spreadsheet would ingest all other partner organization spreadsheets and pull out names, emails, location, and other data of applicants who had been marked as accepted on the corresponding application results spreadsheet. It would then assign them registration codes.
Summit organizers would gather batches of emails to send—via their existing email marketing platform—to applicants who had been accepted. Applicants would find in the email their registration code and a link to the registration gateway (which used a URL parameter to automatically populate the gateway input).
Some clarification about sheets and spreadsheets: a spreadsheet can contain multiple “sheets” that will show up as tabs on the open spreadsheet.
The workflow looks like this:
- User fills out the application of a summit partner organization, for example Food and Water Watch.
- The form results show up as a row of data on Food and Water Watch’s form result spreadsheet.
- A staff member from Food and Water Watch marks the user’s application as “accepted” by setting the cell under the “accepted” column as TRUE.
- On the Food and Water Watch sheet (or tab) within the master spreadsheet, the rows marked accepted would be imported automatically.
- All the accepted applicants records would automatically flow into another sheet (tab) within the master spreadsheet called the “feed” sheet.
- Staff would copy entries from the feed sheet and paste them into the master sheet within the master spreadsheet.
- Once the rows of accepted applicants were placed into the master sheet (tab) they would automatically be assigned registration codes.
- Staff would then send an email to the accepted applicant’s email with that registration code.
- Once the registration code was used to purchase a ticket it would be marked as used on the master sheet (tab) of the master spreadsheet.
Importing data from one Google Spreadsheet into another
There is a function in Google Spreadsheets called IMPORTRANGE(). It accepts two parameters, a spreadsheet “key” and a range (e.g. A2:C5). The key is the alphanumeric string within any spreadsheet’s URL:
Using IMPORTRANGE inside the QUERY function allowed us to pull and sort the data. There was a sheet for each partner organization’s spreadsheet of applicants within the master spreadsheet. The QUERY function looked like this:
"SELECT Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col13, Col14 WHERE Col1 = TRUE and Col2 is not null"
The Col# refers to the data column of the range of spreadsheet data selected by IMPORTRANGE. For example, if one imported C:Q (columns C through Q) Col1 would be C, Col2 B, and so on. As such, the SELECT section is “selecting” only the listed columns of data to display.
The WHERE clause tells the QUERY which rows of data to pull in. Only data where Col1 is true and Col2 is not null is pulled in. Col1 was the “accepted” column, meaning that only records that a staff member had marked “TRUE” would match the query. Col2 related to the timestamp column. We did this to limit the results to only the rows of data that contained actual data in them, knowing rows with data were sure to have a timestamp.
Within the master spreadsheet was another sheet called “feed.” It used the QUERY function to pull all accepted entries into a single list.
The feed list used conditional formatting to mark duplicate entries. From here, organizers would copy and paste rows of data into the master sheet. This part, and marking a particular applicant as “accepted,” were the only direct human intervention in this whole process.
The Master Sheet
This is where the real magic happened.
Once blocks of data were pasted in (starting at row E in the above image) a formula would generate a unique “regcode” by combining the name and timestamp of the rows data. This code would then be used by the registration gateway plugin installed on the summit’s WordPress website.
The Registration Gateway
We decided to use a WordPress plugin to manage ticket sales. The challenge was that it would have to handle our gateway on the Summit’s WordPress website. To accomplish this, we locked anyone without a valid registration code out of the ticket sales pages. To gain access, a visitor would have to input a registration code that would need to be checked against the master spreadsheet.
We had to get a form on WordPress to talk to a Google Spreadsheet. To do this, we created a custom WordPress plugin that used the Google Sheets API to check the input value of a form.
The gateway plugin also handled the logic for marking registration codes as used on the master sheet. Watching a spreadsheet magically fill in data as users purchased tickets was a cool sight.
Furthermore, it would block users from using the same code twice while simultaneously pulling data – like a name and email address – into WordPress to auto-fill form fields. It made life easier for the user.
The final step, once the accepted user had entered their registration code, was the actual purchase of the ticket. We opted for the WordPress Plugin Tickera. It was not only able to handle credit card payments but also generated printable tickets, and had tools for registration on the day of – such as a downloadable app that would allow organizers to scan tickets either printed or off people’s phones.
By using Tickera’s WordPress hooks, we were able to fire functions within our gateway plugin when tickets were successfully purchased. This marked the corresponding registration code as used.
While the plugin worked out-of-the-box, we spent a considerable amount of time to fit it into the existing theme used by the Summit’s website. I thoroughly enjoyed the challenge of it.
Putting it all together
Building the system is just the beginning. Once we had everything in place, we tested for all kinds of edge cases, made on-the-fly updates as the requirements changed, and produced a load of documentation for organizers so that they could use the system effectively. Bonus: organizers were also were able to spit out real-time demographic reports!
In the end, we were able to solve some complex problems with elegant solutions in a short time frame. We hit a constantly moving target, for which I am very proud. By stepping back from the problem and taking our time to thoroughly examine the solutions we were able to save many hours of labor as well as thousands of dollars.
I’ve only touched on the technical detail of how this was implemented. If you’re interested in other details about any parts of this process please leave a comment below.