GitLab UX Research, 28 Jan 2022

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: GS Query Walkthrough 1-28

Description

No description was provided for this meeting.
If this is YOUR meeting, an easy way to fix this is to add a description to your video, wherever mtngs.io found it (probably YouTube).

A

Hey everybody, uh this is going to be like a walk through basically and a do as I do. Example of how to use the google sheets query I'll, be kind of explaining things as I go, but I'll try to keep it as functional as possible, because I don't want to get too lost in the woods of what you can and cannot do in data analysis.

A

So I will try to focus on how to take this data, which is a copy of the research priority calculator that loren made and how to- and I put in a whole bunch of fake data just to use, as example, and how to create a couple.

A

Easy queries using the google sheets function uh and I'll talk about its overlap with sql, mainly because it is a version kind of a sequel and eventually, I think the goal would be for everybody to feel somewhat comfortable being able to query some basic data in sizes and sizes uses sql, as do pretty much all standard business intelligence, sort of solutions like that. So it's a really powerful language and it's actually pretty easy to learn. So with these examples, it should be kind of easier to understand what's going on.

A

So what I'm going to do is take this data and eventually I'll turn it into something like this, which has a view of the data, and then it has just a couple sections of high level, either summary or lists about things that we're kind of interested in and the use case for this would be like for ourselves.

A

This is essentially all of our projects and research projects that are going on in our stage, so this would be like if we wanted to know everything that's going on right now or the next year, or so everything that's planned um and the different aspects of it. That's coming up. So it's like a a nice summary view of all of our data.

A

So I'm going to take this and I will just create it one step at a time and everybody if, if you'd like anybody who wants to, can go on their tab and try to create it at the same time as I'm going through this, and if you have any questions, you can look at my tab or other tabs and you can go in and look at my my queries. You can go in here too, and look at each one and see what it says. um Just try not to copy it.

A

The goal is to try to get better at it. So try to you know, try to figure it out yourself and write it yourself, even if you are just going back and forth and looking at what else, whatever other people have done so anyways, let's get to it.

A

um One of the easy tips also is to expand this equation thing, just bring it down, because it's easier if you enter on a new line each one, uh each like line of code, you know quote quote-unquote it's it's we're kind of fake coding here, but it is an easy way to learn how to do this.

A

So anyways uh we're going to first start with equal sign and then query and then just hit tab, and the first thing that it wants is going to be the the data that it's actually going to be querying so the table, so we're going to go to the sheet that we have the table and I'm going to do just all of the columns.

A

This is the best I'm I'm going to say that this is the best practice, because this way, if we add more rows of data here, it will automatically read it, and it's a little bit neater too, when we're writing things and we're looking back and trying to figure out like if there's an error, what's going wrong, it's a little bit easier to look at this, rather than to try to figure out what specific rows it's pulling from and whatnot.

A

So it's kind of easier if you keep all data on a separate sheet and then use that one sheet as like your raw data, basically so anyways we're going to do that and then we're going to do a comma and then we're going to write empty quotations for now another comma and then we're going to write 3.

A

This three is um how many headers your data has. So in this instance, we have three. We have. The first row is saying: ux researchers, product managers. Second row is the column name, and the third row are questions, so we don't care about any of that data. So we're going to say we have three headers and then go back inside this quotations and I think it's option enter. I use a windows keyboard on a mac computer, so my keys are a little funky but just hit option enter.

A

I think it is um so you can enter in this or you can hit a new line in this without uh breaking what you're doing so. Anyways we're going to do this because it's just a little easier to organize and then we're going to write, select and then asterisk and then hit enter and it'll go back to your sheet and then you'll see all your data and it will be everything so I'll kind of walk you through what happened so select is the sql term or function for grab that column.

A

Basically, it's just going to say whatever it is that you're doing I want to see uh this column and star or asterisk is the sql code for everything so for this select star or select, select asterisk. We're just saying show me all of the columns of my data.

A

So the next thing we're going to do actually is we're going to put a limit on it, um and this is done by saying limit and we're going to limit it right now, just to 20.. This is just for example, purposes.

A

