RA-Services Usage Report Prototype
Report Description Detail
This report is modeled on ID405 (REP-243). It is a report on number of circulation transactions by service-point and transaction type, with time aggregated to date, day of week, and hour of day. Can also be filtered to a particular date range (either a full year or a smaller date range, as needed). Alternately could be aggregated to transaction count per week. Alternately could be filtered to a single service-point. This report can be used to report weekly statistics over a full year or to review hourly statistics for a shorter time period to review staffing needs.
Note: the only data we have about renewals is a total count, no location or timestamp for the individual renewal transactions. (Confirmed on May 13, 2019 with Marc Johnson, who said "That is correct, there isn’t a separate record of each renewal. That would need to be inferred from the log of requests made to APIs at the moment. Some of it _might_ be inferrable from loan action history.") In principle, we could at least infer renewal time by comparing versions of the loan record to see when the renewal count changes, but for this report that is not useful because we also need a service-point. If the logs track both time and service-point for renewals, we could explore how difficult it would be to ingest the logs into the LDP. As far as I can tell, the log being discussed is a "circulation transaction log" that might be derived from the audit (loan action) history, but it hasn't been designed yet. Cate Boerema, Andrea Loigman, and Joanne Leary had a discussion about this. Joanne and Andrea really want renewals to be tracked, but I don't think anything has really changed yet.
Note: ideally, this would track the following transactions (separately): checkout, checkout from hold, checkout from reserves, return, renewal. Right now, FOLIO does not seem to be storing individual renewal transactions, other than to increment a count. If renewals are ever tracked with time and location information, those should be added. Checkout from hold would require knowing the item state before checkout. Checkout from reserves would require knowing the item effective location before checkout.
Question from report requestor: will self checkout machines be stored as service-points? This would be ideal, but because they are a system integration, it's not clear if they will have the same status as a service-point. If not, should we look into bringing that data into the report some other way?
| Folio Attribute (Module/Path:Object) | Folio Data Element Description | Parameters/Query |
|---|---|---|
| Loan ID (mod-circulation-storage/loan-storage/loans:id) | UUID generated by a loan (identifies a specific circulation transaction) | This element will be counted to generate the number of transactions (COUNT(id)). Note: want to count Loan ID separately for each transaction type (checkout and checkin) since they are separate activities at potentially separate service-points and times. |
| Loan Date (mod-circulation-storage/loan-storage/loans:loanDate) | The date an item was loaned | This data element can be used to filter the report to a date range (where loanDate Between [start] And [end]). It is also used for the date aggregation (to weeks or days/hours). Will also need to (automatically) calculate day of week from this value. Note: this field is stored as a string but should follow a standardized datetime format (RFC3339 / ISO 8601 date and time). From Marc Johnson: "It should be relatively unlikely [to introduce an error]. Clients which interact with the storage module directly could. With the typical circulation flow it should not be possible" |
| Return Date (mod-circulation-storage/loan-storage/loans:returnDate) | The date an item was returned (can be backdated by staff) | Note: this field is stored as a string but should follow a standardized datetime format (RFC3339 / ISO 8601 date and time). From Marc Johnson: "It should be relatively unlikely [to introduce an error]. Clients which interact with the storage module directly could. With the typical circulation flow it should not be possible" This is likely to be the same as the systemReturnDate, and the times when they differ it's probable that the report needs the systemReturnDate because that is when the work is really happening. Some people might want both return dates, but report should filter/aggregate based on the systemReturnDate. |
| Return Date (mod-circulation-storage/loan-storage/loans:systemReturnDate) | Date time when the returned item is actually processed | This data element can be used to filter the report to a date range (where returnDate Between [start] And [end]). It is also used for the date aggregation (to weeks or days/hours). Will also need to (automatically) calculate day of week from this value. Unlike loadDate and returnDate, systemReturnDate does have the date-time format validation applied. |
| Loan Checkout Service-Point (mod-circulation-storage/loan-storage/loans:checkoutServicePointId) | The service-point where the loan checkout transaction took place | This is one value for transaction type (checkout). This and the checkin service-point need to be checked against any service-point filter (e.g., ServicePointID == requestedServicePoint). |
| Loan Checkin Service-Point (mod-circulation-storage/loan-storage/loans:checkinServicePointId) | The service-point where the loan checkin transaction took place | This is one value for transaction type (checkin). This and the checkout service-point need to be checked against any service-point filter (e.g., ServicePointID == requestedServicePoint). |
| Service-Point ID (mod-inventory-storage/service-point:id) | The ID of the service-point | Join mod-inventory-storage/service-point:id to both checkoutServicePointId and checkinServicePointId to pull in the names of service-points. |
| Service-Point Name (mod-inventory-storage/service-point:name) | The name of the service-point | After joining based on service-point ID fields, include service-point name in report (for both checkout and checkin service-points). |
| Service-Point Code (mod-inventory-storage/service-point:code) | The code of the service-point | After joining based on service-point ID fields, include service-point code in report (for both checkout and checkin service-points). Mostly for completeness - not sure what code will be used for. |
| Service-Point Discovery Display Name (mod-inventory-storage/service-point:discoveryDisplayName) | The discovery display name of the service-point | After joining based on service-point ID fields, include service-point discovery display name in report (for both checkout and checkin service-points). The display name fields need to be checked against any service-point filter (ServicePointName == requestedServicePoint). Users will want to filter using name, not ID. |
| Item Status (mod-inventory-storage/item-storage/items:status:{name}) | The item's status (e.g. Available, Checked out, In transit, etc.) | Want to retain the status of the item before it changes to Checked out (will be mostly Available, but will sometimes be On Hold). This is important because if the item was on hold, then a staff member had to go retrieve it, and knowing that a staff member was necessary can influence staffing decisions. These transactions cannot be done by a patron at a self-checkout machine. Report requester acknowledges this is a stretch goal for this report. I expect any connection to item information will require looking for the version of the item that was active before the time of the checkout (or checkin). The checkout transaction will create a new version of the item record where status has changed, so this would require looking for the same id but a timestamp immediately previous to the timestamp of the checkout version. Could be tricky. |
| Item Material Type ID (mod-inventory-storage/item-storage/items:materialTypeId) | The item's material type id | This id joins to mod-inventory-storage/material-type/material-types:id |
| Item Material Type Name (mod-inventory-storage/material-type/material-types:name) | The name of the material type for this loan | This is the name of the material type for the loan. Material type was requested because some material types (e.g., equipment, technology) require staff work to complete and could not be accomplished at a self check-out station. |
| effective location (calculated in LDP) | The item's effective location (calculated from permanent and temporary locations of holdings and items) | Want to retain the effective location of the item from before it is checked out to see if it was on reserves when checked out. This is important because if the item was on reserves, then a staff member had to go retrieve it, and knowing that a staff member was necessary can influence staffing decisions. These transactions cannot be done by a patron at a self-checkout machine. Report requester acknowledges this is a stretch goal for this report. I expect any connection to item information will require looking for the version of the item that was active before the time of the checkout (or checkin). The checkout transaction will create a new version of the item record where status has changed, so this would require looking for the same id but a timestamp immediately previous to the timestamp of the checkout version. Could be tricky. |
Note: I suspect that our schema will want to break the loan into two separate transactions - checkout and checkin - but if not, there may need to be some transformation to that format as part of the query. Otherwise, it will be hard to count a loan once for the checkout date and again for the checkin date. I expect the final report would look something like this:
| Service-Point Name | Service-Point Display Name | Date | Day of Week | Hour of Day | Item status before action | Item effective location before action | Material Type | Transaction Type (Checkout/Checkin) | Count of Loan ID |
|---|---|---|---|---|---|---|---|---|---|
| Circ Desk 1 | Circulation Desk -- Hallway | 2017-01-14 | Saturday | 19 | On Hold | Reserves | laptop | Checkout | 2 |
| Circ Desk 1 | Circulation Desk -- Hallway | 2017-01-14 | Saturday | 19 | Checked Out | Reserves | book | Checkin | 5 |
- Angela Zoss (Old) will talk to Marc Johnson about loan-storage/loans:loanDate (and returnDate) - data validation? what format?
- Angela Zoss (Old) will talk to Andrea Loigman about transaction types, sample report, difference between returnDate and systemReturnDate
- Angela Zoss (Old) will share prototype of report with Andrea Loigman and David Bottorff (UC)
- Angela Zoss (Old) will share report spec with full FOLIO Reporting SIG // 5-13-19
- Angela Zoss (Old) will ask Marc Johnson if it's true that renewals are just being tracked as a count
- Scott Perry will look for a sample service-point usage report
- Sharon Beltaine will talk with Emma about doing this as LDP report, moving report issue from UXPROD to REP