How we saved the People’s Summit nearly $10,000

This is the story of free and open source solutions that made an event more inclusive and less costly.

On the weekend of June 9th, 2017, thousands of progressive americans came together in Chicago for the 2nd annual People’s Summit. There were inspiring speakers, such as Nina Turner, as well as  brilliant panels that enlightened, informed, and educated the throngs of activists who had gathered from around the nation. The keynote speaker was Bernie Sanders, who asked the assembled crowd of over 4,000 attendees, “How many of you have run for office, or are actively involved in local campaigns? Stand up.” Half the audience stood. It was truly inspiring.

A few months earlier, in April, organizers came to us, looking for a solution to sell tickets more profitably. Last year, Eventbrite had cost them an exorbitant sum of money in fees. Fees that could have been spent on stipends to help people attend—one of The People’s Summit’s main goals. So, this time around, they wanted to deploy their own ticket sales system.

In the end, we delivered a unique solution that not only helped them achieve their goals, but also saved them ~ $10,00 in fees.

The First Problem: High Eventbrite costs

Eventbrite charges a fee per ticket ($0.99) and takes a percentage of the ticket (2.5%). We created a spreadsheet where we did the math, taking the tickets sold through our system and applying the eventbrite cost to them.

ticket type cost approx ticket sales eventbrite fee per ticket total fees
Scholarship Ticket $0.00 474 $0.99 $469.26
Low income/Student $45.00 1042 $2.12 $2,203.83
Regular $115.00 1283 $3.87 $4,958.80
Solidarity $225.00 230 $6.62 $1,521.45
Institutional $350.00 68 $9.74 $662.32
eventbrite total $9,815.66

This spreadsheet doesn’t include the one-time fee paid to Good Good Work for creating the new ticketing system or payment processing fees—which can’t be avoided. In the long run, however, this sum will be the total savings for each of The People’s Summit’s events.

The spreadsheet takes the fee from Eventbrite and sticks it into our formula with the ratio of attendees based on a past event.  Because there are always additional fees when credit cards are a payment option, the Stripe fee is an unavoidable expense, even in a new system.

By setting up our own ticket shopping cart with the WordPress plugin Tickera, we were able to provide the same functionalities that Eventbrite has:

Example of the PDF ticket generated by Tickera
  • Online cart, sales page, etc.
  • Multiple tickets with different prices
  • Payment processor (using Stripe)
  • Paper ticket generation with Tickera via PDF download
  • Day of event check-in via Tickera phone apps that could scan printed QR codes

The major feature that Eventbrite has and that a WordPress plugin can’t provide is exposure. People go there to find tickets! For many events that might be an issue, but The People’s Summit had enough exposure on their own. They knew that they would sell out before even making an announcement, so they didn’t need Eventbrite to make them more visible than they already were.

Because we were able to handle all the other features through Tickera—which had a $99 price tag—we could avoid paying third-party fees.

Some key points here include:

  1. The power of open systems like WordPress.
    Because WordPress is a free and open system designed to be extended with plugins, there’s a whole ecosystem and user-base available to developers who wish to solve problems, such as ticket sales and event registration. This ecosystem can provide inexpensive solutions to millions of people in a decentralized way. Where Eventbrite has to maintain many servers and staff to keep everything running, that overhead is distributed among the WordPress community of users and developers. It is all-around more economic.
  2. An investment that gets less expensive with age.
    With a one time investment in hiring Good Good Work, The People’s Summit now has a system that will save them money over and over in the next few years. While they saved ~50% of $10,000 this year, next year they will save 100%. And as Eventbrite fees increase, they will continue to save more and more. A little investment of resources now will net a huge win in the future. Ultimately, profits will go exactly where they’re meant to go rather than into the pockets of third-party websites like Eventbrite.

The Second Problem: Making a more inclusive summit

Summit organizers knew they were in the unique situation of having more people who wanted to attend the summit than what the space allowed. The event was going to sell out, which would skew the attendee profile towards people who could afford to purchase tickets fast. This wasn’t what organizers wanted.

The People’s Summit wanted their own, personalized ticketing system that could circumvent the need for a website such as Eventbrite. They also wanted a more open application process that could empower partner organizations to select attendees from their diverse crowd of applicants. They wanted the conference to be a true representation of the American people; diversity in age, race, location, identity as well as individuals with a different mental or physical stance, outside of the usual binaries. Its final goal was really to make the event all-around inclusive while saving them as many third-party ticketing fees as possible.

Our solution had to be flexible, fast, and easy enough for organizers to use. We immediately began researching the problem. There were three main systems to consider:

  • 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 selected, the website needed a way to verify the 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—minus the Eventbrite fees.

Once we fully understood the problem and the requirements, the solution and its design quickly became clear.

Here’s the chart mapping the review system we created. Click here to find out more about the process.

The Good Good Work team always aims to empower our clients to use and adapt the systems we create for them. That’s why we live by the principle of meeting people where they’re at technologically. In the case of The People’s Summit, we opted to use Google Spreadsheets because that’s where the organizers were doing their work. We didn’t introduce any new or hard-to-grasp tools because we felt it was better to follow our stakeholders, even if there might in fact be more effective tools out there.

As we were working closely with organizers and talking to them about the system in a holistic way, we were able to develop systems that saved countless staff and volunteer hours in addition to the final ~$10,000. I’ve created a more detailed technical overview. Go check it out!

The Final Product consists of…

  • An application and registration process that allowed partner organizations to accept the right applicants and automatically grant them access to buy tickets.
  • A ticket sales platform that we integrated into the existing summit website which could handle the sale, distribution, printing, and collection of tickets for the event.

In the end, we were able to solve some complex problems with elegant solutions in a matter of weeks. We hit a constantly moving target, for which we’re all very proud. By stepping back from the problem and taking our time to thoroughly examine the solutions, we were able to save The People’s Summit many hours of labor as well as thousands of dollars. We managed to automate a system that our client didn’t even imagine could be.

This years registration was pretty smooth, in large part to the staff and volunteers and the system we put in place.

The People’s Summit can now do all their own ticket sales, no longer reliant on Eventbrite. They’re mostly self- sufficient; they might now be dealing with more overhead, but it comes with more control.

In fact, with a little more investment, the system we built for The People’s Summit could be generalized and used by the smaller partner organizations who don’t have the resources to hire the developers to do this.

Each time organizations use open systems like WordPress, they support all the little organizations who don’t have the resources. We built something that could be used and re-used and we supported a group who has already built a successful ticketing system (Tickera), which then helps them continue to make their product better.

If you’re organizing an event and think that a system like this might be helpful – or you’re into saving thousands of dollars, give us a holler.

The post How we saved the People’s Summit nearly $10,000 appeared first on Good Good Work.

Building a Custom Ticketing System with Free Tools

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:

  1. 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.
  2. 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.
  3. 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.

A form response spreadsheet with a row being marked as "TRUE"
A form response spreadsheet with a row being marked as “TRUE”

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:

docs.google.com/spreadsheets/d/1Kwiz0eiY_ADysLMrPqycty2CcH-cBjsl7XodVB2v02g

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.

Ticket Sales

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.

The post Building a Custom Ticketing System with Free Tools appeared first on Good Good Work.