FEATURE
How to Build a Desk Statistics Tracker in Less Than an Hour Using Forms in Google Docs
by Sunshine Carter and Thomas Ambrosi
| In less than an hour, using Forms in Google Docs, we were able to build a workable system after years of delay. |
The University of Minnesota–Duluth is the second largest campus in the University of Minnesota system. The UMD library, which serves more than 11,000 students and 500 faculty members, is primarily an undergraduate library. The reference team consists of eight librarians, including author Sunshine Carter, reference and electronics resources librarian, and author Thomas Ambrosi, head of reference services. The reference team assists with more than 13,000 in-person and 3,000 chatreference questions per year. Team members single-staff the desk 66 hours a week during the school year, with reduced hours in the summer.
Until recently, reference librarians had been collecting daily usage statistics on a paper tally sheet, which a student worker then transferred to a spreadsheet. This was a process largely unchanged for many years. Essentially, the tally form consisted of hour-long time frames on the left with transaction types along the top. Reference librarians simply had to put a tally mark depending on what type of question it was and when it occurred. The type of transactions recorded were a) <1 min or directional, b) 1 to 5 min Reference, c) 5 to 15 min Extended, d) >15 min Consultation, and e) IM (instant message). This met the minimum requirements for our annual statistics reporting to the National Center for Education Statistics (NCES) and the Association of Research Libraries (ARL).
Our paper method did have its value. It was quick and easy for the reference librarian: one transaction, one tick mark. The downsides of our paper form, however, were many. The data needed to be gathered and entered into a spreadsheet by a student reference worker, which took valuable time away from other tasks. Outside of summing up the data, the information was hard to analyze. There were also additional questions that we wanted to answer. How much computer assistance are we providing? What are our busiest hours? How do our busiest hours change over the semester? There was no place to record any notes associated with a particular transaction. Our designation for IM only indicated that a chat had occurred. A long instant message chat was given the same weight as a quick, directional chat. Last but not least, it used more than 300 sheets of paper per year.
In 2008, the reference team decided to investigate digital tracking tools. Rory Litwin and Sarah Beaubien, both former UMD reference librarians, led the way as we researched our options. We focused on three possibilities: Libstats (http://code.google.com/p/libstats), Desk Tracker (www.desktracker.com), and an enhanced Excel program. After months of debate, the reference team decided to use Libstats because it provided the flexibility we desired at a price we could afford (free).
After many discussions, the reference team had determined the variables we wanted to track in Libstats. Customizing Libstats was the easy part. Unfortunately we ran into technical issues with installation, and the implementation date of July 1, 2009, was pushed back indefinitely.
With no immediate solution in sight, the reference team decided to attempt creating its own reference tracker using Microsoft Access. The database was set up and tested at the reference desk, but after discussion we determined that Access would not work for us. The Access database required too many clicks to use the drop-down menus, and no one was enthusiastic about using it.
At this point we had a pretty clear idea of what our reference tracker should look like. We had a desire to have a quick way to record reference desk transactions that would not require the assistance of computer staff. Simplicity was the goal—to minimize clicks, with the option to add notes and other information as needed. Some librarians wanted no more than two mouse clicks to record a transaction, while others were willing to enter in additional information.
During a reference team meeting, one of the authors (SJC) quickly drew a mock-up of a reference tracker on a legal pad. The layout of the tracker included a grid that would record the type of question (directional, reference, referral, etc.) against the mode of the transaction (in person, chat, email, or phone).
We investigated the idea of using MySQL with a web interface. It was a feasible project but still required the expertise of the library systems team to make it operational. Then Carter recalled hearing about Forms in Google Docs (www.youtube.com/watch?v=IzgaUOW6GIs). We immediately went to work and created a working prototype of a new statistics tracking program in less than an hour. After years of false starts and delays, we had come upon a simple way to accomplish what we wanted: easy recording, instant compilation, easy export, quick customization, expandability, and web accessibility. We decided to call it SimpleStats (see Figure 1).
Forms in Google Docs
The University of Minnesota has been using Google Apps since 2010, so we were able to access Forms using our UM Google Docs (Forms is also available with personal Google accounts). Forms in Google Docs allows data to be entered into a spreadsheet from a form. The form can be accessed via the web or directly emailed to users.
The steps to creating a desk statistics tracking tool are simple:
1. Create a Google account (if needed)
2. Log into Google and open Google Docs
3. Open a new spreadsheet
4. Create a form
5. Add/format questions and customize form
6. Create an access point
7. Start recording
First, a Google account is necessary to use Forms in Google Docs. An institutional or personal account will work, but the institutional account has more authentication options. If you don’t have an account, you can get one at www.google.com/accounts/NewAccount.
Once logged into Google Docs (either your institutional account or http://docs.google.com), create a new spreadsheet. A blank spreadsheet will open. From the “Tools” menu, select “Form” and then “Create a form.” A pop-up window will appear that allows you to create your form. There is no need to enter anything directly into the spreadsheet (such as headings). By adding items to the form, you are essentially setting up your spreadsheet for data entry.
You’ll want to title your form and include any other descriptive information you can. Questions can be added in either text, paragraph text, multiple choice, or check boxes, or you can choose from a list, scale, or grid. For UMD library’s SimpleStats, we only used two questions. The first is a “text” question that allows our librarians to record a note along with the transaction type. You can decide whether an answer is required or not. We chose to make this question optional.
The second question is a 4" x 7" “grid” that allows us to record all types of reference questions in various formats. The four modes we have listed are In-Person, Chat, Email, and Phone. The seven types of reference questions we have listed are Directional, Computers/Printing, Supplies, Research 1-9, Research 10-19, Research 20+, and Referral. Contrary to what you might think, this is not a required question. By not requiring an answer you can select as many radio buttons as is appropriate (or none at all) to record a transaction. For example, if we have a patron who asks a research question followed by questions on how to print, this can be recorded on one submission form. Additionally (see Figure 2), a time stamp is automatically added to the spreadsheet when a transaction is recorded (we try to record all transactions immediately after they occur).
If you’re using an institutional Google account, you’ll also want to ask yourself a couple of questions before you’re done with your form. Do you want librarians to be able to edit their form submissions? As for us, we are only allowing responses to be edited by the department coordinator and the document owner. (We may allow librarians to edit their forms in the future.) Do you want to require an institutional login to view this form? This is important if your access point isn’t located on a secure server. Do you want to automatically collect the librarian usernames? For our reference desk version, we chose not to collect usernames. We did activate this option for an office version we subsequently created (more on that later) because we wanted to record who was generating a particular office statistic. If you are using a personal Google Doc spreadsheet, you won’t have these options available.
Some additional features available on the personal and institutional Forms in Google Docs include themed backgrounds and the ability to edit the confirmation response (the response received after submitting a form). Themes add a visual component to your form and can make it look sharp. We chose a visually compact theme for SimpleStats—RefDesk because we wanted to see the entire form without scrolling. We used different themes for all our versions of SimpleStats (RefDesk, Office, and Teaching).
There are three ways in which forms can be used by librarians. The form can be emailed directly from the “Edit Form” area (not practical for use at service desks), or a link to the form can be added to a webpage or emailed. The third and most useful way to access the form is by embedding the code for the form. The code can easily be obtained by clicking on the “More Actions” button in the edit form area and selecting “Embed.” Copy and paste the code into any application that accepts code. During our initial testing period we embedded the code in an internal LibGuides page. During implementation, SimpleStats was moved to its own webpage and tabs were made for each of our versions (RefDesk, Office, and Teaching).
Testing and Implementation
Development and testing for SimpleStats took place in May and June 2011. After the initial development, the form was presented to the rest of the reference team. The reference librarians were encouraged to test it at the reference desk while we continued to use the paper tally system. Some adjustments were made to terminology and authentication features. Once everyone was comfortable with the new system, the paper tally was removed. We began using SimpleStats exclusively on June 27, 2011—just in time for the beginning of our new fiscal year.
The transition has proved to be as easy as we could have hoped. Several librarians use the notes field heavily, which will be helpful for collection development purposes. SimpleStats was implemented over the summer, so it will be interesting to see if an increase in traffic at the reference desk during the school year will change perceptions about the ease of use.
The reference desk version of SimpleStats was so easy to use, we developed two additional versions for our reference team. One version, SimpleStats-Office, records our office transactions and also records the name of the librarian entering data. With this single interface we hope to create a more accurate picture of the amount of reference assistance librarians provide while in their offices. A third version, SimpleStats-Teaching, was created to collect our teaching-related statistics. The Teaching version uses a completely different set of questions in order to elicit statistics related to teaching (e.g., length, audience count, course name, instructor). Our hope with all three versions of SimpleStats (RefDesk, Office, and Teaching) is to provide easy ways to capture the data and reduce the amount of work needed to compile and analyze the data.
Analyzing Reference Statistics
Collecting reference desk statistics is relatively easy, but to make it truly worthwhile there should be some element of analysis. Analysis can be as simple as determining how many transactions happened in a given time period, or it can determine which hours of the day are the busiest. Statistics can be used to report to library organizations as well as to help make management decisions about issues such as desk hours.
The “Summary” feature in Forms (found in the “Edit Form” area) is limited. It provides an overview of all responses, but there is no way to refine the summary into smaller time periods. A tag cloud from our notes (optional question one) highlights popular terms, but without a controlled vocabulary the tag cloud is not useful. Manually analyzing the statistics is another option. The data from Google Docs is easily exportable into CSV, HTML, text, Excel, Open Office, or PDF. We were able to summarize our July 2011 reference desk statistics in less than 15 minutes by exporting our statistics into Microsoft Excel.
We also use Microsoft Excel to perform deeper analyses of our reference desk statistics. For example, we examined the time stamp of all our SimpleStats transactions to illustrate desk traffic as it related to day of the week and time of day (see Figure 3). This analysis took 30 minutes to prepare and develop. We plan to use this particular chart to track our desk statistics for upcoming semesters to evaluate our desk hours and staffing.
Conclusion
In less than an hour, using Forms in Google Docs, we were able to build a workable system after years of delay. Forms created an “aha!” moment for us: nothing to install, nothing to purchase, accessible from any service point, no server space to negotiate, statistics automatically compiled, and easy to analyze. While it took very little time to build SimpleStats itself, we would be remiss not to acknowledge the time and effort invested beforehand. All our earlier work was not wasted. It gave us a clear idea of what we wanted to accomplish with our desk statistics tracker by the time Forms came to our attention.
The original impetus for the reference team was to update our spreadsheet for compiling usage statistics. When we discovered we couldn’t analyze the statistics the way we wanted to with the current tracking system, we realized a full overhaul was necessary. Other institutions thinking of creating or modifying a desk tracking tool should first determine what questions could be answered by collecting reference desk statistics. Once that is established, the next step is figuring out what to collect and when to collect it. This is a time-consuming but necessary task.
It’s really true that our “aha!” moment (“We can use Forms!”) to our working prototype took less than an hour. Further design, testing, and implementation took 1 month to complete. Other institutions, when ready, will find that Forms in Google Docs provides an easy and workable solution for a custom-made desk statistics tracking tool.
|