What limit does? Is it limits? The data limits, the number of rows? That's shown we're going to limit it to 20. Just that way we can do things down here without messing up the data. You see how I can write down here. If we get rid of this limit.

A

You get this error because it wants to theoretically show data all through here, because you're, not it, doesn't know how much data is and is not there because we said grab all of the columns. So, theoretically, all of the columns below are filled with just empty data.

A

um This is easily solved as I did before, but just saying limit and 20, and then that just says okay just give me the first 20 rows anything after that doesn't matter so then the equation doesn't even look at what's down here like I said, this is just, for example, purposes in reality, there's times to use limit times not to use limit. This happens to be a good use case for us, but make sure that you're not limiting data that you could otherwise be seeing in the future if you do use limit.

A

So next up we're going to do um we're going to look at something easy, so we're going to write query and we're actually going to just query this right here.

A

You can replace this with your normal data like I did, but I'm going to just do this because it's a bit easier right now. Oh look at that. I'd already like to queries.

A

um It's just easier right here for this example, and we don't have to worry too much about um making sure none of this breaks in the future or any of that, so we're gonna say uh select first, we're going to just going to say, select a okay, so we're just going to say: I want theoretically the number of projects, so the first thing I want to see is just select all of the projects that I have select the project names and then we're going to actually say count them so, instead of selecting just and just showing them we're going to select them, but count them instead and just show me the number so we're going to use the function, count we're going to do that and we're just going to say count.

A

Okay. So then, now our query is showing the count of all of our projects, which is correct. It is 17. You see right here, it's showing the count of all the projects, but it is also showing oh wait here. I forgot. Let me do this see it's much easier like this, because now you can read it more and it goes to the next thing. So the problem is: is that it's showing us uh a really annoying column header, like name right here and it's that's, not very fun. It's easy.

A

It's annoying to read and it doesn't parse very much information to whoever is reading it, so we're going to label it. uh This is different in sql, but it's actually much easier in sql. So if you kind of learn and understand how this works, it's much easier. If you do ever switch to sql, I'm not going to go into how sql works with it, because we're focusing on this right now. But how to do it?

A

Is you say, label at the very end after uh what else you're doing which this meant that matters a lot and we'll see in the future. So at the end, you're going to say label you're going to copy what you did before you say: label that count of the column a and you're going to use whatever these are called single um quotations inside of it and then you're going to say whatever you want it to be called and I'm just going to say number of issues and there we go and that's what it says.

A

So these single quotations are important, because if I do double quotations, it will break what it is. Reading and I'll show you in a second there you go, so you see how this is now not colored. So the sql code now thinks that this is all of the sequel. And then you just have a bunch of randomness after this and then like another quotation block right here.

A

So if you do the single instead, the quotations will be read from start to beginning, and then it will read this as being contained inside of those quotations. That's put inside of the sql code.

A

Okay and this label function is saying: I want to label uh this exact thing: it's a little clunky, so it's best probably to copy and paste if you're having trouble so label this exact thing space and then whatever it is, you want to call it oops, because I there we go okay and then I like to typically bold it and put it in the middle, because it's a header, that's just a good practice for me with headers, okay. So the next thing I want to count is the average priority score.

A

So the priority score is a additive score using weighted values of all of the other columns, um and it is an actual number from zero to a one hundred percent. I assume I don't know if it could actually go to zero um but whatever and the number is a percentage. So we wanted to take a look at the average percent. So we're gonna.

A

Look we're gonna, say: query, query this data and then we're gonna go to the next one: we're going to hit two quotations in the middle hit them to the next line, and then we only have one header in this data right here, because it's looking at this- and we only have this top row.

A

So we only have one header and I want to look at like I said the average priority score so first, I want to just look at the project score, so I say select and I want to say j always like you saw before always refer to it as the letter name at the top.

A

This is unlike sql, but it is a pretty good practice to be strict with where, like what column, you're you're calling. So it's kind of annoying, but it's not the worst. So first we're just going to want to look at priority score. So this these are all of our scores.

A

Right here and now I want an average of those so very similar to count instead of count we're just going to say avg and we're going to put it in parentheses and because I learned before that that gives us an annoying name, I'm going to label it right now, and the best thing to do is copy and paste it. So I'm going to copy and paste this avg space, and then I want the column to be labeled average priority okay.

