The RSFC recently learned of an error with how CONTU is run on the back end of Rapido which makes it necessary to manually run CONTU through article request data to ensure you do not pay copyright on items before they meet the Rule of 5 thresholds in the CONTU Guidelines. This guide will walk you through how to download the Rapido Copyright CONTU Report and how to clean it up to submit to a vendor to generate a copyright invoice.
The error with how CONTU is being applied is being fixed by Ex Libris, so this process will not be necessary in the future.
CONTU Rule of 5
To understand why it is important to clean up this report you may want to know what the Rule of 5 is. Under the CONTU guidelines, libraries do not need to pay copyright on Resource Sharing items meeting the following criteria:
Libraries only pay copyright on article requests published within the last 5 calendar years.
Libraries do not pay copyright on the first 5 articles filled for the same journal that were published in the last 5 calendar years.
It is also commonly accepted that libraries do not need to pay copyright on duplicate filled requests for the same patron. For example, if I requested two copies of an article titled “Rapido in the CSU” the library would only pay copyright once because the patron would receive identical PDFs. If another patron requested the same article the library would need to pay copyright on both copies in accordance to the CONTU guidelines.
The Rapido Copyright CONTU Report is a file listing all the articles filled for patrons at your library. To clean it up, you will be removing items published outside of the past five years, removing duplicate requests delivered to the same patron, and removing the first 5 filled requests for each journal. The final report would be the items needing copyright payment according to the CONTU Guidelines.
Accessing the Rapido Copyright CONTU Report
To access the report login to Alma and go to Analytics → Reports → Rapido Copyright CONTU Report. On the resulting screen put in the date range you want to analyze and click OK. Scroll to the bottom of the report and click Export → Data → Excel to download the report.
Formatting as a Table
Once you open the Excel report you will want to format the data into a table. Select the first cell, most likely A1, scroll to where the data ends, press and hold the Shift key, and select the final cell with data in it in the last column. With all the data selected, go to Home → Styles → Format as Table and select the style of table you prefer. It the resulting pop up Check that you already have headings and click OK.
Remove Items Published Before the Past 5 Years
Removing items published over 5 years ago is trickier than it should be because the years are not all formatted the same. It is a bit time consuming, but the dates can be made to all have the same format to make the process easier. People with more Excel experience may have some extra tricks, but I found it easiest to Sort the Publication Date column from A-Z.
After sorting the Publication Dates I cleaned up the dates published over 5 years ago. I am ran this report in 2022 so I removed items published prior to 2018. By sorting A-Z the requests are mostly in order by year. I selected the years I want to delete by selecting the Row Number, scrolled until I hit a year we needed to keep, and pressed and held the Shift key while clicking the Row Number of the last row needing removal. I then right-clicked the selection and selected Delete.
I repeated this task until the only years remaining were the past 5 years.
Remove Duplicates
To remove duplicates I recommend highlighting duplicate article titles and sorting first by User Primary Identifier and then by Title.
Sort By User Primary Identifier
Open the drop down menu by clicking the arrow in the User Primary Identifier heading. Select sort A-Z.
Sort By Title
Open the drop down menu by clicking the arrow in the Title heading. Select sort A-Z. You may be wondering why you are sorting twice. The main sorting will be by Title, but the duplicate items will still be sorted by the User ID making it easy to find them.
Highlight Duplicates
Select the entire Title column by clicking the column letter (probably I). Go to Home → Styles → Conditional Formatting → Highlight Cell Rules → Duplicate Values. Click OK in the resulting pop up. Duplicate article titles should now be highlighted red.
Scroll through the Excel and look at the User Primary Identifier next to the highlighted Titles. If there is a duplicate for both the Title and the User Primary Identifier delete rows until there is only one request for an article with the same patron. There must be duplicates in both.
For example: the patron with the ID 123456789 requested “Let’s Talk about Skin Cancer” twice, but so did a patron with the ID 987654321.
Delete one request for patron 123456789 from the report.
Do this through the whole report until there are no more duplicates for the same patron.
Remove The First 5 Requests Per Journal
Count the Journals
First sort by Borrowing Creation Date by A-Z and then Journal Title by A-Z. Then add a new column to the left of the Journal Title column and name the new column Journal Counting. This column should be a new H column. In the first cell of this column inset the following formula:
=COUNTIF($I$2:$I2,I2)
I2 is the first cell after the Journal Title heading in the Journal Title cell. After pasting in the formula press Enter. This formula will count each instance of each Journal Title.
Copy to a New Sheet
At this point you will want to copy the information to a new Sheet. This is so the numbers generated in the previous step can be sorted without being automatically changed by the counting formula. Select all data by clicking the triangle in the top left corner of the sheet.
Once everything is highlighted, right-click any cell and select copy. Once the data is copied to the clipboard, go to the bottom of the sheet to click the + symbol to add a new sheet.
Click into cell A1 and then right-click and past the data (values only).
Select the first cell, most likely A1, scroll to where the data ends, press and hold the Shift key, and select the final cell with data in it in the last column. With all the data selected, go to Home → Styles → Format as Table and select the style of table you prefer. It the resulting pop up Check that you already have headings and click OK.
Highlight Requests Past 5
Highlight the Journal Counting column and go to Home → Styles → Conditional Formatting → Highlight Cell Rules → Greater Than…. Format cells greater than 5 and click OK.
This will highlight numbers 6 and up. Sort the Journal Counting column from Smallest to Largest or by cell color (both work). Delete all cells with numbers 1-5. This will be most requests.
Clean Up Owned Requests
The previous steps will manually perform the CONTU Rule of 5, but it does not take into account cleanup for items owned by your library. You do not need to pay copyright on items you subscribe to online because you already paid the publisher for access. I do this by adding a new column titled Available Online and then search each article title in Primo and mark if the request is held by the library. For the final report to submit to a vendor like the Copyright Clearance Center or Reprints Desk, I remove all the rows with items owned by the library. I also remove the User Primary Identifier column so the vendor does not see the patron numbers.