Data Informed Library Advocacy from #AASL19 – Part 2

Gathering and Visualizing Quantitative School Library Data

As I shared earlier in part 1 of this blog post, I’d like to dive deeper into the actual numbers, or quantitative data that we collect and share that demonstrate the impact of our school libraries. (You can access my AASL National Conference presentation at

First, we need to keep in mind that library numbers and statistics are pretty meaningless to anyone else unless they are connected to your school and/or district priorities. Once you have determined how you can make a strong case on what these numbers mean, you can start gathering, organizing, visualizing, and reporting them to your communities and influencers.

Setting Up & Gathering Data

For ease of access and portability, I use Google Sheets as my data repository. I have a workbook set up for all of the data I collect, with separate sheets for each category of data–circulation, database usage, etc. At the end of each semester, I download the workbook as a PDF and store it in several places as archives–my local computer, a network drive, and a flash drive, following the LOCKSS Principle. I start a new workbook each school year for fresh data, and I link those internally within each workbook so I don’t have to dig into my drive to find them in case I want to do a year-to-year comparison.

Once you have determined where you are going to keep your data, it is time to start gathering. The most common library data we collect and report is circulation. Since our district uses Destiny, I grab circulation for every campus each month using Reports>Library Reports>Library Statistics. I scroll to the very bottom of the page and put in a date range, say December 1, 2019, to December 31, 2019. The report opens in a new window, so I am sure to disable my pop-up blocker. I take the number from the report and insert it into my spreadsheet for the month.

Statistics Snapshot

If you haven’t been collecting your circulation stats yet this year, there is some good news! On the same screen, you can select the dropdown to view statistics for the last number of (up to 99) months. This will give you your monthly statistics for up to the past 8.25 years!

Library Statistics

If you use a system other than Destiny, please reach out to your system vendor rep to learn how to gather circulation statistics. Once I have the numbers, I use SUM formulas in Google Sheets to create campus totals, then district totals. In addition to overall circulation, I also calculate per student circulation, which is very helpful in comparing campuses of different sizes. Per student circulation also accounts for changing enrollment numbers from year to year. I do this by using the IMDIV function to divide the number of checkouts by the number of enrolled students. Most districts do a snapshot enrollment count in the fall; that is the number I use for this calculation. Since Denver Public Schools is a big district with over one hundred schools of varying sizes, per student checkouts allows us to compare schools’ performance equitably, since larger schools usually have bigger overall checkout numbers.

Per student circulation

In the screenshot above, I have also highlighted schools that have above average circulation for their level. In my experience, elementary schools almost always have higher per student circulation than secondary schools. There are a number of reasons for this, and highlighting these figures can help us make a case that perhaps having secondary students come to the library regularly for checkout might not be a bad idea. I always scheduled checkouts once per month with all of my English teachers, for example, and my circulation numbers at middle and high schools were generally higher than the other secondary schools in my district.

If you are working at the campus level, you might want to do something similar by going back to the reports screen and running the top Homerooms/Grades report:

Top Homerooms/Grades Report

This nifty little report can be useful for competitions, shout outs, and creating a sense of fun among your teachers and students.

Database Usage

These statistics will vary greatly in how you collect them based on which database vendors you are working with. In essence, all of our traditional school library database vendors, such as EBSCO, Capstone, Gale, ProQuest, and others, collect usage monthly. Contact your vendor rep to learn about the data they collect and how you can access it. At the very least, you should know how often your students are searching various databases to be informed about how useful they are to your school or district. Once you have a sense of how much these resources are being used, it can be informative to see usage increase after you teach a lesson using a specific resource. Ideally, you should see a spike in usage after a lesson, then a higher plateau of usage once students and teachers are aware of or have become more familiar with the resource. You can use the same spreadsheet functions above to compile numbers and perform per student calculations.

Making the case for database usage is pretty straightforward; for every search in an academic, professionally curated database, that is one search not done using Google. Using numbers to justify the expense of databases is also helpful, especially if you can provide a per student or per search cost for these resources to your decision makers.

Visualizing Data

I use a couple of charts and conditional formatting within Google Sheets to help myself and others spot trends, make informed choices, and look for areas of celebration or concern. The following chart is a stacked bar chart that shows our district circulation over time, with e-books in red (added in 2016) as a growing percentage of our overall circulation:

DPS Circulation Totals

Stacked bar charts like this are helpful for showing the percentage of a collection or service, especially over time, as I have done above. Someone viewing this chart gets a sense of circulation trends in Denver Public Schools since 2010, and we can imagine the conversations that can ensue regarding staffing levels, the introduction of e-books in 2016, and other trends we spot in this visualization that a list or table of numbers simply cannot provide. I also made a second version of this chart, using the most recent years and data labels:

DPS Circulation - 2016-Present

The intention here is to show the actual numbers associated with print and e-book usage, as well as portray a positive growth trend over the last few years. The way I craft my story with this visualization is very different than the 10-year history shown above; this one is a more hopeful, exciting tale of growing, thriving school libraries and not one of libraries coming back from a really low point in terms of circulation in 2016-2017. As you craft your visualizations, keep your goal(s) in mind and carefully consider the story you want to tell. In the graph above, one ask may be to give library services some PD time to ensure all teachers know and can teach their learners to access OverDrive and/or Sora, helping those e-book numbers grow. Perhaps it can be to show the desire for books, and that staffing and supporting a campus library can grow the print checkout totals. Finally, one can argue that increased access to e-books has led to an overall increase in print circulation as well! Here are the chart setup options I used for this stacked bar chart:

Chart Editor

Conditional Formatting

Conditional formatting using a red, yellow, green color scheme can give a glimpse into how a campus is doing, this time, in terms of their e-book circulation. I conditioned the cell containing their total checkouts, and also calculated their per student checkouts for the equity reasons mentioned above:

Conditional Formatting

To apply conditional formatting, highlight the range of cells you want to add this formatting to, then select Format from the menu, then Conditional Formatting. A sidebar will open where you can set your color scale, minimums and maximums, percentiles, etc.:

Conditional Format Rules

Campus librarians can use conditional formatting to highlight several trends, such as circulation by homeroom, teacher collaborations, library programming, or other valuable metrics. Your library data can help you tell the story of your library, and how you visualize and present that data is only limited by your imagination!

Please tweet me your questions or ideas @lenbryan25.

Author: Len Bryan

Categories: Advocacy/Leadership, Blog Topics

Tags: , , ,

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.