Hi All! We are sending out the emails to our winners this week (there's a lot of emails so may take a few days) but I thought it would be good to explain how the winners are being decided and some other interesting stuff from the results so far. Also, you still have a chance to enter as I won't shut down the form until tonight! CLICK HERE TO ENTER

How are we choosing winners? Google Forms will put your entries into a Google Sheet for you, so all the stuff I am discussing here is done in Sheets using formulas or plug ins.

Step 1: Determine how many of each key type to give away

I'm giving away 200 total keys, so the first step is to determine how many keys we should give away based on the key choice response. I want to split the keys up based on the platform and region breakdown but I also need to establish a max keys I can give away in some cases, like Switch where I have only a fixed number of keys and they allocate them regionally. 

To start, let's look at the biggest chunk or requests. About 45% of people want a Switch North America key, which would be about 90 keys total (200 * .45 = 90). Luckily, I have enough keys to make that work so no need to have any complex formula to establish a max keys per type. A simple pivot table should do the trick.

I made a pivot table to show me the totals by key choice (you can google how to do that if curious) and the overall total. then add a column to show what percentage each row is of the total. Then, another column to calculate the key counts to give away. I'll use the ceiling of the percentage to fudge it a little higher in the player's favor (this means I'm going to give away a few extra keys).

Step 2: Establish a random value

OK, now we need a value to use to determine the winner randomly. A random value in a Google Sheet is going to need a formula to calculate it and a value to use as the seed. 

I found a random formula after a little googling that seems to work pretty well. It's meant to work with a date but I can't use it for the submission date because that was too deterministic. Luckily it works well with the row number!

=VALUE(TEXT(INT(((XXX*COS(DAY(XXX))*PI())-INT(XXX*COS(DAY(XXX))*PI()))*1000)+IF(INT(((XXX*COS(DAY(XXX))*PI())-INT(XXX*COS(DAY(XXX))*PI()))*1000)<100,WEEKDAY(XXX)*100,0),"000"))

Now the only issue, is that if I re-sort the sheet, then the row number changes. Fortunately, I can easily use the ROW() function to populate a cell with the row number, then set the value by copying the rows and using CTRL+SHIFT+V to paste it back into the cells.

Now I can reference the column in place of the XXX in the formula above and I have a random value for each person who responds based on the row number of their entry.

Step 3: Determine the winners

The most fair thing to do is to sort the sheet by key choice and then the random value and take the top number of rows according to the number of keys. This is very simple now!

Step 4: Email the winners

I use the Google Sheets tool YAMM (yet another mail merge) to send the emails. It's a really nice tool if you need to send out a bunch of similar emails and you don't want to have to send them all by hand. It will track opens and clicks but isn't as spammy as something like Mail Chimp since your gmail is actually sending all the emails. The tool just creates the messages for you.

Some Other Interesting Stuff

If you filled out the form, you noticed, I asked for "Your favorite turn based tactics game" as part of the entry. As you can tell from the above, neither that nor the mailing list sign up question was in any way involved in the winner calculation. I thought it would be interesting from a few different angles. 

Mainly, I always want to see if I have missed anything in the genre. There are a TON of great games coming out every month and it's impossible to play them all. Plus there were plenty of years in the past where I didn't get to play any games and so I probably missed some classics. This is a great way to get a list of stuff to research. I also am interested in knowing what DOE players say is their favorite game. This can possibly help me in understanding what kinds of features players may like in the next game I make. 

Since this field was a plain text entry, I really need to normalize the data to get it into a format that is understandable on a high level. Basically, I want to group the response data and establish what the most popular games are and also have a bucket of "other" responses. First I created a list of the 40 odd most popular games that appeared in the list. Some games, I grouped by series (Civilization and Divinity for example) while others I separated out granularly (old X-COm, new XCOM, XCOM 2 for example). This isn't really scientific, as I just grouped it in a way that made sense to me.

As you might imagine in a question like this, the "other" ended up being the most popular response overall at about 17%. I'll share the top 10 games or series and then some of the "other" responses.

Now, you might say that adding the 3 XCOM entries (or even new XCOM 1 and 2) would make it the most popular. I agree but to me those games are worth examining on their own since they each do something different. Personally I grew up playing old X-COM but XCOM:EU got me interested in the genre again after not playing much strategy for years. I personally didn't like XCOM 2 as much (at least until WOTC) so to me it made sense to break them all out and see what people thought. 

Here's the rest of the list that had multiple responses: Pokemon, Final Fantasy, Mario + Rabbids, Tactics Ogre, Front Mission, Fallout, Battletech, Shining Force, Jagged Alliance, Valkyria Chronicles , Heroes Might & Magic, Wargroove, Depth of Extinction, Shadowrun, Invisible Inc, Darkest Dungeon, Fallout Tactics, Wasteland 2, Super Robot Wars, Pathway, FTL, Mutant Year Zero, Fell Seal, Banner Saga, Xenonauts, Terror From The Deep, Steamworld Heist, Phantom Doctrine, Overland, Laser Squad, Hard West, Vandal Hearts, Transistor.

Other responses were pretty interesting since a surprising number indicated they don't actually play these types of games. Or maybe DOE was the first one they tried. I hope if they liked DOE that they try some of the other games on the list. Most of them are classic games that shouldn't be missed. It would take too long to list all the other games people mentioned, but suffice to say I will be looking at most of them!

In conclusion, the giveaway was a big success for me and I'm really glad so many people were interested in taking part! It was fun to put this together and figure out how to choose the winners and I hope they like the game!