Monthly Archives: May 2009

Using Google Spreadsheets as a database (no, it really is very interesting, honest)

This post by Tony Hirst should be recommended reading for every journalist interested in the potential of computers for reporting.

Why? Because it shows you how you can use Google spreadsheets to interrogate data as if it was a database; and because it demonstrates the importance of news organisations releasing data to their users.

Put aside any intimidation you might feel at the mention of APIs and query languages. What it boils down to is this: you can alter the web address of a Google spreadsheet to filter the data and find the story.

Simple as that. 

Hirst uses the example of the spreadsheet of MPs expenses recently released by The Guardian (they’ve also published Lords expenses). By altering the URLs this is what he generates (I’m quoting his bullet points):

OK, you need to know the words to use (and if you have a link to an easy reference for these let me know*), but this is still a lot easier than using programming languages and databases.

As I say, this also illustrates the importance of publishing raw data so users can interrogate it in their own ways, which is precisely what The Guardian’s Data Store has been doing, meaning that people like Tony can create interfaces like this.


*Tony has very generously created this page which helps you formulate your search – and generates the URL. If you were working on a different spreadsheet you could just replace the spreadsheet URL and change any column references accordingly.

UPDATE: Tony also has a version which allows you to pick from Guardian datasets.

Telegraph drops to 5th place in Google results for MPs expenses

Google has dropped the Telegraph to 5th place when you search for MPs expenses for some reason, as revealed here.

Last week Google had pages from the BBC 1st and the Telegraph 2nd – even though the Telegraph is the primary source of all this material.

Today the search results are even worse:

  1. In first place, we have the BBC, with one page from yesterday and from October 2004 – is this what seachers want?
  2. Then comes the Guardian, with its MPs’-expenses landing page followed by a story from Saturday. That might be fair enough for 2nd place.
  3. Then – tangentially interesting I suppose, but the page is dated 2004.
  4. Then the Daily Record from Saturday. I’ve nothing against Scottish newspapers. But really – ahead of the Telegraph?
  5. And finally, the Telegraph with one page from Sunday and its MPs’-expenses landing page.

The Telegraph is benefiting from the 3 news stories above the normal results. And Google is probably having trouble identifying the original source because no mainstream news organisations link back to the Telegraph. But for a topical news story, this set of web search results is really bad.

Search results for MPs expenses at Google

Search results for MPs expenses at Google

Wolfram Alpha for journalists

The much-hyped search engine – sorry, “computational knowledge engine” – Wolfram Alpha launched over the weekend. Its use of databases and semantic search should be particularly exciting for journalists because a) it searches parts of the ‘hidden web’ that most search engines don’t reach (i.e. databases); and b) it has the potential to throw up quick answers to questions about relationships and facts that Google is also not great at.

Now, note that I say “potential to” – Wolfram Alpha is in its very early days. Below I talk about what it can do now; I expect it will be years before it truly becomes a game-changer.

The ideal search engine for a pub quiz  

Wolfram Alpha deals in facts. It deals with natural language questions quite well, but only if you’re looking for something very specific about something well known. 

How old is Barack Obama?‘ worked, for example; as did the same enquiry for Gordon Brown, and indeed, possible future Labour leader Alan Johnson. It could also give me the ages of Madonna, rapper Chuck D and David Beckham, but not Newcastle midfielder Kevin Nolan.

How about ‘5 largest countries in the world?’ Not immediately, but it did suggest ‘largest countries’ which brought up rankings by area, population and GDP . Continue reading

‘It’s an interaction crisis’ – Umair Haque on how economics are changing

I only recently came across this video of Umair Haque talking about some of the economic changes we’re living through and outlining 5 principles for businesses looking to adapt to those. Well worth watching.

Umair Haque @ Daytona Sessions vol. 2 – Constructive Capitalism from Daytona Sessions on Vimeo.

Search Options: Google adds more intuitive search tools, ‘takes on Twitter’

It’s often said that Twitter’s big advantage over Google is its ability to allow you to conduct ‘real time search’ – if an event is happening right now, you don’t search Google, you search Twitter.

But today Google has announced a series of features that, while still not offering real time search, take it just that bit closer. For me it is the most significant change to Google’s core service in years. 

Here’s the video:

This week, while talking to my students about the ability to search by date in Google, the computer assisted reporting blogger Murray Dick mentioned how unreliable the feature was, so I wouldn’t get too excited. 

What is new, however, is the ‘recent search’ facility, which brings up results from the past hour or two. Continue reading

8% of traffic from social sites gets an amazing 8% of its visitors from social sites like Digg, Delicious, Reddit and Stumbleupon, Julian Sambles, Head of Audience Development, has revealed.

The figure explains how the Telegraph is now the most popular UK newspaper site.

75,000 visitors a day

The Telegraph had about 28 million unique visitors in March, which means social sites are sending it almost 75,000 unique visitors a day.

Search engines are responsible for about a third of the Telegraph’s traffic Julian also revealed – or about 300,000 unique visitors a day.

This means the Telegraph gets 1 social visitor for every 4 search ones – an astonishingly high ratio.

You can read more of what Julian said about the Telegraph’s social media strategy here. The statistics were originally given for an article on social sites on FUMSI.

