v29 #5 Let’s Get Technical — One Library’s Collaborative Approach to Simplifying the Ordering Process with Spreadsheets

by | Dec 11, 2017 | 0 comments

by Susan J. Martin  (Head, Acquisitions Services, University of Chicago Library)

and Christie Thomas  (Head, Data Management Services, University of Chicago Library)

Column Editors:  Stacey Marien  (Acquisitions Librarian, American University Library)

and Alayne Mundt  (Resource Description Librarian, American University Library)

Column Editor Note:  In this issue’s column, we feature one library’s experience with eliminating an ordering backlog.  Susan Martin, Head, Acquisitions Services of the University of Chicago Library and her colleague Christie Thomas, Head of Data Management Services, describe how they tackled handling a backlog of orders for foreign language titles. — SM & AM

The Situation

The University of Chicago Library serves a diverse university community of faculty, staff, students, and researchers with over 11.3 million volumes, 62,300 linear feet of archives and manuscripts, and 153 terabytes of digital materials.  In August of 2014, the Library implemented an open source library system, OLE.  As with any new system implementation, there were many challenges as Technical Services staff adjusted to the system and developed new workflows.  Two Technical Services units, Acquisitions Services and Data Management Services, collaborated to address the challenge of ordering backlogs.

The Problem

The OLE implementation required adapting a high-volume acquisitions workflow to the new acquisitions module.  The department was able to cope with the new labor-intensive workflow by developing batch loading processes for many major European and Latin American vendors.  In August 2016, the department also had to grapple with the ordering volume that accompanies a new fiscal year with fewer and newer staff due to staff changes and vacancies in Acquisitions.  At the time, ordering priority was assigned to materials in Western European languages, the majority of which were directly placed in vendor’s web-based ordering systems.  For these materials, the order information is received in MARC format with order data embedded in 9xx fields.  Data Management batch creates the bibliographic record and order using established workflows.  This process is fast and efficient, providing access to the bibliographic and order data in OLE within 24 hours of receipt from the vendor.

However, for orders from vendors who cannot provide MARC records with embedded order data, our experience was drastically different.  These orders had to be created manually.  The ordering assistants searched OCLC to locate a bibliographic record, imported the record into OLE, and then created the purchase order.  This workflow took considerably longer to accomplish not just due to OLE’s multi-step order process, but also because of difficulties working with some of the materials, specifically those from the Middle East and Eastern Europe.

By September 2016, the ordering unit faced a backlog of 1170 individual title requests in Arabic, Persian, and Turkish.  These orders needed to be placed immediately to ensure receipt during the current fiscal year, and to allow the selector to view accurate fund balances.  However, placing these orders would take significant time — every 100 orders equaled 33 hours of acquisitions staff time.  It would take eleven weeks to order just this backlog.

There had to be a better way.  

The Information

With the goals of eliminating the backlog, leveraging economies of scale, and creating a more efficient process for future ordering, the Head of Data Management Services approached the Head of Acquisitions Services and volunteered her unit’s services for developing a batch order process for these materials.  Prior discussions with a colleague had revealed that it was possible to create MARC records from spreadsheets and we were able to build upon the process outlined by Mikyung Kang in the Handbook for Korean Studies Librarianship Outside of Korea when developing our workflow for creating MARC records with embedded order data from spreadsheets.1

We reviewed the current process and available information.  Orders were already emailed to the acquisitions department as Excel spreadsheets from the Middle East selector’s student assistants.  These orders were stored on a shared computer drive so the ordering assistants could easily access them.  These spreadsheets had almost complete bibliographic and order information: title, author, publisher, ISBNs, OCLC numbers (if available), prices, fund codes, donor plates, and vendor catalog numbers.  Data Management needed some additional information to create a brief MARC bibliographic record and order record, for example:  language, vendor ID, building code, room number.  The information also need to be provided in a consistent format.

The Players

  • Head, Acquisitions Services
  • Head, Data Management Services
  • Bibliographer for Middle Eastern Studies
  • Middle Eastern Studies student assistants
  • Supervisor, Monographic Ordering
  • Data Management Assistant

The Process

We opted to use Google Drive for storage and access to the shared order sheets.  Google’s Sheets have the flexibility of Excel with the added benefits of tracking document versions and allowing simultaneous access to multiple staff members, features that the Library’s shared drive space did not have.  

