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 research rooms, excluding any entry that is not a research room. 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 by right clicking and selecting “Remove”
   10. Once you are finished making your edits, click on Close and Load 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.
      
      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
    
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. On the right will be a box labeled "PivotTable Fields" It will have one large box (Range) and 4 smaller boxes (Filters, Columns, Rows, and Values). 
18. In "Range", click and drag Event/Name to the "Filters" box.
19. This will appear in the spreadsheet:
    
20. Click on the downward arrow to open the drop down menu
    1. Check the box at the bottom labeled "Select Multiple Items"
    2. A checked box will appear next to the word "All"
    3. Click the + next to "All". All of the rooms should have a check mark next to them. Uncheck any you do not want to include.
    4. Click OK
21. Working with "Range" again, click and drag "Door" to the "Rows" box
22. This will now appear:
    
    
    1. Click on the downward arrow next to "Row Labels" to open the drop down menu
    2. Uncheck "Blank" then click OK
23. Again, working with "Range" check the box next to "Date". When you do this, several more items will appear. See the image below. They will also appear in the "Columns" box.  
    1. Hover the cursor over "Date (Year)". A downward arrow will appear to the right. Click on this arrow.
       1. A pop up window will appear similar to what you worked with in previous steps. Select the appropriate year and make sure all other years are unchecked. Then hit "OK".
    2. Click and drag "Date (Year)" from "Range" to "Rows" and put it below "Door" in that box. When you do this, it will disappear from the "Columns" box. 
    3. Repeat with "Date (Month)" selecting only the relevant months.
    4. Uncheck "Date (Quarter)" because you don't need it. 
    5. Hover the cursor over "Date" in the "Range" box and again, click the downward arrow.
       1. This time, select "Date Filters", then enter the day before the first day of data you want. So if Jan. 2 is the first day of data you want, input Jan. 1.
       2. Click and drag "Date" to the "Rows" box. 
       3. You don't have to use Date but it can help you see patterns a room's usage is close to the minimum but not quite where it needs to be. 
24. Now, click and drag Event/Name to the Values box.
25. This is what the final settings should look like (if you haven't checked "Date":
    
26. Save your work as "Year-Month-Date Usage" in the Audit Analysis Reports Folder.