Quizzical Vizzical

makingof

Let’s get vizzical, vizzical! I wanna get vizzical! I think that’s how the song goes anyway.

logo_optaSome of you may know a company called Opta. They’re the leading provider of sport data in Britain and provide feeds to just about everyone, including major broadcasters, sporting clubs and governing bodies. What they don’t know about data isn’t worth knowing.

optaquizAnyway, I know what goes on there as my brother Simon is their Director of Marketing. And one of their main industry events is a twice a year football quiz event where they invite all the big players from media, online, broadcast and other experts to get together and battle it out. The questions are ultra-fiendish and the competition fiercely fought. It’s a very popular event.

So my task was to viz up the results from the last quiz and see if we can infer some trends and have a bit of fun.

And here it is. The 4th OptaJoe Football Quiz results viz.

optaviz

So how’d I do this?

1. Data

For a change this was the easy bit! A spreadsheet of questions, teams and the scores for each team. I used a very simple Alteryx module to transpose the data into the correct format, but that was a 2 minute job.

That gave me a data structure like this. Perfect for Tableau.

datasheet

2. Viz design

This was the hard bit. I needed this to be clear, accurate and look smart. Opta plan on using this viz as part of the promotional material for their next quiz so it needed to be spot on.

Top Panel – I usually put the focus of the viz in this area. And the focus is obviously the result. I used one of my favourite views, the dual axis bar chart with shape terminator to show the results.

Bottom Left – I wanted to show how the standings were fluctuating as the rounds went on. For example, did Sky Sports run away with it, or did the lead change hands multiple times? Who finished strongly and who faded at the finish line? Did that with a simple line chart. Not so effective with all the teams selected but as with many views, very effective when only a subset is selected.

Bottom Right – Now I love this view. This is a matrix using the rank calculation to show how each team ranked per round. It works great with the colour coding and allows you to easily see the teams that were constantly good or bad. It also adds an element of colour to the viz.

rankTake The Guardian for example, who clearly would have won the quiz but for a terrible round midway though the event where they ranked 17th.

You can also see how some teams actually won a couple of rounds, like the Mirror, but also had some shockers that cost them a decent overall finish.

Font – Now Opta have a very well defined brand identity, and part of that brand is the typeface they use. I managed to identify it as “Chalet Paris NineteenSeventy“, which was as close as I could get to their font. I managed to download this font and install, which allowed me to select it as one of the usable fonts in Tableau. Unfortunately I got inconsistent behaviour when publishing to Tableau Public so the end result isnt as good as I would have liked. Any report consumer would also have to have that font installed to get the full effect.

You can go grab the font from here if you want.

Filtering – The chosen filtering was determined by the intended use of this viz. For example, this viz is likely to be shared amongst the teams to generate banter and a bit of a laugh at how well / badly one team has performed against one of their industry rivals. So I decided to group up the teams by industry sector, to allow a direct comparison within a couple of clicks.

filteredviz

Take this example of the viz filtered to show just the TV companies taking place. You can see the final outcome, round by round progress and comparative rankings per round. It’s even better when you clear the round selector and iterate through the rounds, effectively replaying the quiz from the start. Very cool.

So there you have it. I’m well pleased with this one. I’ve never tried to vizualise quiz results before and I think this format works well. It’s a good example of the viz design being heavily influenced by the audience and what I imagined they’d do with the viz in terms of generating banter, chat and competition in the pub.

For more information on the OptaJoe Football Quiz, here’s their live blog from the event. And here’s some more info on the participants.

Oh and if you think you can do better than some of the teams then here are the questions. Good luck!

Questions 1st half

Questions 2nd half

Regards, Paul

Advertisements

Snowy Sports – Winter Olympics Dashboard

makingof

Hey viz fans.

I’m baaaaack. Thanks to everyone that send good wishes for my shoulder surgery last week. It was a frustrating few days so I thought I’d get vizzical as quickly as possible.

