Wallpapering Fog

Adblocking is asymmetric warfare and publishers can't win

noreply@blogger.com (Neil C) — Tue, 16 Aug 2016 10:57:00 +0000

For those of us who like to keep an eye on developments in web advertising blocking (adblocking), there was a flurry of excitement last week as Facebook announced that it had designed and released a way to defeat the blockers. Adverts inserted into Facebook's news feeds have been made harder for an adblocker to tell apart from regular posts by your friends. If the blocker can't spot the ad, it can't remove it.

This has been coming. Various companies have been trialling experiments ranging from asking people nicely not to block ads (The Guardian) to attempting to completely prevent access to a site for those running adblockers (CityAM).

The response to Facebook from Adblock Plus has also been coming. Its Open Source community defeated Facebook's new tech in less than 48 hours.

On Thursday, Facebook announced another change.

And Adblock Plus killed that one the following day.

I've been plugging away on Twitter for a while with the opinion that big publishers can't win an arms race with adblockers. In this post, I'd like to explain why.

Adblocking is asymmetric warfare. On one side, you have large companies - publishers and ad-tech suppliers - who deliver adverts. On the other you have a growing population of people, estimated at 12m (pdf) in the UK, who want to block ads and a series of smaller software projects which allow them to do that.

Effective asymmetric or guerrilla warfare is all about harassment, persistence and the ability to adapt quickly against a large and powerful, but much slower opponent. Guerrilla combatants don't win a definitive victory, but eventually their opponent is forced to give up and make a deal, because they're burning through resources and not achieving very much.

In order to deploy its new solution, Facebook had to develop the code, test it and then deploy it onto a system serving 1.2 billion users, without breaking anything. Adblock Plus took less than 48 hours to nullify that solution - twice - and did so for free.

The blockers haven't even really had to get sophisticated yet to remain effective. All most adblockers do, is check against a list of server addresses that deliver adverts and web page elements which contain them and remove those before the page is shown to a user.

For a newspaper or Facebook to show an ad, a bit of code is inserted in the web page that effectively says:

[start advert]
Call ad-server and request an ad!
[end advert]

Facebook made that code more difficult to spot, but apparently not difficult enough.

Let's say Facebook got really clever with hiding their ads and mixing them up so the code for their containers and servers looked exactly the same as your friends' posts. What could blockers do in response?

1. Compare the posts in your news feed to your Facebook friends list and zap anything that doesn't match. It requires more coding than just killing advertising flags on a web page, but it's not really difficult... There are already plugins that have this functionality.

2. Use the legal requirement that adverts are clearly labelled as being adverts, as a way for blockers to find and remove them. You've got to write "ad" in the corner of an advert to obey the law. This stuff really isn't that hard.

3. Start training machine learning models to use the content of adverts to identify and remove them. A Gmail spam filter for the whole internet. Are you sure you want to start this fight?

None of those even touch on the idea of weaponised adblock, which it's not unusual to see suggested on tech forums. Currently, adblockers are passive; they prevent an advert from loading. But they don't have to be. Adblockers could deliberately load and click on ads in the background, thousands and thousands of times - without showing them to the user - to screw up the web's revenue model. I'm absolutely not saying they should, but they could, and the idea isn't new.

Are you really sure you want to start this fight?

This is why the advertising industry must acknowledge - whatever its position on the morality of adblocking - that it can't win an arms race with the blockers. Adblocking counter-measures are slow and expensive to roll out and industry wide, will need the cooperation of a huge number of participants.

Blockers can rely on a passionate community of skilled programmers who iterate and deploy new solutions quickly. The blockers don't have to win every time or force a dramatic surrender, they just have to make life difficult and expensive for the ad industry until it eventually gives up.

Advertisers, agencies and publishers must acknowledge three key facts:

1. Adblock penetration is growing rapidly and this is a problem

2. Publishers cannot win an arms race with adblockers

3. A large and growing section section of the population is on the side of the adblockers and so is the law.

It is imperative that the advertising industry identifies the reasons for the rapid growth of adblocking, in order to work towards an alternative to the current situation, where over 20% of people in the UK consume all non-paywalled internet content entirely for free.

This alternative is likely to encompass a model where adverts are no longer a vector for malware, are significantly less irritating to users and provide for much better privacy.

(no, this chart doesn't follow my data vis best practice)

As I've written previously, our new world may well be a much nicer place for large publishers delivering quality journalism. Their audiences won't be trackable onto thousands of tiny websites, so large publishers will be in a much better position to charge a premium for advertising space.

For better or worse, this new world is coming. If you target younger, tech savvy audiences, it is already here. It can't be fought so we must work out how to be effective within it.

Enthralled by tech we don't understand

noreply@blogger.com (Neil C) — Wed, 22 Jun 2016 11:54:00 +0000

The advertising world has discovered this week that an app purporting to crowd-source spotting migrant boats in trouble in the Mediterranean did no such thing.

It rendered the same static image for every user and when they 'spotted' the boat in it, asked for their contact details.

On the plus side, it did update with the weather forecast for Libya.

The app just won a Bronze award at Cannes.

Apple has pulled it from their app store and an investigation has started.

Whatever the morality of producing the app in the first place - which is pretty shocking - what the episode brings home to me, is the need for senior managers making decisions about tech, to actually understand the technology. Not to be programmers themselves necessarily, but to have a good idea of what is possible, what is easy and what is difficult.

A bunch of judges at Cannes decided that the migrant spotting app was worthy of an award.

None of them apparently had the nous to say "hang on, where are they getting their live satellite images?"

Of course the app can't exist. Marketing creative agencies don't have access to live satellite video streams of the Mediterranean. This isn't 24 and Jack Bauer's not an agency staffer.

Google Earth isn't a live stream.

You can get live images, if by 'live' you mean one per day. And it's not cloudy.

At the very least when it comes to technology, if we don't know, then we need to find an expert and ask. There are charlatans out there in the world. Some of them are software vendors, some do digital advertising and some make apps. If we're not to be taken in, the level of tech savvy in our industry needs to increase and quickly.

Six tricks to make your data visualisations look better

noreply@blogger.com (Neil C) — Thu, 14 Jan 2016 08:30:00 +0000

I saw a tweet yesterday from @colinttrainor and couldn't agree more with this idea.

(tweet shown split into two for clarity)

Whatever software you're using, there's simply no excuse for accepting the defaults and not trying to make your charts look more professional.

But where do you start? It's easy to look at great design and agree that it's great, but when you're looking at an Excel default, inspiration is much harder to come by. What do you change first?

These are a few tips, which I've picked up as I tried to make my own output look better and hopefully a few of you might find them useful too.

1. Make yourself a colour palette.

Your company very likely has a corporate colour palette that you're expected to use for PowerPoint, and whether it's good or bad, it doesn't half help to simplify your choices. Rather than selecting from the entire spectrum of colours, you'll have six or eight to work with that (hopefully) are designed to complement each other.

You can make your own colour palette really easily and use it to give your charts, your blog and anything else you build a consistent, professional feel.

Go to Adobe Color and have a play. Find some visualisation examples that you like, which will help you to decide on the feel of your palette. Do you like striking contrasts, or more subdued tones? There isn't a right answer, but what you choose will have a dramatic impact on the impression that your charts create.

2. Simplify, simplify, simplify.

If I had to reduce my Effective Data Visualisation for Marketers presentation down to a single tip, it would be this (paraphrasing Tufte): If it isn't essential to your chart, get rid of it.

Turn off gridlines, reduce colours, turn off legends and axes if you can, Look at Colin's tweeted example above. Most of the difference between the two, comes from turning off gridlines and axes, leaving only data.

3. Black text looks amateur.

Have a look at virtually any professionally produced visualisation, infographic, or presentation document. Is the text black? Look closely, is the text jet black, rgb(0,0,0)? I bet it isn't.

On a white background, changing all of your text to a very dark charcoal shade of grey, just works. I'm not a designer so I don't know why it works, but it does. Just do it.

4. Are you sure you want a white background?

The answer might well be yes, but this one is worth thinking about. Clean, white space is a very good thing but a shaded background can lift your visualisations and give them extra pop.

You probably don't always want black, but it's worth keeping in mind to make bright colours jump off the page.

If not black or white, then light greys can look good. When you look closely, it's surprising how many visualisations that you might initially assume have a white background, actually don't.

Here's one of my football match single-page dashboards. The background is a very light, warm grey. On the white background of this blog post, it's easy to see, but against Twitter's dark image borders (which is where I use these), it's not nearly so obvious.

Bright colours definitely aren't for backgrounds and one other thing you absolutely must do if you change the background, is to change everything that was white background, into your new colour. Miss a spot and it will look awful.

5. Times New Roman? Yuk.

Like colour palettes, this is something that most companies do, but few individuals take the time to set up. It makes a difference.

Take the time to pick a font. Here are a couple of resources to help.

By the way, there's a reason that the example fonts above are a picture and I didn't just change the font in this blog post to type them. I want to show you exactly the fonts I chose and not every web browser will render all fonts correctly. If your computer doesn't have a particular font installed and a website tries to use it, then you'll get something else.

Have a read about web safe fonts.

Web safe fonts aren't important if you're drawing charts in Excel and publishing pictures of them, but they are if you're building Tableau dashboards to publish online. If a font isn't available, Tableau falls back onto Times New Roman and makes your dashboards look horrible. Some of my dashboards do this and I really need to fix them.

In Tableau, it's a good idea to use a very common font that's close enough to what you really wanted, than to try to use something beautiful, which nobody else will be able to see.

6. Different is good

There's one more overarching theme which I'd like to mention and it comes from the world of advertising: Different is good.

Different is eye catching, and it will set your work apart.

Even if Excel, Tableau, R and other programs' default charts were beautiful (which they aren't), there'd still be huge value in changing them. If your work looks like everybody else's work, then it's not eye catching, will blend in with everybody else's work and not get noticed.

Find your own visualisation tone of voice and your charts will stand out much more. It's not always easy to break away from the defaults and try to make something better, but it really is worthwhile.

Finally, remember...

Good artists copy, but great artists steal.

Picasso (maybe)

Google image search is your friend. Good luck!

Adblocking could be the saviour of high quality journalism

noreply@blogger.com (Neil C) — Wed, 12 Aug 2015 12:23:00 +0000

Reading the latest news, adblocking is heralded as the harbinger of the online apocalypse.

Many internet users have decided that they don't like to be tracked and profiled.

They don't like to be irritatingly diverted from the article that they're trying to read.

They don't like auto-play videos.

They don't like ads for things they've already bought, following them around the internet.

And they especially don't like their mobile data allowance being chewed up by advertising.

All of this means that the user base of adblocking software is on an upward trajectory and publishers are starting to worry.

According to PageFair, 21% of UK internet users use an adblocker. Amongst younger, tech-savvy audiences, the rate of blocking is much higher.

For publishers, this is a serious problem, because they get paid per thousand eyeballs viewing ads on their webpages.

For example, if I visit The Guardian, I see this

That banner at the top is generating a small amount of money per visitor for The Guardian.

With a blocker? Poof! It's gone. The page resizes into the gap and you'd never even know it had been there. The Guardian gets no money at all for my visit.

As an aside, Ghostery gives me a list of every tracker that's logging my visit to The Guardian and many of these will subsequently try to follow me around the web and see where else I go.

It's quite a big list...

That's FIFTY EIGHT separate trackers, just from a single visit to The Guardian homepage. No wonder some users want to opt out.

So adblocking continues on its upward trajectory, gains increasing penetration into mobile devices, and in the near future hits and then passes 50% of all users. All of our national newspapers go bust. It's inevitable, right?

Well no, actually, I don't think so.

I think adblocking could be very good for high quality journalism, but there'll be some short-term pain before we get there.

First a little bit of technical info...

Adverts are fairly easy for users to block, due to the way that they're bought and sold and embedded onto web pages. When you visit a newspaper's website, it loads the article you want to read from its own server and makes a request to some other server(s) for adverts. If you keep a big list of the servers that are used to deliver advertising, you can block them in your browser. The article loads and the adverts don't. Simple.

Now imagine the adblocking rate is 100% of internet users. What happens?

Any user, visiting any website, sees no third party adverts at all and publishers get no money.

But advertisers still want to talk to the The Guardian's readers. Of course they do, The Guardian's readers spend a long time staring at those pages and they're an educated bunch, with disposable income. Advertisers like people with disposable incomes and will work quite hard to get their brands in front of them.

Advertisers also no longer have an option to place their ads programmatically - splattering them across the web on loads of different sites and trying to follow individual users around - because as soon as they try to do that, the adblockers kick in and their messages disappear.

Suddenly, there are big online advertising budgets available, but no way to spend them programatically across lots of smaller websites.

So newspapers start selling advertising space the old fashioned way. They agree to place adverts on their site - delivered from their own servers, woven into articles and much tougher to block - and they agree a price to do it.

They might even negotiate that deal on the telephone. How quaint.

It's a model that looks a lot more like the traditional way of selling advertising space. Individually struck, higher value deals with large companies, rather than automated small-ads scattered across the web.

Why doesn't that already happen? Well sometimes it does, but currently, large proportions of online advertising budgets are used to buy programmatically. If you've got a choice between negotiations with individual newspapers, or bunging some cash at an ad exchange on the promise that the exchange will find your exact target audience wherever they are on the web and show them your ad, then of course you'll pick the automated route.

