The UX’ers know-nothing guide to working with SQL
Why you should learn and how to get started
Several years ago I took a few online SQL lessons. The basics from those lessons have been useful to me in my role as a design researcher at Medium. They’re also much more accessible than you might realize.
Why learn? 3 ways SQL basics can boost agency.
Adjust a research recruit.
As a researcher, I sometimes need to make small changes to research recruits. For one study we needed with writers whose stories got lots of recommends. I wanted these writers to have published recently so the experience would be top of mind.
The query I got from our data scientist returned writers who had published within the last 2 days. Two days proved too restrictive for my recruit — we didn’t have enough results to work with. So I went to the query and changed the variable.
I got the info I needed without delay, and I didn’t have to bother my colleague with yet another task.
Build simple tools to make my life easier.
During a diary study, I needed to ask participants different questions based on their publishing frequency. Checking each writer’s profile was tedious. So I wrote a short query that told me when these participants had last published something. Easy enough, and it freed time for more interesting work.
Get familiar with data.
With tasks like those described above, I eventually became familiar more with our metrics. Reviewing queries gives me a better understanding of our key metrics. This put me in a stronger position to understand how metrics translate to the user experience, and why they go up or down.
How to get started, and still keep it simple.
Building the right query from zero is often out of my reach, and it can be intimidating. Good news is, there are easier ways to start. Here’s a few very simple ways to start:
Review old queries.
Any time I get a query from our data scientists, I always read it over myself. Little by little, I’m becoming familiar with the language and our data set. In doing this, I can also check that the query has everything I requested. This is important for understanding what a recruit query delivers.
Ask someone to show you where to run queries.
We’ve got a very inviting setup at Medium that lets you run queries in your browser. Depending on your company’s set up, you might need credentials and a password. You probably won’t need to install anything. Yay!
In any case, you should ask a trusty colleague how to find out what tables you keep and what the variables are within those tables.
Change a variable within a pre-existing query.
I do this frequently when I need to make a simple change to a recruit. Perhaps the original variables need to be more or less restrictive. I might seek out users who have been active for 1 month instead of 6. Or I may need to talk with people in a different location. At a startup, these changes can happen fairly often.
These types of changes will mostly be in the WHERE clause where all the conditions go. Sometimes a change isn’t as simple as I’d have anticipated. That’s a good learning opportunity.
Learn the building blocks of a query.
In SQL, this is SELECT / FROM / WHERE. A while back I had a list of users I needed to send a survey to. I needed to remove all the folks who opted out of messages from us. I was able to spin up a very basic query myself and sent the survey without delay.
You should be able to learn the basic structure of a language within a lesson or two. There’s heaps of places to start: CodeAcademy, Khan Academy, W3Schools.
Keep tinkering. Don’t give up.
Yes, you’ll get error messages. But messing up is a good way to learn. If you’re starting out, have a colleague check your work to make sure you’re getting the appropriate data.
Working with queries can be fun (and frustrating, sure). I get a little thrill from gaining new competencies, going beyond my comfort zone, and maybe saving time in the process.
There’s a more important gain, too. From all that tinkering, I have a deeper familiarity with our metrics. I have a better understanding of our abilities and limitations when recruiting for studies. I also understand how we define “loyal” or what counts as having read a story. I can use this when I’m interpreting metrics or making inferences from qualitative studies.
That’s all for now. Go out there and try something new!
