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.

It might be free but it ain’t cheap – UPDATE

UPDATE: I wrote this post in haste while upset and worn out after a meltdown of a system I’ve been caring for for a long time. After talking to some people in the network, who I insulted with my brashness, it’s clear that I’m burnt out and much of this response comes from that. I’m sorry and also not sorry for this public display. Humans are emotional creatures, we are real and we mess up. I’m in a challenging place in my life right now and I let it get the better of me. For that I forgive myself and I hope you’ll understand dear reader.

Look, it’s not as dire as I make it out to be. Actually things are amazing, power is drawing to the edges of this network away from the center, away from me. I fear that what I’ve created for the network will break and leave people behind, and while we have much word to do, it’s not as bad as it seems to me. There are new excited people who are taking up the slack. This network is full of amazing people who will handle the mess of wires I’m leaving behind.

While I still believe that the recent failure should light a fire, it’s also true that even if all this tech stuff disappears tomorrow ALC and the network of people doing the work wont. What I’ve created, with the help and support of many fantastic collaborators is doing an amazing job 98% of the time. I’m proud of what I’ve built here. Things are changing, that’s all, and I’m changing, my involvement is changing.

From where ever it is in the world I am behind a screen the good things can seem so small and far away while the problems near me so big. It’s a mistake of perspective. Perhaps it says more about me than anything else. Perhaps it’s weird for those of you who are new, but I’ve never been involved in anything where I can feel safe to be a messy idiot and still find love and support from the people around me.

We are surely building something amazing together and it will be fine.

Redactions are struck through while additions are in underlined

—-

The ALC website went down today. I’ve know for years that this day was coming. A malware bomb that exploited something and was able to get into our system and blow up. The good news is that it’s gone, the site is back up. The bad news is we don’t know where the hole was and thus we don’t know if we plugged the leak or just made room for more water to pour in. We are very fortunate to have had Loren and Dan available to jump in and save the day, I would have not been able to do this on my own.

This attack happened at almost the same time that our server hit a limit of “script executions” which is a fancy way of saying lots of people are doing stuff on our website, more than our $12 per month hosting allows for. Which, of course, is good news. We are growing, there are more people than ever using the site. These things are just growing pains.

So, my dear network of agile learners and facilitators and parents, it is time to have the talk.

There is a joke in my nerd world that free software is free as in kittens, not as in beer. A beer you get, enjoy, and are done with it, buzzed and not a dollar lighter. A free kitten is a gift of responsibility. You have to take care of it, feed it, make sure it doesn’t die. It’s a burdensome gift. It’s also a gift that returns more love and affection than a single beer ever could.

Agile Learning Centers is a kitten. We, the volunteers behind the scenes, did you a great disservice not warning you adequately. We made it all look so good and clean and put together—and it really is in many ways—but we’ve mislead you. You have, possibly unwittingly, become You are the co-owner of the ALC kitten. Now your kitten is a bit sick, vomiting on the nice rug, and the vet bills are going to start coming in.

That is to say that we now have a great opportunity. It’s time to create the network. There is no staff at the center, there is nothing but a few overworked volunteer knowledge holders who have been around just a little bit longer than you have. There are no leaders but yourself. What I’m asking—demanding—of you now is to take on more responsibility, more work, more time, more costs. I can’t tell you how you’re going to do it, though I might be able to support you, make suggestions, warn you about what didn’t work in the past, but mainly I’m going to talk to you about websites and servers.

In a funny twisted way the website binds us together and it will be, I hope, the catalyst for taking the ALC network to the next level. We can no longer support the website on volunteer time. I have written a detailed breakdown of the current state of technical infrastructure and my recommendations for what to do next. I suggest you skim it, just to get a sense of what’s going on behind the scenes. To get a sense of what you now co-own own and are responsible for.

There is a lot that needs to get done, but it all starts with you coming together with your fellow co-owners and building out a body that can determine the will of the network. This is certainly already happening all across the network.

Have you noticed that there is no mission or vision on your network website? I can’t publish a vision because we’ve never made one that was truly representative of the network. Why? Because the network hasn’t come together yet to make it happen. I think we all share a mission and a vision, only it hasn’t been articulated.

The network hasn’t come together to outline what is important to it. What it wants it’s website to DO. I’ve been stuck for a long time with hundreds of different facets of the website I could have worked on, but never knowing what is actually needed.