A

So what this is going to give us should give us is select the j column, which is the priority score, take an average of it and only show me the average and then label that average um average priority and look at that there we go so uh what you can you can mess with it in sql. You could add more things in here to format it differently. You could go um times 100 to oh, apparently you can!

A

Oh because it's see this is what happens when you kind of go on the fly there we go, you could do times multiply 100 um if you wanted to actually look like a um a percentage. But honestly, it's pretty easy because we're in google sheets we're just gonna make this a percent oops make this a percent and then format the column again or the header, and I like this to be whatever it could be like this okay, so that is now the average of all of our scores.

A

Okay and then the next one is going to be a little bit of a different one. So we're going to branch out now, but we're still going to do the same things before query all of this data and then one header and then start on a new line. So this one we're going to look. We want to look at the timeline, so we have an optional column at the end for the timeline when this research should be completed by it's optional.

A

Just because from what I saw it didn't seem to be always filled out, but it's it would be helpful to fill it out. Whatever. That's not the point of this um but say we want to look at whatever the next uh one. Our next upcoming research is going to be finished, whether it's by us or not by us. It doesn't matter we just want to know when the next research is going to be wrapped up, because that's usually when I, as a researcher like to go in look at the results, make sure everything checks out.

A

It all makes sense make sure that they wrote their summary up at the top of their issue and that they have any actionable insights created when necessary, so just to keep a good tab on that. We want to see when the most recent or most upcoming uh research is based off of that last column on the m.

A

This m column over here, so we're going to say, select, m and we're going to start off differently, we're actually going to order it and we're going to order we're going to say order by m, because we want to order by that same column and we're just going to hit enter. And here we go.

A

This actually is the order that we want, because it starts with the most most upcoming, whatever closest and then the farthest away. But just so we can see we want to say. Let's say we want to change that and we can go descending, and we want to write this after the column that we select and we can say, d e s c hit enter and then it flips it upside down. So it makes this.

A

This is the last research that we currently have planned is most likely be to be completed at this date at the longest. Apparently, um and then maybe we want to force it to back what we had before and that would be ascending. So then you just do asc, oops, not that there we go and there we go, and we would just want to see the most recent one whatever the most is. So this is a really useful time to do. A limit limits can be used in a couple different ways. um It can be used.

A

I like to use them to set general guidelines like you saw before, for where the data is so that way, I can mess around with the sheet and format the sheet. Accordingly, it could also be used in this instance to select the top one top two top three or bottom three of a given list, um and it's really good for listing things and selecting just a portion of those.

A

It's one of the few um row wise operators really because it is saying how many select, how many rows you want, whereas every every other function, more or less is going to be column wise, which is you know doing this to this column, selecting this column, changing this column so and so forth.

A

So just some background into that. So we're just going to say limit and we're just going to say one. We want just the most recent one whatever it is, and then here we go and then let's say we want to label this as well, so we do it after the limit. These do matter. um The order does matter when I share this to you guys I'll, give everybody the link to a good page for documentation on it and in that page they'll have an order of each of the functions that order matters very much.

A

You can only do certain functions after other functions um or before certain functions they so select always begins. It's always the start, because you're sling you're, saying just show me this data to start grab these columns just to start and typically uh in normal sql limit is the last one in this label is often is going to be the last one. So we say I think they say: label should be the last one, because in sql you can change the name of a column, and in this you can really only change what you display.

A

As the name of the column, you can't you're not really changing the name of the column, just what it's displayed, as I think, is why it's last so we're going to go. We're going to do this and we're going to say, label m uh and we're just gonna say what should we say?

A

um Next research do on there all right next research due february of 2020, and I'm actually gonna merge these, because that's a little long and I like it to look better there we go and at the top we can create a little summary. These are just going to be.

A

See this is why I can't spell okay high level summary there we go and bam. So this is an example of using google, google sheets queries or creating queries that are answering one specific question and generating one specific piece of information. It is one item of information instead of a list or um you know several rows of data or equations or a formulated equation. That's outputted! It is just a simple question that gives us one piece of data on one column.