Citizen journalism and investigative reporting: from journalism schools to retirement communities

The myriad numbers of citizen journalism sites that pop up everyday seem to suggest that the media can fulfill the purposes of democracy by merely offering their audiences a forum to express themselves.

However, to tap into its full potential, participatory journalism should try to do something in addition to what mainstream reporting already does – such as expanding source diversity, shifting focus to neglected sections of the population, or pursuing different angles and perspectives on a story. If not, it is not doing much more than using its readers as a form of cheap labor, and perhaps laying off journalists while it’s at it.

Citizen journalism is hardly beneficial when it merely propagates the flaws of traditional reporting. Huffington Post’s Off the Bus produced many stories on the US Presidential campaign last year – but the one we remember most vividly is Mayhill Fowler’s reporting of Barack Obama’s “bitter” comment – the story that put gotcha journalism from mainstream reporters to shame.

The paucity of good investigative reporting through citizen journalism is not surprising, considering the amount of effort such stories require from news organizations in terms of coordination and oversight. Perhaps, most importantly, they require a huge time investment from the audience. While people might be easily persuaded to relay food-item prices from their grocery bills, they are less likely to pursue public officials or make trips to government offices to retrieve information.

Which is why magazines like The Nation are allowing their audiences simpler methods to contribute to significant news stories. With its “Ask the President” feature, the weekly is encouraging readers to pose questions for the Obama administration’s upcoming press conferences. Queries that receive the most votes will get asked by Nation journalists, pending agreement from the White House. This is perhaps the digital equivalent of newspapers inviting their readers to town hall meetings to question public officials.

News organizations are also trying to encourage investigative journalism “from the desk.” With the amount of interactive tools available online, it is perhaps easiest to get readers to contribute through their computers since they already spend several hours in front of them. National news stories especially lend themselves well to this form of reporting.

The nonprofit investigative journalism site Propublica hopes to analyze Barack Obama’s stimulus package by encouraging audience contributions. Data and documents will be available on the site, and readers will be encouraged to offer ideas for stories and topics of newsworthy content. Details of how distributed reporting will be implemented have not been worked out yet, but as Senior Editor Eric Umansky reasons, the breadth of the stimulus projects and their potential effects are so huge that there simply are not enough traditional journalists to cover the subject. But with the help of citizen reporters all around the country, Propublica can do a better job of reporting on all angles of the story. When you recall that the same idea allowed Talking Points Memo to break the news about the Bush administration’s firing of eight US attorneys in late 2007, it is easy to be optimistic about Propublica’s venture.

Another idea that is gaining popularity is the coupling of journalism school projects to citizen reporting. In this fast-changing media world where every citizen is a reporter, students of journalism should be specifically trained to tap into the vast talent available in the community, writes Elizabeth Zwerling. That is exactly what the Annenberg School for Communication in Los Angeles is attempting to do with its hyperlocal news site, Intersections.

The project is shining the spotlight on the less privileged classes that mainstream media has long ignored with its profit-centered interest in affluent communities. Online journalism often reproduces this censorship of omission because of the inherent digital divide. Students at the Annenberg School, however, are being trained to report on hyperlocal issues affecting urban LA communities. Local residents, many of whom include working class immigrants, work with students to transmit their photos, videos and stories through cell phones.

Sites such as Texas Watchdog, on the other hand, are implementing programs to train civilians to become watchdogs of the government; the program teaches citizens to access and review public documents, among other things.

Another potential goldmine for citizen journalism at the hyperlocal level appears to be populations of retired individuals, who have both the time and inclination to perform watchdogging functions for their communities, as Jack Driscoll found with Rye Reflections, a user-generated site run by retirees in a small community in New Hampshire. The drastic reduction in local news reporting by newspapers that have cut down their resources and budgets has meant that citizens are willing to take up the slack. This sort of community reporting offers people intellectual and social stimulation while fulfilling civic needs, according to Driscoll.

In addition, retired professionals can often lend their specific expertise to investigative news stories, as former engineers and lawyers in the community of Fort Myers, Florida proved during the News-Press’ investigation of a local utility company. However, Driscoll does not succumb to the rosy-eyed view that this sort of reporting can replace hardcore investigative journalism at the national or international level, or in specialized fields like science and medicine.

It’s little surprise then that the much talked-about Huffington Post Investigative Fund hopes to tap into the expertise of seasoned journalists to kick-start its investigative reporting exercise. Down the road, it will harness the power of its citizen volunteers. As Jay Rosen, who will serve as a senior advisor on the project, writes, “the best approach is to have no orthodoxy and to support very traditional investigative reporting by paid pros who are good at it, as well as teams of pros and amateurs, students working with masters of the craft, crowdsourced investigations, and perhaps other methods.”

A tall order to be sure. But news organizations need to quickly find ways to compensate for the dearth of resources and personnel in order to continue to perform in-depth investigative reporting, lest journalism may become completely irrelevant.

They’re not online (and why)

A tremendous onslaught of facts on the digital divide from Helen Milner – followed by some very useful analysis of the reasons why people don’t use the web. For my money, this is required reading for anyone interested in the wider issues around online journalism and engagement.