ESBD_File_349144_RFP-WT24-0002 Digital Course Materials Q&A.pdf

RFP-WT24-0002 Digital Course Materials Q&A

Request #1: Adopted Course Materials Data

Please provide a comma- or tab-separated value file for the course material adoptions (including supplies) broken down by campus location for the Fall 2021, Spring 2022, Summer 2022, Fall 2022, and Spring 2023 academic terms. The file should have each adoption (unique course material item – course/section pairing) on a single row with the following column values:

A. Calendar Year*

B. Term*

C. Campus*

D. Department Code*

E. Course Number*

F. Course Name*

G. Section Code*

H. CRN (if available, unique identifier for the section)

I. Actual Enrollment

J. Title (Item Name for Supplies)*

K. Book Type (Print/Digital/Supply)

L. Author

M. Publisher (Vendor for Supplies)*

N. ISBN (SKU for Supplies)*

O. Edition

P. Retail New Price (if available)

Q. Digital List Price (if available)

R. Duration of Digital License (if available)

S. eBook or CourseWare Indicator

T. Required/Recommended/Choose # Adoption Status

Request #2: Anonymized Student Data (Enrollment and Credit Hour) - as of census date

We need “anonymized” student enrollment and credit hour data that will be used to demonstrate course materials savings paired with the cost of adopted course materials by course/section requested in item 1.

This data will only be used for modeling and none of this data will enter systems. By anonymizing the data, will not have any student IDs or any other identifying information.

Please provide the Anonymized Student Enrollment and Credit Hour Data in a .CSV or .XLS File Export from the SIS for each of the following Terms as of Census Date for that term (typically 5 days after add/drop deadline):

• Winter 2022 (Include if a Quarter School)

• Spring 2023

• Summer 2022

• Fall 2023

• Fall 2022

Please include the data for all terms in one sheet (1st Tab) & include the following fields in the .XLS File:

• Student ID*

• Term*

• Dept*

• Course*

• Section*

• CRN

• Instructor

Credit Hours/Units for that student and that enrollment as of census date* *Only for multiple campus institutions: Student Campus Enrollment Designation (Campus Name) *

Lastly, If possible: Student Type/Population (e.g. Freshman, Sophomore, Junior, Senior, Graduate, etc. at the start of the listed term). If this adds times to the data request processing time, please do not include.

Request #3: Anonymizing the Student ID Data in Excel before sending to

Place Exported Data in a separate Tab of Excel Workbook

Add a 2nd Tab in Excel then:

Copy the column with all student IDs from one Tab into column A of the 2nd Tab

In 2nd Tab, Perform the ‘Remove Duplicates’ command in Excel for the data in Column A

In 2nd Tab, In column B, Type 1 in cell B1 and 2 in cell B2.

Select cells B1 and B2 and hover over the small green square (the ‘fill handle’) in the lower right corner of the cell (Image A) and double-click. This will instantly autofill the series of numbers 1,2,3,4, etc. for all student IDs in column A (Image B).

Move to the original Tab and add a new column to the right of the column containing Student IDs. Name that new column B ‘Anonymized Student ID’

Use the Vertical Lookup or VLOOKUP Excel Function to match the ‘Anonymized Student ID” value to the Student ID value In cell B2 of new column, ‘Anonymized Student ID’ on the 1st Tab, Enter the formula =VLOOKUP(A2,'2nd Tab'!A:B, 2, FALSE) where A2 is the first Student ID. Press enter.

With cell B2 selected, hover over the small green square (the ‘fill handle’) in the lower right corner of the cell (Image C) and double-click. This will autofill the VLOOKUP formula for the entire column with the anonymized student IDs.

To copy and paste the entire new column, ‘Anonymized Student ID” as values only (removing the formulas and specific student id data): Still in the 1st (original) Tab, right-click to highlight column B, and select Copy

Right-click on the destination (column B) and click on Paste Special…select Values and click OK.

This copies the Anonymized Student ID values, not the formula/formatting (the VLOOKUP values)

Still in the original Tab, select column A (Student ID) and click Delete to delete the entire column of (non-anonymized) data

Lastly, delete the second tab of the spreadsheet created to anonymize the data

The file is now “anonymized” with student IDs, enrollment, and credit hours that can pair with the cost of adopted course materials you are providing and use to demonstrate course materials savings.

Answer) After discussion, this approach is really not what we are looking for. Our model is to buy a subscription service and pay a set price for inventory access to a large digital library. For instance, what access can be provided to X inventory at Y price.