A

So next we're going to try something like that, but using different functions and using slightly different use cases. So we want we're going to be focusing on one column for our next couple queries, but we're going to using different filters on it. So, first, I'm just going to do a couple cells away, write, query and then select the data that I want.

A

This is the same every time there we go all right so for this we're going to say, select and we want to know uh a breakdown of each of our tiers for our priority scores and that's found in the support level. So priority scores above an 80 percent receive gold priority scores that are, I believe, below 50 50 and below, I want to say, um are bronze and scores that are between 50 and 80 are silver. I think um refer to the sheet to know more but anyways.

A

We want to see a breakdown of each of those and how many are in each so there's a couple ways to do this and I want to show first a kind of the data e way and then next I want to show the here's how to show something way, the the slightly better way to present it.

A

So first, what we want to do is we're just going to say, select the k, the column, that we want priorities or support level and then also next we're going to do comma and then we're going to say count that count, k, okay and then so that we're now selecting two columns and next we're actually going to hit an aggregate function, and this is going to say group.

A

I think it's going to be group by okay, okay and then hit enter there. We go okay. So what this does is it first, it says: grab me the the column, k and also grab me how many num, how many rows are in each column, 4k and we're going to group it by k, so we're going to say for each unique variable that k contains uh select all of the the rows that contain that variable. So in this we have three variables.

A

We have bronze gold and silver and we have seven rows in bronze three in gold and seven in silver, so this outputs, the data and what we would call long format, because this is um I never mind, I'm not going to go into long format. It doesn't matter, um but this shows it in a way that some people don't read it as easily and sometimes it's not quite um simple to sort things like.

A

If we were to go here and we were to say order by k, say we want it in order of you know: bronze silver gold. Well, it doesn't know that it doesn't know that it should go bronze silver gold. So, whatever we do to it, it's not really going to look like how we want. You see so these are just some limitations of this way of doing things, so, instead we're going to do it a little differently, we're going to filter and we're going to do it one at a time.

A

So first we're going to look at gold, so we're going to say it's kind of similar last time, we're going to say, count, ok and then hit enter and instead of grouping by we're actually going to filter and we're going to say, filter- and this is where we use logical operators. Logical operators are um in a simple equation that gives you a yes or a no, it's very simple: it is binary, yes or no true or false.

A

Each row can either be true or it can be false, um can't be both or anything else, and it can only contain two. You can use these filters.

A

um Oh sorry, we're actually not going to say, filter, we're going to say where forgot what language I'm coding on, so we're going to say where um but anyways each of these binary filters can be combined to do multiple things like we'll do later put to start we're just going to use a string binary, saying we want k, which is the support to equal and then we're going to use the single column. um Quotations again we're going to say we want it to equal gold and it needs to be exactly as it is spelled so.

A

Capital g, I'm gonna, say select and there we go. It's gonna, be say three. So, uh but again we don't really know what this reads at so we're gonna label this and we're gonna label it as gold.

A

There we go okay, so this is now saying give me k the support level column count all of the rows that are in it, but only count the rows that actually say gold and then just label that column and then what we're going to do is we're going to copy this oops copy it.

A

There we go and then we're going to paste it in specifically do this, because if you transition, if you go like this, it could mess up the data for where you're pulling you see how now everything has shifted one, and you see how you didn't even notice before. uh If you do this a couple times, it will mess up your data. If you go like this, let's keep doing it until there you go.

A

So you see how this data is all the way over. Here it keeps shifting it. You don't know, because the column k is still contained within it, but it will slowly start corrupting your queries.

A

So don't do that best practice would be to go in here, control all control copy and then go in here and paste it, and there you go so now we have different ones and the only thing we're going to change is where it says: gold we're going to change to silver on both of them silver, silver and then we're gonna do the same thing. So we're gonna go from gold to bronze gold to bronze, and there we go so now quite easily.

A

We have the count of each of the bronze silver and gold support levels in a way that we can actually read pretty easily and is digestible for everybody, and for this we're going to say, support level summary. I really can't spell good.

A

