Library e-resources usage & cost analysis – creating a template spreadsheet.

Part of my work in the DMU Library Content Delivery Team is to head up the capture, collation and dissemination of library e-resource usage stats. The library buys many different types of online resources, from many different vendors, and all of these resources need to be continually evaluated and assessed to see if they are returning on the financial investment DMU Library has made in purchasing them for DMU students and staff.

DMU Library continues to make extensive use of JUSP. JUSP saves time in journal usage stats workflows by removing the need for library staff to manually access and download journal usage reports from individual publisher admin sites. JUSP acts as a one-stop-shop for library staff to view and export COUNTER journal usage stats from different vendors, in various types of usage report formats (from specific core title usage to more general usage trends over time).

As part of DMU Library workflows, Content Delivery staff create resource usage/cost analysis documents for subject staff to review and evaluate for upcoming renewals. This assists DMU subject librarians to make prompt and effective renewal/cancellation decisions for the content the library purchases. This analysis contains raw usage data (e-journal downloads, e-book section requests or database metrics depending on the type of resource) from previous years of subscription and resource costs paid by the library during those years of subscription. To save time and create some sense of uniformity to the analysis created, the library harvests and displays annual usage based on the DMU financial (and SCONUL reporting) year – August to July.

Once the raw data is uploaded to the analysis file, Content Delivery staff then attempt to visually represent the data in some way. This representation is usually in the form of graphs, charts or tables. This visualisation of usage and cost data makes it easier for library subject or management staff to spot and interpret usage and cost trends, which in turn, should better inform their resource renewal decisions.

Content Delivery are constantly looking for ways to reduce the time it takes library staff to create these cost and usage analysis files. The files are often quite large, and creating the “visual” aspects of the analysis (charts, graphs) can be time-consuming and require an advanced understanding of Excel. If a number of renewal files need to be created simultaneously, especially around periods in the year when a number of resources expire at the same time, it is difficult to create documents quickly and disseminate to subject librarians in a timely manner (even if we are using added-value services like JUSP to organise metrics). Content Delivery strive to give library subject colleagues more time to digest the analysis we create, but sometimes this is difficult to fulfil due to the number of files being worked on at any one time.

After attending a recent JUSP Community Advisory Group meeting, librarians on the group spoke about the creation of usage “template” files, and how it would be valuable to have different examples of these templates hosted in the Community Area of the JUSP site. This would then showcase how different HE libraries process and evaluate their resource usage stats. This gave me inspiration to look at DMU processes and see if I could create an analysis file template myself, based on earlier streamlining of usage stats processes by Content Delivery staff. My hope was that DMU Library staff would be able to upload raw data to a template file, and then use set Excel formulas within the file to create usage/cost analysis at the touch of a button. As long as the raw data was entered into the correct cells in the file, the formulas should create successful data outputs.

I created tabs in my template Excel file. There would be tabs to data “dump” raw usage stats from specific DMU subscription years (2011-12, 2012-13, 2013-14 etc). I then had a tab for subscription costs (these would have to be transferred from a separate file we keep to record e-resource subscription allocations and costs). The final tab, marked “Usage & cost analysis” was where the analysis calculations would occur. I set formulas in the analysis tab to create automatic data outputs and visualisations.

Tabs were created for annual raw data and subscription costs.

Tabs were created to upload annual raw data and subscription costs.

The data outputs in the template are not ground-breaking – I wanted them to be basic and clear to review (in a table format), showing % increase/decrease in use and cost, and also calculating an annual cost per download figure. I also inserted two visual representations of the data contained in the table – a column chart to show cost per download for the years selected, and a line chart to map total usage against total subscription costs. Again, as long as the correct data is entered in the correct cells, the formulas should do the rest and automatically configure the graphs/charts.

Set Excel formulas create figures for analysis and drive visualisations of the data.

Excel formulas create figures for analysis and drive visualisations of usage and cost data.

You can see the usage stats template I have created by logging on to JUSP (your institution has to be signed up to the service) and visiting the “Community Area” section of the site. The template contains dummy usage data to show how the template works, but hopefully, you will be able to remove the data and add your own, and the outputs should still work (as long as the raw usage data totals and sub costs are allocated to the correct cells within each tab!).

DMU Library have also recently invested in ProQuest’s Intota assessment tool to manage library online content, so I am hoping this service will provide more online tools for Content Delivery to use to effectively interrogate and review resource usage and costs.

Please do let me know what you think of the usage template. I hope to see more of these types of templates appear on JUSP in the future – it would be great to see how other libraries analyse usage stats and subscription costs and highlight best practice in this area.


P.S. I want to say a big thank you to a former DMU Library colleague, Chris Voss. A lot of Chris’s hard work earlier in 2014 has gone into the creation of this current usage template.

About Mitchell Dunkley

I work in the Content Delivery Team at De Montfort University Library. I manage DMU Library's e-resources portfolio and I am involved in library systems admin, collating resource usage statistics and troubleshooting. All comments are my own.
This entry was posted in DMU, Libraries, Library, Usage stats. Bookmark the permalink.