Creating the Monthly Stats Report

Introduction

The job of the stats person is to collect stats from various sources, enter them into an Excel spreadsheet, then from this information create a monthly report that is given to the board.

Timing

The timing for monthly stats report is attached to the timing of the Library Board meeting, which is on the fourth Tuesday of each month. Find the fourth Tuesday on the calendar. Go back two Fridays: that’s when your monthly stats report is due to the Administrative Assistant.

Go back to the Friday before that: that’s when contributors’ stats are due to you.

The Administrative Assistant can accept the report as late as Tuesday by 8 p.m. of the week before the Library Board meeting, but I find it easier to remember: due to me on Friday, due to Administrative Assistant the next Friday.

Some Tips

1. If this is your first time:
   1. Take some time to study the monthly stats report. It’s long, so you’ll find it easier to work in it later if you have some idea of the categories, etc.
   2. Set up a couple of folders in your email program for stats-related emails and reports. For example:
      1. One folder for current-month items.
      2. One folder to drag items to when you’re done with them (keep six months worth of items); you can always re-run Sirsi reports if needed.
2. For backup purposes, in the “stats box,” keep a folder with paperwork from each month; keep three years worth of stats paperwork.
3. Write a reminder in your calendar for when you want to:
   1. Send a reminder email to contributors letting them know when you need their stats.
   2. Get your finished report to the Administrative Assistant, who makes copies for the board packets.  Also send a copy to the director for her to examine.

Where Stats Come From

When two people love each other very much…The stats come from five sources (see attachment A for a line-by-line look at which source provides which info):

1. Contributors: the branch contributors send their stats to you via email. In-house contributors enter their stats into an Excel spreadsheet made for them. This spreadsheet is located at: Public > Stats > Contributors Here! > Contributors Stats
2. NetLibrary web site: the e-audiobook downloads for the month can be accessed from their website: http://extranet.netlibrary.com/resourcecenter/.
3. Stats drawer: in the stats drawer each month you’ll find:
   1. Library traffic (gate) count and
   2. Overdue notice count (Jamie)
   3. Meeting room reservation forms (Jamie)
4. ILL Stats are generated from VDX and OCLC
   1. http://piglet.state.wy.us/
   2. http://www.stats.oclc.org/
5. Sirsi reports: these reports are automatically emailed to you on the 6th or 7th of each month. The reports you’ll receive are:
   1. Items Marked Used (ALBY mo. item marked used report)
   2. Overdue notices count (Notice Count)
6. Director’s station: these circulation reports are available after logging on to DS, under the Stats folder:
   1. Item by Location (Circ Home loc/item type)
   2. Item by User (Circ Home type/user prof.)
   3. Branch Circulation (Circ Cent and Circ Rock River)
   4. Various customized reports for specific counts (Look through the selection in the Stats folder in DS).

Note: if you need to make changes to or rerun reports, speak with the Access Services and Technology Librarian. To get to the correct reports wizard, sign into Workflows under ALBYCIRCMGR.

Getting to Know the Stats Spreadsheet

The stats spreadsheet (Public > Shared > Stats > stats 2008-09) tracks activity for the fiscal year, which runs July to June. The spreadsheet is made up of a number of parts:

1. First, there is a worksheet containing stats for the previous fiscal year. (This is the tab marked FY07-08.) Within this worksheet, as you scroll across the top headings, you’ll notice individual stats for each month of that fiscal year, then YTD (year-to-date) accumulative stats for that fiscal year.
2. Second, there is a worksheet containing stats for the current fiscal year. (This is the tab marked FY08-09.) Within this worksheet, as you scroll across the top headings, you’ll notice individual stats for each month of the current fiscal year, then YTD (year-to-date) accumulative stats for the current fiscal year. This is the spreadsheet where you enter new numbers each month.
3. Finally, there is a worksheet for each monthly stats report (this is the one that goes to the board). You don’t enter any stats into this one: it draws completely from the other two worksheets.

Spreadsheet Notes:

The row titles in each worksheet match exactly: i.e., row 82 is the same no matter which worksheet you’re in. Since the monthly stats report is designed, using formulas, to pick up data from the other worksheets, it’s important to pay attention to the impact of any changes you make. For example, if you add a row in FY05-06, add the same row in the other two worksheets (and be sure to re-check the formulas).

The row headings in FY08-09 and the monthly stats report are linked – if you change the name of a row in FY08-09, it will automatically be changed in the monthly report.

All the spreadsheets make use of formulas. The YTD accumulative portions, for example, are all done automatically. Keep your eye on these formulas (mostly by making sure nothing on the monthly report looks out of whack), and make sure you understand what the formula is doing before making any changes.

Monthly Checklist

1. Send a reminder email to contributors letting them know when you need their stats.
2. Prepare the spreadsheet.
   1. Open the spreadsheet: Public > Shared > Stats > stats 2008-09
   2. Make a copy of the worksheet for the last monthly stats report and rename the copy with the current month.
3. Go into the (just created) current monthly report and:
   1. Update the headings.
   2. Update all the formulas to pick up the correct data (you can use Replace command).
4. Prepare the stats folder.
   1. Within the stats folder there is a folder named 08-09 MONTHLY STATS FILES. In that folder, make a new folder for the current month. This is where you’ll store Excel files you make that are associated with the Sirsi and director’s station reports, as well as a copy of Contributors Data.
   2. Move the previous month into the 08-09 Done folder.
5. Enter the contributors data.
   1. Open two windows in Excel and have Contributors Stats and current monthly report open. Type the data from one to the other.
   2. Type in the data from the branch contributors. Print off branch data for the stats box.
   3. For backup purposes, copy and paste the Contributors Stats file into the current monthly stats file. Rename it after the month and year.
6. Enter the NetLibrary audiobook downloads
   1. Go to http://extranet.netlibrary.com/resourcecenter/ and login with the library username and password.
   2. Under Reports click on Reports Section.
   3. Click on Activity by Title.
   4. Set the correct month
   5. Select “Custom Collection Usage as specified below” and “add all”
   6. Select “View usage of the selected collection(s) by the members selected below” and “add all”
   7. Click Submit
   8. Enter the number listed next to “Total Accesses” to the spreadsheet.
7. Enter the stats drawer data.
   1. Library traffic (gate) count
   2. Read and Return
8. Enter Misc Data
   1. Meeting room stats from Happy Jack
9. Enter ILL counts
   1. http://piglet.state.wy.us/
      1. User library barcode and PIN
      2. Go to reports, monthly stats, enter (don't need dates)
   2. http://www.stats.oclc.org/
      1. Use ILL (not Cat) authorization and password
      2. Use Borrower Activity Overview Report and Lender Activity Overview Report
10. Enter data from the Sirsi reports.
    1. Items Marked Used report: scroll to bottom for ### Transactions Selected.
    2. Overdue notice count from Notice Count report-pull into Excel and do totals.
11. Enter data from the Director’s Station reports.

Reciprocal Borrower - to compute Reciprocal Borrower circ, use the "Circ by User Lib" report in DS.  Export it to Excel, remove the columns for ALBY, ALBY-CENT, ALBY-ROCK, and UNDEFINED.  Then compute the total from those that remain.

Information on the old method of compiling stats from the Items by Location Report.

Edit (online or printed out) the monthly report for looks and correctness. Print out a copy and give to the Administrative Assistant. File all paperwork in folder in stats drawer, and recycle the oldest paperwork.

Treat self to punch and pie, etc.