Now it’s time to force take account of the issue, because we need direction, we need a plan, and so we need a body to get that plan together. This website needs to be updated, it needs a strategy to maintain it, it needs resources to keep it going. You have to provide those resources, you have to make the plan. The website is a reflection of everyone and right now it’s sick, it’s going to die, your kitten is sick.

Because this isn’t Facebook or Google, you aren’t a product to be mined for data and sold to the highest bidder, you are the creator, you are the driver, you are the owner. YOU the person reading this YOU HAVE TO DO IT, dissolve this illusion that there are other people who are in charge, we’ve all got to get it together. Some of us have been around a little bit longer but we don’t know! No one is going to provide this for you, it’s just like the ALC you are a part of. It takes everyone to make it happen.

Sorry if you didn’t sign up for this, but it’s on you now. The website is going to fall apart or not keep up with your needs if we don’t all take an active role. The beauty of this problem is that it is also an opportunity to create new celebrate the new leadership in our network and form the processes for us to all take collective ownership of this cute little kitten.

How?

I don’t know how you’re going to do it.

Here’s what I suggest: build a spokes council. (Borrowed from a long tradition of decentralized organizing)

  • You are going to talk to the people you see every day about this post. Get them to read it. Discuss what you think it means. Discuss what I’m asking of you. The parents, the kids, as many of the people around you that co-create whatever version of ALC you are making real.
  • You’ll choose someone to represent your cluster, your ALC or startup group. The group of stakeholders you share space with. Their first job will be to connect with all the other people who have been chosen as spokes. Many of you are already connected with other people in your region throughout the network, that’s a good start.
  • Now the spokes will start to figure out how to meet together, how to have a conversation. Start to meet online, do it often, swap out the spokes often too. Just start sharing digital space.
  • Maybe it’s only the ALCs in your time zone for now, but the beauty of the spokes council is that you can have those spokes select another spoke to represent the regional group, then start the process over. The regional spokes will get together, meet up and talk about the needs of their regions.
  • Repeat this process until there is a meeting of spokes that can speak on behalf of the whole network.

Once you have that then it’s just a matter of creating a feedback loop.

  • Local ALCs sit and talk about what’s present for them, what they need or can offer the network. A spoke is selected to represent those views.
  • The spoke goes to a regional meet up of other spokes, and those people have a conversation on behalf of their ALCs.
  • If need be, those regional meetings select a spoke to meet with other regional spokes and speak on behalf of their regions.
  • The spokes report back, and the cycle repeats itself.

Then take ownership of the network and tell me what you want the website to do. Tell me how we are going to solve the problems I’ve outlined and all the challenges I have no idea about.

This might take a long time, you are busy, I know. It might no look anything like what I suggested above. It can be slow, but something needs to happen.

“Who if not you? When if not now?”

The post It might be free but it ain’t cheap – UPDATE appeared first on Drew the ALF's blog.

Lange Studio Website Refresh

My partner put me in touch with an old client of theirs. They needed help to add a page to their existing WordPress site and fix a few cosmetic issues. I agreed to help and dove in, expecting a simple process. The theme was using a baked in version of page builder which I quickly found was both out-of-date and totally not working.

I was to extract text and images from a PDF and apply them to a page in WordPress. I was able to extract the text as HTML (formatting it with some regular expressions) and gathered the source images from the client.lange-ig-pdf-page

Once I attempted to add it to the page using Page Builder I found that the raw HTML input wouldn’t capture the input text. No wonder the client was having problems! I was able to hack the system by adding the text directly to the input element via the browser’s development console. An impossible task for someone with a only a working knowledge of browsers and web technology.

After shimming the content into the page and fixing some other esoteric theme related issues I was done. This simple act of adding a page to their website cost 300% more than it should. In fact it shouldn’t have taken an engineer to do such a basic operation.

The client had three options. Try to fix the site, recreate the site, or keep using this hobbled site. Because the site hadn’t been maintained the off-the-shelf theme, WordPress core, and many plugins (some integrated with the theme) were all out of date. Updating and fixing the site might introduce a host of new problems and might not even fix the site.

To keep the site as is and just deal with the wonky broken system would cost way more in the long run for the client. The whole point of using a CMS like WordPress is to make tasks like page creation simple enough for a client to manage on their own. The process of adding content to a page was so onerous that it would be faster to just build pages out of HTML!

So my recommendation was to recreate the site in Squarespace. The client accepted this and we began work.