Adblocking takes away the automated route. Users don't like the tracking it entails and in increasing numbers, they're opting themselves out.

If you can't track and follow an individual user onto sites where its cheap to advertise, then you're going to have to buy your audiences in bulk, in the places where they congregate.

Adblocking doesn't leave an internet barren of advertising. It leaves large websites, with high quality audiences, in an incredibly strong position to negotiate their own deals.

For newspapers, it removes a huge number smaller websites from their competitive set and puts them back in the much stronger position that they used to enjoy.

Adblocking at scale would redirect online budgets towards those sites that can guarantee large, desirable audiences for advertisers. It could be the saviour of high quality journalism.

Is Microsoft's Power BI a Tableau killer?

noreply@blogger.com (Neil C) — Thu, 06 Aug 2015 10:16:00 +0000

Have you heard of Betteridge's law of headlines? Then you already know the answer to this post's headline. No it's not.

Why not? Good question. And there is one feature where Microsoft's Power BI manages to lay a glove on Tableau, but we'll save that for later. First, why are we here?

My last post on Wallpapering Fog was part reminiscence, part rant and part lament about where it's all going wrong with Microsoft Excel. You see, I used to like Excel a lot and mostly, I really don't any more. It's still very useful, but in terms of new features, the bad stuff is starting to outweigh the good.

I had a comment on that post from the design leader on Power BI - which is very flattering - asking if I'd tried the general availability version of their new software and I hadn't, yet. I have now. My IT department is also pushing Power BI so I really needed to take a proper look.

A bit of background if you're unfamiliar with Wallpapering Fog - I've been using Tableau (full-fat and Public) for four years and love it, but wouldn't say I'm wedded to it. I'll be very comfortable jumping ship if something better comes along, but for now Tableau is the best BI software on the market by quite a distance. I think using the best and looking for better is a healthy attitude to take.

Before Tableau, I used Excel Services. It was rubbish. Hopefully Power BI is better.

Onto the test. Is Power BI any good?

I went to powerbi.microsoft.com and signed up.

I got a button that said "Get Started" and clicked it.

The website paused and said "working on it".

Hmmm. I haven't asked you to do anything yet. What could you possibly be working on? As a (reluctant) SharePoint user, I know that "working on it" message all too well. This is not the most promising start.

Still, it's gone in a couple of seconds and I've promised to be as objective as possible with this review. We're in, let's move on.

I'm deliberately going to pile into Power BI without reading any instructions at all, because that's exactly the approach I took with two of its competitors - Qlik View and Tableau - and with both, I was able to make good things happen pretty quickly. I only needed help later, as more advanced features came into play. That's the benchmark.

This will also be an early review and I haven't tested Power BI extensively. I may have missed things, but seeing what you can achieve with a piece of software in 24 hours is a useful exercise. In my experience, great software - like Photoshop or Tableau - will blow you away immediately and then keep on giving as you discover more depth. Before I'd invested any time in Tableau and long before I'd got involved with its community, you can read that it did that.

What to do first? I need to connect to some data so we can put Power BI through its paces. Let's have a look at the options.

I can load a csv file or spreadsheet, but decided to have a crack at an API connector and was presented with a slightly strange array of options.

Acumatica (who?) and Circuit ID (also who?) but not the usual array of social, stock price and economic data connectors. Odd.

Anyway, Google Analytics is there. Everything connects to Google Analytics. Let's try that.

Oops. Five minutes of Power BI thinking about things and then I gave up and refreshed my browser.

Maybe I'm being too ambitious (I'm not, I managed to do this in the competition's software). I loaded the example retail dataset.

A dashboard appeared!

If I'm being picky, some of the formatting is a bit scrappy - especially considering it's the only bundled example - but it's a dashboard. Let's not be too picky yet.

I can click around, highlight and filter and go into edit mode, but after a couple of minutes, I found a button for "Power BI for desktop" and downloaded it. I had hopes of more connectors for local databases and did find those. After I eventually installed it.

Yeah, so like I said. Eventually installed. Including rebooting a work PC that doesn't hurry itself to reboot, that little lot took the best part of twenty minutes.

Why don't I already have the latest version of IE? Because it's rubbish. I use Chrome and Firefox.

On the plus side, from the desktop, the Google Analytics connector works.

From this point on, the niggles stopped and Power BI was... Fine. Not good, not great. Fine.

I loaded some traffic data from Google Analytics and drew a couple of charts. I noticed that dates don't automatically drill from years, through months, to days like they do in Tableau and so manually created a month field.

Power BI's got a weird distinction between measures and columns, so creating that date column involved a brief false start. Apparently dates can only be a column, not a measure. What's the difference? Tableau switches effortlessly between dimensions and metrics, which is a very useful feature. You can split data by a numeric field (using it as a dimension), or you can add that numeric field up. At the point you create the field, the distinction doesn't matter - it's just a column of data and you choose what to do with it afterwards.

Creating charts was straightforward enough, but quite limited. Microsoft have ditched the rows and columns interface from Pivot Tables (that Tableau adopted and supercharged) and gone with a vertical interface. It works, but it's nowhere near as intuitive.

That list of options for summing, averaging and counting is again... fine. Just the basics. No frills.

Enough Google Analytics. I restarted Power BI and connected to one of our advertising datasets on SQL Server. 350k rows that describe what different companies have been spending on advertising over the past few years, to see how Power BI would handle a little bit more data.

It coped fine. Connecting was slightly counterintuitive because you have to tick a little box next to the table name, that's not obviously a tick box. Just clicking on the table name brings up a preview, but leaves the "Load" button greyed out and leaves you scratching your head for a bit.

Data loaded, I quickly put together this summary view of UK advertising spend.

You can filter across the whole page, or just on one chart. If you click on something - a date, or a category - then every element on the dashboard immediately filters to what you've selected.

I like that sort of universal filtering behaviour, when I can control it, but it doesn't look like you can here. Everything filters when you click something, whether you like it or not. That has the potential to confuse the hell out of non-technical end users of your dashboard as they idly highlight a date and most of the data on the view suddenly vanishes.

In keeping with the imposed filtering, you can drop a filter element onto the view (top right on my little dashboard) to make it more obvious what's going on, but you can't attach that filter to specific dashboard elements. You filter everything, or nothing.

If you look closely at my dashboard, you'll see that the dates (which are slanted, yuk) are in the wrong order. That's because in our SQL Server, they're stored as strings.

I tried to make a new column that would be a correctly formatted date and this happened.

What's wrong with that? I don't know, it looks fine to me. Apparently Power BI doesn't know either, because the error message is blank.

I tried calling it Formatted_Date, removing the space. Nope.

I tried a few other ideas. Nope.

Maybe DATEVALUE doesn't work like in Excel. That would be daft.

I gave up.

Overall, Power BI feels like it's doing the bare minimum. You can drop charts, text and maps onto the screen. You can sum and average data. You can format axes and change colours. That's about it.

There are features to create variables (when they work) and to connect data sources together, but BI software will stand or fall on the front end and how you are able to present data back to a user.

Drawing on a couple of my own recent projects, this is Tableau. So's this. Both built in the free, Public version. As far as I can see, you've got no chance of producing visualisations like these in Power BI. It's not outright bad, it works, it's just miles behind the competition.

Power BI hasn't come out swinging. It's a cagey, cautious entry into data visualisation that seems competent, but nothing more than that.

Before we go on, I should mention price, because it's important. Full fat Tableau Desktop is over £1000 a copy. It bloody well ought to be good.

Full fat Power BI is $9.99 a month. These two pieces of software aren't targeting the same market.

It's fairer to compare Power BI with Tableau's free offering - Tableau Public.

Tableau Public is full-fat Tableau Desktop, with database connections stripped out and you can only save your visualisations to Tableau's cloud. We've established that in terms of the sophistication of visualisations you can build, this means Tableau will blow Power BI out of the water. What about when you publish your dashboard to the cloud, for others to see?

Data access and refresh is where Power BI wins over Tableau. It can connect to database sources and APIs and auto-refresh from those sources so that your online dashboard stays updated without you needing to do anything. This functionality is free and for $9.99 a month you can do those refreshes at high frequency, with a bigger data storage allowance, though one that only takes it up to the allowance that Tableau offers for free.

OK Microsoft, you just got my attention. It might be just a line chart and a map, but an auto-refreshing line chart and a map is interesting.

What's the catch?

Well if I had an auto-refreshing dashboard, I'd want to embed it into this blog, or into hilltop-analytics.com, but you can't do that without paying $5 a month for SharePoint Online. I use SharePoint at work and it might just be the worst designed piece of software I've ever come across. I'm not even sure it was designed. No, I'm not paying my own money for it.

For now, if you want to build a sophisticated, good looking visualisation, you need Tableau.

If you want to build a more basic visualisation that refreshes itself, then Power BI is an option, but you'll only be able to share it on app.powerbi.com, not embed it.

There are hopes within the Tableau community that Public will get Tableau's new web data connector, which is coming in the next mini-release. If that happens, then Power BI is dead in the water, because its auto-refreshing (at a low price) USP will be gone.

In a business context, Power BI is in a potentially strong position. If your company has already bought into Microsoft's Office 365 and SharePoint stack - as mine has - then Power BI will integrate with that fairly cheaply and allow users to publish visualisations to each other. I wouldn't be at all surprised to see it gain a fair amount of traction.

Unfortunately, as analysts, that will mean investing our time making the argument that Power BI's competitors, while significantly more expensive, are significantly better. Tableau and Qlik View are changes to how analysts work and massive boosts to their capability and efficiency. Power BI is not that. If you just want to automatically publish a table and chart of financial results to the exec team though, it will certainly be useful.

Overall, Power BI is a 6/10 product and in places it doesn't feel finished. It's useable, but limited. Give it a try, but don't expect too much.

Footnote:

I haven't mentioned Power BI's other innovation of natural language search. The idea is that users can type in "sales in France" (or similar) and the dashboard will show them that.

I haven't mentioned it because it's a gimmick. Cortana (I assume it's got Cortana's engine) isn't the Starship Enterprise computer and it's not going to be intelligent enough to be useful. You'll see this message a lot.

Unfortunately, I already have experience of this feature playing really well in a controlled demo, where the salesman knows it will work. It's going to make it harder to explain to an excited senior exec that Power BI isn't really very good, when "you can talk to it and it just understands!"

Footnote 2:

In this review, I may have said Power BI can't do something, which it actually can. If that functionality comes from bolting on Power-something-else, then I'm not interested. Power BI, PowerView, PowerPivot... the Microsoft data ecosystem is a bit of a mess at the moment and it needs a more coherent offering. Power BI should work out of the box as a single download, as its competitors do and that is how I've tested it.

We need to talk about Excel

noreply@blogger.com (Neil C) — Tue, 02 Jun 2015 13:26:00 +0000

I'm not sure when it happened. I've got a feeling that the writing's been on the wall since the introduction of the 'Ribbon' menu.

I was the guy who could make Excel dance... Shortcuts flying, interactive dashboards, external data connections and VBA. I loved Excel.

A colleague, observing me building a spreadsheet a few years ago, said, "F*ck me, it's like watching Minority Report".

Proud moment.

Not any more.

Modern Excel is a mess.

It's worth taking a moment to consider how we got here. Excel was first released as Windows software with version 2.0 in 1987. It's nearly thirty years old.

Back when I started out as an analyst in 2000 - who was genuinely excited that a company had seen fit to employ him and to allocate him a desk and a PC - we were using Excel 97. This was the first version to contain proper VBA and also came with Clippy, the universally reviled Office assistant.

Clippy aside, Excel 97 was pretty good. It had most of the useful functions and features that you'd find in modern Excel and it worked.

Crucially, Excel was what you got. It was restricted to 65k rows and its charts looked bloody awful, but there wasn't really an alternative.

Having VBA baked-in made Excel tremendously flexible (and the bane of IT departments everywhere). With a bit of creativity, you could use it for statistical modelling, interactive dashboards, as a calendar, a project planner, a to-do list... And we did. Excel got (ab)used as a solution to every business problem going.

Back in 2000, Excel was the centre of an analyst's world. What happened?

Specialist software has chipped away at Excel's 'jack of all trades' USP.

If a computer can do it, you can probably make Excel do it. That's no exaggeration. VBA is behind Excel and so if some functionality doesn't exist out of the box, then you can add it. You want games in Excel? Here are fifty. Be warned: I make no guarantee those games won't royally screw up your PC. VBA can do that too.

When you break down the uses for Excel, you find new competitors are encroaching on all sides. Competitors that are designed to do a specialist job, to do it really well and that integrate with each other to provide a complete solution. For statistical modelling, you've got R, SciPy, Matlab... For visualisation, you've got R (again), Tableau, Qlik View... For data storage you've got a vast array of options and for data processing (ETL), you've got Alteryx, Pentaho and again, the list goes on.

That's just the things that Excel is actually for. Under the list of things that Excel has been abused to make it do, there are hundreds of better options. Many of them free. If you want a to-do list, for goodness sake pick something that's designed to do that job.

Excel is like a Leatherman multi-tool. You can get most DIY jobs done with it if you try hard enough.

But a Leatherman is rarely the best way to do any specific job. You want a proper screwdriver, or a full-size hacksaw, or to have a corkscrew for your dinner party that's not also attached to a pair of pliers.

