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.

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.

Karass Website

After creating The Karass Inn’s logo I set to work creating a website for them. They used checkfront.com to handle the booking side of things so I was left to simply create the presentation side.

http://thekarassinn.com/

Screen Shot 2015-12-27 at 11.34.47 PM

I used the Make Theme with only slight modifications to the CSS. Mainly to create the pop-ut Book Now button. The site looks great on mobile too:

Screen Shot 2015-12-27 at 11.35.02 PM

The address, phone, and email at the top work incredibly well on mobile. By using proper Uniform Resource Identifier schemes with the links available applications will launch when they are clicked. You’re probably already familiar with mailto: which almost all email links use, it looks like this:

<a href=”mailto:TheKarassInn@gmail.com”>TheKarassInn@gmail.com</a>

While the phone uses tel: like so:

<a href=”tel:8028755878″>(802)-875-5878</a>

So when you’re on a phone and click the link it automagically launches the phone application and makes the call.

I love being able to add these little details!

If you’re ever in Chester Vermont you should stay with The Karass Inn

Two Old Guys Go Hiking Book Cover

My dad recently wrote a book about hiking on the Appalachian Trail. He reached out to me to do a book cover, here’s what I made him:

old-men-book-cover-DRAFT1

I used one of my favorite fonts: norwester and integrated a public domain hiking icon from the noun project

The real fun bit was that the original photo he wanted to use only had one old guy hiking, so I used some photoshop magic to add in the other old guy.

Screen Shot 2015-12-27 at 11.57.27 PM

A surprisingly quick project that came together really nicely.

Hate Has No Business Here Poster

msa-welcome_fb-ad-censor-face

So there’s been a bunch of mean people supporting terrorists by spreading xenophobic hate speech in the news recently. In response Common Roots Cafe in Minneapolis put up a sign in their window stating that “All are welcome here“.

All are welcome here

I was contacted by The Main Street Alliance to re-create the poster for the #hatehasnobizhere campaign.

msa-welcome-poster_poster-11x17

I also created social media assets for sharing across the internet.

msa-welcome_prim-fb-ad-stand-with

msa-welcome_fb-ad-just-black

This included Twitter and Facebook cover images and profile pictures.

Screen Shot 2015-12-18 at 2.30.24 PM

I added all these into a shared Google Drive folder.

It’s been amazing to see my poster pop up in small businesses across the country, check it out.

 

Platform Cooperativism Dump!

A few weeks ago I went to the Platform Coop conference in NYC. It was an interesting cross section of several circles of friends. There was my coop chums, and my nerd friends, and my free libre open source people, and the intentional community folks. Everyone who is doing cool things was there.

What follows is a dump of links and information I randomly collected in my notebook. It’s not really linear and you can just jump around picking up links and little gems.

Is it Valuable to Them!?

Get your value proposition correct. Why should I join your coop, platform, group, etc. what’s in it for me? Nail this down early. Why is your thing valuable.

There was a lot of talk about how Airbnb and Uber are bad actors who exploit their providers (the drivers and homeowners) yet again and again people said how happy those same providers were with these exploitative platforms.

Personally I think that most people have been trained to roll over and accept abuse, especially if it’s not as bad as it could be. Most people don’t seem to care who steals their data or if they get a fair cut. At least they are getting a little scratch while some big guy fucks them.

College Educated people are taking blue collar jobs.

All these over educated millennials are taking jobs that others in their cast would scoff at. Taskrabbit and house cleaning services are making it okay to work jobs that only immigrants and high school dropouts would do.

Labor Displacement, or stealing jobs from immigrants. Which is kind of funny considering how freaked out everyone seemed to be about it being the other way around. Turns out cleaning someone’s home isn’t beneath a masters in art history… This, of course, means that tech savvy young people are pushing out the working poor (they just can’t get a break).

Start with the people you want to end up with.

This is a cool idea, when you start a project consider that the people you start with might very well be the people you end up with. So you might as well pick good people.

I think this also gets into diversity of groups. If you want a diverse group you can’t start with a homogeneous one, or you can but it will be more difficult to end up with something that isn’t just as homogeneous.


 

Society seems to think that Monopolies are okay, but we hate cartels.

Check out OuiShare:

Screen Shot 2015-12-07 at 5.16.37 PM

New economy from Europe! I love the new economy, it makes so much more sense than this current economy.


 

Through ownership we become free.


 

http://backfeed.cc/

Triangle-SideWay-300

I don’t quite get what backfeed is, but it’s something.


DEMAND MANIPULATION FACEBOOK


New Economy

http://www.cicopa.coop/

The International Organisation of Industrial, Artisanal and Service Producers’ Cooperatives, or CICOPA, has been a sectoral organisation of the International Cooperative Alliance (ICA) since 1947. Its full members are representative organisations of producers’ cooperatives from different sectors: construction, industrial production, services of general interest, transport, intellectual services, artisanal activities, health, social care, etc. Its associated members are support organisations promoting cooperatives in those sectors.

marketing cooperative

This idea seems close to what Agile Learning Centers is becoming. A coop of small businesses (schools) that pool their resources to manage a central marketer who maintains branding, web, social media, etc. for the coops involved.

Business Model Canvas

Screen Shot 2015-12-07 at 5.30.02 PM

This is a tool to figure out your business! I found this tool for creating it.

What is your Coop’s accountability process?

Blockchained Together

So what is the blockchain? Decentralized Digital Property. You own digital property with no need for a centralized authority to grant (or remove) access.

