The Lockbox Data Collection Project

[Arctic Fox]

I told the Clan about this one. I'm grabbing data from all of them to submit

[Stickasylum]

Great to see the positive response to this thead!
Cost per try is how many keycodes you are paying for each item. It's not so much the cost per try that matters, but Drop rate / Cost per try shown in the Efficiency graph, where higher is better. This is a measure of the likelihood of obtaining a orange/purple/blue for each keycode you spend.

As it is currently computed, the "Drop rate / Cost per try" statistic is actually the expected number of drops per 100 keycodes spent. It would probably be more clear to drop the % sign (but keep the multiplication by 100) and change the name to "Average drops / 100 keycodes" or "Expected drops / 100 keycodes".

Also, maybe take out "tries" altogether and just use "cost per item".

Thanks for putting this together! It should really help make the lockbox system more transparent!

[Mustaine]

Reporting in all my T4 boxes.

[Enepttastic]

Just saw this thread. Submitted results from lockboxes I just opened and will try to remember to keep reporting.

[Noupoi]

Glad to see how many submissions we're getting!

As it is currently computed, the "Drop rate / Cost per try" statistic is actually the expected number of drops per 100 keycodes spent. It would probably be more clear to drop the % sign (but keep the multiplication by 100) and change the name to "Average drops / 100 keycodes" or "Expected drops / 100 keycodes".

Also, maybe take out "tries" altogether and just use "cost per item".

I can see how 'try' can be confusing to someone skimming through the spreadsheet. I've changed it back to item instead.

However, I don't agree with your conclusion on 'Drop rate / Cost per try'. 'Drop rate / Cost per try' is calculated as the drop rate divided by the number of keycodes you are spending, which is the drop rate for each keycode you spend. As an example, the current drop rate for a T2 Orange item is 2.96%, and you spent 4 key codes on that item. This means that for each keycode you spent, you got a 0.74% chance of getting an Orange. It's perfectly valid to express the likelihood of a drop, a probability, as a percentage.

Converting from probabilty to actual numbers, if each keycode out of the 100 keycodes gave you an item, you would expect 0.74% (the chance per keycode) of those items to be Orange, which does work out to 0.74 Orange items. Only, the statistic displayed is one of probability (again, chance per keycode), not the expected number of drops (out of 100) as you suggest, so the the percentage sign should be kept. Q.E.D.?

[Armanewb]

Not trying to mess up your data, but you currently have several suspicious entries. Rows 27, 29, 30, 91, 185, 290 all have some ridiculous data statistically (e.g. 10/10/10/10, 0/0/0/10, 3/4/2/9) and may be someone trying to screw with your numbers.

Also, some of the data doesn't add up (may want to implement a validation) e.g. row 27, you'd expect to have a multiple of 3 with a Tier 3 lockbox, but there are 40 items there.

Edit: Did a quick check, 28 entries fail the basic validation. If you remove them, the results look more like:



Problem is, Tier 2 boxes have a low sample so we don't know if they are actually any good.

[dreadburn]

Just added data for my 6 tier 3's and 2 tier 2's. Very nice thread so once I had read it I started collecting data today from my boxes.

Still haven't seen a Legendary yet

[Warjack]

I always buy T3..i think its the best way for keycodes/good loot. Got already two oranges...a Saw machine gun and a bolt action sniper...exactly the weapons i needed!

[Kerry]

Just know that it is far better to get the Tier 2 boxes over the others. As stated on the lock boxes themselves the item drop chances are only uncommon and above.

It would be different if the tier 4 gave a minimum of purple items only.

That is how it works now and I suspect it may get changed as there is absolutely no reason anyone should buy a lockbox above tier 2.

Going on 10 oranges from tier two boxes so far, including shields and grenades. Good luck!

[Noupoi]

Not trying to mess up your data, but you currently have several suspicious entries. Rows 27, 29, 30, 91, 185, 290 all have some ridiculous data statistically (e.g. 10/10/10/10, 0/0/0/10, 3/4/2/9) and may be someone trying to screw with your numbers.

Also, some of the data doesn't add up (may want to implement a validation) e.g. row 27, you'd expect to have a multiple of 3 with a Tier 3 lockbox, but there are 40 items there.

Edit: Did a quick check, 28 entries fail the basic validation. If you remove them, the results look more like:



Problem is, Tier 2 boxes have a low sample so we don't know if they are actually any good.

There are some data entries that look suspect, but I'm not sure if I want to manually delete data, as this would mean that we are being selective we the data.

It would be a good idea to implement basic validation to check that the submitted data is a multiple of the number of items from that lockbox. Being totally honest, I'm not familiar with Google Sheets nor the Apps Scripting language, and am not sure how to best go about doing validation on ranges.

Having said that, this is one of the formulae I hacked together to perform said validation:
=ARRAYFORMULA(SUM(IF(ISBLANK('Form Responses'!E2:E)=FALSE,IF(MOD(('Form Responses'!E2:E+'Form Responses'!F2:F+'Form Responses'!G2:G+'Form Responses'!H2:H),2)=0, 'Form Responses'!E2:E, 0),0)))

There's a sizeable difference between the new 'validated frequency' column and the originals, so I'd rather someone check over my formulae to before I changed the main frequency column in case I screwed something up.

This is my attempt at explaining wth the formula does: Checks if row is for submission of specified tier (in this case, T2), and then checks that the SUM of all the results from that row is a multiple of the number of items in the lockbox. If any of these checks are failed, the total for that row is returned as 0.