We’ve been working with Todd Andrlik to help him automate his ranked list of top marketing blogs called the Power150. As he Todd Andrlik to help him automate his ranked list of top marketing blogs called the rolls out the new product, we thought a small case study of the project was in order.
Todd has been painstakingly maintaining an Excel spreadsheet of sites. To publish a new ranking, he had to check each of the ~200 sites by hand, looking up the Google Pagerank, the Bloglines profile, and the Technorati ranking by hand. (My hands are tired just thinking about that.)
He would then sort the results in the spreadsheet, and then create an HTML table that reflected the new ranking.
The amount of effort in this process was daunting, and it prevented Todd from adding too many more sites to his process. We traded some emails and talked about options for publishing the list.
Clearly this was a great use case for using system APIs and automation wherever possible. In a few days we worked up a solution that dramatically reduced Todd’s manual steps. Here is how the new process works:
1. Todd now maintains a Google Spreadsheet with a list of marketing blogs. The spreadsheet contains the blog title, URL, and RSS/Atom feed URL, and his personal rating of the site. He does no manual checking of any other stats.
2. We wrote a libray of PHP classes to use the APIs for Google Spreadsheets, Google Pagerank, the Bloglines API, and the Technorati API. (We may release some of this code as open source if there’s interest.)
3. For each site in the Google Spreadsheet, we get all statistics needed from each site API and cache the results on a 24-hour basis. The sites are then sorted according to Todd’s algorithm, and then PHP is used to generate the HTML table of the results.
Todd can now publish a new list of rankings as he sees fit, and all of the manual stats checking, sorting, and HTML generation is done away with. He can now easily add a lot more sites to the Power150 portfolio without a ton of work on his part. He still reviews each site and gives them a score, but the manual gruntwork is nearly eliminated.
- For now, Todd can publish the new rankings on demand with just a few clicks. We’re still keeping an eye on how well it works before we go to a fully-automated solution.
- OPML generation of the Power150 feeds would be nice.
- We’re also open to open-sourcing our PHP libraries for Google PageRank, Technorati, and Bloglines. If that is something you might be interested, drop a comment with a valid email address (it won’t show on this page) and we’ll let you know if and when that happens.