La`Zooz – Rideshare on the blockchain

http://lazooz.org/

I’ve converted their white paper into a PDF you can download here.

trustless database…?

DCO

Distributed Collaborative Organization

DAPPS

Distributed Applications (read more)


“The trust web” vs “The trustless web”

6097bc9eb4a1f881cba1f1d138a88327ee032089

http://www.swarmbot.io/


Liquid Democracy

https://liqd.net

There was so much more, but I am out of time now.

Four Step Game Development

I’ve been binge watching Mark Brown’s Game Maker’s Toolkit series on YouTube. Mark is a game critic that has been creating these video reflections on games, typically on aspects of their mechanics. I’d like to discuss one of Mark’s videos called Super Mario 3D World’s 4 Step Level Design in which he looks at the super successful level design of Super Mario 3D World. Have a look:

The game introduces a dazzling amount information to the player. Looked at as a whole it might seem overwhelming yet, according to mark, it works really well. I’ve been seeing this idea of overwhelming game mechanics a lot lately. Some of us at ALC recently consulted with a school who wished to introduce self directed learning to their students. This is like going from a linear 2D scroller—where everything is spoon fed to you—to a massive open world game. In ALC we have a number of games (or tools) such as Kanban boards, Set-the-Week, and Gameshifting that support self directed learning. When put together all these games seem, to the uninitiated, as overwhelming and complex. Just look at the Kanban, with it’s infinite variations and ability to expand to fit almost any situation.

kanban-examples

The teacher at this school was keen to introduce our tools to her students, but it was clear to the ALFs present that this would be too much too fast. I wanted to support her in doing this, but more level heads were able to “limit the work in process” and we ended up suggesting a super simple Kanban and Gameshifting board to start.

I think that we can look toward Super Mario for inspiration when trying to introduce our games.

The 4 Step Game Design

As outlined in Mark’s video Mario is using a “four step” design principle.

  1. Introduction
  2. Development
  3. Twist
  4. Conclusion

This provides a useful template for introducing our tools, let’s focus on the Kanban, as it lends itself to this methodology.

One: The introduction

kanban-post-elements_simple-kanbanWhen Mario introduces a play mechanic it will typically do so in a safe environment with low stakes for failure. The player can then try and fail without risking a life while they feel out the mechanic.

The introduction to the Kanban can happen in two ways. First, it can be simplified into it’s most basic form, which I would describe as a two column board with these headers:

  • What I want to do today
  • What I’ve done today

I use the word want very deliberately here. It’s lower stake than will and makes failure a bit less sharp (changing a players perception of failure is for another level). The instructions are absolutely clear in these titles. Hand this Kanban and some sticky notes to someone and it’s instantly clear what they are going to do with it.

I’ve used a method like this when introducing ALC or our tools to people in the past.

endor-foldy-kanban-crop

By using a kanban to manage the presentation I am able to model the tool in a useful way. This, again, is a safe way to introduce people to it. They are participating with the tool, but not having to take care of it themselves.

Two: The development

kanban-post-elements_dev-kanban

As we move through our Mario level we are challenged by a slow development of that mechanic. More complexity is added as the player moves through the development.

With our Kanban it’s easy to see how to do this. We can just start adding columns. In the example of introducing Kanban to a school we might start with a two column and after a few weeks add a third column then a fourth.

The idea is to develop the tool over time with the player. Add new columns as the player realizes new needs.

Three: The twist

kanban-post-elements_twist-kanbanIn Mario the twist is typically the addition of an unexpected challenge that makes the player have to interact with the mechanic in a different way.

With our Kanban the twist can come in the form adding elements that expand the functionality in unexpected ways. For instance adding rows (“swim lanes”) to group tasks.

The twist, I think, should be drastic and complex. It’s meant to show where such a tool can go. The twist opens up the players imagination to possibilities.

Four: The conclusion

kanban-post-elements_dev-kanban copyNow we are done. In Mario the conclusion consists of one last use of the mechanic to show mastery by the player. Like a pat on the back for learning a new skill.

For our Kanban the conclusion can come from having our student walk away with their own modified Kanban. After seeing the “twist” they might well be inspired with a concrete way to improve their Kanban.

We expand the possibilities then conclude with an appropriate application of the flexibility.

Games as tools, tools as games

I think there is quite a lot to learn by thinking about our tools and practices in the context of games and game mechanics, especially video games. For the past 20+ years video games have been teaching people a wide variety of skills. Video games are coming to be the dominate storytelling medium in our society. Modeling our own pedagogy from the best that games have to offer gives us a powerful ally in expanding the reach of our ideas. I will certainly be looking more at game development and its practices to see how I can leverage it in my own practice.

The post Four Step Game Development appeared first on Drew the ALF's blog.

ALF Cheat Sheet v 0.3

I’ve updated the ALC Network Cheat Sheet and updated it to a more accurate title. The updates better describe some of the communication channels, add the new ALC WORKS! Trello board (formally the projects board) and bring in a whole new column that describes protocol in the network around what channels and infrastructure are used for what kind of activity. I am drawing from a number of sources such as @tomis’s Network Ecosystem Charter and the Virtual Culture Committee process which have not been widely agreed upon in the network. That means this document does NOT represent the consensus of the network, but my interpretation of it and how it might be soon. With that disclaimer out of the way, have a look (after the jump) and please leave your comments! 

alf-communication-channels_main

 

The post ALF Cheat Sheet v 0.3 appeared first on Drew the ALF's blog.