monthly statistics procedure.doc6/24/2010 lh,mb,td

Monthly Statistics Procedure

I.Serials, and Emono, Summary and Details

II.Volume, and Non-Print, Summary and Details

A.Create Review Files

B.Record Totals in SerStatistics Volume Summary

C.Export and Count Non-print

D.Export and Count Serials

I.Serials, and Emono, Summary and Details

  1. Print the Monthly Statistics Review Files spreadsheet.
  2. Start creating review files for serials (# 1-10)

-each review file will build from the previous – ex. review file # 2 will use # 1, #3 will use # 2, etc.

-record file number and number of records in appropriate columns

  1. When serials review files are completed, retrieve the SerStatistics spreadsheet (thiscan be found in the Statistics folder on the Websnap network drive

-do not delete review file # 2 – you will need it later.

  1. Save a copy of SerStatistics(\\websnap.library.ucsc.edu\techserv\Statistics) on your desktop
  2. Go to Details – enter the date (yyyymm) in column A (date) – where yyyymm is the year/month you are gathering statistics for.
  3. Copy the following cells from the previous month, and paste in the cells below – or highlight those cells and drag down to the cells below: E, AP – AQ, AV – DD. (These cells contain formulas).
  4. In column B, enter the BIBLVL=s/i total,in column C, enter the BIBLVL=s/I BCODE3>d and h total, and in column F, enter the BIBLVL=s/i BCODE3>d and h 856> (fromMonthly Statistics Review Files).
  5. Do review files for columnsG, H, I, J, K, L

-the letter in parenthesis ((F), (C), etc), means that you use the review file from that column to create the new review file.

-when creating review files for Science use saved querySERIALS science (electronic)

-Do not delete the review files from column F(e total/856 >) and column I (CDLe)– all others may be deleted upon completion.

  1. Go to Summary in the SerStatistics spreadsheet.
  2. Insert a blank row (just above Titles Added/Deleted) - Copy the cells in columnA to column BW of the previous month, and paste in the cells below – or highlight those cells and drag down to the cells below.
  3. Create a review file for Online Paid (columnsAQ - AS) in Summary.

-use review file from Details column F (e total)

-retrieve saved query: serials 793 O open/CalDocs 856Ofree/depository

-enter total in column AS.

-cell AS has a formula (ex: =20429-Details!L44) – just replace old total with new total.

-also create a review file for Science titles and enter in column AR

  1. Create review file for Online Free – Gov Docs (column BB)

-use review file from Details column F (e total)

-use search criteria: Type= BIBLIOGRAPHIC, Field= !Marc Tag – 086, Condition= !=not equal to, Value= blank

-enter total in column BB

  1. Create review file for CDL Online Free – Gov Docs (column BG)

-use review file from Details column I (Cdl e)

-use search criteria: (Type= BIBLIOGRAPHIC, Field= !Marc Tag – 086, Condition= not equal to, Value= blank) -OR- (Type= BIBLIOGRAPHIC, Field= !Marc Tag – 793, Condition= has, Value= U.S). -OR- (Type= BIBLIOGRAPHIC, Field= !Marc Tag – 793, Condition= has, Value= CalDocs)

-Enter total in column BG

  1. Create a review file for CDL Online Paid (column AU – AW) in Summary

-use review file from Column I (CDL e)in Details

-retrieve saved query “serials 793 O open/CalDocs 856Ofree/depository”

-enter total in column AW

-create a review file for Science titles and enter in column AV

  1. Create review files for columns M - W in Details

-save review file from column M (Print total: s3=1/a) and P (Dedup Print: 856=)

-create review files for Dedup Adds (column Q– S)– use the Dedup Print (column P) review file and use search criteria: Type=BIBLIOGRAPHIC,Field=CREATED, Condition=between, Value A=Start date, Value B=End date.

-enter total in column S

-create Science review file for Dedup Adds and enter in column R

-Dedup Deletes (column T – V) – use numbers from outrecs delete statistics.

  1. Create a review file for Print Paid(columns R–T) in Summary

-use review file from column M (print total)inDetails

-use search criteria: Type=CHECKIN, Field=SCODE2, Condition=equal to, Value A=$ (PAID SUBSCRIPT)

-enter total in column T

-create a review file for Science and enter in column S

  1. Create a review file for Dedup Print Paid (columns V – X) in Summary

-use review file from Details column P (dedup print )

-use search criteria: Type=CHECKIN, Field=SCODE2, Condition=equal to, Value A=$

-enter total in column X

-create a review file for Science and enter in column W

-create review files for Dedup Print Paid del (column Z – AB) – use numbers from outrecs delete statistics.

-create review file for Dedup Print Paid adds (column AD – AF) – use the Dedup Print Paid review file and use following search criteria: Type=BIBLIOGRAPHIC, Field=CREATED, Condition=between, Value A=Start date, Value B=End date.

  1. Create a review file for Dedup Print Free – Gov Docs (column AO)

-use review file from Details columnP (Dedup Print)

-use search criteria: Type= BIBLIOGRAPHIC, Field= !Marc Tag – 086, Condition= !=not equal to, Value= blank

-enter total in column AO

  1. Create review file for columnsDX inDetails
  2. Create a review file for Unique Paid Print/Online (column BQ to BS) in Summary

-use review file from column D (print+e unique) in Details

-use the following search criteria:

-retrieve saved query “serials 793 O open/CalDocs 856Ofree/depository” – group these lines

-append a line to the search – Operator=ORType=CHECKIN, Field=SCODE2, Condition=equal to, Value A=$

-Enter total in column BS

-cell BS has a formula (ex: =20429-Details!K44) – just replace old totalwith new total.

  1. Enter serial deletes in columns BU – CF(print paid, print free, online paid, online free) in Details. These numbers can be obtained from the outrecs deletes.
  2. Go back to the Monthly Statistics Review Files spreadsheet and create reviewfiles for monographs. (# 11-18)

-[NOTE] the monograph files are very large (500,000+ ) so you may need more than one review file for some of the searches.

-the only monograph review file you will need to save for later is # 11(BIBLVL=m 856> BCODE3>d+h)

  1. When the monograph review files are completed, go back to the SerStatistics spreadsheet.
  2. Go to Details – in column S, enter the total for BIBLVL=m 856>BCODE3>d+h from the Monthly Statistics Review Files spreadsheet.
  3. Create monograph review files for columns Z – AG

-create review files for CDL Emono Adds (column AE - AG)– use the cdl emono (column AA) review file

-use search criteria: Type=BIBLIOGRAPHIC,Field=CREATED, Condition=between, Value A=Start date, Value B=End date.

-enter total in column AG

  1. Create monograph review files for columns AH-AI
  2. Start a review file for Ebooks w/ Attached Items (# 21)

-Create a file of bibliographic records

-use the E-mono x toc (column AH)

-use search criteria: Type= BIBLIOGRAPHIC, Field= Linked Rec, Condition= exists to, Value= Item

-record file number and number of records in appropriate columns

  1. Create monograph review files for columns AJ-AO, and AR – AU

-create review files for Non-CDL Emono Adds (column AM – AO) – use the non-cdl emono (column AI)

-use search criteria: Type=BIBLIOGRAPHIC, Field=CREATED, Condition=between, Value A=Start date, Value B=End date.

-enter total in column AO

-create Science review file for CDL & Non-CDL Emono Adds and enter in column AF & AN

-CDL Emono Deletes (column AB - AD) and Non-CDLEmono Deletes (column AJ – AL) – use numbers from outrecs delete statistics.

  1. Columns AR – AT(ebrary) can be obtained from the ebrary statistics.
  2. Enter mono deletes in columns AB - AD (cdl emono del) and AJ - AL (non-cdl emono del) in Details. These numbers can be obtained from the outrecs deletes
  3. After all review files are completed, go to Summary
  4. Go to the last row under Titles Added/Deleted.

-Copy the cells in column A to column AV of the previous month, and paste in thecells below – or highlight those cells and drag down to the cells below.

II.Volume, and Non-Print, Summary and Details

A.Create Review Files

Estimated totals are indicated in brackets to the right of each step number

  1. Start review files for Non-serial Print (# 19,20) [880K, 225K]

-[NOTE] the MCH Non-serial Print file is very large (880,000+ ). You will need to empty and reuse the same review file 2-3 times to obtain a complete count. The SCIENCE Non-serial Print file will contain 200,000+ records.

-On subsequent searches:

  • set the item record range to begin with the last record number found by the previous search.
  • insert a line into the Boolean search criteria as follows: Type= Item, Field= Record #, Condition= “>”, Value A= [enter the item record number here, including the “i”]

-record file number in appropriate column

  1. Start a review file for Serial Print(# 22) [26K]

-ensure that the review file being searched is the daily serials review file: “SERIALS bib lvl=s/I b3!d/h yyyy-mm-dd”

-record file numbers in appropriate columns

  1. Start a review file for Non-print (# 24) [100K]

-use search criteria: Type= Item

-Search the range of item records

-record file number in appropriate column

  1. When Serial review (# 22) file is complete, start a review file for SCIENCE Serial Print (# 23) [9000]

-use search criteria: Type= Checkin

-search the Serial Print(# 22) review file

-use saved query SERIALS science (electronic)

-record file number in appropriate column

  1. Start a review file of Volumes Added (# 25) [2000 (varies)]

-use search criteria: Type= Item

-Search the range of item records

-insert a line into the Boolean search criteria as follows: Type= Item, Field= Created #, Condition= between, Value A= [first day of prior month, eg. 06-01-2010], Value B=[last day of prior month, eg. 06-30-2010]

-record file number in appropriate column

  1. Start a review file of Uncounted Titles (# 26) [26K]

-use search criteria: Type= Bibliographic

-Search the range of bib records

-record file number in appropriate column

  1. As each review file is completed, record the number of records retrieved on the Monthly Statistics Review Files spreadsheet

B.RecordTotalsinSerStatisticsVolume Summary

  1. Open your saved copy of the SerStatistics spreadsheet
  2. Go to Volume Summary. If necessary, copy the following cells from the previous month, and paste in the cells below – or highlight those cells and drag down to the cells below: H-J, N, R, U, X, Z, AG-AL. (These cells contain formulas).
  3. Go to Volume Details. If necessary, copy the following cells from the previous month, and paste in the cells below – or highlight those cells and drag down to the cells below: N,P,X, AG, AI, AQ, AZ, BB,BL, BV, BX,CF. (These cells contain formulas).
  4. Transfer review file totals from the Monthly Statistics Review Files(# 19-21, 25, 26) to the SerStatistics spreadsheetVolume Summarytab

C.Export and Count Non-print

  1. Export select fields from the Millennium review file created for Non-print (# 24) as follows:
  2. Export item type on line 1
  3. Export item location on line 2
  4. Field Delimiter Control character: 9
  5. Text Qualifier: "none"
  6. Open item_type_counts.xlsx
  7. Go to the Exported Itypes spreadsheet tab
  8. Paste the contents of the exported text file into columns A and B, replacing the old data
  9. Go to the Itypes Count spreadsheet tab
  10. For each category, 9, 11, 13, 14, copy the total for each Itype into SerStatisticsspreadsheet Volume Details tab

D.Export and Count Serials

  1. Export select fields from the Millennium review files created for Serial Print (# 22) and SCIENCE Serial Print (# 23) as follows:
  2. Export checkin record number on line 1
  3. Export "h" on line 2
  4. Field Delimiter ASCII character: $
  5. Repeated Field Delimiter: "none"
  6. Text Qualifier: "none"
  7. Complete the following steps for each exported text file:
  8. Rename exported file "holdings.txt"
  9. Ensure "count_volumes.pl" and "holdings.txt" are in the same folder (preferably in an isolated folder not containing other files), or else edit script to indicate the full paths to all .txt files
  10. Double-click “count_volumes.pl.” This will create a new file: “volume_count.txt”
  11. Open volume_count.txt, scroll to the end to view the number of volumes counted
  12. Record the total in SerStatisticsspreadsheetVolume Summary tab, either column Qfor Serial Print (# 22), or S for SCIENCE Serial Print (# 23)

Created 4/17/2008