Use Airtable to make Research Synthesis simpler
A How-To guide on using Airtable for better note-taking and sense-making for User Research
As User Researchers, we love when our participants open up and talk freely. But (sort of) despise it when we have to scour through mountains of quotes to make reconstruct a model of how humans think and what they do. There is a point in every research project when a researcher comes across an insight and our eyes glitter like Gollum’s.
There are different frameworks for analysing notes but the common thread is observe patterns and interpret them as unbiased as possible — so fun!.
Excel, my dear! ⿳
A lot of researchers rely on good old Excel/Google Sheets for this. It is robust, allows us to dice up quotes and move them around for sense-making. But it has its own limitations. For starters, it is really boring to look at. Don’t we spend enough hours hunched over our computers and muttering user quotes to ourselves in insolation like crazy old ladies?
Besides my love for pretty, shiny things, a spreadsheet application makes it really difficult to compare notes across different sheets. When you have 20 sheets for each participant, then combing through the notes feels like watching a Tennis match.
Frameworks like the Rainbow Spreadsheet are great for analysing feedback from Usability Studies. But it doesn’t work for user interviews for a couple of reasons:
- There is simply way too much data.
- People don’t exactly answer the way you want them to.
- People talk about things that you didn’t ask, which may or may not be helpful.
Because of these reasons, we can’t throw away things that don’t seem to fit what we have in mind. Sometimes the best insights come up from organic conversations. To make sense of data, we create tags and typically use colours to distinguish them. This seems like a good idea, until you run out of colours and wish you could only see black and white.
Combine the constant neck-swiveling by moving across pages and a cornucopia of colour-blindness inducing palette of tags, you start questioning every life choice you have ever made.
Yay! Post-its. 📝
Virtual whiteboarding tools like Miro/Mural have made collaborating online fun. After all which designer or researcher doesn’t like Post-its? If developers measure their productivity in lines of code/hour, we will measure our productivity in no. of post-its written/hour.
Virtual whiteboards are like the fun, young Gen-Z compared to the old, staid spreadsheets — pretty colors, post-its, emojis. Did I say emojis already!!? 🤯
Again it works for small volume of data like Usability Studies but when you start to plug in 20 user interviews on post-its, you will zoom in and out so much that you would end up hypnotising yourself and wonder if you are inside the movie Inception. Am I really writing this blog post or dreaming about writing?
Another issue with virtual whiteboards is you can’t sort/group stuff dynamically. Dragging a post-it across a giant whiteboard is like catching hold of a toddler in an amusement park. Don’t you dare run away from me! Daddy’s tired. 😩
Repo, Repo, Repo. 🕺🏼
Research Repositories are the in-thing these days and Thank God for that. Besides being able to organise research findings and making it accessible to everyone, these tools also make analysis simpler. You can upload a video recording, get it transcribed automatically, highlight quotes and tag them, zoom out and see patterns across participants. It truly does make researchers lives simple. But there is one glaring drawback: the auto-transcription doesn’t work well for Indian accent.
I have tested with multiple tools and even Otter.ai but the quality is poor. Once I tried to fix a transcript generated by Otter — spent 3 hours to fix errors and extract quotes for a 1 hour conversation!
Another issue that happens typically is participants switching to regional languages like Hindi in the middle of a conversation when they are not comfortable speaking in English. Also I work in an Edtech company aimed at K-12 students, who may not form sentences with perfect grammar or speak with proper intonation. Throw in a spotty internet connection and all hell breaks loose.
The effort spent in cleaning up an auto-generated transcript is simply not worth the effort for my use-case. So I got thinking, if I have to listen to recordings and take notes, does it still make sense to use a Research Repository for analysis? I am not debating their ubiquitousness for collating findings. As a small research team, we are not there yet on the maturity scale. All I am thinking of is about the ROI from a cost and time investment POV.
Airtable to the rescue 🦸🏼
While exploring alternatives I noticed some folks using Airtable for organising information. I came across many videos, templates and blog posts for using Airtable as a research repository. It seemed quite good because you can customise it based on your company’s needs. My excitement turned to despair soon when I tried to use Airtable. This looks like a spreadsheet but behaves like something else. How did I even end up on this page !?
There were 2 issues with all the content out there:
- They don’t talk about how to use Airtable, which has a steep learning curve.
- The content is all about using Airtable as a repository, but I want to use it for one project first and see if it suits my needs.
I came across this article after I figured things out on my own. 🤦🏼While it does address how to use Airtable for analysis in a single project, the approach isn’t granular enough for deep analysis.
What is an Airtable?
Here is a simplified explanation of what an Airtable is and how it is different compared to other spreadsheet programs. Airtable is basically a Database Management System (DBMS) at its core where data is stored in the form of tables. Each table has rows and columns. The rows are called records and columns are called fields. For example, if I were to store the personal details of employees like name, ID, contact, etc belonging to company ABC, it would look like this image below. Every row corresponds to details about a particular employee and every column corresponds to individual fields about an employee.
To make it easy for retrieving data, a DBMS uses a primary key. It is just a field which has unique values to identify each record. In the example above, Employee ID is the primary key. There cannot be 2 records with the same ID. But in the case of Airtable, there are no such strict conditions. The same value can be repeated multiple times, making it flexible like a spreadsheet. There are ways to make it unique if needed but I will not delve into those details now as they are not necessary.
Setting up an Airtable
So now that you have a basic understanding of Airtable, it is time to start using it. You can Sign up for a free account on Airtable.com and create a new Base from scratch. Avoid using the templates till you get familiar with the interface. In Airtable, each file is called a Base (short for Database) and each Base contains one or more Tables. You can give the Base any name.
I am going to use the example of entering data from a semi-structured interview for this tutorial. Once you get the hang of it, you can use Airtable for organising information from any other research method.
Rename the Table as Area Of Interest 1 and update the column names. Basically quotes from all participants pertaining to this topic go in this table. Similar tables can be created for other topics and have one for capturing Miscellaneous feedback.
- The first column in a Table will always be the primary key. I like to keep this for the Notes from the interview as it is quick to scan through from left-to-right.
- The second column is for the Tag that I want to assign for each quote from the participant. Will come back to this in a bit.
- The third column is for the Emotion observed — positive, negative or neutral. This is optional.
- The fourth column is for the Participant identifier — name or ID as preferred.
Notes
By default, this field accepts Single Line of Text. You can customise it to accommodate longer quotes by choosing Customize field type ->Long text.
Tags
When it comes to tagging, sometimes researchers use closed tags or open tags or a combination of both. We create a new Table to list all the tags. You only need 1 column for the tag name and can delete the others. If you have pre-defined tags, then add them here.
Now we go to the table Area Of Interest 1 to cross-reference the tags. Click on the column Tag and choose Customize field type -> Link to another record -> Tags (table we created earlier). Now you can create new tags and reference existing ones easily for each quote you add.
If you see a popup like this below, Skip it.
Emotion
We can create a predefined list of emotions like Positive, Negative, Neutral, etc to choose from so we can save time while entering notes. To do that, Click on the column Emotion and choose Customize field type -> Single Select. Now input the options you want and click Save.
You can verify this back in the table.
Participant
You can enter the ID or name as text or setup another table like we did for Tags and link it. For simplicity sake, I am going to let it be as-is.
Entering the data
- Listen to the interview recording and add quotes in the Notes column. You need not add each sentence as a separate entry. A bunch of related sentences can be entered together.
- There are no hard and fast rules I follow when I do this. Sometimes, during analysis phase I notice that there are 1–2 ideas being conveyed in a quote, so I split them up. Go with your gut.
- When it comes to note-taking verbatim or paraphrasing, it depends on the researcher. The former does retain the complete context but does take time while the latter approach makes it easier to parse the notes later for analysis. However this has the risk of researchers starting to synthesise even while they are taking notes. This could lead to erroneous conclusions.
- Personally I use the 80/20 rule. I retain 80% of quotes as-is and skip/paraphrase certain lines based on the conversation. If the same person does the interview and the note-taking, then the context and personal observations makes it easier. Regardless, the Airtable format we have created works for all approaches.
- Once you are done with the Notes, add a tag or map to an existing one. Each quote can have multiple tags as well.
- Then identify the emotion of the participant and close it up with their identifier.
Analysing the data
The best part of using Airtable is the analysis bit. Choose the Group icon and then select Tag. Now you get to see the notes categorised by Tags. I find this approach of seeing related quotes from all participants at a glance, easy for analysis.
You can apply multiple conditions to Group By to look at things from different perspective. You can create a taxonomy of tags as well and use Airtable for analysis.
In the below image, I have Grouped quotes based on Tag and then by Emotion.
There you go folks — a simply way of using Airtable for UX Research analysis. You can find templates for User Research here and many more published by other researchers on Medium and other websites.
Do let me know what you think!
