![\"What](\"http:\/\/blogs.magicjudges.org\/usanorth\/files\/2016\/04\/2016-04-13-10_45_55-Exemplar-Wave-4-Address-Form.png\")
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. \u00a0This acts as a central address collection form for the whole of Exemplar for that wave.<\/p>\n
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.<\/p>\n
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. \u00a0(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.)<\/p>\n 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. \u00a0Using a vlookup, I populated the address data to fields for each person. \u00a0I flagged each as “listed” or “not listed” based on DCI number entered. \u00a0This is a really good unique key, but it has pitfalls. \u00a0If someone enters their DCI with dashes, or mis-enters their DCI, it falls down. \u00a0I ran the data through a scrubbing process to remove hyphens to try to cut off some of this. \u00a0Having an immediate dashboard of who had filled in the form or not was really helpful, I found. \u00a0I could see right away who had entered address information and bug specific people to fill out the form.<\/p>\n I also decided to make personalized letters for all the people I was mailing to, including the nominations. \u00a0This 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.<\/p>\n I used the CSV export of the full nominations (which you can get for yourself at the Recognition Windows page<\/a>) 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.<\/p>\n 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. \u00a0You can see my needlessly complicated data chopping sheet on the right, and an example letter as well.<\/p>\n The next step was to make foil packets! \u00a0Yay, foils!<\/p>\n I copied the way Steven Briggs<\/a> 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. \u00a0I then took each back to back set, and put it in a toploader, so that a single packet would be two toploaders. \u00a0I 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.<\/p>\n I then prepped a handful\u00a0of things – I printed the inserts, signed them all, and folded them into 8ths (3 folds), which wraps around a pile of toploaders pretty well. \u00a0I 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.<\/p>\n 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.<\/p>\n I got through the stack of packets, and stack of envelopes, and my target number of packets matched the amount actually packed. \u00a0Sweet, I don’t have to tear everything apart and recount!<\/p>\n 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.<\/p>\n I have some changes in store for the next time I do this process.<\/p>\n First, my packing was really heavy. \u00a0One of the people in the region got 4 packets, and I did not realize this until I was packing. \u00a0I had weighed a single packet, and estimated high for weight for 3 packets, totally not realizing there was someone with 4 packets. \u00a0I 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. \u00a0Totally a newb move, I’m super sorry Lems<\/a> Instead of every packet needing two toploaders, I plan to take a page from Nicholas Sabin<\/a> I also used the Large Envelope shipping, which I did not realize until after I had purchased did not come with full tracking. \u00a0(Rebuying would have been an expensive mistake.) \u00a0I should have used Parcel, which gets tracking automatically when purchased online. \u00a0(It is slightly more expensive, but I think worth it.)<\/p>\n 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.<\/p>\n For the inserts, I plan to leave them as they are in a general sense. \u00a0I’m going to use full names from now on. \u00a0I had a couple double names, and I had to unfold the insert and look at the nominations to figure out who was whom. \u00a0I 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. \u00a0It’s hard to predict line lengths, so it might end up just staying as it is.<\/p>\n Well, that’s all I have. \u00a0Please 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.<\/p>\n — Rob McKenzie writes about the process for mailing Exemplar foils for the North region for Wave 4.<\/p>\n","protected":false},"author":69,"featured_media":29,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[17],"tags":[],"language":[],"class_list":["post-136","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-exemplar"],"_links":{"self":[{"href":"https:\/\/blogs.magicjudges.org\/usanorth\/wp-json\/wp\/v2\/posts\/136","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.magicjudges.org\/usanorth\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.magicjudges.org\/usanorth\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.magicjudges.org\/usanorth\/wp-json\/wp\/v2\/users\/69"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.magicjudges.org\/usanorth\/wp-json\/wp\/v2\/comments?post=136"}],"version-history":[{"count":25,"href":"https:\/\/blogs.magicjudges.org\/usanorth\/wp-json\/wp\/v2\/posts\/136\/revisions"}],"predecessor-version":[{"id":180,"href":"https:\/\/blogs.magicjudges.org\/usanorth\/wp-json\/wp\/v2\/posts\/136\/revisions\/180"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blogs.magicjudges.org\/usanorth\/wp-json\/wp\/v2\/media\/29"}],"wp:attachment":[{"href":"https:\/\/blogs.magicjudges.org\/usanorth\/wp-json\/wp\/v2\/media?parent=136"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.magicjudges.org\/usanorth\/wp-json\/wp\/v2\/categories?post=136"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.magicjudges.org\/usanorth\/wp-json\/wp\/v2\/tags?post=136"},{"taxonomy":"language","embeddable":true,"href":"https:\/\/blogs.magicjudges.org\/usanorth\/wp-json\/wp\/v2\/language?post=136"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"A](\"http:\/\/blogs.magicjudges.org\/usanorth\/files\/2016\/04\/2016-04-13-11_04_09-Foil-Allocation-By-Region_Judge-Google-Sheets.png\")
<\/a><\/a><\/a>Prep for Envelopes, Packing Envelopes<\/h2>\n
<\/a><\/a><\/a><\/a>What would I change?<\/h2>\n
\nRob McKenzie
\nMagic Judge Level III
\nJudge Regional Coordinator USA-North
\nMinnesota<\/p>\n","protected":false},"excerpt":{"rendered":"