Part 18 – S1 C1 P17 – Analyse That

This will be the last post in this little block of Nerdyness – I will be getting back to the Strollers for September in game before doing an analysis blog of the games up to the start of October – We’ve only played 3 league games so far so the stats are pretty thin to this point.

Today I want to show you how you take the attribute scores we now have and calculate how well the player fits certain roles based on the Key and Preferable attributes for that role/duty.

Firstly though lets look at the Attributes themselves, To do this I’ve have written a simple query, encapsulated in in a view (A view is a query that acts like a table – it is recalculated every time you query against it).

What we can do now is do a simple SELECT against this view to give us the results back in the SSMS results window. The Create script is here: http://www.mediafire.com/file/rgtrmfo1l200uot/attribute_role_relation_view.sql/file

The Power of this though is looking at individual attributes. For instance for what role is Passing a Key Attribute.

So with a really simple query we see 23 roles where Passing is Key. We can do a count of the roles to see what attributes are key the most to least (For Outfield Roles)

Without running the numbers would you have said Off-the-Ball would have appeared in the most roles as a Key Attribute?? I would have said Passing. This may just be of interest, but I’d be tempted to look for players now with solid Off The Ball and Decisions regardless of where they play as you’d give yourself a lot of flexibility. There are only 43 distinct roles – Off the Ball, Decisions & Passing are Key in over half of them….Indeed of the top 10 6 are Mental attributes, 4 Technical – Indeed the highest Physical is Acceleration in 13th. This is why LLM is so difficult 🙂 We tend to have OK Physicals, so picking people with good numbers for at least a few of these top 10 attributes is vital.

Interestingly Anticipation is the most numerous Preferable attribute (27 out of 43 roles) – it shows that the difference between a good player and a really good player is on the Mental side in FM IMO.

The main thing I look at though with regards to the attributes is how my players relate to the player roles from a points-out-of-20 standpoint.

I’ve created another view – script here: http://www.mediafire.com/file/k5sa2y7jqgw7xam/players_dataset_view.sql/file – that pulls out the score against each role_duty for all the players in the current batch. Writing relatively simple queries can get you some interesting info out:

Above we are looking at all natural Wide Attacking Midfielders plus their overall score (Sum of all key and Preferable attributes) for Winger & Inside Forward on Support. Civil Service Strollers has 4 natural Wide Attacking Midfielders (AML or AMR, or AMRL). Chris Barras is the best Inside Forward on Support from an Attribute standpoint at the club currently with a 39.6% (last column on the above) score, or an average across all 15 attributes of just under 8/20). Andrew Mackay is the best Winger on Support at the club (45%, 9/20). Only one of our WAM’s has a strong or above right foot (Alieau Faye) so playing an IF on the right and a Winger on the left works from a depth perspective.

You can then add a little bit more code to make a report telling you the top 5 players in each role at the club, for their natural position and AM – assumed best role, best position only and for non-natural positions.

And this report looks something like this:

In the report itself there are filters meaning only the best 5 players are shown per position (If there are 5 players who can play in that position/role), there’s also sorting and as you can see conditional shading added for players who are 1st in their position or have scores over 50 (10/20). Big Kev Waugh averages 12’s in the key areas for Central Defender on defend – that’s why he’s such a beast at Lowland league level. We see our Wingers at the bottom from the previous example – Andrew Mackay is the best Winger on Attack, Alieau Faye the best winger on support when looking at just Key attributes. However Andy Mair is the best Winger on Attack and Andrew Mackay the best on Support when looking at both Key and Preferable (So more well-rounded). All-in-all you can gain some insight into how your team ticks from a purely attribution standpoint. When you get enough statistics though you can start running more complex regression analysis (I wont be doing this for a good few years or so in game)

The SSRS rdl file for the above is here: http://www.mediafire.com/file/q72tbvobgvjz5xx/Player_Profile.rdl/file

I hope these posts have been of some interest and will give you some ideas around getting data out of FM and using it. I will expand on these posts over time (When I get to October 1st in-game I’ll show you how to automate the load of the raw data so the file name doesn’t have to be the same all the time by using a ForEach Container in SSRS for instance) but for now back to the Lowland League 🙂