I’m gonna start this post with a caveat – Don’t treat this viz as authoritative! Please! I had some massive challenges with the data and tried a few things for the first time so I just can’t guarantee that it is as accurate as some would like. As with most of these vizzes, they are as much to showcase the capabilities of Tableau / Alteryx as they are intended to be informative. If you’ve got an issue with that then proceed no further.

I love sport. All sport. Especially the Olympics. And as you will know we are coming up to the start of the 22nd Winter Olympic Games in Sochi, Russia.  So I thought I’d viz up the medal history going back to the first competition in 1924.

And here it is!

sochidash

So how’d I do this?

1. Data

Now this was *almost* a great success story. Let me explain.
All the medal history was held at http://www.databaseolympics.com, see the Winter Olympics section. The trouble is, you have to navigate through a couple of levels to get to the sports you need, there’s no way to view all the results in one go. When you do get to the data it’s in a nice format for copying into Excel, see below.

data

If only I could get it all into one place. Then I had an idea.

First off I managed to get a list of all the base level URLs I needed to cover each sport for each games. There were about 90 of them. e.g. www.databaseolympics.com/sport/sportevent.htm?sp=ALP&enum=110

Then it was dadda!! – Alteryx time. Let’s see what you got eh. Erm… Well I was pretty confident that this could be done but I’m far too inexperienced with the tool to get anywhere. Luckily a good friend of us at VN is the current Alteryx Grand Prix Champion – Chris Love (@chrisluv) of The Information Lab. I thought I’d see if he could solve the issue.

This is what he sent back to me… Turned out to be fairly tricky.

lovemail

And here’s the Alteryx module that cracked it. Happy to send it to anyone if you want to take a closer look.

alteryx

Absolutely awesome. I’ll be honest, I don’t yet fully understand how it works but I’ll be looking into that in more detail. I’m a little disappointed that Alteryx doesn’t seem to do web queries a little easier, nor can it process html files (even though it’s ok with xml).

So there we are – almost. I now had good data for all competitions up to 2006. Trouble is there was no data on that website for the Vancouver 2010 event (argh!). I did manage to locate the information from Wikipedia but it was a totally different format and had a number of other variances from the other data.

Another caveat is that I’ve treated every team medal won by an individual as an individual medal. For example, the bronze medal won by the GBR Ice Hockey team in 1924 counts as 10 bronze medals – not strictly accurate I know.

I managed to use Alteryx again to transform some of the 2010 data into a better format but I couldn’t get away from the fact that I had to do a lot of manual crap to get the info into the format I needed. With that, there are sure to be some mistakes – hence the above caveats.

Anyway….

UPDATE: Due to some pretty bad data quality issues with the source detailed above (missing whole events etc), I switched to this alternative. It looks to be a lot more accurate but again I can’t vouch for the quality. Let me know if you see any issues.

http://www.olympic.org/medallists-results

2. Viz design

Timeline – Quite like this view. Simple plot using an Olympic Ring image as the mark, plotting the year of the competition.

saraOne thing I really wanted to do was to represent the official flyer of the competitions and have that change with user selection. I’ve been meaning to try out Shawn Wallwork’s tip on how to do this. http://community.tableausoftware.com/thread/119079.

In summary it’s a tip for creating a dynamic background map, based on user selection. Check it out. Very smart.

Filters – I then created a couple of worksheets to act as filters for Sport and Medal type. Simple enough, using shapes. Needed to make sure the medals were consistent in size and style.

pictoFor the sport selector I used the official pictograms for the Sochi games. I think they’re awesome and look perfect for this style of viz. I positioned them centrally to act as a central selection panel. Love the look and feel of the images themselves. Give it a bit of use as filter action and job done.

medalsMedal Table & Individual Records – These were simple enough. The only cool bit was the use of small medal shapes for the gold, silver and bronze medals. Looks smarter than a basic text or highlight table.

