Dynamic item recommendations

So today, at AADL, we quietly rolled out dynamic item recommendations. That means that when you’re looking at a catalog record, you’ll be given Amazon-like recommendations (“Users who checked out this item also checked out these library items..”).

This little feature uses data from our opt-in patron history system. Since we’ve been offering that system for about a year now, we’ve been able to accumulate over 300,000 data points. This is another great example of how to reuse your existing data to enrich use experience. Like I’ve said before, you need to use the data you have to its full potential–even if it’s not the data’s primary purpose.

Ok, so let’s look at the nuts and bolts of this. I was able to cobble this together with two SQL queries. Actually, I was able to get the same results with a single nested query, but for some reason, non-cached results took upward of ten seconds, so splitting the query seemed like the right thing to do.


$bibinfo = $xmlopac->get_opac_data($bnum);
$mattype = $bibinfo[mattype];

$rec_uids = db_query(“SELECT DISTINCT(uid) AS uid FROM iii_hist_data WHERE bnum = ‘$bnum'”);
$subcrit = ”;
while ($rec_uid = db_fetch_array($rec_uids)) {
if ($rec_uid[uid] != $user->uid) {
$subcrit .= $rec_uid[uid] . ‘, ‘;
$subcrit = substr($subcrit, 0, -2);
$rec_bibs = db_query(“SELECT DISTINCT(bnum), COUNT(bnum) AS total FROM iii_hist_data WHERE mattype = ‘$mattype’ AND bnum != ‘$bnum’ AND uid IN ($subcrit) GROUP BY bnum HAVING count(bnum) > 2 ORDER BY RAND() DESC LIMIT 5″);

if (!db_num_rows($rec_bibs)) { return; }
while ($rec = db_fetch_array($rec_bibs)) {
// Do your business here

So basically, you can see what’s happening here. First I get an array of all the other users who have checked out this item. Then I get a list of items that three or more of those people have checked out and display it randomly (so it’s not always the same list). Pretty easy, of course you need to grow that data before it becomes useful. 300,000 records makes the results pertinent, 300 does not.

If you’re curious, head on over to AADL’s catalog and test it out.. you might stumble on something new.

The next step, of course, in all this SOPAC business is to try to determine how check-out patterns are changing and whether it will have an impact on collection development.

About this entry