The WordCamp Speaker team worked really hard to generate a large pool of applicants and applications for WordCamp Seattle 2017, so processing them was going to be a headache! Our website’s infrastructure had some really fabulous tools to accept applications and automatically set up session and speaker posts for each person; however, we were still on our own for reviewing and rating speakers.

In the past, that has led us to time-intensive hand-made Google Forms. This year I was determined to automate the process. The following is the fairly technical way I handled this.

For 158 submissions, this took me about three hours to set up. If I had this set of instructions to help me, I think I could’ve done it in one! Be aware that this assumes you’re good with spreadsheets and mediocre with regular expressions!

Note: I’ll have a few WordCamp-specific things in here, but I suspect that much of this could be easily adapted for other conferences.

Our Process & Blind Reviews

We wanted to start our speaker review by having a panel of about 20 folks from our community blindly rate each application. We hoped this would help us find the best applications (since we can’t actually review the talk itself in most cases) and ensure a diverse range of topics for conference attendees.

I’ll share more about the reasoning for a blind review and the review criteria we set in a future post.

How to Set Up a Rating Form for WordCamp Sessions

So you can keep track as we go, here’s the final format I wanted to achieve:

To create that, I used a combination of spreadsheets, regular expressions, and a Google Forms add-on, a process I describe starting…now!

1. Collect the Entries & Export

Like I said, WordCamp.org has a great tool for accepting applications. Once they’re all submitted, you can export them to a CSV spreadsheet file. Getting the data is the easy part.

But one word of warning! If you modify the form after you begin accepting submissions, you will mess up the form export. Get your form ready at the beginning and then do not modify it once the application opens. If you have to, only add new fields to the end. Moving fields, deleting fields, or adding new one in between others is an invitation for problems.

Once the application is closed, you’re ready to export. Go to Feedback > Export CSV and then export your form.

2. Clean Your Data

Start by giving each row a unique ID starting at 1. This became really useful later when we were trying to cross reference form ratings back to the original application.

Now open your CSV and clean it up. If you messed things up like I did in step #1 by editing the form after releasing it, take the time to get your data in order now.

If you’re trying to do a blind review, this is also the step to create a new column containing an anonymized talk description. (Just copy-paste the original description column and edit the new column so you’re not destroying information.) Many folks cite their websites or other traits in the description. In our case we removed gender pronouns and all other identifying details we could without obfuscating the talk.

3. Prepare your “Questions” & Descriptions

If go back and review the format we wanted, you’ll notice that in Google Forms nomenclature, the “question” is the talk title while the “description” of the “question” is a combination of the audience, format, and talk description. Those three pieces of information for the description were all in different columns of the spreadsheet, so here’s how I handled that so I didn’t have to do it by hand.

1. Copy the entire spreadsheet and make a new version for modification.
2. In the new copy, remove all the columns you won’t be using in the form. For me, that left the “ID” added in step #2, the title, description, format, and audience.
3. Export the spreadsheet to a TSV or some other format that uses an uncommon character as the spreadsheet delimiter.
4. Open the file in a text editor like Sublime Text that supports regular expressions, something way outside the scope of this article. Now use regex to remove the delimiters you don’t want, leaving only two columns.

If you didn’t follow that last step, essentially, I was able to do a complex Find & Replace so that my new spreadsheet only had two columns. If I had used a pipe character as my delimiter (“|”), I took this:

{id} | {title} | {audience} | {format} | {description}

and turned it into this:

{id}. {title} | {audience} • {format} • {description}

Finally, I saved and imported this new file into Google Sheets and was left with a two-column spreadsheet with the question and description ready to go.

4. Add the Form Maker add-on and use it to create the forms

So far, this has just been a lot of work. This step is where the magic happens!

The Form Maker Google Sheets add-on allows you to turn a spreadsheet into a form. Using that tool, I could then simply paste my rows into their template, click “Create New Form” and voila! New form.

Since it’s easy to make forms now, consider breaking up submissions into multiple forms. We made ours roughly in groups of 30 and our reviewers loved this because they could rate in batches rather than having to do it all in one go.

5. Everything else 😜

Once you’ve distributed your forms and received ratings, all that’s left is to vet speakers, select speakers, make your schedule, get RSVPs, review their slides, and make sure they have enough coffee to get through the weekend!

Here are a few small things I ran into during the review phase that were useful:

• The Form’s results with ratings had data so that each talk’s scores ran in columns, but my speaker submission spreadsheet was in rows! The Google Sheets TRANSPOSE() function was exactly what I needed to deal with this.
• When evaluating ratings, we looked at the AVERAGE(), MODE(), MIN(), MAX(), and overall distribution of ratings. Some applications had a low average but a lot of very high scores which we interpreted as niche interest. Other scores with a higher average but a lot of low scores also made us dig in deeper to find out what influenced those ratings.

Not Perfect, but Better Than Last Year

This process is still a little complicated and not without its hitches. There might be a better way to do it, but it worked for us. At the end of the day, what it came down to was that this was way better than manually copy-pasting all this information into Google Forms. I hope this helps someone else!

Let me know if this helps you and any ways you improve on the process!