Final touch was to add an athlete wildcard search, and then a mouseover info sheet to highlight the caveats discussed in the post. I also added a country code custom list filter so you can compare how one nation has performed compared to another.

3. The Finished Product

sochidash

So there you go. A viz of the Winter Olympics. Fun to do that one and great to be back in vizzing action. Lots of ways this one could be improved but I didn’t have the time on this occasion.

Regards, Paul

FIFA Ballon d’Or 2013 Dashboard

makingof

Hello there everyone!

Thanks to everyone for the great feedback on my recent Pinball viz. Was a lot of fun doing it, and glad it brought back some youthful memories for a number of people. I wasn’t intending to produce another viz so soon, but this one is fairly timely.

See I love football. I mean LOVE it. For some reason I’m still playing every week in the Surrey Sunday leagues, my 21st season. The old bones creak a bit these days and I’m not as quick as I once was, but the enjoyment is still there. So like many I watched with interest as the recent World Footballer of the Year awards were announced. Sorry I’d better say the FIFA World Footballer of the Year awards in case Mr Blatter is watching.

The actual results weren’t too much of a surprise. Ronaldo, Messi & Ribery making up the top 3. But I wanted to see how those votes broke down and see if I could pull out any trends using Tableau. Especially as there has been much chat about whether these votes are a bit of a farce or otherwise.

And here it is!

dordash

A viz of all the votes, showing geographical distribution, points breakdown per player and voter type (coach, captain or media).

So how did I do this?

1. Data

Data was pretty easy to get hold of. In a rare display of process transparency FIFA publish it here. Each voter names their top 3 players from a short list with 5 points given for first place, 3 for second and one point for third. Total points wins the trophy. Simple.

dordataWell sort of simple. See this format has the player name as a measure, I want it as a dimension. Copying this data into Excel for analysis wasn’t a goer, it just didn’t seem to be manageable with Tableau in that format.

reshaperSo this gave me the chance to try out this Excel Tableau “data reshaper”. I’ve heard other people talk about it but never used it myself. Simple enough to install and bingo – worked first time.

formatteddataMy data now looked like this, with the player name as a dimension. Worked fine when connected to Tableau. Perfect.

2. Viz design

I really wanted this viz to show the following

  • Geographical distribution of votes
  • Is there a trend of votes between the 3 voter types
  • Any oddities in voting?

With that I made the focus of the viz a map with pies. Now I know this looks a bit cluttered when all the marks are visible, but it does seem to work when the set is restricted with filters or even highlights.

The player filter allows me to easily see where each player’s points came from. The points calculation was done with a simple calculated field to assign either 5, 3 or 1 point to each player’s SUM(Number of Records).

I like highlight tables so I thought I’d provide a full points breakdown. This is more interesting when combined with a filter.

Regarding the regional grouping, that was manually done. Could have split it into ‘official’ FIFA regions I suppose but didn’t have time.

3. The Finished Product

FIFA Ballon d’Or 2013 Dashboard

dordash

What’s this telling me?

There are a few interesting trends that this viz allows me to infer.

mediaFirst off, although Ronaldo won the award, you can see that he got a large portion of his votes from captains and coaches. When I select media as the filter then you can see that Ribery easily won the vote of media representatives, except in the Americas region – kind of expected given that Messi is Argentinian. That’s an interesting finding as FIFA controversially extended the voting deadline to allow Ronaldo’s awesome performance against Sweden to be considered, a move that many felt cost Ribery the award. Ronaldo only topped the vote for one region, Europe.

It’s also easy to see that there was a lot of national favouritism. I.e. assigning points to the player of your own nationality even thought they are clearly not one of the favourites. Examples include – Zlatan Ibrahimovic, Gareth Bale, Yaya Toure, Radamel Falcao, Andrea Pirlo, Robert Lewandowski & Luis Suarez – all of whom received significant points from their home nations.