That was obviously a joke. I was supposed to say well never mind. I just realized that not everybody would get that um okay, so next we are going to actually make a list. So this is going to be something like a use case where you want to say we have a lot of data, but we're only actually going to be interested in a certain amount of this data and we're not really interested in how many is there or what's going on with it, we're really interested in taking next steps with them.

A

So we want to identify what's theirs and then like what projects are an issue and then move forward with it on something else. So we actually need to know what projects it is so for this we're going to go over here a little bit on this one and we're going to write query we're going to do the same setup. You can copy and paste your queries and just change the middle. I do that a lot.

A

I'm just doing this, because it's it's easier for my mind, one set at a time so anyways, so we are going to want.

A

Let's try something simple: we're going to want only to look at the high support level high priority issues, so we're going to want any issue: that's over 80 percent, but in this case that's already labeled in the k column again, so we're going to go select and let's say we want everything, select everything and then we're going to say where so we're going to have a filter and select everything where that k column is equal only to gold again and we're gonna hit yes, and then we realize wow. This is a lot of columns.

A

We don't need all of these columns for sure. So, let's limit these columns, we say okay, so we obviously want to know the project name and in the future. This could contain the link or whatever to the issue id whatever it is, and we're also probably going to want to know the research type because in our research we use that pretty often to kind of generally know what's going on with it, whether it's simple or complex and those other things.

A

Obviously we have the information here, but we don't necessarily have to show it all in this one query. So we're going to do that and because we're doing this because of the priority score, we're only interested in high priority items, we're also going to include the priority score, we're going to include j and there we go, but we think about it. It's like okay! Well, it doesn't really make sense very much to sort it like this, where the lowest priority is on top.

A

If we have this score, it probably makes sense to sort it like that, so we're going to order by uh what is it we're gonna order by j and we're going to whoops say order by uh correctly and we're gonna want it?

A

I'm not very good at this, it's descending right there we go all right, yeah, we're gonna, want it descending, and then we can see here. These are our apparently top three uh highest priority issues that we're really concerned with.

A

uh So one thing like I did before what we can do is we can put a limit on here and we can say I mean realistically, the maximum amount of high priority issues that we'd have at any given moment would not be greater than 10. I would really hope, unless one of you guys are really stressed out, so we're just going to say limit 10 and then that allows us, because this is a list and not a count like a lot of these other ones, because this is something that could theoretically go on forever.

A

We don't want to ruin it by accidentally putting information in here sometimes so. It's good just put a healthy limit. I like to call it so anyways here we go and, lastly, we're going to create another list, but it's going to be something that's a little bit more different and a little bit more unique. So let's say for me for my secure protect group. I don't just want to look at the high priority items.

A

I also want to look at things that are complex, um that we don't know very much about and or that concerns the whole company or something like that or or actually I'll, even change it I'll say. You know something that is definitely complex um or I don't know something that you know leaves some ambiguity to it. So that's an area where, as a researcher, I could come in and help people shape what they're understanding or what they're looking at a little bit more.

A

We also want to say that it will. It is low confidence, something maybe the the product manager, it's a new field, and this is probably more generative in nature, or they are just kind of fishy about previous research. So they want to make sure that this one's correct.

A

So we'll just do that, and we can say maybe a healthy limit of only show me for something within this year or something like that. Okay, so we're going to start with a query and we're going to look at this data again uh here we go.

A

Okay and we're gonna start we obviously we will definitely want a um in data analysis or in databases, a is, or this column would be referred to as a unique id or a key, um that's something that is generated. Obviously you guys would be familiar with this in general, because it's generated in things like qualtrics, but it's incredibly popular in database structures, because a unique id is the one piece of information that you're likely to tie in with another table, because it is the one piece of information that is truly identifiable for that observation.

A

All of these other things are what would be. We call attributes on to that observation, but the unique id- or in this case the project is the actual um id that we're going off of.

A

That is a very helpful concept to learn when you're not doing this now, but when you are joining other columns, because you're often going to be joining on a unique id or a key of some capacity.

A

So anyways we're always going to want to select a in a query like this, where we're going to list things we're also going to want to select b, because we want to know what type of project it is and we're going to want to know j, because these are kind of helpful high level or, let's say k, because that's even easier to understand and read high level things that we want to look at. So we're going to say, give me all of those and then we're going to start to work on our filters.

