Page Comparison

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
   Image Modified
4. Select “New Query” then “From File” and “From Folder”.
   Image Modified
   
   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.
   Image Modified
6. Allow the rows to load. Depending on how big the file is, it may take a few seconds.
   Image Modified
7. On the tool bar at the top of the screen under “Query Tools”, click “Query”
   Image Modified
   
   1. Select Edit on the far left
      Image Modified
   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.
      Image Modified
   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.
      Image Modified
   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.
      Image Modified
   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.
      Image Modified
   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. Highlight the three remaining columns and click Remove Duplicates under Tools.
      Image Modified
      1. 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
    Image Modified
14. A pop up will appear to select the table or range, which you should’ve already highlighted.
    Image Modified
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:
    Image Modified
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:
    Image Modified
    
    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:
    Image Modified
25. 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.Save your work as "Month Year Usage"