Getting started with statistics

Koha 3 has always had a selection of built-in reports, and Koha 3 adds additional reports and new a “Guided Reports” system (partially sponsored by this library). The Guided Reports system is still a little rough around the edges, but folks are doing some interesting stuff with them, in particular by using the system’s ability to run custom SQL queries via the Koha interface.

I’d like to take some time to explore how we’re starting to leverage the data that Koha collects to build some reports about how the library is being used. I’ll start with circulation statistics.

The Statistics Table

The health of the library is (for better or worse) judged by its circulation statistics, so that’s first priority. In Koha, every checkout, check-in, and renewal is recorded in one table in the database called statistics. Here’s what it looks like:

Field Type Collation Null Key Default
datetime datetime Yes MUL NULL
branch varchar(10) utf8_general_ci Yes NULL
proccode varchar(4) utf8_general_ci Yes NULL
value double(16,4) Yes NULL
type varchar(16) utf8_general_ci Yes NULL
other mediumtext utf8_general_ci Yes NULL
usercode varchar(10) utf8_general_ci Yes NULL
itemnumber int(11) Yes NULL
itemtype varchar(10) utf8_general_ci Yes NULL
borrowernumber int(11) Yes NULL
associatedborrower int(11) Yes NULL

Some of those columns aren’t even used–I’m not sure if they were in the past, or whether there were plans for them for the future. other, usercode, and associatedborrower don’t seem to be in use.

  • datetime records the time and date of the transaction.
  • branch is the location of the transaction.
  • proccode is related to tracking patron fines, payments etc.
  • value records a currency amount (for fines, payments, etc).
  • type records the type of transaction:  issue [checkout], return [check-in], renew, payment, or writeoff.
  • itemnumber is the id number (defined by items table) of the item that was handled in the transaction.
  • itemtype is a category assigned to the item as defined in Koha’s Item Type management.
  • borrowernumber is the id number (defined by the members table) of the patron involved in the transaction.

When you check something out in Koha, a line is added to this statistics table (SELECTing datetime,branch,type,itemnumber,itemtype, and borrowernumber):

datetime branch type itemnumber itemtype borrowernumber
2008-10-07 14:07:31 CPL issue 40235 CIRC 20351

To get a quick look at how much you’ve circulated today, you could run this:

select count(*) from statistics where year(datetime) = year(curdate()) AND month(datetime) = month(curdate()) AND DAY(datetime) = day(curdate()) and (type='issue' OR type='renew');

The SQL matches the year, month, and day of transactions against today’s date and limits the results to checkouts and renewals. Here’s our count for March 16, 2009:

count(*)
2190

1 row in set (36.76 sec)

Notice how long the query took. The statistics table gets really big: ours has data going back to May 2003, and it has 7,581,248 rows in it. That makes for some slow queries.

Getting a quick count is a great way to put a number to your day’s work. At the end of the month, though, you’ll want to get some good numbers to show your Board about what kind of business you did. Let’s break it down by collection code so we can see what kind of materials our patrons checked out. We’ll look at the Athens branch’s circulation last month:

SELECT items.ccode, COUNT( statistics.itemnumber ) AS count
FROM items, statistics
WHERE statistics.branch = 'APL'
AND statistics.itemnumber = items.itemnumber
AND year( statistics.datetime ) =2009
AND month( statistics.datetime ) =02
AND (
statistics.type = 'issue'
OR statistics.type = 'renew'
)
GROUP BY items.ccode

We get a nice breakdown of how each category (or at least a selection, in this example) fared during the month:

ccode count
AB 427
AF 1355
AV 1002
CDM 681
DVD 1006
EASY 1754
JF 519
JNF 1028
LP 667
MYS 770
NF 1994

You should be able to try out these examples yourself using the Guided Reports system in Koha (or right in MySQL if you have direct access to your database).

3 thoughts on “Getting started with statistics

  1. Sharon Moreland

    Owen,
    Bless you for showing me how to combine Issues and Renewals into a single statement, freeing us from running the statistics wizard at all!
    Sharon
    NExpress

    Reply
  2. Humberto Blanco

    Hi,
    First, thanks for this article. In second place, I have a question for you. using reporting, how can i know who (staff member) was loan of material?, because i can know the borrower but not the staff member.

    Reply
    1. oleonard Post author

      If you have the system preference “IssueLog” turned on all checkouts will be recorded in the Koha database’s “action_logs” table. You’re supposed to be able to access this information from the “Modification log” tab on a bibliographic record in the staff client, but for some reason this isn’t working. You can query the database directly, however at the moment Bug 7241 prevents the action log from recording the item number of a checkout. Instead it records the biblionumber. This could present problems generating accurate reports. I’m testing the patch for that bug now, so I hope the fix will be in the next release.

      Reply

Leave a Reply to Sharon Moreland Cancel reply

Your email address will not be published. Required fields are marked *