persie

Lotta love for Robin van Persie in the Carribean (see left).

It was also interesting to see that neither Ronaldo nor Messi voted each other in their top 3. Understandable I suppose, if a little childish.

I can also see a surprising amount of countries that didn’t bother to send in all their votes. I’m looking at YOU Turkmenistan.

So this voting does seem a little contrived and a bit like the Eurovision Song Contest at times. Clear favouritism & agendas at play.

Ok that’s it. As always feedback appreciated. And I know it’s not perfect, so don’t take it too seriously.

“FIFA – For the game. For the world.” – Yeah whatever Sepp.

Regards, Paul

That deaf dumb and blind kid…

makingof

#He’s a pinball wizard there has to be a twist. A pinball wizard, got such a supple wrist.. do do de do… How do you think he does it? What makes him so goood?#

Good morning all. I don’t start every post with a song but at the moment I can’t get that tune out of my head. And it’s no surprise given the subject of the post.

Peter Gilks recent viz about video games consoles got me reminiscing back to the heady days of 8-bit gaming and all those days I spent in dodgy arcades in the North-East of England. And I got thinking about all the great pinball tables I used to play, both back then and later at University. So as with most thoughts us BI folk tend to have, it gravitated into a Tableau viz!

And here it is.

pinballviz

A viz of all pinball tables where more than 1000 units were manufactured. The timeline shows release date, coloured by manufacturer. I’ve also pulled a table rating and the exact number of units produced.

So how did I do this?

1. Data

pinsearchData is obviously the key to any viz. And luckily the folks at The Internet Pinball Database have a detailed repository, easy to query and returns the results in a format that I can easily get into Excel. So step 1 was to use their search functionality andsearchresult pull out a list of all tables with >1000 units produced and copy that into Excel. Simple enough. I was hoping to locate a really detailed datasource and given the fact loads of people take their flippering very seriously it wasn’t a surprise to find this resource.

2. Viz design

Now I knew from experience that pinball had a “golden age”, was hit by the advent of video games and then made a bit of a comeback. But would that be reflected in the data? So my first view was a timeline by year, coloured by manufacturer to show the big players like Williams and Gottlieb etc.

That was simple enough to create but I wanted to give it a kind of 8-bit graphics feel, like you’d get on a table display, and that was done with a stacked bar chart, carefully choosing the colours and border around each cell. I really like the look of it, especially from a distance. Works great when using the manufacturer highlight also.

The other 2 views were self explanatory enough, highlighting the ratings and units produced. I had planned on limiting them to the top 20 tables so I didn’t have annoying scrollbars (I hate those things), but a couple of people I showed it to said they’d like to see more detail. Let me know what you think.

#Aint got no distractions. Can’t hear those buzzers and bells. Don’t see lights a flashing. Plays by sense of smell. Always gets a replay, never seen him faaalll… # Sorry – there it goes again… Be gone Daltrey!

3. Filtering

I wanted to highlight and filter according to manufacturer. Highlights look great on the timeline with all those little squares lit up like a video game, but it would also be nice to filter the whole sheet by manufacturer to tell their own story through the years and view their top / most produced tables. This was achieved by allowing the tables by manufacturer chart to double up as a dashboard filter.

I also added a filter for machine types, so you can go back the really old-school days of Electro-Mechanical tables, before they all became Solid-State.

4. Graphical Grief

I really wanted to get some more graphics into the viz, pictures of tables etc, but ran into two issues.

Firstly I did manage to find all the images on ipdb.org, but there were far too many to use as custom shapes or icons. So I figured that I’d be able to have a web page object that linked back to the image at source, displaying the table whenever it was selected by the user. That would be ace.

All I needed was the URL path for one image for each table. Now I’m sure as hell not gonna manually copy the URLs into the datasource so managed to achieve this with the help of a colleague (@GlenRobinson72) who knocked up this VBScript.

