Analyzing 90 day research room audits (PIVOT edition)

How to analyze html data from research rooms audit: (in progress)

1. Ensure audit files have been uploaded to the shared drive from the study room computer: LEAD > Circulation > Rooms > Research Room Audits > (selected folder of audits)
2. Open a new Microsoft Excel sheet
3. Click on the “Data” tab
   
4. Select “New Query” then “From File” and “From Folder”.
   
   
   1. Click Browse.    
   2. Select Computer > LEAD > Circulation > Rooms > Research Room Audits > Reports for XXXX
   3. Click OK, then OK again
5. Another box will appear – just click Load at the bottom.
   
6. Allow the rows to load. Depending on how big the file is, it may take a few seconds.
   
7. On the tool bar at the top of the screen under “Query Tools”, click “Query”
   
   
   1. Select Edit on the far left
      
   2. The far left column is labeled Content. Click on the downward arrows.
   3. Click on Table 0 for the door numbers and date/times accessed to appear. Click OK.
      
   4. Right click on the Date column. In the drop down menu, select Transform and click “Date Only”. This will remove the time stamps, which will potentially skew the data counts.
      
   5. In the Event/Name column, click on the down arrow for the menu. 
   6. Click “Load More” to see all the event name options.
      
   7. Select only the 90 Day Research Rooms, excluding the long term rooms, DAS, UHC, and Cliff Mead’s rooms. We do not analyze that data.
      
   8. Click OK.
   9. Now delete any unnecessary columns, such as the Function column and the Source.Name column. Just right click and “Remove”
   10. Once you are finished making your edits, click on Load and Close in the upper left hand corner. This will take some time to load so be patient.
8. Now in the upper tool bar, under “Table Tools” click Design.
   1. 1. Highlight the three remaining columns and click Remove Duplicates under Tools.
         
      2. Leave all the options selected (Door, Date, Event/Name) and click OK. Excel will tell you how many duplicates were removed. Click OK again.
9. Next, click the downward arrow in the Event/Name column and unselect anything that is not a 90 day research room.
10. Congrats! It is now time to PIVOT!
11. In the tool bar, click Insert.
12. Again, highlight the three columns
13. Click PivotTable
    
14. A pop up will appear to select the table or range, which you should’ve already highlighted.
    
15. Check the box to “Add this data to the Data Model”
16. Click OK
17. To start, under Range in the PivotTable Fields box, click and drag Event/Name to the lower Filters box.
18. This will appear in the spreadsheet:
    
19. Click on the downward arrow to open the drop down menu
    1. Check the box that says Select Multiple Items
    2. Click the + next to All and choose which rooms you would like to include in the table
    3. Click OK
20. Select Door from Range and drag it to the Rows box
21. This will now appear:
    
    
    1. Click on the downward arrow to open the drop down menu
    2. Uncheck Blank then click OK
22. Check the Date box under Range and select which dates you would like to include or exclude
23. Now, click and drag Event/Name to the Values box – it will say Count of Date but we need the distinct count so click on the drop down menu and choose Value Field Setting
    1. Under Summarize value Field by, select Distinct Count at the bottom of the list
24. This is what the final settings should look like:

1. You will now need to save the data you have created. Excel does not like to save PivotTables so you will need to copy the data and paste it into a new Excel file for analysis. Save your work – be sure to include the terms you are analyzing in the subject line for ease of finding it later.