A specialist's toolkit looks like this. One tool - the right tool - for each job.

This is Excel's problem in 2015. It's trying to do everything - often by bolting on more plugin tools - and so it's doing almost everything badly.

Excel's a great way to make some very average looking data visualisations, or to store your data in a way that makes it really difficult to manipulate quickly and to refresh. Excel can deliver a crap interactive dashboard to a (SharePoint) web page and it can do statistical modelling that's really hard to repeat, and leaves no audit trail.

Yes, you can sort of fix those issues, with plugins and macros and hacking and creative thinking, but that's back to fixing your motorbike with a Leatherman, when you could have had the full range of Snap-On tools.

Modern Excel has one more problem. And it's a biggie...

You can't be a beginner's introduction and a specialist's cutting-edge tool at the same time

Yes, I'm going to start with a rant about the Ribbon menu. It was a stupid idea when it was introduced and it's still a stupid idea now. When you watch an experienced user manipulate a familiar piece of software, you'll rarely see them touch the mouse, because it's a slow way to do what you want.

Microsoft introduced the ribbon to make features more prominent for selection with the mouse (and presumably with a view to the arrival of touch-screens). With subsequent releases, more and more features have moved into areas where they are difficult or impossible to access with the keyboard; try formatting a chart, or even saving a file in Excel 2013.

This might sound like a petty complaint, but it's a symptom of a very serious issue. The Ribbon and mouse / touch control were a big two-fingers to experienced Excel users.

Excel has been progressively dumbed-down to make it easier to access for inexperienced users.

Which is absolutely fine.

Except that simultaneously, Microsoft has introduced PowerBI, with features that aim squarely at advanced data manipulation and visualisation. I've tried them and to be frank, they're not up to scratch. They're awkward to install, difficult to use and when you do get them to work, they produce very average looking output.

Excel has ended up in a place where it's too advanced and has too many features for novice users and it's not as good as a dedicated toolkit for specialists. That's not a comfortable place to be.

Where now?

Excel has a strong defensive position, in that big IT departments like it because it's part of a suite of Microsoft software that they're already buying. As a business analyst, you also need Excel plus other tools - if only because everyone else still uses it - so it's not going anywhere in a hurry.

That defensive position is being eroded on all sides though. Particularly because you can get a lot of the competitors that I've been discussing for free. If your corporate IT environment isn't completely locked down, then you can make a lot of headway with open source, start to get your best work out into the world and then argue about commercial software licences later...

There is also one thing that Excel is truly brilliant at and it's not to be dismissed lightly. Sometimes you want a multi-tool. Just for a quick job, because it's easier than delving into the big toolbox. Excel is a fabulous tool for this. For quickly reformatting one-off data, for banging out a functional chart, or for correlating a couple of variables, you can't beat Excel.

Microsoft should recognise this use for Excel and take it right back to basics. Turn it into a Leatherman; a lightweight, portable, do-anything, data scratch-pad, that's not trying to be more.

They'll still need a full featured BI solution of course, and possibly something else that targets less experienced users, but stop trying to make Excel the scaffold that holds the whole data analysis structure together. It's not working and if my experience is anything to go by, it's leading experienced users to actively dislike the product.

If Microsoft don't produce that lightweight scratch-pad for data, I firmly believe that somebody else will and that could spell the end of Excel as a tool for serious analysts. Excel will have been replaced for the one task at which it is still the best option.

In praise of modesty

noreply@blogger.com (Neil C) — Mon, 23 Mar 2015 09:07:00 +0000

One of these people invented the World Wide Web.

The other talks about it.

One of these people is the best footballer in the world.

He's not the one in the gold shoes.

More of this please.

The Hitchhiker's Guide to the Solar System | Tableau Iron Viz Entry

noreply@blogger.com (Neil C) — Mon, 16 Mar 2015 13:35:00 +0000

So this year, I couldn't resist. Tableau set the challenge to build something - anything - with Wikipedia data and they've put prizes on the line. It's enough to tweak the competitive streak in any analyst...

But what to build? I started out with Football League data as it's familiar and I do a fair bit with those numbers already, but then @ChrisLuv did something really nice along the same lines and I got a bit bored scraping even more football results. It felt like time for a short break from football analysis and it would be nice to learn something new during the build.

I'm not sure where the Solar System idea that I've gone with came from - possibly a friend complaining recently on Facebook that his daughter keeps attacking his ship in Elite: Dangerous - but a quick browse on Wikipedia turned up nice, consistent datasets for planets and moons and some really lovely imagery. Definitely dashboardable.

I've been trying recently to reduce the numbers of screens in my Tableau work, because experience is showing that users can sometimes be reluctant to move around a multi-screen dashboard. If the landing page does a good job, you've got a good chance of converting new users to your interface, but if it requires more clicks to get to data, then things become much less smooth.

