Problem Space:

• Need to compare e-journal entitlements lists against what is activated and/or able to be activated in your knowledge base (KB).  I'm defining "entitlements lists" as lists of the e-journal titles and the subscribed/purchased coverage to which we are entitled.  These lists can be e-mailed by publisher representatives or downloaded from publisher websites.

• For the most part, publishers do not format entitlements lists in Knowledge Bases And Related Tools (KBART) format, see http://www.uksg.org/kbart .

• Selected additional problems with entitlements lists:

• Entitlements lists that do not show all title changes with separate titles, ISSNs, and coverage for all the titles in the history

• Entitlements lists that show separate lines for each subscribed year of the title, which makes it hard to pull together the true start date and end date into one KB entry.

• We have tried using Excel's VLOOKUP functionality to match titles by unique identifier, but it seems as though there are lots of problems ​in the vendor supplied data ​that make it hard to compare coverage (and even titles, sometimes).

• My requests:

• “If you have a process that you think works pretty well for matching up entitlements with what's available in the KB, would you mind contacting me?  I would love to learn about your process(es) and the tools you use. We are especially interested in automating the process with Excel or Google Sheet templates, if anyone has been doing that.​ Or are there any workshops or presentations that anyone knows of on this topic?”

• “Has anyone had any success in getting better entitlements data from publishers?”

Summary of Responses:

• Multiple people indicated that they are attempting to check title lists “by hand,” and that this is a time-consuming process.

• Per Adam Rusbridge (a.rusbridge@ed.ac.uk), the SafeNet project (http://safenet.blogs.edina.ac.uk/) is working on building an “entitlements registry” for UK higher educational institutions, and thus has an interest in how publishers record entitlements. The people involved in SafeNet are working with folks at KBART. RKG note: I’m sure more information will be forthcoming from this project.

• Matching techniques for matching entitlements files against KB files (these are at a bird’s eye view, not very detailed):

• Process courtesy of Diane Westerfield (Diane.Westerfield@coloradocollege.edu):

• Complexity of process varies by the data quality of each source file.

• Split KB files into 3 pieces: (1) titles with ISSN as match point, (2) titles with eISSN as match point, (3) titles with no identifier, which will need to be compared separately.

• Using Access, match titles with ISSN against each other to determine which should be activated.

• Using Access, match titles with eISSN against each other to determine which should be activated.

• For titles with no ISSN, perform clean-up on titles using Excel and/or Python string-slicing scripts to eliminate initial articles, convert ampersands to “and,” replace double spaces, delete incorrect diacritics.

• Using Access, match on title, exporting the titles to be activated. (Alternatively, Diane has also learned how to match titles using Python.)

• Recombine all titles to be activated, activate in KB.

• Process courtesy of Christine Blackman (cblackma@williams.edu):

• Using a unique identifier for titles (e.g., publisher title IDs from unique URLs), use nested OR/EXACT array formula, e.g., '{=OR(EXACT(M2,DataInAlma!H$2:H$725))}') to determine exact matches (value returned by the formula = “TRUE”) that should be activated in the KB.

• Sort the sheet for only the exact matches, and activate these.

• Process courtesy of Chenwei Zhao (chenwei.zhao@gmail.com) - this is for comparing coverage dates to determine whether titles are earlier titles in a title history and whether or not they should be activated in the KB. It is also used to determine which start and end coverage dates should be used as the start date and end dates in the KB file.

• In comparing date ranges between entitlements files and KB files, use the LEFT and RIGHT functions to reformat dates to make them comparable.

• Use IF function to compare dates and determine whether titles should be activated or not.

Requests for holdings information from publishers and KBs:

• Entitlements files to be sent to KBs should reflect all titles and coverage years for which libraries have access (not just perpetual access).

• Please format entitlements files in KBART format, and likewise, please allow KBs to accept KBART formatted files.

• Publishers: please reflect all title changes in the entitlements files with separate titles, ISSNs, coverage dates, and coverage enumeration for all the individual titles in the title history.

• Publishers: please consolidate all consecutive entitled years into one entry per title, rather than reporting a separate entry for each individual year of entitled content.

• Ideally, publishers should be able to send the entitlements files over to the KB in an automated fashion, so that libraries do not need to reconcile the lists. I understand that some vendors are already doing this, and hopefully more vendors will join this effort.