ipdbscriptIt works by taking a text file of pinball table names, and for each one it connects to ipdb.org, searches for the table, finds an image file and parses the URL, which it returns back to me. I then manually copy that into the datasource csv as a dimension. Dead easy. It doesn’t always get the best image for each table but that’s less of a concern.

Then the next step was to create a dashboard URL action, that referenced the new photo URL dimension.

And finally a web page object that dynamically changed URL destination based on what table was selected.

imageproblemThat works great. BUT – the web page object has no control over the size of the image returned. I’d wanted each image to resize into the web page object but what ended up happening was that only a tiny portion of the image was displayed with huge scrollbars. I was unable to find a workaround to this.

For example, see the image in the bottom right corner of the screenshot (left).

I did find a compromise. I removed the line in the script that stripped the “tn_” from the URL and this meant that the thumbnail image was returned. Too small to be of real use but more workable than the large image.

If anyone knows a way of getting around this then let me know. It would have been the icing on the cake for this viz. I suppose I could have downloaded the images locally, resized them manually and then used them. I wonder if Alteryx could be used to solve this problem?

5. If I’d had more time

I managed to find a data set of world’s best pinball players and their ranking points totals from 2013. That would have been good to represent, and maybe correlate with their favourite tables. The data is at http://www.ifpapinball.com/ if anyone is interested.

I also wanted to represent the World Record score for each table, but wasn’t able to find that data.

The Finished Product

I’m well pleased with this. In particular the way the colour matrix works for the timeline chart. Looks funky.

http://public.tableausoftware.com/views/PinballTables/PinballWizardry#1

And there are a surprising number of trends to infer from the data. You can see the golden age of pinball from the mid 60’s to late 70’s, when the impact of Space Invaders and Pac-Man led to a crash in table production. And then the mini-revival in the early 90’s, which is when I ploughed loads of my student grant into them. Stern are still producing tables to the present day but these are in lower numbers than 1000 so are not included in this dataset.

Looking at ratings you can see Gottlieb have by far the biggest presence at the top of the ratings chart, even though they didn’t produce individual tables in huge numbers. But they clearly have a cult following as the ratings suggest. Williams and Bally seemed to go for large numbers, consistently across the golden age, with Williams continuing into the 90’s revival. Of course none of that would have been possible without the early pioneers like Chicago Coin and Genco.

No surprise to see The Addams Family as the top produced table. That thing was everywhere. I never got on with the magnetic madness on that table although everyone else seemed to love it. I’ve called out my favourite table in an annotation. As far as I’m concerned it was directly responsible for me not getting a 1st in my undergraduate degree so has a lot to answer for.

So there it is. Hope you like it. Feedback invited as always. And don’t buy that stuff about the deaf dumb and blind kid. I’ve tried playing blindfolded….

Regards, Tommy – agh sorry!

Mind the gap

Good day viz fans!

Hoping you’re ok this fine morning. What’s that? How am I? Well glad you asked cos I’m annoyed. Very annoyed. You see nothing annoys me more than a bad viz and I’ve finally had enough. Here’s why.

Trekking into central London on our beautiful commuter service is a joy. Actually it’s not, it’s a colossal pain. But anyway, it gives the brainy marketeers of all sorts of companies a chance to display their wares in more elaborate and interesting ways. And that’s ok. There are hundreds of meters of blank wall to slap a fancy advert on, or let us know some cool fact or bit of info that’s going to make our day easier.

20131115_164924And there are lots. Adverts for the latest technologies and fancy holidays all of which are good enough and inoffensive, sometimes very well done. And then there’s London Underground. Now they own the damn place, so can display pretty much what they want, where they want it – you know for maximum impact. But what do we get. We get this (pictured left).

“Delays cut by 40% (Whoosh!)”

