Post War Analysis spreadsheet


I developed this in Google Sheets to be able to analyse the performance of the alliance in wars. The names are blurred on purpose.

Scoring the most points in war has to be balanced with how many points are conceded in defence. In the screenshot the player with the 3rd highest attack score came in 12th when the defence points conceded were factored in.

Using the sort buttons at the top of the columns you can easily see who had the best balance of offence and defence (variance), which defence gave away the most points per hit, and more.

The coloured heat map for attack and defence shows how close each hit was to wiping out a team. It shows which defences which soaked up the most flags (which can be good or bad depending on how many points per hit there were). It shows were players are picking easier targets (based on team value) and how successful (or otherwise) those attacks were (white squares usually mean that several opposition heroes remained after the attack). It can also help highlight which players have weaker heroes for their final few flags.

The best thing about the spreadsheet is it is designed to minimise the amount of data entry required. Essentially it is done by taking a screenshot (3 fingered tap on the phone) of each page of war results. Each screenshot is then put through LINE’s OCR and the text copied and pasted into the spreadsheet. Approx 20-30 minutes per war depending on how well the OCR recognises names with symbols. Common OCR misrecognitions can be configured as aliases for alliance players so they picked up successfully on future occasions (these are usually due to spaces or numbers in names).

Not everyone will agree with the need to do this sort of analysis and uneven war matches will always occur that will skew the results. The highest level alliances and low level alliances may not see much benefit either. I welcome people’s thoughts on the use of the war spreadsheet and the use of data analysis for improving performance in E&P in general.

3 Likes

I would also add the @Ssnake survivability rate (Check his Youtube video), because sometime the number of attack on each player can vary and can render the number of points gain against the number of point defended wrong. You already have all the stat to calculate this: (Number of time an attack is survive) divided by (total number of attack).

2 Likes

@Iceman9t9 I’m a huge fan of stats, especially those that deal with defense. Like @Nimppy said (thanks for the tag Nimppy), I would simply add a couple of last columns to calculate the survivability rate which is an easy metric for your alliance members to grasp on how well they are doing against the average survivability rate of the war (I also record the other team as well for extra data). If you want to see how it works and if it would be relevant for you, check out my channel Ssnake Pit Gaming for a few examples and explanations on how it works. I won’t post any videos in here because I don’t want to hijack your post, but they should be easy to find. I applaud you on this undertaking because it is time-consuming but I strongly believe it will ultimately give you an edge in wars down the road. My last bit of advice I guess, is perhaps you also want to record which heroes each player was using. This is also time-consuming but you can then see trends of which heroes work best, and see which members are being lazy and just recycling the same old team that doesn’t perform well. Many people never go back into the war logs so by doing this, you can point out deficiencies (or successes). Good luck Iceman!

2 Likes

A very good suggestion thank you. I will add this.

I will check out you channel for the details Ssnake. The suggestion about the defence teams is a good one too. The time taken to do this is one of the reasons I spent so long on the OCR input and the formulas to parse the input. I have started putting together a text guide for other to use this but it may require a video as well.


All done. The trickiest bit was calculating whether an attack was a one shot kill. Highest and lowest point team values can vary by quite a range. In the example of player 5 I calculate that the hit of 54 was a probable one shot kill on a weak team but the hits of 46 were finishing off a 4 man team.

I do it manually for recording hits and haven’t found it too difficult to sort out if the hit was a one-shot or not if you record the hits in the order they happened. Fun fact: In well over a year of recording, I have not once seen an opening hit of 49 on a fresh team. It doesn’t exist for some reason; no defense team is worth exactly 49 points.

1 Like

Is there any chance, that you make a version available, so I can try it for our Alliance? :heart_eyes:

Yes. My LINE ID is drennai. I can send you the link to the google sheets file if the one below does not appear. Brief instruction in Intro tab but message me if any queries. Link is below and you can save a copy for yourself as this is read only.

https://docs.google.com/spreadsheets/d/1LTsgVhI0s_JxsS2qlBmVOMQlPNOHcEFarcqPjBfoXLA/edit?usp=sharing

When entering the war results in the Input tab just overwrite the one already there in column A6 onwards. The other columns in that tab auto calculate.

I would be interested to hear how you get on.

image001.jpg

image002.jpg

1 Like