Feeding the hunger of data junkies with Google Docs
Last week we silently released the first version of our API. Sido explained our new XML export options and the new API key in his last blogpost. In short: we’ve implemented two new XML export options. You can use these exports to create XML files with the content of your test and the results of your test. We built this feature as a first step in opening up Usabilla and to make the data you collect in Usabilla accessible in any other tool. To demonstrate some of the possibilities with these new feeds, I’ve created a Google Spreadsheet that imports test results. In this post I will explain step by step how this Spreadsheet works and how you can build your own.
ImportXML in Google Docs
Google Spreadsheets, which is part of Google Docs, offers some powerful features to import external data into your spreadsheets. With the “ImportXML“, “ImportData“, “ImportHTML“, and “ImportFeed” functions you can import data from external online data sources. You can use the “ImportXML” function to import data from your Usabilla tests and display the data in your spreadsheet.
The import functions for Usabilla data looks like this:
Import your test (title, tasks, pages, etc):
In the above functions you see three undefined variables you need to specify yourself:
- The unique ID of your test
Each test in Usabilla has an unique identifier. You can find this identifier in the URL of your test (/rate/#######). Just click the button ‘Invite Particpants’ (for an active test) or ‘Preview’ (for inactive tests) buttons to open your test and see the URL. The identifier is a 13 digit variable with letters and numbers (eg. 17648937944ae).
- The xpath to query your data
You can retrieve data from an XML file with xPath queries. W3Schools has an excellent tutorial on how to use xPath. In short you specify the path to the data in your XML file, just like in a file browser.
For some Usabilla cases it could be interesting to see individual notes for each participant in the test. Especially when you combine Usabilla with an additional survey (see here how simple this is). Create a new spreadsheet in Google Docs and start experimenting.
To display the id’s of each participant in a test you can use the following function:
The xPath explained
The xPath searches for each participant node in the XML document, regardless the location in the XML tree (because of the ‘//’). The ‘@id’ matches the attribute ‘id=###’ for the selected participant. The output of the ImportXML is a list of all participants in the XML document and Google Docs automatically expands this list in a column.
*** UPDATE: The following section is unfortunately outdated. We’ve updated our API. Please contact us (email@example.com) for help. ****
Step 2: Display notes for each participant
We’ve already create a nice list of ID’s for all the participants in our test. The next step will be to display the notes for each individual participant.
To access all notes per user for a specific task I’ve used the following xPath:
=ImportXML("http://api.usabilla.com/v1/export/results/XXXXXXXXXXXXXX.xml?token=XXXXXXXXXXXX, "//participant//page[#]//task[#]//notes | //participant//page[#]//task[#]//notes[@count=0]/@count")
The xPath explained
The following xPath retrieves the notes in your XML file for each individual user:
//participant//page[#]//task[#]//notes | //participant//page[#]//task[#]//notes[@count=0]/@count
No worries. It looks worse than it really is. The xPath can be divided into two parts. Part one:
This first part retrieves all notes from our XML file. The double ‘//’ matches for each participant in the file, no matter the location of this element in the XML tree. The # marks the number of the page your want to retrieve. So if you want to retrieve the first page of your test you just use 1, 2 for the second page, etc.. The same for tasks. Each page has one or more tasks. If you want to retrieve the first task for a page, you simply fill in number 1. So the following example displays all notes for the first task of your first page in your test: ‘//participant//page//task//notes’.
The xPath also contained a second part:
I’ve added this second part to display a zero for participants with no notes for this task. The | is being used as an “and” and indicate that the importXML can match one of both xPaths. I matched for zero notes participants, to be able to create a complete overview for each individual participant (also the ones without notes). The xPath to display all participants contained all participants, and we want to display the participant ID in a column next to the notes.
We’re just getting started
Take a look at the complete Spreadsheet and create your own copy to use it for your own cases or adapt it to your own needs. Log in to your Google Account, open the spreadsheet, and create a copy of the sheet (File / Make a Copy). This is just an example on how you can use the XML exports to create your own reports in software like Google Spreadsheets. The possibilities are nearly endless and we’re eager to help you out with your exports. Contact us (firstname.lastname@example.org) if you need any help with your tests or if you would like to share a case study.