Permalinks, low-rent data viz and other stupid Caspio tricks

Today marked the release of a new Times investigation into the poor performance of for-profit fundraisers hired by not-for-profit charities. The poster child is Citizens Against Government Waste (CAGW), an advocacy group that rails against reckless government spending.

According to reporting and analysis by Charles Piller and Doug Smith:

Records filed with the California attorney general's office show that over the last decade, for-profit fundraisers for [CAGW] kept more than 94 cents of every donated dollar.

And the bigger picture:

In more than 5,800 campaigns on behalf of charities that were registered with the state attorney general from 1997 to 2006, the fundraisers reported taking in $2.6 billion. They kept nearly $1.4 billion -- about 54 cents of every dollar raised.

As part of our effort to package the story for the Web, I worked with Times staff to publish all of the records collected for analysis as an online database. What we came up with allows readers to look up the track record of individual charities, browse charities of similar types, and quickly seek out the most and least efficient charities using a goofball visualization I cooked up with our graphics guy, Thomas Lauder. You can check it out here.

The app was pulled together using Caspio, a browser-based program for building data-driven web applications. While it is technically true, as the site claims, that developing a working Caspio app requires "no more programming," my experience has been that you're going to have to invest a significant amount of time hacking at its kludgey GUI to come up with something half-way decent. Whether you want to invest your time doing that, or mastering a more robust development option, is entirely up to you.

Other, smarter people have invested a goodly amount of space to explaining Caspio's deficiencies, so I'll leave that to the links. Instead let's break out below a couple tricks that helped me at least marginally improve today's product, in hopes they might be useful to somebody. (Though I suppose any "improvement" is a matter of opinion! Let me know what I fucked up.)

Hack 01: Roll your own forms

Caspio offers several templates. The one I use most often is the "search-and-result" set. It accepts a user's input and returns any matching values. Might sound complicated, but it's the same thing as Google. You pop something in, and you get back any hits. You can examine specimens in the wild here, here and here. (Thorough readers will notice that, at least at the time of writing, the Cincinnati app is dead on arrival, bearing only the cryptic message "DataPage does not exist. (Caspio Bridge error) (50501).")

Since the "search" and "result" sides of the app are glued together in a single panel, the search box can't be very easily plugged in around your site. You'll have to find a way to make Caspio's gunky JavaScript code work in each and every location where you want to encourage user input. The result is that most Caspio apps -- including all three linked above -- tend to live in backwater, standalone pages, lampooned by Matt Waite as "data ghettos." (Personally, I prefer "Ghettos of the Mind.")

That might be acceptable if you're looking to make a destination page for your corporate intranet, like an employee directory. But it's just not good enough for news Web sites, which draw a huge share of their incoming traffic on the homepage and the first page of featured stories. If your database isn't prominently displayed there -- and it isn't unless you've got a search box or other entry point gaping open on the page -- you've losing a whole lot of potential traffic. I think there's something to be said for a "data central" section, but you're probably giving up a lot of clicks if you're waiting for people to hit the vague looking "data" link in your left-nav bar.

So what's the hack? It's pretty simple. Just build a search-and-result box without a search, which you then provide with your own custom HTML. You can then reuse the search box anywhere you want: the frontpage, right-rail, story-level reefer or -- heaven forfend -- standalone "data ghetto."

Here's how you do it, shot by shot.

First turn on the advanced options and allow parameters.

Tell Caspio it should look for an external parameter in the URL, rather than use it's native search form.

Tell it which field it should run the inputs against. In this case, we're building a search on a data table's "name" field.

Now instruct Caspio to look for the user input after a query string variable called "name," and to evaluate it against the data table using "contains" style matching, as opposed to "exact" or "starts with" matching. If you were using a unique identifer like a primary key for the lookup (as you likely would if you were building a dropdown menu rather than a search box), you would probably want to use an "exact" match instead of "contains."

Then finish up by telling Caspio how to handle what to do with blank variables or circumstances where you don't have a match.

Now you should deploy the Caspio app as you normally would, and then craft an HTML form on a different page that points to its location, placing the user's input in the query string. For example, the search box in our charity app looks like this, with all the styling removed:

<form action="http://www.latimes.com/news/local/la-charity-search-name,0,5949050.htmlstory" method="get">

<input maxlength="100" name="name" size="6" type="text" />

<input type="submit" value="Go" />

</form>

That'll send people to the following link, where they'll see the search results as they're formatted by the Caspio GUI.

http://www.latimes.com/news/local/la-charity-search-name,0,5949050.htmlstory?name=Red Cross

Hack 02: Permalinks for easy deep linking

An added benefit of using Hack 01 is that your results pages can have permalinks, albeit long and ugly ones. The link above will always call up the results for a search of "Red Cross," and if you build all your drilldown pages this way, using a primary key as the external parameter, they'll each have a distinct URL. That came in handy with the charity story because it allowed me to deep link charity names and types from the story down into the database (ex. Citizens Against Government Waste and disaster relief)

Hack 03: Low-rent data visualization as a novel entry point

Once you set up the query string, there's no reason that your custom entry point must be an HTML form. My editors wanted to group the charities by their fundraising efficiency and give readers the chance to look at them group by group (i.e. which are the best, average, worst, et cetera.) We could have made a dropdown box, ordered list or sortable table. But the idea Thomas Lauder and I hatched instead was an interactive grid modeled on the Morningstar Style Box that sorts charities by the size and efficiency of their fundraising efforts. I built it with an old A List Apart trick so that each square links to the list of charities in its category. Take a look at it here. We also made a smaller version, currently on the site's frontpage and in a story-level reefer. Here's a hideous screenshot to prove it. You'll have to go to the site if you actually want to play with it.

Alright, I've got a few more up my sleeve, but that's probably enough for now. Per usual, far be it from me to say that these methods are the only or most efficient way to solutions. They're just the ones I got done on deadline. Feel free to tell me where I screwed up, or how I can do it better next time.