You’re going to set up your own personal BigQuery account so you can practice SQL on real data.

What is a BigQuery?

It’s this thing that takes commands and gives you back data like spreadsheets. It can do this on pretty big datasets, quickly.

What kind of commands does it take?

Well, you can tell it all sorts of things. You can say:

“I command you to give me the total revenue for each country from my Google Analytics table but only for countries in Europe."

It doesn’t work quite like that. You have to speak a special language, that language is called SQL.

How do I learn this magical language?

You can sign up for our course at supercooldata.com.

It suits anyone from product, marketing, SEO or finance (any person really?). Sign up and try it out for free.

The main thing I’ve learnt from years of teaching is that if you don’t practice what you learn, it just gradually disappears from your brain.

That initial time investment was all a big waste of time which you could have spent doing other productive things like watching Netflix documentaries.

That’s why setting up a place where you can practice what you learn on your own real data is so important.

This will make the whole course 10.6x more useful. We wrote a SQL query to get that number - it’s 100% real, believe me.

It will take less than 10 minutes to go through, I kept it short so you don’t get bored.

Or, you can just follow the video which we conveniently made for you too (this article covers the first 3 minutes of the video):

Terms

BigQuery: It is a database. You send it SQL and it sends you tables back.

Table: It’s kind of like an Excel spreadsheet but you can’t edit it directly. It’s got rows and columns.

Column: Each table in a database has columns with data. The data can be numbers, words, dates etc, pretty much the same as a spreadsheet.

Instructions

Are you ready for the best BigQuery setup tutorial the world has ever seen?

• Go here: https://cloud.google.com/bigquery
• Click “Try BigQuery free”
• Click “I agree…”
• Click “Continue”
• Fill out Step 2
  • Choose either “Individual” or “Business” (I don’t think it matters, I chose “Individual”)
  • Put in a valid credit card
    • $300 free credit
    • Won’t be charged unless you upgrade to a paid account
• Click “Start my free trial”
• Now you are in Google Cloud Console (Don’t worry about this too much, we just want to use BigQuery which is one of the many offered by Google Cloud).
• A project has already been created called “My First Project”

• Scroll down to the “BIG DATA” section in the left menu bar and choose “BigQuery”

• You can also get to this link by navigating to: https://console.cloud.google.com/bigquery
• Make sure you are logged in to the correct gmail account AND have the right project selected
• Click “ADD DATA”
• Click “Explore public datasets”

• Search for “google analytics”

• Click “Google Analytics Sample” result
• A new window will appear
• Click “QUERY TABLE”

• Edit the query in the query editor and paste this in:

SELECT
  device.deviceCategory,
  trafficSource.source
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
LIMIT 1000;

• Click “Run”
• You’ve run your first query

• There are many more public datasets to explore for practice as you learn more SQL

• You can click on a specific dataset to view all the columns in the table available for querying

This dataset is only a sample dataset and is not real data.

If you work for a company that has purchased Google Analytics 360 (very expensive) then you can set up the export with these instructions: https://support.google.com/analytics/answer/3416092?hl=en

That’s it! Congratulations.

You made it to the end of the tutorial and now you’re ready for the next part where we will actually load your real data to BigQuery.

See what I did there?

I split it up into two parts because it was getting a bit too long and I wanted to make sure you actually did both…

Next Tutorial: Load your data to BigQuery by clicking your mouse a bunch of times