At my previous company, even though we mostly used Snowflake, I had a bit of experience with BigQuery because we had our GA360 data there.

So when I decided to set up my own company, supercooldata, I needed to choose a data warehouse for our analytics. Most of our existing customers are SEO and marketing professionals who already use BigQuery so we decided to give it a go.

There are some things I love about BigQuery and there are some things I don’t. I’ll leave the comparison to Snowflake and Redshift for another day.

There is one thing that stands out as extremely powerful.

BigQuery’s integration with Google Sheets.

You can create an external table from a Google Sheet in BigQuery and then query the live data directly from SQL.

For people who have only ever used BigQuery, this won’t seem like a big deal.

For those that haven’t, this is completely revolutionary. It blew my mind when I found out about it.

Why is this so cool?

1. Merging automated and non-automated data sources

It’s a common use case to want to create reports from automated data feeds as well as manually inputted data.

Being able to combine both with SQL and put the result into a report provides a holistic picture of what’s going on. You would otherwise have to schedule some job to load the Google sheet at some regular interval.

With BigQuery and Google Sheets, the sync is completely live. You can check the dashboard, edit the Google Sheet, refresh the dashboard and then the changes will automatically be reflected.

2. Create internal data collection apps

Many software engineers have created internal data entry apps for employees - I have worked on a few. The overhead to create such an app can be quite high as it often involves bespoke UI or functionality.

You can make powerful internal data entry applications with Google Sheets and BigQuery.

Your internal users can input data into Google Sheets, a scheduled job can run a SQL script and the outputted data can then be used to alert someone to an action they must do.

This replaces the need for many internal tools.

Here’s how to do it.

It’s extremely easy, no hacks and it seems to work pretty reliably - I’m impressed.

1. Click on the dataset where you want the Google Sheet table to be created. I created a dataset called ‘google_sheets’ to organize my queryable Google Sheets.

2. Click on “CREATE TABLE” (for wide screens) or the “+” icon

1. Choose “Drive”

2. Put in the link to the Google Sheet from your browser

3. Choose “Google Sheets”

4. The name of the dataset is up to you but it should already exist. I previously created one called “google_sheets”

5. This can be up to you and doesn’t need to be the same as the Google Sheet

6. Most of the time Auto-detect works

7. Under Advanced options, you normally need to skip the first row as that has the column names.

8. Then click “Create table”

Now, I can query the Google Sheet directly from BigQuery. You can see the user_feedback table on the left.

Any time you edit your Google Sheet, your connected dashboards will be updated.

For this example, I can now combine my manually inputted user feedback data with my Google Analytics events for each of my users.

Adding in Google Forms into the mix makes this combination even more impressive.

In my BigQuery account, I have a table called onboarding_survey.

This is automatically populated from a Google Form which users submit. I have combined this data with my mailchimp data to provide new insights using SQL.

This is a very powerful feature of BigQuery and I hope you find it as useful as me.