Accessing and Sharing your Metrics within your Institution

Below are some the metrics that can be pulled from the DCS.

• Number of Active Requests and Published requests made per user Report: Active Requests per User
• Academic Request Route per system, Academic Request Route per individual Report: HEI Report
• Rejected requests per ‘Reason for Rejection’, Rejected requests per course, Rejected requests per academic requester Report: Rejected Requests
• EHESS orders by course Report: EHESS Reporting
• Number of courses with requests, number of items of content, number of archived items of content, number of requests by course Report: HEI Report
• Peak dates for creating content requests Report: HEI Report
• Student Access Metrics Report/s: Downloads per Content Item & Downloads per Content Item - Length of Access
• Automate sharing your metrics

Reports can be found under the Reports tab: https://contentstore.cla.co.uk/Report 

NOTE some sections of this article refer to processes (such as Vlookups or PIVOTs) that are described in earlier sections and the details of how to create these are not repeated. Reading the article chronologically will reference these in the correct order, otherwise you will sometimes need to scroll up to an earlier section.

Number of Active Requests and Published requests made per user

Under Reports, select the Active Requests per User report. Then select the date range you are looking to report on. 

The Report lists the user, the number of Active Requests and the number of Content Items in that date range. An Active Request will be all requests created and Content Items indicated that the request was published.

Academic Request Route per system, Academic Request Form per individual

Run the HEI Report , using the relevant academic year (usually you will use the current Academic Year, as this includes all requests which have not been deleted).

Now find the Request Route under column AM. This lists Academic Form users and API usernames for third party technology parties or individual HEI's use of the API.

If you just want to see the request route you can filter out any request received via an API and rename all the other cells to Academic Request Form. First add a filter to the report by highlighting all cells and then, in the 'Data' tab, select 'Filter'. Then use the filter to un-select any API or blanks (you should never have more than 1 internal API for your institution at any one time). Highlight all the data on the page and, on the 'Insert' tab, click 'Pivot' and click 'OK'. Select Request Route and then drag Request Route into values to see the breakdown of the routes for Academic Requests.

You can also do the same Pivot before renaming the individual requesters in order to see how many academic requests each requester has made.

Rejected requests per ‘Reason for Rejection’, Rejected requests per course, Rejected requests per academic requester

To test this run the Rejected Requests report, selecting the date range you would like to look between.

In the report, highlight all cells and insert a Pivot table (see section above for guide).

Select the item you would like to see the breakdown of and drag it down into the Values box. In the below example we can see 'Reason for Rejection'. If you want to manipulate or utilise the data further you should copy it from the Pivot table and paste elsewhere.

EHESS orders by course

To access this please run your 'EHESS Reporting' report and select the date range that you wish look between.

You might want to just check certain items, such as 'Accepted' items. To do this, add a filter and filter for 'Accepted'. Copy and paste the results into another tab.

You can then pivot on this new tab and select 'Course Code' and drag this to the Values as well.

Copy the Pivot table and paste to the right, so we can manipulate. 

You could add a different field too. Perhaps add 'Total order cost' by dragging this to Value instead of Course Code (leave Course Code under rows). Use the Value Field to change from Count to Sum.

Again you can paste this to the right to pull through other fields using a Vlookup (see section below for guide), and you could as sort by 'Total Order Cost' (see section below for guide).

Number of courses with requests, number of items of content, number of archived items of content, number of requests by course

Run the HEI Report. Now add a Filter and filter for Released under the Column 'Status'.

At the bottom right of the screen you can see the number of Released items of content. You can repeat this for any other status to see the results.

If you copy and paste this filtered result into another tab, you will now only have the 260 results

Highlight the tab and add a pivot table. Select the Course Code in the Pivot table and you can count the number of Courses. Remember to remove the 3 irrelevant rows at the top when counting.

To get the number of Released requests by course simply drag Course Code into the Values section of the Pivot Table.

Now you can see the request per course. Use a Vlookup from the previous tab to add in any other relevant course details (such as Course Name or Requester).

To do this, please highlight the data that includes all the details you want to pull through. The first column highlighted MUST be the Course Code, as this will be the unique reference that the Lookup uses to match against. If there are columns to the left of Course Code that you want to include you will need to copy the Course Code Column and Insert Paste Special the entire column into the first row BEFORE you create the table (you cannot have a filter in place when you do this).

Once you are happy that all the columns you need are to the right of Course Code, highlight your table. Using Shift + CTRL and the directional arrows can be used to make highlighting easier. 

Once highlighted, name your table.

Now enter the Vlookup to pull across the required data. First by adding the extra fields you require in the new table.

Now click on the first cell you wish to populate (so this is the cell F2 in the above). Then in the Formulas tab, click on Insert Function.