We set up order templates with set fields.  When possible, these fields had data validation rules applied in the forms of format criteria and drop-down menus.  A few constant data fields (vendor id, building and room codes) were protected from editing.  We wrote detailed step-by-step instructions for both selector and acquisitions staff.  These instructions contained a field by field glossary outlining the source of the data and how it should be entered.  Selector and acquisitions staff received training on the specific data entry requirements and the changes in workflow and procedures.

The spreadsheets included:

  • Title (Transliterated)  
  • Author (Transliterated)
  • Place of publication (Transliterated)
  • Publisher (Transliterated)
  • Publication year (Validated to accept only one date.  Either the Gregorian or the Hijri date, but not both)
  • Volume (This must remain 1)
  • Cost
  • List price (Cost + estimated shipping costs)
  • OCLC number (If available)
  • ISBN (Only one and without no hyphens)
  • Language (Drop down menu with LC language codes)
  • Fund code (Drop down menu with select fund codes)
  • Donor plate (Drop down menu with selector donor codes)
  • Vendor catalog number
  • OLE vendor ID number
  • Building code (For delivery address)
  • Room number (For delivery address)  
  • Notes (For special locations, edition information, special processing instructions, etc.)
  1. Order selections are entered into the order spreadsheet by the selector’s student assistants.
  2. When the order is ready to place, the student assistant emails acquisitions with the name of the file to be ordered.  Acquisitions acknowledges the receipt of the order and provides an estimated time frame for getting the orders verified and placed typically within five business days.
  3. The Monographic Ordering Supervisor verifies the order, removes and forwards any selections requiring manual entry, such as standing orders or serials.  The Supervisor also removes any added volume orders and orders written in Western European languages.  These are assigned to Acquisitions Assistants for treatment.  
  4. The Monographic Ordering Supervisor verifies and corrects the remaining order information for any data entry mistakes and then notifies Data Management that the order sheet is ready.
  5. Data Management integrates the order load request into their routine batch processing workflows.
  6. The Data Management Assistant exports the Google Sheet as an Excel spreadsheet and validates the data.
  7. After any corrections are made, the Data Management Assistant uses the MarcEdit Delimited Text Translator to generate a file of MARC records with the bibliographic and order data from the spreadsheet.  Many templates have been generated for mapping bibliographic and order data for different situations, for example when bibliographic and order data is available versus when only order data is available.
  8. The Data Management Assistant then imports the file of MARC records with order data and informs Acquisitions when the records are available in OLE.
  9. The loaded orders go through the department’s regular daily duplication check.  This is a batch process run by Data Management Services which identifies duplicate orders in a daily report.  Acquisitions staff check the report and verify if any of the flagged orders are duplicates.  Any duplicate orders are canceled with the vendor and voided.  This process eliminates the need for pre-order searching.
  10. The remaining orders are extracted via a report app and emailed out to the vendors each Monday.  

The Results

The results were dramatic and immediate.  The backlog was eliminated, and the department can keep current with new order requests.  Every 100 orders now require 2 hours of work by Acquisitions staff — a staff time savings of approximately 93%.  Each spreadsheet of data, regardless of how many orders are included, takes Data Management staff approximately 30 minutes to process.  This average does not include the time it takes for OLE to process the files, which is largely unmonitored.

We are also seeing a reduction in the overall time between selection and receipt of the materials.  We plan to take a more in-depth look at the selection-order-receipt time frames to analyze and quantify any improvement in that area.  We hope to see an improvement in our fill rates for materials from that region of the world due to the faster ordering process, and preliminary analysis indicates a 17% increase in the fill rate of our Arabic language materials over a 10 month period.  A parallel process is used for some Slavic materials which has enabled acquisitions to eliminate its order backlog for those items and has cut staff time in that specific workflow by 90%.  We would like to more fully investigate the fill rates from all the various vendors and over a longer period of time.

This process has evolved to include non-romanized scripts in 880 fields of the (increasingly less) brief bibliographic records for Arabic, Persian, and Korean language materials.  We have also generated templates that allow for the batch processing and ingest of bibliographic and order data for additional vendors and materials.  


  1.  Kang, Mikyung, “Acquisitions: Firm Orders,” in Handbook for Korean Studies Librarianship Outside of Korea, ed. Committee on Korean Materials, Council on East Asian Libraries (Seoul: National Library of Korea, 2014), 61-69.



Submit a Comment

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

Share This