Citizens’ Agenda, Fusion Tables and JSON (Show your work)

There is a lot of positive buzz around in regards to “The Citizens’ Agenda” project that I’m involved with. You can see all the great work that Studio20 and The Guardian are doing here. And don’t forget to tweet any #unasked questions you might be thinking about.

This initial set of data being published is a set of 839 categorized questions asked at  20 Republican presidential debates. The results were stored in a Google Fusion Table. While not completely normalized, there are a lot of interesting queries and visualizations to be made with the data.

My initial thought was to get it into JSON format. Then we could build on top of it and query it in more fruitful ways.

I had never even seen Google Fusion Tables. I take that back. I had watched a demo at the Knight-Mozilla OpenNews “hacktoberfest” in Berlin in September by Chris Keller, but had never used them myself.

So I said, “Hey Chris, what do I do?” He provided some examples using JQuery which were really helpful to see how the API works in general.

And he pointed to some existing ways to get JSON back from the API including Fusion Tables to JSON and a very interesting undocumented feature of the Google API itself.

As I mentioned, the table data was not quite normalized. For example, in a column called “Question directed to,” the value could be “Romney” or “Romney, Paul.”

So a query for “Romney” might miss that second example, since matches need to be exact. Also, the column with the date had additional text in that field describing the  particular debate.

In other words, getting meaningful queries was going to take some text processing from within the application. So I thought I’d try out the PHP API client that Google provides.

Here is the result. This example returns the entire data set in JSON. I will followup with some more specific queries. You can see a live example at http://citizensagenda.net/debate-questions

// include fusion tables API lib
// http://code.google.com/p/fusion-tables-client-php/
require('../classes/fusiontables/clientlogin.php');
require('../classes/fusiontables/sql.php');
require('../classes/fusiontables/file.php');

// PHP CSV function breaks because of new lines so I use parsecsv library
// http://code.google.com/p/parsecsv-for-php/
require_once('../classes/parsecsv.lib.php');

// authorize (use your creds)
$token = ClientLogin::getAuthToken('my-google-username', 'my-google-password');
$ftclient = new FTClientLogin($token);

//select the data (must know fusion table id)
$fusiontableid = 'fusion-table-id';
$csvreturn = $ftclient->query(SQLBuilder::select($fusiontableid));

// instantiate parsecsv object and process the google data
// this populates $csv->data with an array
$csv = new parseCSV();
$csv->auto($csvreturn);

//encode array into json, set content-type header and print it out
$json = json_encode($csv->data);
header('Content-type: application/json');
echo $json;

Innovation key to sustainability in journalism

It’s all still settling in, but my experience last week in Berlin has caused my outlook on the future of journalism to go from one of great concern to one of great promise.

I was honored to take part in what we have come to call Hacktoberfest, a five day hackathon sponsored by the Knight Mozilla News Technology Partnership or MoJo (Mozilla Journalism).

Twenty developers and journalists joined a bunch of Mozilla folks and members from five participating news organizations (Boston Globe, The Guardian, Zeit Online, Al Jazeera English and BBC).

What transpired was an incredible “mosh pit of brains.” I may be misquoting someone on that, but you get the idea. (also not to be confused with a Bad Brain Mosh Pit)

I learned a lot.  I learned about Wiener Sausages and German beer, for sure, but also about the sausage making necessary for news innovation. The collaboration which ensued turned good ideas into great ones and even spawned completely new projects out of common desires and needs. It was the fellowship of the thing.

But it wasn’t just academic discussion about futuristic dreams. We addressed questions like “how can we fix the broken comments model on news sites and the web in general?” And, “How can we pay for investigative journalism when sites rely on a page-view model?”

In fact, sustainability and business model ideas permeated the discussions that surrounded the hacking. All of those ideas seemed to accept that innovation was a key part of building a successful news business, both now and in the future. It’s hard to believe we still need to beat that drum in 2011, but we do. More of the same will not cut it, even if it is more profitable in the short term.

The most recognizable feature of the Berlin skyline is The Fernsehturm, or television tower. It was built in the late sixties to symbolize the the power of the German Democratic Republic. As I wandered the streets beneath it, I couldn’t help but think that this metaphor for Communist Berlin had a lesson for news industry.

It can either use its resources to convince the world from high up that it is strong, necessary and omnipresent, or it can join the democratic revolution of news happening on the ground. Because, for better or worse, it’s happening. The metro has left the station.

I’m reminded of a scene in Citizen Kane that serves as a lesson for what stagnation can do to a company, and that there are those who would rather just let things wind down naturally.

You’re right,” says Charles Foster Kane,  “I did lose a million dollars last year. I expect to lose a million dollars this year. I expect to lose a million dollars next year. You know, Mr. Thatcher, at the rate of a million dollars a year, I’ll have to close this place in  . . . 60 years.”

Unfortunately most newspapers don’t have that luxury.

But that shouldn’t make them short-sighted. It’s a cold but undeniable truth that news companies have two paths.

One is to slowly decline, maintaining as many of the employees that rely on the print product for as long as possible.

The other is to make a bet on the future, which may hasten the termination of some employees but will enable those companies to garner a stake in the future of both journalism and marketing on the web.

I choose the latter.

It’s not margins that will save these companies but innovation.