Greetings all,

We are a new region, and I am new to the Exemplar mailing process as RC.  I want to walk through some of the backend process I had to do as RC to get this mailing out.  Partly to show off the work, and partly because I love feedback – if I am doing something in a really wonky way, ask about it, and I’ll probably fix it.  At the very least I’ll explain why I made the decision so that it’s not just your RC doing random stuff.

I’m going to start fairly far back in the process, so feel free to skip the first section here if spreadsheets make you sleepy.

Addresses and Mail Merges

The Exemplar Team sends out an email to all judges that got nominations in a wave with a google form to fill out with their address.  This acts as a central address collection form for the whole of Exemplar for that wave.

One of the things asked is that this form is not circulated beyond the Exemplar nominees – I think this is an obsolete request, but it makes handling the data a little easier if we have a smaller sheet, so we leave it in.

The Exemplar lead (Bryan Prillaman) produces a sheet with every judge that is getting foils for the wave, with their names, DCIs, region, number of nominations, and the number of foil packets they will get.  (Every packet is the same, but some people end up with more, this is based partly on the number of nominations, but may include other factors, and changes wave to wave.)

This wave, I worked some spreadsheet magic and extracted address from the google spreadsheet that the address form dumps to, and pulled the data live to the foil allocations sheet.  Using a vlookup, I populated the address data to fields for each person.  I flagged each as “listed” or “not listed” based on DCI number entered.  This is a really good unique key, but it has pitfalls.  If someone enters their DCI with dashes, or mis-enters their DCI, it falls down.  I ran the data through a scrubbing process to remove hyphens to try to cut off some of this.  Having an immediate dashboard of who had filled in the form or not was really helpful, I found.  I could see right away who had entered address information and bug specific people to fill out the form.

I also decided to make personalized letters for all the people I was mailing to, including the nominations.  This is precisely the job that mail merges were created for, so I employed Excel and Word rather than Google Docs for this – Docs really falls down on mail merges.

I used the CSV export of the full nominations (which you can get for yourself at the Recognition Windows page) and chopped up the data, and used some shenanigans to push all the recs for a person onto one line so I could use fields most effectively.

I then did a mail merge, dropped the fields into place on a template sheet, and generated the ~40 pages of letters I would need to print.  You can see my needlessly complicated data chopping sheet on the right, and an example letter as well.

Prep for Envelopes, Packing Envelopes

The next step was to make foil packets!  Yay, foils!

I copied the way Steven Briggs built the packets for the Midwest in the mailing he did, because I liked the results.  I am making some changes for next wave, though, which I will talk about in my wrapup.

First, I pulled out cards into pairs, and put two cards back to back in a single penny sleeve, for all ~70 packets I had to build.  I then took each back to back set, and put it in a toploader, so that a single packet would be two toploaders.  I then took the two toploaders that made up a single packet, and used a tiny bit of blue tape to tape the tops together – ensuring the cards would not slide out and making sure the packets stayed together as a unit.

I then prepped a handful of things – I printed the inserts, signed them all, and folded them into 8ths (3 folds), which wraps around a pile of toploaders pretty well.  I printed ready-to-ship address labels and stuck them on bubble mailers, and sat down with a stack of prepped foil packets, labeled envelopes, folded up inserts, and my spreadsheet of who gets how many packets.

I start packing envelopes, using the first name on the insert plus the name on the address, double checking that it matches the spreadsheet, and packing in packets equal to the target number of packets.

I got through the stack of packets, and stack of envelopes, and my target number of packets matched the amount actually packed.  Sweet, I don’t have to tear everything apart and recount!

I then took the pile of envelopes to the post office, and dropped them off during my lunch as ready-to-ship, and they flew away on the wings of magic to their destinations.

What would I change?

I have some changes in store for the next time I do this process.

First, my packing was really heavy.  One of the people in the region got 4 packets, and I did not realize this until I was packing.  I had weighed a single packet, and estimated high for weight for 3 packets, totally not realizing there was someone with 4 packets.  I was wrong for his weight, a little low, and he had to drive to the post office to pick it up and pay the overage.  Totally a newb move, I’m super sorry Lems.

Instead of every packet needing two toploaders, I plan to take a page from Nicholas Sabin and go with team bags – a full packet to a penny sleeve, and all the packets in a single team bag, with a toploader in the bag with them for stiffness, all in a bubble mailer like I used.

I also used the Large Envelope shipping, which I did not realize until after I had purchased did not come with full tracking.  (Rebuying would have been an expensive mistake.)  I should have used Parcel, which gets tracking automatically when purchased online.  (It is slightly more expensive, but I think worth it.)

As far as I know everything got to its destination in less than a week, but the peace of mind of tracking is worth the upcharge.

For the inserts, I plan to leave them as they are in a general sense.  I’m going to use full names from now on.  I had a couple double names, and I had to unfold the insert and look at the nominations to figure out who was whom.  I might play with the way the mail merge lays out on the page – I had several that just barely went off the page, and I was frustrated by this.  It’s hard to predict line lengths, so it might end up just staying as it is.

Well, that’s all I have.  Please let me know if you have feedback, comments on the process, or want to know more, and I’ll try to follow up as I can.

—
Rob McKenzie
Magic Judge Level III
Judge Regional Coordinator USA-North
Minnesota