With over 3000 teams entering the 2014 Fantasy Grand Prix championship, it was a intensely competitive season, but one player, Alex Whittles approached the game with such strategy and method, we’ve never seen before – using his background in Data Analytics to very nearly win the championship. It’s interesting stuff, read on…

The best fantasy F1 team selection across the whole 2014 season was Hulkenberg, Hamilton, Bottas, Mercedes, McLaren, Williams. Which would have netted you 2306 points, before you even get any for predictions. How do I know this?…

On weekends I have two passions; going cycling and watching F1. When I’m not indulging in those I run Purple Frog, a Business Intelligence Consultancy. ‘A what?’ I hear you say… Don’t worry, I get that a lot. Think data, lots of data. You may have heard the latest buzzword ‘Big Data’ being banded around everywhere you look. Business Intelligence is about mastering control over data; turning lots of complex data into knowledge, and helping organisations understand what’s happening, and helping them to make better decisions.

FrogMercF1

Let’s rewind back to March 2014, and a friend invited me to join the Badger GP Fantasy F1 league. I didn’t need asking twice, I signed up and started planning my dream team. With over 25 years of armchair-based F1 expertise this should be a walk in the park shouldn’t it?

But then I started look into the complexity of it. Just choosing 3 drivers from 22, and 3 constructors from 11, there are over 1/4 million possible team combinations. Only 168,000 of these are within the £75m budget, so we have to spend a lot of time juggling our team to get under budget.

The points awarded for overtaking add a huge element of complexity, and adds non-intuitive combinations to the mix such as Max Chilton; Max never finished higher than 13, but his consistency in finishing gave him up to 8 net overtakes per race, or 24 points – almost as much as a win.

So this is where I thought I should combine my passions, let’s see what business intelligence can do to help improve my team selection and help me to glory.

Watch out, here comes the science…. If you don’t care much for techno-babble then skip to the results and analysis below.

First of all we need a source of raw data. For this I used ergast.com. They provide a great API (application programming interface) by URL, so that we can ask for all results from the entire 2014 season by just querying the URL http://ergast.com/api/f1/2014/results?limit=1000, returning an XML dataset. We can also query the qualifying positions, pit-stop times etc. just using different URL combinations.

Using PowerQuery, part of the Microsoft PowerBI tools in Excel 2013, we’re able to pull this data into PowerPivot, which is an analytics data model also within Excel 2013. If you’ve heard of data cubes or QlikView, PowerPivot is akin to having a cube inside Excel.

DataModel

In the data model we have the drivers and teams, the number of points per finishing position, the qualification and race results. We also have a table called TeamSelection, which contains every possible combination of drivers and constructors within our possible teams. After a day of writing some nifty DAX calculations (think Excel calculations on steroids), I had created myself my own F1 team selection engine.

I then created some dashboards and reports using Excel and PowerView, another part of the Microsoft PowerBI stack. We don’t have time to go into the technicalities of the data model and calculation engine here, so I’ll refrain from geeking out too much and will jump straight to the end result with a few screenshots and some season debrief analysis.

Results and Analysis

Top 10 teams

Top10Teams

We can see that over the course of the entire season, Hulkenberg, Hamilton, Bottas, Mercedes, McLaren & Williams was the team selection of choice. It was the number 1 ranked team for the entire season as well as just the last 5 races. However just in the last 3 races it was only ranked 19th With 468 points. Over just the last 3 races, the top points went to Ricciardo, Massa, Hamilton, Mercedes, Williams, Marussia with 501 points. Interesting seeing as Marussia didn’t even take part in the races. Note that this isn’t shown in the screenshot as this combination is only ranked down in 54th over the whole season.

So with our #1 team, how did that selection fare over each race in the season?

RankOverSeason

Interestingly this team combination is ranked 100-10000 for most races, so there were better choices per race, but it had better consistency over the entire season than other teams.

Teammate comparison

The analytics model also provides comparison between drivers.

MercedesDrivers

This clearly shows that Lewis’s championship win was thoroughly deserved, and Lewis was also a better point scoring choice than Nico in the fantasy league. Helped in no small part by his overtaking genius in Silverstone, Germany and Hungary.

If we swap out the drivers for Max Chilton (£4m) and Kimi Raikkonen (£18m) we can see, from a fantasy league perspective, that Max held his own throughout the season, scoring an average 8.63 points per race compared to Kimi only scoring 10.05, but at 4.5x the cost. Go Max!

MaxKimi

Results

So where did this analysis get me this season? Well at one point I was ranked 9 in the world. At that point I got complacent and I didn’t update my model or team for a number of races. I plummeted down to 300 at which point I put some effort back in and I recovered back to 176 by the end of the season. Next season I shall not make the same mistake. Victory shall be mine!

More about Alex

AlexWhittlesAlex is the owner and principle consultant at Purple Frog Systems, a SQL Server Business Intelligence consultancy in the UK with multinational clients in a variety of sectors from banking to aerospace. He has an MSc in Business Intelligence, and is a regular speaker at many data conferences around Europe.