Using Google BigQuery, Google Cloud Natural Language API and Looker to Work-Out Exactly How Much…

Mark Rittman

Back when the incident with my WiFi kettle went viral on Twitter and ended up in the national newspapers, on Radio 4’s The Today Programme and as one of the questions on Have I Got News For You, I scraped the reader comments from the Guardian article that first broke the story along with the Mail Online follow-up article that put a more negative spin on it (“Couldn’t he just flip the switch”, etc).

I thought it’d be interesting to prep and then load all the comments in Google BigQuery using Google’s new Cloud Dataprep tool, then analyze it all using Google’s Natural Language API and some geocoding libraries to find out exactly how much, and at what intensity, Daily Mail readers hated me and what parts of the country I ought to steer clear of for a while, or at least be prepared to make a cup of tea using a kettle that I have to walk over to and switch on.

Visualizing the data that came out of the exercise using Looker, the BI tool I use day-to-day in my role as Product Manager for an analytics service running on Google Cloud Platform, the first thing I spotted was the significant difference in sentiment between Guardian readers (green in the chart below) and Mail Online (red), where you can see the Guardian picking up the story first and comments being typically neutral or positive, whereas the Mail’s readers hated the story almost right from the start and hated it even more the next morning.

As the Mail Online lists the reader’s location alongside each comment I was able to generate latitude and longitude map references for most of them, and mapping them onto a Looker map visualization shows exactly which parts of the UK hated me the most that day.

Clearly The Mail Online’s Scottish readers typically weren’t impressed with this English innovation, and nor were the Mail’s ex-pat readers down-under who seemingly felt the same way.

Once the Mail Online readers’ woke-up to the story in the United States they had a whole new level of disgust with that geek and his rubbish WiFi kettle.

I was about to blame Piers Morgan for this obvious change of attitude towards us Brits but then spotted, reassuringly, that they seemed to hate the Mail Online even more — all is apparently not lost with our American cousins.

So back to the UK. At postcode area level, then, which parts of the UK are a no-go area for a “Geek [who] buys himself a WiFi kettle then spends 11 HOURS trying to make it boil … to work alongside other devices in his £550,000 home.” — nice touch from the Mail journalist there, my wife was actually more annoyed they quoted the price we paid for it several years ago whereas I was hoping they’d bring in that other Daily Mail obsession about whether something does, or does not give you cancer through in this case the kettle’s WiFi base-station. Anyway, Hampshire’s a no-go area for me now, along with parts of Wales and the West Midlands although I’m safe in North Norfolk and Humberside at least for now.

If it all gets too much for me in the UK I can at least take refuge in Hong Kong, a place where they obviously appreciate innovative kitchen appliances integrated with other appliances through python scripts that work until the first device gets a new IP address when you unplug and then plug it in again to move it across the room.

Though I need to get there via some means other than stowing away on a container ship bound for Shanghai from the ports at Gateshead and Southampton, though they’re still a better option than hitching a ride on a trawler from Blackpool, the official centre-of-hate for this story in the UK.

Mail Online readers from Blackpool’s dislike for the story was surpassed only by the intensity of that sentiment, another metric that I could get out of the Google Cloud Platform NLP API that helps you understand the strength of the feeling someone had when expressing an opinion, not just the words they used.

So how did this all look when I swapped out Mail Online readers for the five thousand or so Twitter users that retweeted, commented on or otherwise helped spread the story around the Internet those two days back in October 2016? The story started off from the UK, obviously, but with a negative spin that over time, as it travelled around the world became more positive…

… although the Australians clearly still weren’t impressed.

If you’re in London this Tuesday evening and interested to know more about the work I do with BigQuery and Looker in my Product Management role at Qubit, come along to the Conversion Rate, Analytics and Product Unconference Meetup where there’s still a few spaces free at the time of writing and I’m one of the three speakers that evening.