Site Recreation

I opted to use the ishimoto theme as a base to create the site. The original site was very clean and minimal so it was fairly simple to remake using only Squarespace’s theme and design tools, no code necessary.

Original Site
Original Site
Recreated site
Recreated site

By using gallery blocks and the intuitive content tools provided out-of-the-box by Squarespace I was able to quickly recreate all of the pages. Better yet the client was able to jump in and figure out how to use the system without much hassle. The cost was about twice as much as adding a single page to the old site!

On top of this we were able to leverage Squarespace’s e-commerce tools to begin setting up a system to sell prints directly off the site.

Conclusion

I love WordPress. It is a triumph of free open source software and a gift to the world. With a few plugins and a decent theme you can create a powerful website that does anything for a fraction of the price. It puts power into the hands of the user. However it requires some upkeep, there is a cost to maintaining WordPress. I would say that when compared to Squarespace’s monthly cost (~$12) it’s about even.

The power of Squarespace is that it just works, the server and updates are all handled by someone else and requires no intervention. When all you really want to do is display some text and media (and perhaps a store) it can be a very good option.

As a designer I feared that products like Squarespace would take away from my livelihood, but I find that my skills as a “power user” make it worth while for clients to hire me to do the initial setup and design pass. I can then hand it off to them without needing to provide a long term maintenance agreement. It’s easy for me to build and it’s easy for them to edit, a win-win in my book.

First Network News Call Recap

Our first Network News Call went very well. This post is a recap of the call, with links and all that fine stuff. If you missed the call, don’t worry, it’s available on YouTube! Watch below:

Introductions

On the call was Anthony, Charlotte, Liam, Bear, Tomis, Rochelle, Art.

The Alliance for Self-Directed Education: Update

Jump to this section in video.

Brief summary of where we are at and what we are hoping to build.
How this intersects with ALC (#Duh, but to point out some specific things)
Tipping Points: the self-directed education experience (call to action)
Join the newsletter, share it with your ALC communities: http://self-directed.org

Notes: Tomis & a bunch of people are working on an ASDE
Putting together an online magazine to elevate the voices of people who are doing this in various places
Putting it out to ALC World: are there articles you’d like to write for a large magazine audience?
Sign up for the newsletter at http://self-directed.org

ALC Mosaic’s #OneCampus: Update

Jump to this section in video.

We find out a lot of important information in the next month.

If we need a fire system with sprinklers, costs could be severe. We are committed to making it work and need your support!

–How does this effect the ALC Network?

(ALC Hub: trainings and ongoing startup support…guest house idea)

OneCampus

ALF Calls update

Just to this section in video.

Most up-to-date info on calls: alf.agilelearningcenters.org/newbie

Contact liam@alc.network if you want to volunteer or have questions!

You can view the ALC Calendar at: http://agilelearningcenters.org/calendar

Facil-Pract

CH: Jamie
Mondays, 8:30pm – 9:30pm


Southeast Facil-Prac

CH: Nancy
2nd 4th Thursdays, 7:00pm – 8:00pm


Startups Facil-Prac

CH: ???
TBD


Tech Support Call

CH: Drew
3rd Tuesday, 8:00pm – 9:00pm
Next: Sept 20th


Ops Support Call

CH: Tomis
Every Other Thursday, 4:00pm – 5:00pm
Next: Sept 22nd


Agile Learning Parents Call

CH: Melissa
TBD


Change-Up

CH: Ryan
4th Monday of each Month, 7:00pm – 8:00pm

Network business update

Jump to section in video.

Network Accountabilities spreadsheet

Notes:

This call used to be report backs from Working Groups & decision-making.
This call is now just for announcements.
Network Biz WG has been formed for the decision-making group. CH is Art. Monthly accountabilities call to put forward work
Drew reads through the accountabilities spreadsheet.

I don’t think it makes sense to have read through this spreadsheet…
Presumably CH’s would have added cards about these if they had updates.

Startups WG Updates

Jump to section in video.

Celebrate accomplishments from last month:

  • Membership automation minimum viable product complete!

Goals for September:

  • New Member Automation Complete: This means getting everything from sign-up & payment to automatically adding new members to Slack, the to Membership Directory & Map, etc)
  • Harbor Pilot structures, web docs & pairings complete: Harbor Pilot’s will welcome new schools/projects into the Network & “pilot” them into basic familiarity with our tools & network structures. If you’re interested in this, email startups@alc.network! I will send out more information once the Membership Benefits are firmed up (since this will inform the type of piloting needed).
  • All existing ALCs moved into new ALC Content Type: Thanks Drew for setting up the new Content Type! This keeps ALC Directory information on the website.
  • Make explicit points of contact between all ALCs and Harbor Pilots/Account Managers: This will make visible who’s been checked in on by who, how often, & what support they need.
  • Contact all Startup & Early Stage ALCs about Health Surveys
  • Schedule & organize all ALC support calls
  • Email startups@alc.network w/ questions

