Big Query For Marketers (basics: count distinct, group by, finding functions) - Part 4

[Music] welcome to my fourth video about Google bigquery for marketers many of you wanted me to continue this video series and dive deeper into this topic in this video I will go back to more basic functions of bigquery you will learn how to use count and count this thing grew by I will also show you how to search for any standard SQL functions that you may need hello there welcome to ads courses I will teach you the best PPC and web analytics strategies and secrets if you want to support this channel subscribe and share it okay so let's dive in okay so let's learn some basics today let's start with count function which is like very easy but you can use it in a various different situation okay so I will show you this on data set from Google political acts from bigquery public data set if you don't know how to get access to this public data and bigquery you can watch my other videos where I describe this with details and it's right here so just click on this table and you can query the table okay but I'll just get rid of this limit here okay if we go to the details in here you can see that this is a small table actually if you go to the preview you can see the actual data it's very very simple you've got you know like election cycle report date q1 spent key or to spend your free spent region election which are only one I think and it's for us very simple data but good to actually show how to use this count function if you go to the details here you will see that in the table information that we have number of rows it's 65 in this table only 65 rows so it's very small but you can actually count this number using SQL and to do this you simply write count and if you want to count all the rows all the data you just write it like this in the bracelet and we'll just give you the number let's run this query and as you can see it's 65 same number this is this may seem weird for you but this is just the call name because we didn't add the alias to it right because we count something so we create another column which counts the number of rows but we didn't alias it so we can do this now you simply do this like this as and you give it a name you can give it a name whatever you want it can be like number of rows right and if you run the screen now you will see that this comb will actually have this name number of rows okay so it's very easy and you can do the same use the count function two selected fields okay so we have this table and we have this all these fields right there is a list in here so let's say we want to count the keyword one so let's do this underneath so let's do you are using the same function count function but instead of writing it like that we just use the field name you can press the tab for help and we can name this also like oh let's not name it right now and think about what will this actually counts will it actually count the number of keywords one the individual keywords right yes you can see that they are repetitive here actually there are only few of them unique keywords one or it will or it will count the unique number of keywords so let's see and as you can see it counts the actual number of rows so it doesn't count the keyword actually it counts the number of rows of this field so we can name it keyword rows right okay so what if you want to count the individual unique number of keywords one or any other fields right because as you can see they are repetitive here and there are only few okay in this table it's actually pretty easy to calculate one two three four unique keywords one but maybe you're working on a more complicated table right this is very easy to do so we just write the count and now in the brace that distinct and now we can just copy this let's name it q''-word what number okay and this should actually calculate the unique number of keywords right the distinct number of key we are using count distinct function okay we've got keywords one row and we've got keyword one number which is four you know we've got four unique keywords here and you can do this with different fields so let's say we want to count distinct election cycle from this same table which is actually only one I think if we go to the preview you know election cycle it's all the same let's not forget about the coma you don't have to add the comma at the end the last selection and we rent it should have one number of election cycles should be one and it is okay so it's very easy to use the count function and count distinct which is very convenient in many different situations okay just to show you the same on the table may be more more advanced table like this Google Analytics sample table which is actually pretty which is actually pretty big okay so there are a lot of fields here as you can see some of them are nested if you don't know what I mean you can watch my other video about this but this will actually be pretty simple I just want to show you the count distinct option here okay so let's just let's just format this we can get rid of this limit okay so you've got this okay if you scroll down for example just to show you there is this field hits page path so it's just not that it's page path okay so we want to select this that's just format this to be visible for you and as you can see we can't we just can't use it like this because it's nested I won't describe what it's all about this video I will just put in this formula which will solve this problem I have another video about this topic and how to actually how it all works so let's just select this hits page path and we will see this data right so we've got our all page paths here okay what if we want to count the distinct number of page paths we just read the same as we did before right and we can run this query and see the results so there are three hundred fourteen different page paths and they say that you can obviously it's like number of page we will name it page path right let's run it again and the name is page path and this is the distinct number of paid paths great okay so now I will show you how to use a very convenient function group by ok for this let's work on this table from the public the query public data set I will just paste here okay so we will work on this public data set New York trees tree species and these are the fields like species specific name species common name for fall color etc we want to pull out this these field species common name form and fall color okay from this data set if we go to the preview you can see that this is the species common name it's the scientific name this is the common name right this is the form rounded right for a meeting perennial etc and it's all color yellow red okay so if we would pull out this data like this we would pull out the species common name the form and tall color this data would look like this so we would have our fields as separate columns right but what you can actually do now its group this data by because as you can see there's different issues color names different trees with different forms and with different fall color and right now it's all actually random chaotic and you can group this by and to do this you simply write the group by after dude from part alright we can push the tab so it will auto fill it for you and we can group by actually all of the fields that we selected for now let's select this one only right and you can't do that right you can because you've got the fall color if we would have only species common name and for and form it'll be okay but we also have the fall color now this will and now you will see what this will actually do for us it actually group this data by and so it groups this data so to be maybe this example is not the best one right because we got only one species coming name with one form and one fall color but if the the tree would have different forms of different fall colors may actually have it I don't see it right it would group it for us by by four by four color by species come on name what you can also do is order by order by let me like fall color so it will order the data by fall color so alphabetically right so first will be the cream our own orange red yellow okay so just to show you what I mean Dubai how it works it's actually better to do it on this example right so we only select form we don't select the species common name we select form we select fall color and we grew by form and fall color and order it by form right and if you run this query you can see that groups by form so we get pyramidical paramedical pothole or yellow yellow right it's like this if you wouldn't do the group by and order by this would look like this it will be just random right so group by also a very convenient function very simple to use okay so there are many many different functions in SQL and bigquery and it's difficult to actually memorize this all at the beginning right okay with time you will actually gain experience you will remember most of them but actually don't have to you just need to remember the basic functions and you can also search for whatever you want of the web and a good place for this is the documentation of the Google Cloud I would post a link to this in the description of this video and you have the standard SQL functions and operators in here so you can see them with examples right so this is like the conversion rules right so you can use this you've got the date functions time functions it's all categorized here with examples so this is the best place actually into you also get many different resources like w3schools comm where you can also search for different functions right and tutorials and that's it you know very convenient very simple basic functions to learn when you start your journey with bigquery and SQL that's it for today I will continue this video series in my next video so stay tuned if you want to support this channel simply hit the subscribe button now that's all for today see you in my other videos bye

Channel: Ads Courses

Views: 894

Rating: 5 out of 5

Keywords: digital marketing, performance marketing, google bigquery, big query for marketers, big query, bigquery for marketers, google bigquery tutorial, bigquery sql tutorial, google bigquery for beginners, bigquery google, bigquery tutorial for beginners, google bigquery introduction, google bigquery sql, google bigquery basics, big data, big query for marketers tutorial - part 3, bigquery tutorial, bigquery sql, bigquery google analytics unnest, bigquery google tutorial

Id: --uM4TAc_bg

Length: 11min 45sec (705 seconds)

Published: Mon Apr 13 2020