A

So we're going to say where and first one the easiest one to look at is saying complexity to let's say so d and we're going to say is not equal to no. So this is because we have three three ways: our data can be yes nowhere. I don't know, and in this instance I feel like it's pretty important to include. I don't know because that means by nature. They don't know if it does have complexity and that's an area where I can help them out.

A

So we're going to say is not no and the way to say is not is using the exclamation point and equation sign and that's a is not and then we're going to say and uh we want it. So the confidence score, which is f in this instance, which is this column, is equal to low.

A

So this is something where it could be complex and we're not super confident on it and just give me those, but apparently there's only one, that's pretty weird right.

A

So what we're actually probably going to want to do because this says and is we're actually going to want to put or so the difference between and and or is and will naturally always be more restrictive. It's wanting both things to be correct. So in this instance it's wanting the complexity to not equal, no and at the exact same time it wants it to be a low confidence score. Well, we actually only have one low or we have two low confidence scores.

A

One of them we can see here has high yes complexity, and one of them has no, so we're only seeing one. So if we select or then we see much more, we see all data that matches any of our selectors okay and then we look at it and we're saying okay.

A

But the problem is, is that some of this data is repeated and that's always a waste project. 1, 16 and 11 are our high priority items and naturally they show up here, because high priority naturally leads to being complex and having low confidence because of our equation. But it's not really useful to show both of those twice. So we're actually going to think of something.

A

So we're going to now say um we're going to put it at the start and I'll explain why in a second, but now we're going to say, support level does not equal gold and the reason why we're saying this is because the last one we did and we're going to say and we're going to say this, because the last equation, the last query that we did already showed that data- and we know that that data is the are the ones that have gold in it.

A

So we're going to select no and it's automatically going to remove those pieces of information, okay and we're going to do it at the start, because one very useful tip to get into is to write your operatings operators with these parentheses.

A

So if you group your and and your ores together, it makes it a lot better to understand. What's going to be done to your data, so ors are com or they're combined, typically because it's allowing any of them to happen at once, whereas ands all of them happen have to happen at once in order for it to get through. So if you group them together, it's a little easier to see in that respect, and we can see that these are our oars and we actually want to kind of maybe expand on this.

A

So we said where the complexity is yes or I don't know where the confidence is low and we don't want it to be gold, but let's, but it also makes sense to probably not show solution, validation, too much, because solution validation tends to be pretty quick um and relatively automatic, at least in my stage group. It tends to be relatively automatic. I kind of super supervise the creating of some things in the overview, but I don't get in the weeds too much.

A

So just as an extra kind of layer, I'm going to say b, let them say we're actually going to include over here, we're going to say and b is not equal to solution validation.

A

There we go okay and then we have to decide so, and here there we go.

A

So we have two ands and we can do it like this.

A

There we go so we have two strict filters and two non-strict filters, so this allows any anything that is uh not um it's hard to word double negatives, anything that is not a complexity of no and anything that has low confidence and is not solution, validation and is not repeated before so. This gives us an extra level of insight and we're going to just call this. Maybe projects to look out for- and this could be just a little bit more customized query so for each of you guys try to think of your own.

A

That would make sense. Try to include a couple different filters in there all right and then I'm just doing this because it's easy for me and then we're going to say, hi priority.

A

Projects there we go.

A

All right, sorry, my add brain had to so um each of you try to do the last one. However, you can, uh however, make sense for you or play around with it. Do different things: um try using different combinations of things and break it and see what happens, feel free um yeah and if you have any help, try uh just message me or at me in this sheet, because it's it's my sheet, so I have full access to everything.

A

So, if you just add me, I can look in and see if I can help in any way again tr. I really encourage you to try your hardest to just think about this stuff. It can really help you with the data analysis of things, just conceptually thinking about how data works in this respect.

A

It really just improves your influence when you're communicating with other people who are doing engineering data. Anything like that, because it's a pretty common overlap, honestly, so yeah. Thank you very much and sorry for this recording being insanely long uh have a good day.