Windsor House Update

Jump to section in video.

Notes: 

Old school
Needed to move because of earthquake
Now spread across several different locations
How to coordinate events, which kids are going where based on activities, staff, etc?
Met ALCs at AERO
Using tools to set intentions, create consistent culture, book rooms & locations, etc
Things have been going well!
School opens TOMORROW
Had some cool practice meetings that parents & kids have really enjoyed

Two other free schools in Oregon and SLC are probably going to start using ALC tools
Ashland OR will also be starting tomorrow after meeting ALCs at AERO — speed record for meet to startup 🙂

Cairo training report

Jump to this section in video.

Notes: 

Bear, Nariman, & organizers from 1-2 day workshops & presentations from November 2015 came together to put on a 12-day training 🙂 🙂
English language school gifted to trainers to use as needed
30 participants in 3-day foundation training & building community
5-day summer camp with 30 kids, went super well!
Great sharings, moving, powerful experiences, lots of positive feedback
Quotes from kids:
“I am doing everything I want!”
“I can’t believe everyone’s okay with everything I’m suggesting.”

“I’ve been to 100’s of nurseries and have never liked any of them. This is the best thing I’ve ever done in my life! I’m 9 years old and this is life.”

Then did 3-day launchpad at The Oasis (retreat): 5 ALCs starting up — space for young adults, integrated into a progressive arts college in Cairo, teen ALC, young ALC, camp for kids on a regular basis, 2 1-day/week parent-ALC co-ops
And then a bunch more I can’t type fast enough to get down…

Additional learnings about structuring trainings & workshops

Cairo as a learning city ^ ^ ^
Sandbox (pun bear?? 😉 — connecting resources & spaces to each other
Totes rad!!
Good accountabilities, relationships, & communication

PSA: Having an issue you can’t solve in your community? Try the ALC Culture Committee

Jump to section in video.

Virtual Culture Committee

Heartwood update

Jump to section in video.

Notes: 

Open for 1 month
Have had 3 info sessions
Enrolled 2 new students
2-3 families with 2-4 students in the pipeline over the next month
Currently have 16 students
2 paid & 1 volunteer staff member (all full time)

In Clarkson Community Center ATM (temporary space, shared with other community projects, has been difficult)

heartwoodalc.org – info@heartwoodalc.org

Colorado Springs

Jump to section in video.

3 teens Tuesday-Thursday
3 families collecting every Sunday for game night
First week went well
All founding parents & students were involved

Tampa Update

Jump to section in video.

Notes: 

Heath has left ALC Tampa 🙁 🙁 🙁 due to misunderstandings & miscommunications
Bear has been in touch with Tampa & Heath
Take-away to share: It’s important to get a signed agreement that states your pay, housing, compensation expectations, and have communication agreements be a part of that.

Talk to us if something isn’t working! We can help

You can chat with Rochelle about Blueprint of WE as a way to set up relationship foundations IYI, rochelle@alc.network

Euro Tour Update

Jump to section in video.

Notes:

Bear has been talking to Marcus in Greece about organizing events there
Organizing tour w/ Alex & Pia for Euro in Jan/Feb
Mostly 3-hour presentations, sometimes weekend trainings (w/ one-day practicum with kids)
Reaching out to partners to map it out

You can connect w/ bear on this bear@alc.network

Twin Oaks

Jump to section in video.

Notes:

Drew, Rochelle, Liam, & Abe went to the Twin Oaks Communities Conference!
We did a workshop on Culture Creation, using the CMB & change-up to create intentional culture. 45 people came, Liam thinks it went well! We will publish about it soon.
Rochelle also taught a gameshifting workshop


Special thanks to @rochellehudson for taking notes!

Feedback? email drew@alc.network

 

The post First Network News Call Recap appeared first on Drew the ALF's blog.