Select Vlookup (or search for it if it isn't already available in the list).

Now fill out the details. 

Click 'OK' and this will save. To repeat this formula for all rows in this column, double click on the small square at the bottom right hand corner of the cell (make sure you're in that cell first).

Now repeat the Vlookup for the other columns that have missing fields. The only difference is the Col_index_number, which will change depending on the column in the named table where you want to pull the data from. You can double click on the bottom right hand corner of multiple highlighted cells to repeat all the formulas down across all the rows. Those without a result will leave a '0'.

Once all rows are filled in please copy all cells and paste as Values to remove the formula from the cells.

To remove the '0's you can do a find and replace. Ensure you use the options to 'Match entire cell contents' otherwise you will be removing 0s which appear within cells (i.e. 101Film would become 11Film). 

After clicking 'Replace All' these '0's will be removed.

Now you can sort the courses. Perhaps by number of published requests, most to least.

Highlight the cells using the Control + Shift and directional arrows (as detailed in the section above).

Click on the 'Data' tab and select 'Sort'.

Sort by Published Requests (if that's what you want to call the column) and Order 'Largest to Smallest'. Click OK.

Now your table is ready to share. This might all take a while the first few times, but when you get better at using the Excel functions you'll be able to create all this data in under a minute.

You can also repeat these steps to do all requests or by other results (Archived etc.) or by adding all of them to different columns. To add all the results to the same table use a Vlookup to match the request against the table using the Course Code. See example below.

Peak dates for creating content requests

Please run your HEI Report, as described above.

Now create a pivot table on all of the data included.

Select Date Request Created and then also drag this to the Values field.

Click on the plus icons next to the year to show quarters and the icon next to quarters to show months.

Now you can see what months had the highest use of the DCS in terms of creating requests. If you want to look even deeper into the data, for example individual days, you will first need to filter for one year at a time in the main report, then copy this data to a new tab. Then add the Pivot and repeat the above. The below is by filtering for 2017.

Student Access Metrics

Run your Downloads per Content Item report or Downloads per Content Item - Length of Access report. The former does not include the length of access in the Student Reader, but the report runs straight away, the latter does include length of access and is emailed to you to open.

Choose the date range that you wish to use. You might want to create these reports on a monthly or quarterly basis.

If emailed to you, you will see an email like the below, with a link to the report.

The Downloads per Content Item Report includes, Number of Views, Prints, Downloads to PDF and can include length of time the material has been accessed in the reader, based on which report you run. 

So now you have the data, how best to share?

You could share to each academic, but this could include 100s of academics so we recommend breaking this down if you have a lot of academics. If you have few academics just add a pivot straight away and double click on the counts for Lead Lecturer (as detailed with Subject Librarian below).

For this example we will use Department and internal contact data to send the relevant data to the relevant Subject Librarian. 

As you will see the Department and Subject do not appear in the Downloads per Content Item reports. We did not want these reports to be too overburdened with data, in order to run more efficiently. So, if you require any missing data you will need to source it from the HEI Report and perform a Vlookup.

Run the HEI Report and save this in the second tab of the Downloads per Content Item report. Create a table (as detailed in the sections above) and ensure this starts with Course Code and includes Subject and Department. Name this 'SubDepart'.

Now in the Downloads per Content Item report please add two additional columns called 'Subject' and 'Department' and add a Vlookup to pull the data from the table you have just named (see sections above for Vlookup step-by-step guidance).

Click 'OK' and then add the same lookup for Department. Highlight both and double click on the bottom right hand corner of the right most cell to paste this for all cells.

Now copy columns E and F and paste special as Values to remove the formulas (see sections above for step-by-step guide). 

So now we have Department details in the Downloads per Content Item report, but now I need to add in my Subject Librarians. I will need their names and email addresses and a unique reference to match them against in the DCS. This will usually be Subject Name - though this must match exactly with what you have - or Course Code; but in this case I will use Department to have even fewer contacts that I need to disseminate to. 

What I will do is add my internal data with Subject Librarian details into another tab of the same spreadsheet. I will then highlight and name the tab 'Librarian'. Ensure that the unique reference that you are matching against (in this case Department), is the first column.

Now enter columns for Subject Librarian and Subject Librarian email and add a lookup to pull back the information using the Unique Reference as the Lookup Value.

Do the same for Subject Librarian Email and copy down the spreadsheet. Again copy and past special as Values to remove the formulas (if you don't do this all the results will create an error if you delete the formula table).

You could now filter for each Subject Librarian and copy their details to another tab, but an even easier route (especially if you have more than a couple of Subject Librarians), is to create a PIVOT on this tab, then select Subject Librarian as the result and the Value by dragging it into this field. Double click on the count results to create a new tab for each Subject Librarian that only includes the materials relevant to them. 

You can then share these with the relevant Subject Librarians.

Automate sharing your metrics

If sharing the reports above seems like too much hard work, and you have internal technical support, you can automate these processes.

All DCS reports save as a CSV which can be read by automated scripts and APIs. This means that you could just save the report locally and then local scripts or APIs could do the rest using the metadata available.

Please feel free to share this article with your technical teams or simply show them the reports that you can run and the data that you want to extract and share.