Well that’s inspirational. Really. London Underground get a load of grief for delays and such like, and there’s no doubt that a 40% improvement is something to shout about. SO SHOUT ABOUT IT! Tell me what you’ve done. Where are the biggest improvements? The Northern Line? The Jubilee Line? What time? Rush hours? What have been the benefits? Even draw me a little infographic or something. Anything more than a meaningless logo and a stupid tagline. What a wasted opportunity to really boast about the work you’ve done to the network to someone who walks past this poster 4 times every day.

And there’s more. Here’s another example.

20131114_165804This time banging on about their range of informative Twitter feeds. That’s great. Getting some up to the minute Tweets about the service would be just the ticket. Let me see – I’ll follow erm… and erm… ah.

So how much effort would it be to display what those hashtags or accounts actually are? I do quite like the graphic though I’ll give them that.

Utterly lazy and completely missing the value of the interested audience of tech-savvy people who pass by this poster hundreds of times a year. Sure I could go to the URL they’ve specified but still….

20131115_164857And here’s the final one. I don’t even think I can be bothered to comment on it as it makes my blood boil.

I often wonder how many of my fellow commuters are thinking the same thing as they walk past these non-information posters. We’ve all got a thirst for INPUT – I’m a total Johnny 5 these days, so I see things like these posters as massive wasted opportunities to put a positive message or useful information right in my face so I can’t avoid it. Years ago I would probably have been offended by that approach, now I’m annoyed that they haven’t at least tried.

Especially for an organisation that gets endless grief for performance. You’d think they’d be all over it. Clearly not.

Ah well that’s it for the rant. Have a good day everyone, and remember to use all available doors.

Regards, Paul

Social Data Day @ Google

Hello Tablegions,

I’ve just spent a very enjoyable afternoon at the shiny and brightly coloured offices of Google in the City of London.

20131126_132946The event was part of Social Data Week (#sdday) and was billed as a discussion about social media, data analytics and such like with a product demo of Tableau from Andy Cotgreave (@acotgreave) as well as a couple of panel discussion sessions.

Here’s how the afternoon panned out.

The event was hosted by Rob Easton (@robeaston33), Head of Enterprise Cloud Platform at Google, and he opened up with a brief introduction before handing over to Tim Barker (@timbarker) of DataSift.

tbarkerI wasn’t aware of them prior to this session and Tim laid on an excellent introduction to their services and then a demo. DataSift suck in massive volumes of data from social platforms, filter out the crap and irrelevant content and then feed that to big enterprises who use tools like Tableau to perform their analytics. It was amazing to find out that a single tweet contains about 140 different data points that DataSift can report on. Sounds a really good tool. Tim was also super chatty in the breakout sessions and provided some great insight into their business.  “It’s not information overload, it’s filter failure”.

Next it was over to Rishi Kumar of Unilever (@Rishi_NK) to discuss how they’ve used data analytics tools and in particular Tableau to achieve their business objectives. It featured how Rishi’s work has allowed them to gain valuable insight into data on social platforms. Very interesting stuff. “Data trumps opinion” was one memorable quote.

Then a bit of a panel discussion where Rishi was joined by a couple more guys including Nathan Sage of PA Consulting. Unfortunately I can’t recall too much about the Q & A session but there were a good few questions from a very engaged audience.

bradkNext up was Brad Kilshaw (@bradderskilshaw), Google’s head of Social, who gave us a tour of the Google portfolio of social tools. He seemed very confident in Google’s ability to be the best in all areas. I’ve got some opinions on that which I’ll elaborate on in a future blog, and also around the area of Platform as a Service which is a big topic in my area at the moment.

andyc

The final presenter was the always entertaining Andy Cotgreave (@acotgreave) of Tableau. He gave a demo of the tool and how easy is to turn data into insight. I’m interested in what people thought that have not had any previous Tableau experience. Let me know.

Then it was the final wrap-up panel discussion and again more cool questions.

All in all a good afternoon. I look forward to future sessions.

Cheers, Paul