So, the task I set myself was a one page dashboard to explore the Solar System, and with enough interesting features to be worthwhile entering into the Iron Viz contest (I hope! That's not an intimidating task, honest).

Building on games like Elite, I also wanted it to look and feel something like the in-flight navigation computer in a space exploration game.

There's a bit of how-to guidance in the rest of this post, but first, here's the Hitchhiker's Guide to the Solar System...

Navigation's a straightforward task of clicking the planet you want to see at the bottom of the screen and then you can also browse that planet's moons on the right-hand side.

If you really want to pull apart its inner workings, then download the workbook from Tableau Public and have a play. Here's a quick top-line guide to get things started.

Look and feel

Most of setting up the look and feel of the dashboard involved turning off features in Tableau. No axis lines, no grid lines, no titles for the dashboard elements, no tooltips and no row banding in tables. Everything was switched off via the Format menu and the background set to black everywhere.

If you set up one sheet the way you want it, then you can copy that sheet to make more without doing all of the formatting again. (P.S. Please can we have templates, Tableau? Pretty please?)

A green web-safe "Lucida Console" font on top of the black background gives a nice flight-computer feel.

Select a Planet

This is pretty easy once you know how Tableau custom shapes work.

I downloaded a high quality image (more on image quality later) of each planet from Wikipedia, saved them to my local Tableau shapes folder and then mapped them to the planet names.

The selector is a regular chart, with the axis set to logged distances from the Sun, planet images as shapes and planet sizes defined by their radius. Dashboard actions using that chart as a source, control the rest of the vis.

And a couple of extra tricks to tidy things up...

The planet labels are on the second axis, so that they all display at the same height rather than each one hugging close to its own icon
You can't use planet radius data exactly as it is to set the sizes of shapes, because Saturn has rings and so the planet itself ends up being too small. I created an extra field to boost Saturn's size up to where it should be.

Large image of selected planet

This is done the same way as mapping shapes onto the 'Select a Planet' chart, right? Unfortunately not. You can do it that way, but it looks a bit rubbish, because Tableau compresses shapes to speed things up. If you blow them up too large, they look like this.

It's not totally unacceptable, but seems a real shame to lose those gorgeous high-resolution planet images from Wikipedia.

The trick to getting high resolution images that change on a filter, is to use Map - Background Images. You make a 1x1 scatter chart, with the planet image set to show as background, depending on what's selected on the filter. Don't forget to make the single data point on the chart transparent!

Here are the settings:

The 'target' surrounding the planet is just a floating png image with a transparent background. I drew the four corners in PowerPoint and exported them as a picture. As much as PowerPoint is the work of the Devil, it's a really quick, easy way to create simple coloured lines and add them to your dashboard!

Planet data

This one's easy. It's a straightforward table with a black background and all of the lines and other formatting turned off.

The little benchmarking chart to the right of the table is designed to provide some context. When you look at a statistic like 24.79m/s2 for Jupiter's gravity, is that a lot compared to the other planets?

The chart itself is a Gantt bar, with a dashboard action that highlights the currently selected planet and lets you quickly see how it compares to its neighbours. Using a percentage table calculation instead of absolute values lets us put all the planets on the same mini-chart and stops the ticks for huge "Mass" numbers, compressing everything else down towards zero.

Select a moon, to see data and image

We've seen these techniques before - it's exactly the same as the large planet image and planet data table. I used a simpler chart - without custom shapes - as the selector because some moons are very small and others have very similar orbits, so they can cluster closely together.

Gustav Holst. Just because we can.

This one's a bit of a cheat in that it's not data from Wikipedia, but I just couldn't resist.

Classical music and space games and movies go hand-in-hand, so I used a URL action to call Grooveshark's embedded music player with tracks from Gustav Holst's Planets Suite.

It's not really hard to do - click share on a Grooveshark track and pull the link out of the embed code that it generates. Add that link to your dataset.

Then drop an empty Web Page element onto your dashboard and create a URL action that calls the field containing the link. Tableau will automatically throw the link at the empty Web Page element when you make a selection.

Edit: I previously had some discussion here of issues with Tableau Public, when you try to embed a link to an http address. Thanks to @ChrisLuv you can safely ignore all of that and only need to know that you have to use an https web address for whatever you link to, in order for it to work properly in Tableau Public. Just switch "http" for "https" in whatever you link to and you should be fine.

There's a little warning message hidden underneath the Grooveshark player link, just in case the web browser that's viewing doesn't have flash enabled and so doesn't load the player, but most people should never see it.

I also put that warning text in a calculated field, that hides it when Earth is selected. Holst took inspiration for The Planets Suite from astrology and so there isn't a track for Earth. Wikipedia says so. See, I used Wikipedia data on Holst. It wasn't cheating after all!

And we're done. Phew. Fingers crossed, it's time to press submit on the entry...

My data analysis toolkit

noreply@blogger.com (Neil C) — Wed, 11 Mar 2015 09:43:00 +0000

Growing out of posts like "Losing touch... Or why Excel and VBA won't cut it any more" and "How to do football analysis in Tableau", I've been asked a steady trickle of questions this year about what analytical software I use.

One of the biggest discoveries I've made as I branched out from Excel and VBA is that there isn't a right answer to what software you should use. There are loads of programming languages, loads of dashboard solutions, loads of databases and you can't possibly get experienced with all of them.

It's better to find yourself a set of tools that work and to know those tools well, than to have bits of knowledge all over the place, but not be using any of your kit to its full potential. If your data size isn't measured in terabytes, then you don't need to be right on the bleeding edge.

For outside of work projects, I've got a couple of machines that aren't anything fancy. An older Core i5 laptop with 4GB ram and a Core i7 laptop with 16GB ram. A regular laptop like that, costing between £500 and £1000 is more than enough for chucking around datasets up to tens of millions of rows and analysing them.

On to the software... As I said, there isn't a right answer to what you should use, but I hope this post might be useful to a few people as a starting point. If you've broken Excel and if Access has you tearing your hair out then read on. Opening Microsoft Access at all, is a very strong signal that you need to get acquainted with some of the tools on this list.

And as an added bonus, almost everything I'm going to talk about is free!

Question: Can I get by without Microsoft Windows?

Answer: Yes! You need Linux Mint.

Mint is a very Windows-like desktop environment and since the Windows 8 (Metro) car crash, it's arguably more Windows-like than Windows is.

Easy to install and easy to use, I wrote a short intro to Mint, a couple of years ago.

Be warned, that if you use Linux for anything more than a bit of web browsing, you are going to end up using the command prompt and Googling to fix broken things. But then you'll be doing that with the DOS prompt in Windows too, because a lot of data analysis software is designed around Linux and needs persuading to work properly with Windows.

If you've got an older machine lying about, stick Mint on it. You might be surprised at how good it is.

Question: Which database?

Answer: MySQL Community edition

Yes, there are newer, fancier Big Data technologies out there and I'll learn them at some point, but I want the SQL language I know, in a fast, familiar, free package and MySQL does that.

On Linux, you might have to do a bit of reading to get it to work properly (watch that you need to install the Server and Client packages), but it powers half the internet - including Twitter - and isn't too hard if you're patient and don't mind Googling an error message or two.

On Windows, make sure you download the MySQL Installer. Don't try to work out what packages you want and install them individually. You will inevitably bugger it up.

Once you've installed MySQL, if you're using it for data analysis rather than to power a small website, you MUST customise my.ini (Windows) or my.cnf (Linux), or it will run like treacle. Out of the box, MySQL is designed to run on really low-powered hardware and its memory usage settings are turned way down. Have a Google.

To go with your MySQL Server, you'll want MySQL Workbench, which you can use to write queries and maintain your database. It's packaged up in the Windows Installer that I mentioned, or you can install it separately on Linux.

Don't know any SQL? You should. Start here.

Question: Which programming language?

Answer: I use Python and I like it a lot.

If you're making the switch from VBA Macros, or taking your first steps in programming, then Python's a powerful, approachable place to start.

On Windows, I'd recommend installing ActiveState's Python package, because it handles all the set up for for you and prevents you from getting into a situation where you're pretty sure you installed Python but it just doesn't work.

On Linux, you already have Python installed and you just need a nice piece of editing software to write your code in.

On Windows and Linux, for writing code I've recently adopted PyCharm following a recommendation from @penaltyblog and it's the most straightforward editor I've seen that still has all the features you'll want as you progress.

It might feel a bit weird coming from Excel Macros that the editor you use to write code and the programming language itself are separate things, but that's Open Source for you - it gives you choices. MySQL is the same; you don't have to use MySQL Workbench to talk to your database if you don't want to and there are loads of other choices, but Workbench is an option that works and that's what this list is all about.

No idea how to use Python? Start here.

Question: What do you use for statistical analysis?

Answer: I use R.

R is free and tremendously powerful. It's a proper, highly capable programming language for working with data and the output you can produce using it is amazing.

Unfortunately, it's also got a learning curve that's close to vertical. Your first steps after working through tutorial examples, will not be easy. I'm definitely not an expert, but I'm improving...

The first step with R is to install R Studio, which makes life much easier. Don't try to install and use R without R Studio - it's unnecessarily painful.

The second step is to have a look at a beginners tutorial.

And when you get stuck, have a look on Stack Overflow. R errors can be really awkward things to Google, partly because it's just a single letter and partly because its user forums are horrible. Stack Overflow and R Bloggers will already have answers to most problems you'll hit and if they don't, you've probably got the wrong end of the stick and are asking the wrong question.

Ask a question on Stack Overflow at your own risk. Chances are somebody else has asked the question before and you could have found it by searching. The bar on what is a good question asked in the right way, is also set quite high!

As an aside, if you don't know R or Python, then they are interchangeable for a lot of work, such as web scraping. R is a statistics package with a lot of general programming capability and Python is a programming language with some really good statistics packages. You could try to stick with just one of them.

Question: And for graphs and data visualisation?

Answer: Tableau Public. And R again.

For a basic grounding in Tableau, try my "How to do football analysis in Tableau" guide. Tableau's a fabulous piece of software and a brilliant way to get interactive charts and tables onto the web. It's also my first stop for visually interrogating a new dataset and seeing what it contains.

Tableau Public - which is free - is limited vs. Professional in that you can't load more than a million rows of data at a time and you can only connect to spreadsheets, text files and Microsoft Access. It's well worth working around those limitations though, to get access to the powerful tools that Tableau offers.

When I want a visualisation that's more bespoke than Tableau offers, I turn to R packages, because that's what I know. There are a whole host of other amazing technologies out there for bespoke data visualisation, like D3 and Processing, but it's very much a case of picking your battles and learning what will be most useful to you. I'm not a graphic designer and so am sticking with R and Tableau for now.

Question: Is there still a place for Excel?

Answer: Absolutely, there is.

Excel's still a very useful tool, even if only because you know exactly how it works, so you can use it to solve problems quickly. It's also very handy that almost everybody else has got a copy so you can share spreadsheets easily.

More and more though, I'm seeing Excel as a scratchpad for hacking data around before I put it somewhere more permanent. You can see what you're doing with Excel and for small to medium sized datasets, it works really well, provided what you're building is a one-off view of data that doesn't need to be updated.

Actually, I wish Microsoft would recognise this use for Excel, strip out all the crap and cut it right down. Just a blazingly fast spreadsheet, with worksheet formulas, pivot tables and simple charting. Nothing else. It'll never happen though.

As soon as your work starts to morph into repeatable analytics, or proper dashboarding, get it out of Excel. We used to use Excel for everything because it was all we had. That's not true any more.

The free alternative to Excel is Libre Office Calc and it's just about okay. If you haven't got Excel, it's worth installing but you'll end up in MySQL, R and Tableau earlier, because Calc is a lot less capable when you throw a sizeable amount of data at it.

Question: Any other bits and pieces?

Answer: One or two...

Notepad++ is a must for text editing and definitely put the Poor Man's T-SQL formatter plugin on it, so that you can clean up your SQL queries. You don't realise Wordpad is rubbish until you try something else that works properly.

Pentaho Kettle is cool and worth a look if you want drag and drop ETL (extract, transform, load) for your data. I got very excited about Kettle a while ago and I still use it quite a bit, but you may find that R steps up to do the same jobs as you get better at it.

Gephi is what I used to draw my analyst network visualisation. It's a piece of software focussed on networks rather than general purpose analysis, but a lot of fun to play with.

And finally, Digital Ocean is an awesome web service, where you can spin up a virtual Linux PC (they call it a Droplet) for $5 a month. When I want to run a web scraping Python script without the risk that my laptop will reboot half way through, I stick it on Digital Ocean. You can also put a 20GB MySQL server in the cloud this way and access it from anywhere. It won't be super fast for the basic $5 a month, but it's very handy if you access data from a few different places. If you use this link to sign up, you'll get $10 free credit and I'll get a bonus too. Everybody wins!

I hope that this post might set one or two people off trying new tools. As I said at the beginning, this isn't claiming to be the right answer to what you should use, or even the best answer, but it's a tool kit that's working for me and took quite a bit of sifting through different options to arrive at.

If you disagree with any of the choices, do hit the comments section. I'm always looking for better options, but only if learning them will save time in the long run...

How to do football analysis in Tableau | Part 3

noreply@blogger.com (Neil C) — Mon, 09 Feb 2015 13:33:00 +0000

If you've been working through these posts, so far you've copied some football data from a website into Excel, cleaned it up and learned to build simple tables and charts with that data on a Tableau worksheet.

In Part 3, we're going to create our first interactive dashboard. We'll make a dashboard that lets you select a team and then it will show you which are that team's strongest players.

If you had fun messing about with Tableau in Part 2 and learning by building loads of different views (I really hope you did. Screw instruction manuals. Including this one), then you might want to start a brand new Tableau workbook at this stage and connect to your Excel data again. Have a look at the early bits of Part 2 if you can't remember how that worked.

Make a new worksheet and drag Mins, PS%, Assists and Goals into the view, then split the rows by Player Name and Position. You're looking for a view like this.

PS% is a downright unhelpful name for Pass Completion %, so right click it in the Measures Area (bottom left of the screen, remember?) and rename it. Tableau will then automatically change the name wherever else you've already used it.

It might be good at this point to also change Pass Completion from Sum to Average. It doesn't really matter, because you've told Tableau to split the data by player and there's only one row of raw data for each player, so Sum and Average are the same. If you'd split by team instead though, and then summed pass completion, the data wouldn't make any sense because Tableau would add all of each team's players' pass completion rates together.

Right click the little green lozenge for Pass Completion % and change it from sum to average. If you really want to see the difference that makes, take the Player Name split out of the view for a moment and try Sum and then Average.

It always helps when you're using Tableau, to keep in mind what your underlying dataset looks like, otherwise sum and average (and as you get more advanced, weighted averages) can get you into trouble.

Back to the table... At the moment, our table has got players from both teams in it and we don't want that, because we're building a dashboard that will show us one team at a time.

Drag "Team" from the Dimensions area and drop it in "Filters".

You'll get a pop up showing you Liverpool and Everton. Tick Liverpool and click OK.

Now our table only has Liverpool players in it. Name the worksheet "Player List" (double click the worksheet's tab at the bottom of the screen) and then we're done with this one for the moment.

Add a new, blank worksheet and we'll also make a chart for our dashboard.

Let's try for a chart that will show us who's played a lot of minutes and who's playing well.

Drop "Rating" from the Measures area onto the Columns shelf and "Pass Completion %" onto the Rows shelf.

Tableau will guess that you're trying to draw a scatter chart, because you've dropped Measures where you'd normally drop Dimensions.

What we've got isn't tremendously useful though. It's a scatter, but with all of the players summed into a single point and we want them split out.

Drop "Player Name" onto Detail in the Marks area and Tableau will give you a point for each player.

Detail is essential for scatter plots and for maps. It tells Tableau at what level you want to see your data split out.

This still isn't a great chart though. Nobody has really low passing percentages or a really low rating, so let's tell Tableau that we don't need to see zero on the axes. Right click each axis, choose "Edit Axis", untick "Include Zero" in the top right corner, and press OK.

We're getting there. The chart shows who has good pass completion and who gets a good rating on WhoScored. If you hover the mouse over a point, it will tell you which player it is and whoever is up in the top right hand corner has good passing and a good WhoScored rating.

We can do better than that though. Grab "Mins" from your Measures area and drop it on Size.

Now the chart is showing who's played well (ish. This isn't very advanced analysis!) and whether they've had lots of minutes this season. Larger blobs means more minutes.

Drop "Team" from Dimensions onto Color and you'll be able to See Liverpool and Everton separated out. It would be nice if Liverpool were in red though...

Double click on a colour in the legend and you can change it.

(Worried about colour blind people? You could drop "Team" onto Shape as well. Don't leave it there though, because it will look a bit rubbish and we're only going to show one team at a time in a minute.)

Finally, drop "Player Name" onto Label and suddenly you'll be able to see who's who.

Rename your worksheet to "Player Scatter". We've got two worksheets and can build a dashboard!

The idea of dashboards is that you can drop multiple worksheets onto them and link them together. It lets you build screens that contain a mix of tables, charts and maps, where each piece of the dashboard is coming from one worksheet.

Make a new dashboard screen, by clicking the button next to your sheet tabs at the bottom of the screen.

In the new dashboard screen that pops up, you should see all of your worksheets appear in a list in the top left. Grab "Player List" (you did rename your sheets, didn't you?) and drop it onto the large white dashboard space.

The table you built earlier appears.

Now grab "Player Scatter" and do the same thing. Keep the left mouse button held down and you can see what will happen if you drop the worksheet into different places - at the top, on the left, or on the right. We want it on the right.

You should get something like this. Tableau put the chart on the screen and also the legend for your chart in separate boxes on the right hand side that you can move if you like. It looks rubbish. We can fix that.

Depending on the resolution of your monitor, Tableau has drawn you a default dashboard screen size and shape, but it's unlikely to be exactly what you want.

As a little aside, you can tell Tableau to draw a fixed dashboard size - that will never change no matter who loads it - or one that will try to adapt to different users' monitors by resizing itself. In my opinion, dashboards that resize themselves are usually crap and to be avoided. Tableau's not good enough at reshaping each element on the screen and if somebody loads your lovingly constructed views on a low res monitor, they'll look awful. You can set limits to the resizing, but it's much better to just fix the size and make users with low resolution screens scroll around a bit.

Use the dashboard size options in the bottom left to make a screen that is Exactly ('Exactly' is in the dropdown box) 1150 x 600.

That gives you a nice widescreen shape that will fit on a decent resolution laptop monitor. If you find it's half off your screen and there are scroll bars, you can make it smaller if you want.

Now that you can see both of your dashboard elements properly, it's time to make this dashboard interactive.

Remember adding the Team filter to your table so that it only showed Liverpool? We want to show that filter on this dashboard screen.

If you click in the area occupied by "Player List", you'll see a grey box appear around it. In the top-right corner of that grey boundary box, there's a tiny little down arrow, next to the x. It's really small but it is there and it's one of the things brand new users tend to miss.

Click that little down arrow, find "Quick Filters" and click "Team".

The filter that you created earlier will be pulled onto the dashboard, with only Liverpool ticked. If you hadn't already created that filter, Tableau would have just made it for you and also automatically put it on the underlying worksheet.

Tableau's default filter for a text column like Team, is a list of options with a tick box next to each, so in this case you get an option for Everton and Liverpool and (All) at the top. Sometimes this is what you want, but we'd like this dashboard to just show one team at a time, with no option to select both.

Filters have options on a little down arrow, just like the one we used on the "Player List" screen. Click in the area that your filter is occupying so that its grey boundary box is shown and use the menu to select "Single Value (Dropdown)".

That's given us a tidier dropdown box that will always take up the same amount of space on the dashboard if later on we decide to add more teams to our data.

You can also get rid of the (All) option on that box, so that people are forced to choose a team. In the same menu you just used, go to "Customize" and untick "Show All Value".

Our menu is ready!

Well, nearly. Change it from Liverpool to Everton and watch what happens. The table will switch from Liverpool to Everton, but the chart stays the same. That's no good. Currently, the filter is only linked to our table, not to our chart.

Use the menu on the filter (tiny little down arrow again) one last time to select "Apply to Worksheets" and "Selected Worksheets".

Then tick "Player Scatter" in the box that pops up and click OK.

Now try changing the filter. You've got an interactive dashboard!

When you add filters, you can choose to apply them to just one worksheet, to a selection of worksheets, or to everything that uses this data. The key thing to bear in mind is to make it obvious to users how your dashboard works. Filters that randomly change some things but not others are really, really confusing.

As far as this quick introduction goes, we're done! You've acquired data, loaded it, drawn tables and charts, built a dashboard and made it interactive and that's quite enough for today.

The only thing that remains is to share your masterpiece with the world.

In the File menu at the very top of the screen, click "Save to Web".

You'll get a popup box asking for your Tableau Public login. Remember your Tableau Public login? You made it in Part 2.

Give your workbook a name and hit Save. After a few moments, Tableau will pop up your workbook in all its glory, on the web. You can share it with anybody from here and they'll have exactly the same interactivity through filters, as you did in the desktop software.

Here's my version.

Helpfully, just as I wrote this guide last weekend, Tableau completely overhauled the look and feel of the old Tableau Public site. Thanks guys. Suffice to say, there are share and download links on the web dashboard screen and you can also access your profile and any other dashboards you've uploaded. Have a browse around the site and all will become clear.

Tableau really is a fantastic tool and you can achieve amazing visualisations, much faster, than with anything else I've come across (and I reviewed quite a few). I love it and I hope these three posts will help to remove that initial trepidation for a few football analysts. Get stuck in, connect to data and start playing. You'll get it and if you don't, Google it. And if you still don't, feel free to tweet me @neilcharles_uk.

How to do football analysis in Tableau | Part 2

noreply@blogger.com (Neil C) — Mon, 09 Feb 2015 13:20:00 +0000

"Here's where the fun begins"

Han Solo

So you've read Part 1, you've downloaded and installed Tableau Public and you've got a small Liverpool and Everton dataset to play with. Or alternatively, you took the short cut, downloaded the dataset and came straight here.

Either way, it's time to hit Tableau.

Before you dive into the software, you'll need to create an online Tableau Public account. This will give you somewhere to publish your visualisations and also to save them while you work on them. One of the big restrictions of Tableau Public is that it's cloud-based and you can only save your stuff to Tableau Public's website

You've set up an account? Great, we'll make use of it later on. Now you can open up the Tableau Public software.

You should be looking at a screen like this...

Click "Open Data"and you get a long list of data sources that Tableau can access. The greyed out ones are for Professional users only - they aren't available in Tableau Public - but our dataset is in Excel, which is fine.

Click Excel, find your football data workbook and open it.

You should see Tableau's data loading screen.

You can do a lot of data manipulation here, including joining different datasets together, but we're just loading up a single Excel worksheet for now. If your Excel workbook has only got one worksheet in it, then Tableau will pick that up automatically. If you have more than one sheet in the workbook, then drag the one with the player data across from the left hand column to the empty top window.

Click "Go to Worksheet".

We're in! You should be looking at an empty Tableau worksheet.

If you can use Excel pivot tables then you're going to feel at home here quite quickly but if you can't then don't worry, it's all very straightforward.

Tableau's looked at our dataset and guessed which columns in our data are "Dimensions" and which are "Measures".

Dimensions are things you can split the data by. Player names, team names and positions go in here. "Apps" (number of appearances) shouldn't really be in here but we can deal with it later if we need that data. Tableau's guessed wrong because there are brackets for substitute appearances in the Apps column on our spreadsheet, rather than it just containing simple numbers.

Everything that isn't a Dimension, is a Measure. Measures are data columns that you want to add up, or average, or do whatever else mathematical with. Measures are your numbers. Tableau's put things like Age and Goals and Minuted Played in here, which is what we want.

So, what do you want to see first?

At last week's OptaPro Forum, Simon Gleave (@SimonGleave) showed some nice age distribution charts that plot the ages of players at a club. We could easily draw one of those.

Drag "Age" from the Measures area and drop it into the middle of the table, where it says "Drop field here". Take care to drop it in the middle, not onto the column or row headings areas.

If you're using the sample data, then it will now say 1,330 in the table. If you've put your own data together then you might get a different number because the WhoScored website is regularly updated with new player data.

1,330 is the total of all of the Everton and Liverpool players' ages in our dataset. Useful.

What about the average age of each team? That would be more useful.

Since you dragged in Age, there's now a green lozenge on the "Marks" area that says "SUM(Age)"

You can use this green lozenge to change from Sum to Average. Right click it, find "Measure (Sum)" in the popup and change it to Average,

In the sample dataset, the average age of all of Liverpool's and Everton's players is 26.6.

Let's split it by team. Drag "Team" from Dimensions onto the row shelf at the top of the screen and drop it.

Everton are older than Liverpool! We've just learned something.

Showing numbers to three decimal places is a bit much, so you can change the default formatting for Age if you want. Right click it in the Measures area, choose "Default Properties" and use "Number Format" - "Number (Custom)" to get rid of the decimals.

You could format numbers directly in the view but the nice thing about changing the default is that now whenever we use Age again, it will always appear without decimal places.

Stop reading for a minute and have a play with this table. It might seem odd to say it in a user guide, but the best way by miles to learn Tableau is to play with it. Drop more measures into the view and try splitting the rows and columns by different dimensions and see how Tableau reacts.

That's enough tables. Tableau's all about the graphics, no? Let's draw a chart.

Use the little tab button at the bottom of the screen - the one that looks like a little bar chart - to create a new empty worksheet. The other button - that looks like a little four pane window - is for creating dashboards. We'll get to that later.

Hopefully you're starting to see that in some ways, Tableau's a lot like Excel. It has worksheets and each worksheet is basically an Excel Pivot Table, with rows and columns and measures.

We're going to use this new sheet to draw an age distribution. That means we'll want to count how many players there are, split by age groups.

In our dataset, Age is a Measure to be summed or averaged, not a Dimension that you can split things by, but Tableau can sort that out for us. Drop age into the middle of the view, like you did before and then use "Show Me" to draw a histogram.

We've got a chart!

The Show Me button is the centre of Tableau and it's where you decide what kind of visualisation you want to draw. Think of it like the Excel Chart Gallery, but a lot more powerful.

Depending on what data you've dragged into the view, Tableau will offer you different types of charts in Show Me. This can sometimes get a bit confusing, e.g. you might decide to draw a scatter chart and Tableau says No and greys out the button. It looks at the data you've dragged into the main view and decides you can't draw a scatter with that.

When the type of chart you want is greyed out, look at the tip at the bottom of the "Show Me" box. Tableau will tell you exactly what it needs and when you drag those things in, the option you want will work. Once you start to get used to Tableau and the way that it works, you'll find this happens much less.

Charts in Tableau work exactly like tables and if you get confused, it can often help to think of them that way, or even switch back to a table, sort your data out and then switch back to a chart.

Charts working like tables, means that they have rows and columns and we can split our age chart by team if we want to.

Grab team from the Dimensions area and drop it just to the left of CNT(Age) on the Rows shelf.

Two charts! Now we can really see the differences in age that are driving Everton's older average.

Try dragging Team back off the Rows shelf and putting it in different places - on the Columns shelf, or into Colour or Label in the Marks area. There is loads of flexibility to create the view that you want.

Tableau has five basic ways of showing you differences in your data.

1. You can split it, using the Rows and Columns shelves.
When you do this, you'll get new rows and columns in a table, or new charts, one for each split.

2. You can vary colour

3. You can vary size
(That one doesn't really make sense as a team split - try it. Not every technique is good everywhere)

4. You can change the shape of datapoints

5. You can label different items

Tried all of those? Don't just skim through, I really meant it about playing being the best way to learn. Drag player names in. Swap Age for a different measure. Put team in Color and Label. Go nuts. Break stuff. Junk your worksheet and start again if you need to and remember that Tableau's Undo feature is pretty much bulletproof; Control-Z will always put things back the way they were!

Now that you know the basics of worksheets, you're ready to make your first dashboard...

We'll create some more charts and use them to build an interactive dashboard in Part 3.

How to do football analysis in Tableau | Part 1

noreply@blogger.com (Neil C) — Mon, 09 Feb 2015 13:13:00 +0000

I'm back from a hugely enjoyable OptaPro Forum last week and buzzing with new ideas. It was a fantastic day with some great presentations and it was brilliant to finally put faces to a few analysts' names (and Twitter handles!)

One question came up often enough at the forum to make me think that there might be an audience for this post. How do you use Tableau for football analysis...?

Want to know a secret? Tableau's easy. And it's free. There's really no need to restrict yourself to the purgatory of Excel's chart gallery. This post will cover a few basics and then if there's interest, I might do a follow up that shows how to do some more advanced visuals with X,Y pitch coordinate data.

I'm going to keep this post (actually, since the length blew up as it was being written, these three posts) at a pretty high level: Where to get data, how to connect to it, how to draw some basic views and then make them interactive.

The guide will assume that you can handle yourself with the basics of copying and pasting and inserting columns in Excel and that "install this software; here's a link" isn't too taxing! In general, I'll try to take things step-by-step, with screenshots so you can see where we're going.

First things first, we're going to need a copy of Tableau. It comes in professional and free versions and unless you've got a grand to spare, you're going to want the free "Public" version. Tableau Public is almost fully featured, with just a few restrictions on the types of data you can load and how you save and export your dashboards (more on this later).

If you haven't already, download and install the latest version of Tableau Public from here.

Next, we'll need some data. Very often acquiring data and cleaning it up is the biggest barrier to football analysis and this post isn't going to cover the various sources you could use. Once you get into very large volumes of data, you start to need programming skills in a tool like Python or R and this is only supposed to be a basic intro! We need a small, simple dataset and WhoScored can help us out.

Let's build some visuals to compare Liverpool and Everton this season.

TL:DR. If you don't care about this bit, you can download the mini dataset that we'll use in Tableau and go straight to Part 2.

Go to the Liverpool page on WhoScored and you'll find a table that looks like this.

It's a nice little dataset with player statistics for this season, but it's not doing us any good stuck on the website - we need to extract that data.

There are a few automated tools that are worth trying for data extraction. Import.io is new and is shaping up to be a really great tool, but unfortunately it doesn't pick up the data that we're after when it scans WhoScored's web page. Excel's load data from web feature doesn't either.

We're going to have to do this the old fashioned way.

Click and drag to select everything from the little 'R' in the black title bar at the top of the table, to the bottom right hand corner of the data. Make sure you've got exactly that selected and then Control-C to copy.

(There are probably programmers yelling at their monitors right now. I'm not covering web scraping in this post!)

In Excel, hit paste (Control-V) in cell A1 and you get...

Now go and get the Everton data and do exactly the same thing, pasting it onto a different sheet.

Finally, copy the Everton data from Excel excluding the black titles row and paste it just below the Liverpool data. If you don't skip out the titles row, you'll get column titles mixed up with your player data in Tableau - we only need titles at the top of our table!

As a general rule, Tableau likes data to be in lists. You want your data to be listed downwards, not across the page as loads of columns. WhoScored's data is a list of players with facts about them and that's ideal - a separate set of columns for every player, or data scattered across different sheets won't work.

You can get rid of the Everton data sheet now, you don't need it any more. Give the worksheet with both teams on it a sensible name and save your workbook.

Now we're going to clean the data up a bit. We don't need all of the formatting, so highlight everything, find 'Clear' on the Home Menu (it's got a little pink eraser icon) and choose Clear Formats.

Simple black and white text. Proper analytics.

You could load your list of data into Tableau straight away, but it's still a little messy. Column B has got nothing in it, so highlight that whole column and delete it (Delete icon on the Home menu). The remaining data will move in from the right to fill the gap.

Those "-" signs indicating no data also aren't great for Tableau. Text saying "-" isn't no data, it's a cell with "-" in it. Find and replace "-" with nothing (Control-H) to get rid of them.

The last job in cleaning up is to sort out our player names. If you look in cell B2, you'll see that player names are currently mixed up with their ages and positions. The technical term for this is, "a pain in the arse".

Insert some columns to create a bit of space, starting from column C. Five columns should do it.

Now your data looks like this...

Highlight everything in column B and choose Text to Columns from the Data menu.

Choose "Delimited", tick "Comma" and click finish.

The positions data gets split into column C and because some players can play in a few positions, you'll get data in column D and maybe column E too. For this little project, we don't care about the extra positions, so delete columns D and E. Don't just clear the data out of them, delete them so that everything moves left and your data looks like this.

The playing positions are sorted, but our player's names are still mixed up with their ages. We can split these with a quick text formula.

Age is always a two digit number at the end of the name. Assuming no players are over 99 or under 10 years old, putting this formula in cell D2 will grab the player's age.

"Right" gets the two characters at the end of the text string and "Value" tells Excel to see the result as a number, not as text (e.g. 20, not "20").

The player's name is everything except the last two characters. Put this in cell E2 to grab that bit.

Copy and paste those two new formulas downwards, to fill up the table with names and ages.

You could have waited until Tableau to make these splits as it has exactly the same LEFT, RIGHT and LEN formulas as Excel, but I want you to be able to see the raw data table before we load it. Once you're in Tableau, the data table sits in the background and as a beginner it can sometimes be a little tricky to visualise what your formulas have actually done.

Nearly finished, I promise. If you look at your table of data, you'll notice that there's not a way to know which team each player is on. We've got a record at the top for Raheem Sterling, but it doesn't say "Liverpool" anywhere. That will limit us in Tableau because we won't be able to easily compare teams if that information isn't in our dataset.

The data in column A, labelled "R" isn't useful for anything, so let's put team names there instead. Select cells in column A down to the last Liverpool player, type "Liverpool" and hit Control-Enter to fill all of those cells. Then do the same for Everton and re-title column A as "Team".

The very last job is to add some column titles for the age, name and position columns you've created. Save your workbook and then if it looks like this, we're ready to hit Tableau in Part 2.

Football analyst network vis: New and improved!

noreply@blogger.com (Neil C) — Thu, 29 Jan 2015 11:21:00 +0000

My original visualisation of the football analysis community on Twitter generated a fair bit of interest, so it's time for a new and improved version.

We had one recurring question the first time around: "Where am I on the graph?". It badly needed a search function and thanks to @balbezit, it now has that and whole lot more. Twitter really is a fabulous learning tool! I'd tweeted an early static image of the updated network and got this reply...

I didn't know how to do that, or even that you could. But I do now.

I've also extended the starting group of analysts to the 110 accounts on this list. If you're followed by at least two of those accounts, then you should appear in the network graph somewhere. Following loads of them doesn't count - they have to find you interesting too!

Here's step one. You get a bigger node if more people in this community are following you. It's not sized by follower count in general, otherwise Barack Obama and Cristiano Ronaldo would have the biggest circles, by miles. More closely linked people will cluster closer together.

And next (again via a tip from @balbezit), we can shade the communities in different colours. This is an automatic algorithm in Gephi and I think it's quite effectively separated everybody by their interests.

Broadly, we get...

Red: Core of (largely) Premier League statistical analysts
Light blue: Professional analysis, including OptaPro and Prozone
Green: Journalism
Yellow: The transatlantic connection
Dark blue: Wider interests, including marketing and data vis (I'm in this one)
Purple: FiveThirtyEight

For the interactive version, go here.

It's a big web page. You will have to give it a while to load.

If you use Internet Explorer then you deserve the issue that it will look blurry, rather than crisp and easy to read. Download a proper browser.

Try clicking nodes and zooming and panning around the view!

Help! I can't find my own account!

You need to use the search box on the left. Search for your display name, not your Twitter username (i.e. I'd search for "Neil Charles", not "@data_monkey")

The chart will filter to show your personal following network, but your own node still might not be obvious, if it's small. Near the top of the bar that's popped up on the right hand side of the page, hover over your name. Voila! There you are.

Thanks go to...

@balbezit for the tips, Gephi for a brilliant bit of visualisation software and to Scott Hale for his fantastic Sigma.js exporter that was used to build the interactive vis.

There's a storm coming to marketing

noreply@blogger.com (Neil C) — Wed, 03 Dec 2014 14:45:00 +0000

Rory Sutherland tweeted a fascinating link a few weeks ago. He does that a lot, but this one in particular has stuck with me.

The link points to this article on Wikipedia, about Moravec's Paradox. Essentially, Moravec's Paradox explains that it's easier to program computers to do stuff that we think is complicated, than to do stuff that we think is easy.

Teach a robot to play world class chess? Done. Deep Blue beat Kasparov in 1997 and it's all downhill from there.

Teach a robot to walk as well as a human toddler? Nope. Now we're stuck.

As artificial intelligence improves, Moravic's Paradox suggests that you should be fearful for your job if you work with data analysis and structured processes. On the other hand, there's no imminent danger of somebody building a robot that's adaptable enough to fix the central heating in every different home. The plumbers will be fine. The jobs that we think of as 'easy' - manual labouring and skills that require some physical coordination - are way beyond the capability of today's computing, but the jobs that we think of as 'hard', may not be.

Keep Moravic's Paradox in mind as we look at a couple of new tools.

First, CasualImpact by Google. (Yes, that name needs a space. No it hasn't got one.)

CausalImpact is a tool for estimating what advertising has done to web traffic. You feed it your traffic stats and your advertising stats and it estimates how hard the advertising is working to create more traffic.

In essence, this is how I've been earning a living for the past fifteen years. Google just automated it.

OK, that's over-dramatic, Google hasn't made me redundant, yet. CausalImpact is a very small stepping stone, which only works for website traffic, in many cases won't work at all and you need a fair bit of technical knowledge to be able to deploy it, because it comes as an R plugin.

R plugins are hard, because R is hard. But then people like @jjmulz do helpful things like this.

And suddenly the ground I'm standing on starts to look shakier again. All programming tools are hard until somebody sticks an easy front end on them. If CausalImpact doesn't do it for you, try another Google funded project - the Automated Statistician. The machines are definitely coming.

This is all early days, but you can see where it's headed. Marketing analysis is a process and it has to be a fairly repeatable one, or you'd never be able to sell it to clients as a product. Without a process, every single project would be its own piece of R&D that might or might not work.

Marketing return on investment analysis is difficult, but so is chess and computers are better at chess than we are. You just have to teach them a framework for understanding the game.

What about the other end of the media planning process? The planning bit, before you get to measure what you've achieved? Charging into audience discovery, comes Profiler, from YouGov.

You probably saw YouGov Profiler via social media in the past few weeks. It's great.

Type in almost any subject area and it will tell you about the people who are interested in that topic. The scope of what you can look up is seriously amazing - you have to give it a try.

If you work in marketing, you'll quickly recognise the screens that pop out of Profiler as 'pen portraits'. These portraits are front and centre in every agency's pitch documents and annual plans. First we tell you about the audience who we want to see your adverts and then we tell you how we're going to achieve that.

Click on the 'Media' tab that you get on the output screen, bearing in mind that this is a demo and the full product will have loads more detail.

Damn, somebody's just automated another part of what marketing agencies do.

It is true that few businesses - other than marketing agencies - will buy access to the whole of YouGov's tool, because it would be too expensive for a piece of kit you'll use once or twice per year. Marketing agencies could still act as an intermediary, holding data and tools and running them for clients. We do this a lot now.

Except that if we've learned one thing about the web, it's that the web disintermediates. If you're sat in the middle of a transaction, making money by being a gatekeeper who controls access to a resource, then you should be scared of the internet. High street shops, travel agents, music labels, publishers... sooner or later, intermediary businesses get slapped by the web, because it puts buyers directly in touch with sellers.

If I was YouGov, I'd sell the Big Expensive Tool version of Profiler, but I'd also make it available on a 'pay as you go' model and let individual companies buy data, one query at a time. At the point YouGov or one of their competitors does that, the insight that agencies can create by profiling an audience becomes quite seriously devalued.

Just like Google's CausalImpact, the profiles that a company runs for itself probably won't be as sophisticated as they'd get from a professional analyst working in a marketing agency, but in many cases that won't matter. Amazon can't recommend books like an independent book store can, but it still forced most of the independents out of business.

Marketing agencies have spent years refining their processes. We're proud of our processes and they're what we use to differentiate ourselves from other agencies. We talk constantly about how we have a process to discover things differently, or connect them differently, or to measure the results better.

Computers are good at processes and this is going to become a serious problem for marketing companies and the people who work in them. Pieces of what we do are going to get automated. Pieces of what we do are already being automated.

The real revolution is quite some way off and you probably don't need to worry about it too much yet, because sexy bits of technology that you only just heard about, are usually ten to twenty years away from actually working properly. In the meantime though, we're going to see many innovations that chip away at the agency model and marketing agencies are going to have to work out - again - what it is that they can actually charge clients for.

We only did planning, until our clients mostly evolved onto fairly similar, effective, best-practice media plans.

Then we did 'added value': Processes, discovery, insight and post-campaign analysis.

When the processes, insight and analysis start to be automated, what will we do then?

My strong suspicion is that a marketing agency's true value lies in human interactions and in explaining the world, person-to-person to our clients. Rather than selling 'things'; media plans, PowerPoint decks, research studies and analyses, we're going to have to become much better at charging for these human interactions. If we don't, we'll slowly be automated into irrelevance.

Visualising football analysts on Twitter

noreply@blogger.com (Neil C) — Mon, 10 Nov 2014 10:49:00 +0000

Building on my new-found love of network diagrams, I thought it would be fun to visualise a social graph of football analysts on Twitter.

Who should you follow? These guys. They're fascinating.

Click the image for a (much) bigger and zoomable version.

Small print:

Lots of users following each other moves those users' nodes closer together.

Following, replying to, or mentioning a user on Twitter gets you linked.

Nodes are sized by number of inbound links (i.e. shouting a lot and following lots of people doesn't get you a big circle, other people mentioning and following you does).

Twitter rate limits mean that once you hit a certain number of followers, you don't get any bigger. That's why all of the core people have nodes that are the same size.

This graph undoubtedly flatters my own profile because it's built from people I follow and talk to.

The starting point for the graph was Twitter users in this list. Who's missing? Let me know!

Visualising 45,000 football transfers

noreply@blogger.com (Neil C) — Thu, 06 Nov 2014 14:12:00 +0000

Football's an international business and it's obvious to anybody watching a Premier League game, that players have been transferred in from all over the world.

But which countries' clubs are the most interconnected? Is the old cliché true, that British players don't travel as much as their foreign counterparts? And can we show the relationships between clubs in an interesting way?

I drew the following images with Gephi, using data on just under 45,000 player transfers, taken from SoccerWiki. Gephi clusters teams by the closeness of their transfer activity; a lot of players moving between teams means that they will group together, while teams that are far apart rarely acquire each other's players.

Some of these images benefit from clicking through to the larger version link and zooming in...

45,000 player transfers

Big version. Zoom in and scroll to see detail.

A rough guide to national connections
The UK and Italy stand apart from an interconnected Europe.

Big version

The British peninsular
Note the Scottish spur and island of Ireland.

A few technical notes:

Node sizing is by number of transfers in and out. A larger node indicates more transfer activity.

SoccerWiki isn't a perfect repository of transfer data, but it's more than good enough to draw this sort of network diagram and overall is a really fantastic resource. Although the way that SoccerWiki stores information makes it impossible to put an exact time-stamp on transfers, data covers a range from 2007 to 2014.

I've dropped any team with fewer than 20 player movements - in or out - in order to clean up the diagram. With everything switched on, it renders very slowly and you get a cloud of small, barely attached teams floating around the edges. They're distracting without adding any information to the visualisation.

Views were rendered using Gephi's 'Force Atlas 2' algorithm.

The quiet BI revolution (part one)

noreply@blogger.com (Neil C) — Tue, 15 Jul 2014 10:38:00 +0000

Three years ago on Wallpapering Fog, I wrote a post about why our company (or more precisely, since the company's huge, my department) had adopted Tableau software.

At the time, I said:

"I feel like I'm giving away a trade secret here, but what the hell, you're going to hear about it from somewhere soon anyway."

Having just attended the London Tableau Conference, I can confirm that the secret is well and truly out. It was a brilliant event, brimming with enthusiastic people and great ideas, that deserves its own write-up away from this post.

For this post, I'd like to indulge in one of my occasional crystal ball gazes and look at the future of Business Intelligence (BI). Not BI on the cutting edge - although that is an exciting topic - but BI in regular businesses. Businesses that have small analytics teams, no time and aren't PR'ing a project to the trade press, with all of the doubts and the dirty laundry Tippexed out.

So where is BI - and in particular, regular reporting - for a normal analytics team going to head over the next five to ten years?

1. Data Visualisation and Reporting

Data vis as it applies to most businesses, is now a solved problem (what to visualise isn't. That's part two of this post). You can have good looking reports, automatically refreshed and delivered onto any device you like and even on paper, if you must. They're quick to build, easy to adapt and easy to maintain - more so than Excel-based reports ever were and much more flexible.

The only things you can't do easily, are weird and wonderful innovative visuals that nobody's ever seen before and you can't have all of this functionality for free.

On the first of these problems, I'd argue that this isn't a business issue. Businesses need straightforward charts, tables and standard reports, not animated 3D network diagrams, so software like Tableau will do a great job. I'd also argue that if you're looking for real flexibility, Lyra is something that I'm quite excited about...

On the second problem - cost - you just have to bite the bullet. $20,000 spent on the right BI software will transform your analytics department.

(That's if you give the $20k to your analytics department. DO NOT give it to a centralised IT team. They'll very likely ask for another $230k to make a nice round number, disappear for six months and then reappear asking for more money.)

The real change in data reporting, investigation and visualisation over the next five years or so, is going to be from a situation where many businesses don't yet realise that it's a solved problem, to one where they do.

Tableau's solved this problem and in my opinion is by some distance the best of the new breed of reporting and investigation tools, but if it hadn't been Tableau it would have been Qlik View. And if not them, Spotfire. And... you get the point.

What's going to happen over the next few years is that Tableau knowledge will become more valuable - because more businesses will want to hire those skills - and also less valuable, because loads more people are going to know how to use the software. The end result is basic supply and demand. It might swing back and forth for a bit, but we'll settle onto a situation where many (most?) analysts know Tableau as a regular part of their job. There'll be specialists, just like there are specialist Excel consultants, but most businesses will sort themselves out and nobody will be paid a fortune just for knowing how to use Tableau.

So far, no real surprises and if you read Wallpapering Fog regularly then you've probably heard those ideas before. The next two points are where I see a quiet revolution happening.

2. (not) Data Warehousing

You probably already know how this works. Analysts with Tableau do the visuals, but there's a big SQL database in the back end, looked after by a centralised IT team, which contains exactly 73% of what you want to visualise. A big enough gap that you can't just ignore data that isn't in the data warehouse, but not so big that the data warehouse as it stands is useless.

What often happens in response to an incomplete data warehouse, is that analysts build a hack. The data that isn't centralised is pulled in from ad-hoc spreadsheets and mashed together in Excel or Tableau, which works OK until you need more than a couple of people to update those spreadsheets, or somebody's on holiday. This is the issue we often hit in media agencies; you can solve a problem once, but can't roll out the solution everywhere to all clients because some parts of your 'solution' are held together with gaffer tape and bits of string.

What's needed is some software that's built for analysts and allows them to merge different data sources and to schedule updates, without recourse to a database administrator.

If you were at the Tableau Conference last week, then you'll have seen Alteryx sat squarely in this area. Drag-and-drop, hugely flexible and very friendly, I played with the demo a few months ago and I loved it.

But, it is quite pricey. Especially if, like us, you wouldn't plan on using all of Alteryx's capabilities and are only really interested in blending data sources together.

Did somebody say what about Open Source? Here's my tip of the day. Go and download the Community Edition of Pentaho Kettle and persevere through the thirty minute skirmish it will take you to get it installed and working properly. Your reward will be drag and drop data acquisition, blending and output, all for free. This is how I process a lot of my football data and it's brilliant.

In terms of crystal ball gazing, the analytics department now starts to look quite different. It's running a lot of reports on schedules, freeing up time for investigation and innovation. Nobody does the whole "getting into work at 7am on Monday for a frantic three hours of board report running" any more, which retailers in particular are very fond of. And thank God for that.

In our new world, IT only handles data when it needs to flow in large volumes from a point-of-sale or distribution system. IT does the bit that it already does very well now, but everybody stops moaning that the data warehouse doesn't also contain lots of the smaller user-maintained pieces of information that make a business run properly.

If you're thinking that the new world sounds like the same old BI promises, then you're right, it does. We should have been able to do these things ages ago but it didn't work due to the disconnect between analysts and IT and the slow build time, inflexibility and high cost of software. Analysts received questions and understood what output was needed, but usually only IT had the (inflexible) technology to make that output happen automatically.

The big differences now are speed, cost, flexibility and the number of companies that will be working in this new way. It's no exaggeration to say that you're able to go from raw data, to first-version business reports in two days. You can pin those down to a format everybody's happy with in a couple of months (faster if you make decisions quickly) and then you can fully automate them. Reports are able to evolve because they can be rebuilt and republished very quickly, in hours rather than weeks.

Then what do you do next? It's a serious question with which some reporting teams are going to struggle. When nobody needs you to move data from Google Analytics to Excel and chart the same charts every week, what will you do? The time to start thinking about that is now.

3. Data acquisition

This one's not solved; it's currently being solved and we've got a little way to go yet. Data acquisition is the last barrier between analysts, managers and an automated dashboard containing absolutely everything on which they wish to report.

Alteryx and Pentaho Kettle are fantastic data assembly (ETL) tools, provided your data isn't stored somewhere really stupid. Unfortunately, I work in marketing and our industry specialises in making data as difficult as possible to access.

- It's in untidy, bespoke web interfaces, behind login screens.

- It's in the colour key that somebody has chosen to fill cells in Excel

- It's emailed across, with a friendly "Hello! Hope you had a good weekend. Today's spend number is £2,486."

Database that, smartarse.

What I see happening over the next few years is some new tools and some new ways of working. Provided data is delivered in a consistent format, then the likes of Alteryx or Kettle can make the data acquisition and blending problem go away.

Where data is in web interfaces, we can already scrape it using Python or R, but then you need an analyst who knows how to scrape and that's not such a common skill-set. (Top tip: look for a football analyst - by necessity, we're getting quite good at it.)

We're going to evolve towards XML and other data feeds in addition to the usual user facing tables that come from the majority of web data sources, which again brings the likes of Alteryx into play. The data providers who don't do this should gradually become extinct through a process of natural selection.

Eventually, these changes will form an almost universal API. Every provider's data is different, but you'll be able to get to the data in an automated way and that's 90% of the battle. When you've done that, you only need to solve the data transformation problem once.

We'll also see - as is happening already - advanced data providers like Datasift starting to deliver information into services such as Google's Cloud Platform. A few years ago this wouldn't have helped, because you're just swapping one API for another, but when a critical mass of services all use that same cloud, easy connectors start to appear.

So why do I say that data acquisition isn't a solved problem yet?

Well for one, too many sources are still silos, but a second issue is that user input is still much too difficult. There's no Tableau for manual data entry and we still have to call a developer to create web forms and database schemas and data validation and to link it all together for us. Either that, or we have a central spreadsheet for people to fill in and we pray that they don't break it, or all try to edit it simultaneously.

I'm sure this software will come, but I haven't yet seen it. Microsoft Access forms and VBA really isn't it and neither are Google Forms. Microsoft, for all that they had a massive head start and will claim to have solutions to all of these problems, are nowhere in the BI race and are falling further behind.

If you've seen another solution to the problem of regularly taking validated user input without embarking on a software build or trying to lock down a spreadsheet, I'd love to hear about it in the comments.

The future's bright

In our future analytics department a lot has changed, but it's been a quiet revolution. A lot of things that were difficult are now easy and the business analyst's scope has extended well into traditional IT territory. Or, more accurately, that territory is more clearly delineated between the two departments and issues which neither IT nor analysts could previously solve (for a sensible budget in a sensible time-frame), have been dealt with.

Reports have moved to web browser interfaces - except for those staff who absolutely insist that they need printed ones - and automation takes care of putting them together. Analysts can quickly and visually interrogate their data and as an aside, Excel has moved to being a secondary tool for serious analysts, behind Tableau (or a competitor of your choice).

We were promised all of this a long, long time ago. Most businesses might actually get there in the next five years or so. It's interesting that the process of assembling Business Intelligence is being solved backwards... Rather than from data collection, to merge, to visualise, solving the visualisation element has driven a requirement to be able to better blend data, which in turn drives changes in how we acquire it.

And you know what happens after that? Businesses will start to realise that a lot of the information they've spent years trying expensively to assemble, won't on its own work the miracles that they hoped it would. Not without some other major changes happening too.

My favourite quote from last week's conference came from Fawad Qureshi of Teradata.

"Old business process + expensive new technology = expensive old business process"

That will be part two of this post. When you've got to your ultimate suite of business reports and they're easy to maintain, what happens then? What changes? Does anything happen at all?

The insular world of marketing

noreply@blogger.com (Neil C) — Thu, 22 May 2014 11:40:00 +0000

It's election day! And it's an election day that I'm personally fascinated by, in terms of whether the pre-election polls are anywhere near accurate.

Take a look at the image above. The Sun and YouGov are predicting a narrow UKIP win.

Do you know anybody who's said they're voting UKIP? I don't. Maybe you've got a batty aunt, or a slightly racist grandparent who makes you cringe now and again in public, but do over a quarter of people you know intend to vote UKIP?

Probably not.

This effect caused me to lose a tenner, betting on the London Mayoral election that saw Boris Johnson beat Ken Livingstone. The bookies has Boris as nailed on favourite, but I only knew one person who planned to vote for him. Nobody I knew could name many people who planned to vote for Boris either.

Of course you often surround yourself with like-minded friends, but work colleagues and acquaintances were vehemently anti Boris and surely your work colleagues are a decent random(ish) sample of different opinions?

It turns out not and I lost my tenner.

If you're here, reading this, then you're likely a thoughtful, analytically minded person with either a marketing or football analysis interest. Probably, you're not planning to vote UKIP and you don't know many - or even any - people who are.

Does this matter? In marketing, I think it does. We're trying to sell products to the population of the UK in general and to do that, we need to understand what motivates people in general, not just people like ourselves.

Walk into any big marketing agency in London and the people you'll meet will predominantly be:

Under 35. Many will be under 25.
University educated.
White.
Renting their home.
Unmarried
No kids
Travelling daily on public transport. Mainly on the tube, which obviously only exists in London.

That's a very narrow selection. Even the simple fact that all of these people live in London makes their day-to-day life quite unlike that of 85% of the UK population.

I work for MediaCom North - based in Leeds - and so some of the regional biases are removed in our office, but I bet I still couldn't find a UKIP voter here. I'd be staggered if over a quarter of the voters in the office supported UKIP.

As marketing people, we need to be acutely aware of our own inherent biases so that we can avoid them. Look at the adverts running on TV on any night of the week and ask yourself how many are designed to appeal to an under thirty year old audience. Then ask yourself, honestly, if most of the people buying that product are likely to be under thirty. Cars? Nope. Supermarket shoppers? Nope. Holidays? Nope.

For me, agencies need to be doing much more immersion into the lives of people who don't think like themselves (and I mean real immersion, I love stats as much as the next guy but they're a starting point, not the whole solution). A once a year factory visit or focus group just doesn't cut it.

We should also be hiring and retaining a more diverse mix of people, particularly people over thirty five. If the problem is that those people leave London when they hit their mid-thirties then maybe we need some more innovative solutions to tap into their opinions and experience.

Finally, as a client, I'd be looking seriously at non-London agencies to get some wider perspective. A global car manufacturer would naturally look to the scale of the big London agencies - and maybe they should - but they need to be aware that the people working on their account almost certainly don't own a car, have the money to buy one, or anywhere to park one if they did. That's why virtually all car ads are either full of young people, or a very crude caricature of older people.

Could your agency advertise UKIP and really understand what motivates all of those people who plan to vote for them? Or would you end up with a stereotyped portrait, produced by a youthful, liberal-leaning, well educated planner?

Of course, the question of whether you should take that brief is a whole other issue.

Bigger data isn't necessarily better

noreply@blogger.com (Neil C) — Mon, 19 May 2014 13:08:00 +0000

Sometimes it's hard being a statistician. Sometimes a long established statistical concept jars with your audience and no matter how hard you try to explain it in plain terms, you can see in the audience's eyes that they don't really believe you. Those suspicious eyes staring back at you are fairly sure you're pulling some shenanigans to get out of working harder, or to wring an answer from the data that isn't really there. What you're saying just feels wrong.

Explaining sampling can be like that, particularly when you're dealing with online data that comes in huge volumes and fighting against a tidal wave of 'Big Data' PR.

The audience's thinking goes...

More data is just better, because more of a good thing is always better.

More data must be more accurate, more robust.

More impressive.

Then a statistician says, "We only need 10% of your file to get you all the answers that you need".

And rather than sounding like an efficient, cost effective analysis, it feels disappointing.

"You only need a spoonful of soup to know what the whole bowl tastes like"

A common question from non-statisticians is to ask, "Overall, I have five million advert views [or search advert clicks, or people living in the North East of England, or whatever], so how big does my sample size need to be?"

Which sounds like a sensible question, but it's wrong.

Statisticians call that overall views number the "Universe" or "Population". It's the group from which you're going to draw your sample.

Once your population is bigger than about twenty thousand, it makes no difference at all to the size of the sample that you need. If you say that you've got one hundred million online advert views, and ask how big your sample needs to be, then the answer is exactly the same as if you had fifty million views. Or two hundred million.

Which probably sounds like statistical shenanigans again.

Think about it like this. I've got lots of ping-pong balls in a really big box and I tell you that some are red and some are white and they've all been thoroughly mixed.You can draw balls from the box one at a time until you're happy to tell me what proportion of each colour you think is in the box. How many ping pong balls do you want to draw?

Seriously, pause and have a think, how many do you want to draw? It's a really big box and you'll be counting ping pong balls for a week if you check them all.

Let's start with ten. You draw ten balls and get four red and six white.

Is the overall proportion in the box 60/40 in favour of white? It might be, but you're not really sure. Ten isn't very many to check.

You pull another ten and this time you get five more of each colour. Now you've got eleven white and nine red. Happy to tell me what's in the box yet? No?

Let's keep drawing all the way up to 100 ping pong balls.

Now you've got 47 whites and 53 reds. The proportion seems like it's close to 50/50, but is it exactly 50/50 in the rest of the box?

Every time you draw more ping-pong balls, you get a bit more sure of your result. But have you noticed that we haven't mentioned once how many balls are in the box in total; only that it was a big box? It's because it doesn't matter.

As long as the population is "big" and we draw balls at random, it doesn't matter how big it is.

Here's how your confidence in the result changes as you draw more ping-pong balls from the box:

The bigger your sample, the better your accuracy, but beyond a certain size - say 5,000 - your result is highly accurate and having an even bigger sample doesn't make very much difference.

"But!", say the objectors, "Online, data is basically free and we can use the whole dataset, so we should!"

And that's true, up to a point. Data storage is so cheap it's close to free, but data processing isn't. A large part of the cost is in your own time - you can wait ten minutes for a results dashboard to refresh, or you can sample the data, wait thirty seconds and get the same answer. It's your choice, but personally I like faster.

Outside the digital world, storage is still cheap, but data collection can get really expensive.

The TV industry in the UK is constantly beaten with a stick based on the fact that TV audience figures are estimated using a sample of 'only' 5,100 homes. It costs a lot to put tracking boxes into homes and this number has been arrived at very carefully, by very well trained statisticians. It's just enough to measure TV audiences with high accuracy, without wasting money.

In fairness, The BARB TV audience panel is challenged by a proliferation of tiny satellite TV channels - because sometimes nobody at all out of those 5,100 homes is watching them - and by Sky AdSmart, which delivers different adverts to individual homes. It may need to adapt using new technology and grow to cope, but nobody is seriously suggesting tracking what everybody in the UK watches on TV, at all times, on all devices. That would be ridiculous.

I'll be blunt. Any online data specialist who uses the 5,100 home sample to beat 'old fashioned' TV viewing figures, doesn't know what they're talking about.

Sampling is an incredibly useful tool and sometimes more isn't better, it's just more. More time to wait, more computer processing power, more cost and more difficulty getting to the same answer.

.

Visualising Everton 3 - 0 Arsenal

noreply@blogger.com (Neil C) — Mon, 07 Apr 2014 08:16:00 +0000

I've been playing with 3D visualisations of Opta football data over the past few weeks, trying to build a picture of the action areas in a game. This post is me thinking out loud more than a finished product, but there's definitely something about 3D mapping that does work.

3D is usually to be avoided (particularly in pie charts!) and I've said as much in my guide to data visualisation for marketers. The problem when visualising touches in a football game on a flat pitch though, is that very often you'll see something like this:

It's obviously displaying too much data. Converting to a heat or contour map helps, but unless differences between areas are very starkly defined, it doesn't make important areas of the pitch really jump out.

So, 3D...

I've taken the data from the Everton vs. Arsenal game yesterday and with R and rgl, used it to create a contoured surface. Add flags for for shot locations and a textured surface for the pitch and you get the images below.

You can see - as we've found before - how Everton concede the centre in favour of the wings and the importance of Leighton Baines on Everton's left. Despite that ball movement through the wings, Everton's shot locations are more central than Arsenal's, with Arsenal taking a number of inaccurate shots from wide on the left. Everton's two goals came from almost the same spot, with the third being an Arteta own goal.

I'll keep posting these from time to time and working on the visualisation. They're not a finished product, but I like the effect and think it's worth persevering with. Any ideas, or games you'd really like to see? Let me know in the comments.

Mapping UK Adland

noreply@blogger.com (Neil C) — Tue, 11 Mar 2014 13:40:00 +0000

I've been putting together a lot of advertiser spend data recently, for our own internal Tableau dashboards, and thought it might be fun to throw the dataset at R too and make something less functional but a little bit prettier.

These are contour maps showing the locations of UK advertisers spending more than £500k on TV, radio, print and posters last year. Darker equals more businesses in the area and I've deliberately dropped legends to avoid cluttering up the maps.

Huge thanks to the people behind R and the ggmap package, who are much, much cleverer than I am!

UK businesses spending more than £500k on advertising in 2013 (Click for bigger)

Focussing on England and Wales...

It's not all about London...

Nobody goes South of the River...

Premier League attack patterns visualised

noreply@blogger.com (Neil C) — Fri, 14 Feb 2014 08:30:00 +0000

Yesterday, I posted some visualisations of approach play in the Premier League. They describe how passes into a 'shooting zone' in front of the goal tend to be more successful when they come directly, rather than from wide areas.

I've started to play with these visualisations for individual teams and a few people have asked how they look, so today I'm posting attack patterns for the current Premier League top seven. We're looking at the number and success rate of passes played into a boxed-out 'shooting zone'. Data covers the first half of the current Premier League season, up to the end of January.

For the following heat maps...

Size of square = number of passes
Colour of square = pass success rate

Large and green is good; large and red is not! It's important to look for clusters of colour rather than concentrating on individual squares because when we're looking at only one team, the number of passes included is lower.

Teams are attacking the goal on the right and are listed in order of current league position. Yes, I picked top seven because everybody wants to see how the Man United one looks.

Chelsea
Mixed approach with occasional long passes from deep. Larger number of incomplete passes from wide on the right.

Arsenal
High success rates with close, central passes and very rarely played long from deep. Significant volume of passes from advanced wide positions, but with low success rates.

Manchester City
Varied approach with good success rates from almost all areas.

Liverpool
Mixed approach with low volume of passes from very wide touchline positions. Attacks from right wing weaker than left.

Tottenham Hotspur
Greater success rates through the centre than from either wing, but high volumes of unsuccessful passes played from advanced and wide.

Everton
The Leighton Baines effect. High volume of passes from wide left but with low completion rates. Passes from advanced right also with low completion. Very few attempts through the centre and occasional long balls from deep.

Manchester United
Some approaches through the centre but attacks weighted towards wings. High volume of longer diagonal balls from the right, with low success rates.

How can an attacking team get close enough to expect a goal?

noreply@blogger.com (Neil C) — Thu, 13 Feb 2014 08:14:00 +0000

There's been some great work done in football analytics recently, looking at a team's scoring chances from different positions on the pitch, which has led to the calculation of various Expected Goals (ExpG) metrics. However it's calculated, in essence ExpG gives a player's chance of scoring from a shot, given his position on the pitch. Add up the probabilities for a group of shots and you can work out how many goals a team 'should' have scored from them. Have a look at Statsbomb if you'd like to read up on what's been available up to now.

I've managed to assemble a decent sized database of pass and shot locations from across the first half of the 2013-14 Premier League season and wanted to see if I could take Expected Goals a step further. As an indicator of shot success, Expected Goals typically paints a picture of the penalty area, with the six yard box as a hotspot and becoming colder the further out you move from goal. To a certain extent, its outputs are relatively obvious; if you shoot from closer in, you have a higher chance of scoring and shots from further out are less likely to be converted.

That's not to say Expected Goals isn't a useful metric - far from it - but it doesn't do a great deal for our understanding of how to create goals. We can quantify how much better it is to shoot from closer to the goal, but how do you get closer to the goal in the first place? If your attacks break down trying to reach the shot conversion hotspot, should you even try to get there, or just take your chances from range?

A couple of days ago, I tweeted an image of pass completion data, which we'll be building on in this post.

Pass success rate by destination

The image shows the probability of completing a pass into different areas of the pitch. We're not worried about where the ball is coming from for the moment, but are looking at the chances of passes into different areas being successful.

It's clear to see how - playing from left to right - passing accuracy starts to break down in the opposition half and then drops dramatically at the boundaries of their penalty area.

Even with half a season's worth of passes and shots, we're going to struggle with the number of data points available as this analysis progresses, so let's merge the granularity of that first image into some larger pitch areas.

Pass success rate by destination

We now have a picture of how difficult it is to pass into each area of a football pitch. What about shots?

From the same dataset, here's an average player's probability of scoring with shots from different pitch locations. Penalties are excluded and I've hidden squares with fewer than twenty shots to clean the data up a little.

Shot conversion rate by shot location

As a manager, you're on the horns of a dilemma. Scoring probability climbs to over 30% in the centre of the six yard box, but your chances of passing the ball into that location are slim.

What if we combine the two visualisations?

Pass success rate multiplied by scoring probability, gives an indication of the likely success of an attacking strategy. Pass to an easier area outside the box and shoot from there? Or attempt to work the ball closer, at the risk of losing possession?

Pass success probability * shot conversion rate

It turns out to be far from a clear cut-choice. There's a relatively large area, stretching from the edge of the six yard box, to well outside the area, where penetrating that area with the ball and then scoring once you have are quite evenly balanced at 2-3%. It's not as simple as 'closer to the goal is better' and the balance in one game is almost certainly dependent on passing quality of the individual teams and how well their opponents defend.

If we box out that 2-3% conversion area, we can move the analysis on another step.

Pass success probability * shot conversion rate

How should a team attempt to move the ball into that boxed-out shooting zone? There are three broad choices: Directly from the direction of the centre circle, diagonally, or from the wings.

David Moyes has come in for a lot of criticism this week following Manchester United's draw with Fulham, where his players hit over eighty crosses in ninety minutes. We should be able to show here whether crossing, or a direct approach, is the more successful strategy.

Probability of achieving a successful pass into shooting area

Note that I've changed the colour scale on the above image to peak at 75% rather than 100%, since the average success rate of these passes is lower than when considering the whole pitch. Squares are only shown if they've been the origin of at least twenty passes.

Once you move beyond the eighteen yard line, pass success probability drops off quickly. Touchline crosses from a 'chalk on his boots' classic winger have success rates as low as 30%. Other things being equal, the best chance of passing the ball into our key zone comes from a direct, or diagonal move.

If you're thinking "but that's not fair, most of the passes included here will be targeted at locations outside the box", then you're right. Let's tighten up our key shooting zone, to a central area of the eighteen yard box surrounding the penalty spot.

Probability of achieving a successful pass into close shooting area

Still want to hit crosses all day?

The probability of a pass from the wings finding a team mate in the shooting zone is 30-40%, while moving through the central area has a success rate of 40-50%.

This isn't the end of the story, but it's where I'll stop for now. There are many more factors to be considered, including absolute volume of passes and the fact that a successful pass isn't the same as creating a shooting chance. This analysis will provide a base to work from though and one that I'd like to extend next into different types of teams.

Ultimately, I hope that this type of analysis could answer question such as...

Should teams with worse passing shoot more often from long range? And vice versa, where is the optimal shooting area for a team that passes with a very high success rate?

How do optimal strategies change, based on specific opponents?

(using significantly more data) Can we identify hotspots where passes into the shooting zone have higher success rates? Versus specific opponents? When specific defenders are on the pitch?

Eventually, I believe an approach like this might be able to identify defensive weaknesses in a specific team and optimal attack strategies for their opponents.

24,000 tweets about #Sochi

noreply@blogger.com (Neil C) — Fri, 07 Feb 2014 16:43:00 +0000

Who's excited about the Winter Olympics? Happy about the games? Angry about their location?

Let's find out...

Searching Twitter for #Sochi yields 29,800 individual tweets.

Running those through TextBlob yields 24,000 tweets that can be analysed for sentiment - positive, negative, or neutral*.

And throwing the whole lot at Google Fusion Tables lets us map them.

Here they all are. Blue for neutral, green for positive and red for negative.

Or for bigger, go here.

Just the happy people?

And just the angry people.

That was fun.

Thanks to some brilliant people who make brilliant tools; Google for Fusion Tables, and the development teams behind TextBlob and Tweepy for their Python modules.

* Please note that automated sentiment analysis is far from perfect. Especially the way I've implemented it.

The three rules of business data visualisation

noreply@blogger.com (Neil C) — Fri, 07 Feb 2014 15:51:00 +0000

I love data visualisation; sometimes just for its own sake, but mostly when it makes life easier.

The Earth Wind Map is an example of the former. It's hypnotically beautiful.

This type of data visualisation isn't so good in business though, except to use as marketing material. If you want to build a stunning animation of your customers' behaviour to put on a big screen in the office, that's great, but watching it for five minutes every Monday morning is unlikely to help you identify problems with your website. If we want to gawp at something beautiful, we call up the Earth Wind Map; if we want to know whether to take an umbrella tomorrow, we go for a simpler forecast.

In business - and I count non-traditional businesses like sport within this too - data visualisation has two main purposes.

To help you understand the best strategy to adopt.
To get you to that strategy faster than you otherwise could have.

In order to achieve those ends, I work to three simple rules when visualising business data. The ideal business report, (visualisation, dashboard, call it whatever you like), should achieve these three things as quickly and as simply as possible. The higher up the management chain the report's audience, the simpler it needs to be and the more 'added extras' become a distraction rather than useful additions.

I'm not dismissing visualisations for inspiration, or for investigation, but in business the aim of communicating data is to make the right decision and to make it quickly. This is what reports are for and so I try to design reports to communicate these three things.

1. Where am I right now?

For the metrics that you know are important (you have identified those metrics, haven't you?) Where are they right now? This could mean yesterday, a total for the past seven days, a summary of the last fixture your sports team played, or any other - relatively short - time period that works for the business.

It's absolutely vital that you don't get carried away with which metrics you visualise here. I've written before:

"As analysts, we're often the ones selling dashboards, so lets be honest about what they do well. They show data. So to be useful, you have to be someone who needs to see that data - and I mean really needs to see it. Just the number. Not why the number, or where it came from, or what you might want to do about it."

Only visualise metrics where you fully understand what they mean and know at least some of the levers that you can pull to make them change. If sales drop, you know what that means. If some single number that's a complex blend of customer values, retention, acquisition, marketing ROI and God knows what else changes, what are you going to do about it? Simplicity is good. It's also much harder than complexity.

To divert into my football analytics sideline for a moment, this is why I'm not a big fan of numbers like PDO. The definition is complicated, the name is confusing and as a manager it's hard to know what to do about it, when it's not where you'd like it to be.

That's not to say there shouldn't be complicated metrics (for example to use as predictive tools) but I don't want them on my management visualisation.

Very often, the best way to communicate some simple KPI numbers is a simple table. Who says a data visualisation can't be 'just' a table? In the right place, tables are awesome.

Here's a visualisation of website metrics, that will work well provided you already know a bit about your website.

2. Is that good?

So now I know how much I sold last week and how much traffic we got to the website. But is that good? Put each number in context.

Context can mean a comparison with the past, or with a fixed target, or even vs. key competitors.

It doesn't matter how you do this - colour coding, text flags, Harvey Balls - as long as it communicates quickly and clearly. Personally, I'm quite partial to an old school traffic light, if only because even in the marketing industry, it's hard to find somebody who can get 'green is good' wrong.

Our weekly table of web traffic stats gains week-on-week or year-on-year comparisons and a set of traffic lights. Now you can instantly see if any of these numbers need attention.

3. Is it changing?

The last piece of the puzzle is to know if your metrics (otherwise known as KPIs - this isn't revolutionary stuff!) are changing.

Part one told us where we are.

Part two told us if where we are is good.

Part three tells us if the position is becoming better, or worse.

This section is where things can get overcomplicated if you're not careful. If you've got eight KPIs and you want to show a twelve week trend, then you've now got ninety six numbers to communicate. Tables just became a really bad idea.

Sparklines however, are fabulous.

Sparklines are mini charts designed only to communicate spikes and trends in data. All this section of the report is designed to do, is to give a manager a quick visual representation of 'going up', or 'going down' and how fast.

Our website report gains a set of twelve week sparklines and we're done. In one small report, we can see at a glance where we are, whether that's good and whether it's getting better, or worse.

I love data visualisation, but in business, we need to drop the pretty pictures and understand why we're visualising in the first place. Infographics are awful for communication. They're actually worse than writing down your report as long-hand text.

If a business visualisation doesn't help you to understand the best strategy to adopt and do it faster than a table of numbers would, then it's not worth having. Build infographics (if you must), learn D3 and build beautiful animations, but recognise that they're marketing collateral, not serious business tools.

In business, we need to know where we are, if that's good and if it's getting better or worse. The longer it takes to communicate that, the further behind your competitors you'll be.