Google BigQuery Tutorial

in this video I'm gonna give you a quick overview on Google bigquery a lot more coming up hey their measure geeks Julian here back with another video today we want to talk about Google bigquery now you might have heard of Google bigquery it has become more popular over the last few years that it has been out really because marketers who want to dive deeper into the data and take the data out of Google Analytics or your Facebook ads or Google Ads and dive deeper into the data need a tool set in order to house that data and then do a little bit of an analysis that's where Google bigquery comes in so in this video I'm gonna take you through Google vikre the basic functionalities so you'll be able to use it as well in your workflow and know what it's useful for but this is in no means a full tutorial on Google bigquery there's much more to discover if you're interested in finding out more about bigquery we're actually working on a course within our measure master's program you can find a link down below now we got lots of cover so let's live in today our journey starts at cloud or google.com slash bigquery because Google bigquery is actually part of the Google cloud platform now the Google cloud platform has many different tools bigquery is just one of those needless to say you need to have a Google cloud platform account you can create one for free don't have to put in your credit card information and if you don't want to upgrade you can actually leave it at that you can access bakery in the sandbox itself which is quite limited but sufficient for our demo today so what is bigquery exactly well it says here serval is high scalable and cost-effective cloud data warehouse designed for business agility that's a handful really what you just need to remember is the data warehouse part Google bigquery can store our data just like in a database these data base can be huge and you can analyze them transform your data or process it further for your needs now you might be familiar with this concept because you have been working with something like a spreadsheet before especially the applications like Google sheets or Microsoft Excel also let you import data just like this and a table format you have your columns up here and then you can work with this data or for example use formulas to calculate things so for example some and you get to some of your column here you might also use filter options and try to get closer to answer your question and work with that data to gain insights now this is all fine to a certain degree but what if you have more sales data so for example we have hundred records here here is a sheet with 50,000 sales records and right away and notice that it's not as responsive anymore so when we add for example our sum again takes a little while to calculate especially if you pull this over to other rows you get this bar up here or if you were trying to enter your photos if sometimes feel that the application is stuck and sometimes you don't have an indication if it's actually still working so right now I would reload no it did it no it didn't do it let's see if you can still get the filter to work I'm gonna go up and let's click on filter here in general everything is slower and sometimes even unworkable with a larger data set so here we go but always this bar shows us the progress I don't really want to start putting a pivot table on here because that would probably take a while now this is a scenario where you might want to look at the query there are also other methods of analyzing big data this is in nowhere really big data in that sense but it shows where the spreadsheet applications sometimes hit a limit so let's enter a Google bigquery as I said you need to have a Google cloud platform account you create a project up here in the tool set down here with all the different offerings and services of the Google cloud platform you want to look for bigquery and open it once you are in you can see here we are in the sandbox we can upgrade to have the for bigquery experience but we will leave need to leave a credit card in order to do so this sandbox account is a bit limited but it will suffice for our demo today so what are we looking at right here this is the big query interface on the left side here we can see our cloud project and we can add some data to it so first thing that we would need to do is create a data set let's call this demo I'm gonna skip all these different configurations and go to my data set so here's my demo and now I can create different kind of tables now we can create an empty table but then nothing would be in it we want to create a table where we upload our data obviously there are many different forms of feeding data into bigquery you can do this for example with other services like Google Cloud Storage or Google Cloud BigTable you can also upload data from drive or build a cloud function that pushes data automatically and you could also utilize third party platforms such as super metrics or evokes bi to pull your data from Facebook's have API and fill it all in to Google bigquery or utilize the open big hurry API it really depends on your workflow for our demo I will just upload our file it's a CSV file so the file format is CSV right here and then we can give it a name and put in this schema now the schema is very important because we need to model after what we have in these columns right here and what data type it actually is so for example this would be a string we have floating values here as well numbers obviously but bigquery already can auto detect that schema if it's correctly filled into the CSV so we'll go ahead and simply create this table also skip all of these different Advanced Options won't go into detail today and then it will create a load job if it's a bigger file and now our demo data set is created by the way if you want to get access to this demo data set I just downloaded it from this website efore excel comm and we have here sales records that go into the millions although with our sandbox we can only upload 10 MB so you need to stay with the 50,000 100,000 right here and now we have our sales records in here and we can see the schema these are the different columns that were detected and when we look at preview we see all that data that was filled in and it's basically the same format that we see in our sheet now what can we do with this table we can query it and this is where SQL comes in in this window you put in your query which is based on SQL and then you can run all kinds of different operations in query for data what would this look like let's click on this query table and this is a simple SQL statement select what you want to select from the schema right here well we can put in the asterisks to select everything from this table and limit your output 1000 let's run this and after just one second we get our data back and we can see the result right here it's basically the same table as we have selected everything but you can obviously if you know SQL and the power of it pretty much do any kind of analysis that you would also be able to do in a sheet so let's go over here and actually format this a little bit so it's a little bit better readable and here you can see the keywords that SQL uses if you want to learn more about SQL I'm gonna have some resources linked up down below but here you can go ahead for example only select certain columns here I can actually go back to the sales record and instead of the Asterix you just click on the schema epi one so for example let's get these different rows here and you see this is what what has been entered click run again and we only get these certain columns back obviously we can also do stuff like we did before so when we want to calculate all the different the total profit we can put in some formulas like some run this again and we get our result back we could also order our data or group it together so for example grouping together would be let's go back to our example here let's actually group the sum total profits by region command here and then we need to have a keyword called group I and region let's run this and here we go we get the results per region now this is a table name that was automatically created we can also create our own column name and transform our data this way now if you run this analysis more often you can obviously save this query you can also save the results if you want to push the data somewhere else and another interesting one is to save this view this will actually create another table within your Vickery data set based on the query that you have just written so let's put in profit by region now we actually need to also because this will actually create another table that we can now use it only has two columns as the schema data and we can utilize this data even further and Curie for it and see what's inside work with it more and so on so if you want the Google bigquery for large data that can be super valuable to utilize to do some different analysis with SQL if you are very familiar with that I'm actually a big fan of transforming my data inside of Google bigquery so it's in the right format but still leave it kind of raw just like this and then utilize the power of data studio because what we can do is simply go to explore data right here and it will open up our data set if we are connected with the accounts in data Studios data Explorer now this is not a full dashboard is simply one chart that you can choose right here and then like in data studio you can go ahead and add dimensions or metrics and if you want you could also utilize filters up here to say you only want to look at one certain area of your data set now you can run any kind of analysis right here you could also build calculated fields planned data with other data together or use custom and within your custom metrics that you create in data studio once you are happy with your data set so for example let's go back and actually try to recreate from our region we have our total profit it has been already summed up let's get around and record count here and make a nice bar chart out of it - like your what you see you can also save this with the data source and integrate it into a new report once it's saved create a new report from here you edit through your report and then your visualization should appear inside of your data studio report and obviously you can create more graphics around us with the new data source of Vickery so this was a quick overview on the capabilities of Vickery obviously there's much more to explore by yourself inside of the big query interface google has a great documentation here and although we just went through a very little example here the query is capable of analyzing for example Google Analytics raw data your Facebook Ads account or many other different data sources you can also add one of the public data sets through your bigquery account and then happy analyzing alright so there you have it this is a quick overview on Google bigquery I hope you now know what it is and if you have worked with Google bigquery already and I'd love to know from you in the comments down below what your specific use cases are and as mentioned at the beginning if you want to dive deeper into Google bigquery we have a course that we are working on within our measure masses community you can find the link down below or if you're not interested in that you can just stay tuned to this channel because we are sure to bring out more tutorials on everything measurement every week definitely subscribe right over there so you don't miss out now my name is Julien the next time

Channel: MeasureSchool

Views: 86,726

Rating: 4.943222 out of 5

Keywords: Google BigQuery, BigQuery, Google Cloud Platform, Google BigQuery Tutorial, Big Query

Id: MH5M2Crn6Ag

Length: 15min 1sec (901 seconds)

Published: Wed Jun 10 2020