Feedburner Stats with YQL

A short while ago (read: earlier today) I got the urge to add the (tiny!) subscriber count onto my blog here: mostly so that I can see the fluctuations without having to log into the Feedburner site and click around, but also to show the lucky few of you who do visit that you’re in esteemed company! Feedburner has a really nice little API which could be used directly except that I couldn’t find a JSON output format from it (to use via JavaScript) and, well, I wanted to keep playing with YQL. So here’s what I did.

Building on the idea of open data tables, I made a very quick table (see it on GitHub) which plugs into Feedburner’s API and allows us, with a few different options, to grab data about a specific Feedburner feed by using queries like select * from feedburner.feed where uri="cowburn"¹.

Querying against the table

Since the table is just my own, and not part of the main collection of contributed tables, we have to tell YQL about its location before we can do any queries on it. To do that, simply use use pointing to the url of the XML file: use "http://github.com/salathe/yql-tables/raw/master/feedburner/feedburner.feed.xml";

Once YQL knows about the table, we can query against it (by default the table name is constructed from the file name, feedburner.feed.xml means we use feedburner.feed as the table name). The table is set up to allow three different methods of grabbing the feed data.

1. Up-to-date Statistics

If just the feed name is provided then only the most up-to-date information about that feed will be returned. Feedburner does not let you get stats about today, so the most recent data will always be from yesterday. So select * from feedburner.feed where uri="cowburn"¹ gives one result containing yesterdays statistics; subscriber count (circulation), the date, the number of hits the feed got and the reach of the feed.

2. Get last n days of stats

If you’re after more than just the latest figures, simply add a days clause to the where portion of the query: … where uri="cowburn" and days="7"²

3. Get a specific set of dates

If the last n days is too recent for you, there’s also the option of passing along a range or series of dates with, conveniently enough, a dates clause. This value follows the format for formatting date ranges of the Feedburner API. So to select feed data between July first and seventh from 2009 you would simply add … where uri="cowburn" and dates="2009-07-01,2009-07-07"³

Note that you cannot combine the days and dates in the same query as that would be silly.

Adding Subscriber Count to the Blog

To add the subscriber count to the page (next to the “subscribe” link in the header), just takes a few lines of JavaScript (I used jQuery) to pull a JSON-formatted response back from YQL. The console even automatically creates an URL for you to copy and paste in the section entitled “The REST query“.

The actual jQuery/JS code is not particularly noteworthy, all it does is prepare the URL to the YQL API, asks that URL for some JSON and then parses the response into a HTML span tag which is injected within the “subscribe” link of the navigation. This blog post is more about the data table, than grabbing JSON and injecting elements into the DOM (which is super-easy with jQuery anyway). Would you like that process described a bit more thoroughly? My feed widget with YQL and jQuery post shows you some actual code.

Well that’s all there is to say really. If you want to use the Feedburner data table then by all means feel free—it’s up there on GitHub so you can link to the file or fork and make your own changes.

Footnotes

1. Try select * from feedburner.feed where uri="cowburn" in the YQL console
2. Try select * from feedburner.feed where uri="cowburn" and days="7" in the YQL console
3. Try select * from feedburner.feed where uri="cowburn" and dates="2009-07-01,2009-07-07" in the YQL console

P.S. I promise (with crossed fingers just in case) that the next blog post will not be about YQL!