What is Google BigQuery and why should marketers use it?

Explore the possibilities BigQuery opens for your company and discover use cases and best practices.

janet wong
7 min readOct 15, 2021

When I first heard about Google BigQuery, I would have always thought that this term is owned by the Engineer/Analyst to work with — there is nothing related to me as a Marketer. But obviously, this is not true. As long as you work with Google tools, i.e., Google Analytics, Google Data Studio, Google Tag Manager, you will eventually come across one day realising how correlated they are.

And this has not included the exponential volume of data growth within your marketing apartment, which leads you to another question: How can you harness the large sum of data for analysis? What are you going to do when you reach the end of the spreadsheet?

So, these reasons prompt me to think about the importance of BigQuery and trigger my desire to understand its basics. From taking part in the online course CXL of Google BigQuery, this is what has summed up that it helps you to:

  • Consolidate all your data in one place — no more silos
  • Automate your marketing reports — break the routine
  • Develop your skillset to become a data-driven marketer
  • Learn more about Google Cloud and its possibilities
  • Discover the complete analytics process from data collection to data visualisation

Google BigQuery Explaining- Data Analytics Infrastructure

Working with your analytics tools like GA or Adobe Analytics comprises two parts — data collection and data visualisation. In most cases, you can't interfere with how your data is collected, stored and moved during the analytics process. But with Google BigQuery, you can collect the data, consolidate it in a single place, store it within the warehouse, work with some data formula/function to transform the data you need, and eventually visualise the data.

Data Analytics Infrastrcuture. The slide is taken from CXL for reference.

You can find plenty of different schema of data analytics structure in BigQuery, and then retrieve the selected data combining other tables, datasets, etc. And visualising this data to do your marketing analytics.)

Data Analytics Infrastrcuture. The slide is taken from CXL for reference.

Google Cloud Platform comprises various tools, but we will only be looking at Google Cloud Storage, BigQuery and Cloud Function in this article.

Intro to GCP. The slide is taken from CXL for reference.

One fairly important thing is your cloud payment. When you create your cloud organisation, you will build your project connected with your billing account. In the cloud payment schema, you could access your client/partners' project without accessing their billing account.

Cloud Payment Schema.

So, your BigQuery Data project is structured by your datasets and tables in the general use case. BigQuery also has jobs (a.k.a tasks) with the logs that show you every process that happens within your data in your historical information.

BigQuery Data Strucutre.

Now you understand the concept of how BigQuery works in general, let's walk through the free BigQuery features for your SQL and data management and build your own data structure for your future data warehouse.

Google Cloud Platform Account Set up

To login into the Google Cloud Platform, you need to visit this link and set up an account. You will be asked to insert your credit card details, but we are using free BigQuery for learning purposes in this article, and it can function without a credit card.

Click Get started for free to complete your setup.

Once you complete the setup, you will be redirected to this dashboard with the major functionalities. If you don't have the credit card details entered, you will likely use BigQuery in a sandbox mode which it's okay at the beginning.

* Small tip — enter "billing" in the search bar or find it at the left nav menu to remove the payment method if you have unintentionally added your card.

Google Cloud Platform Dashboard.

You'll find the nav menu on your LHS that lists different services of GCP, but most of them are not running unless a free trial is activated. ( It helps you to access the feature rapidly.) since we will only talk about Google Cloud Storage, BigQuery and Cloud Function, you can pinpoint the most common use of the feature.

Pin the everyday use of the features on your left nav menu.

Google BigQuery Interface

BigQuery has features built-in that allow you to recap all the works you did, proactively save specific queries and share them with others. Here're the features you need to take a look at:

  • Query history- include the text of all queries that you have run previously, but note that this history is limited to 1,000 queries and looks back up to 6 months.
  • Saved queries- save the specific queries that are commonly used.
  • Job history- show the record of what was going on in the BigQuery. For example, automated imports from Cloud Storage with the help of Cloud Function to BigQuery tables.
  • Transfers- activate a transfer of data from different Google services to Google BigQuery.
  • Scheduled queries- If you have an automating dashboard, this feature will help you schedule some queries and renew your data in a specific timeframe you set.
  • Reservations- This is a new feature that is still in beta. Reservation has two pricing tiers: on-demand model pricing and flat-rate pricing. On-demand pricing lets you pay the amount of data you consume, whereas Flat-Rate pricing refers to a stable monthly cost of BigQuery that does not limit the amount you use. The large enterprise would have preferred flat-rate pricing over the on-demand.
  • BI Engine- is a business intelligence engine that makes your analysis faster, which we will dive into more details later.
  • Resource- let you explore the public dataset, pin/add an available project, and connect via API.
  • Query editor: let you write your query

Google BigQuery Sandbox — Add Public Dataset

So, let's explore BigQuery Sandbox. In Sandbox mode, you can try BigQuery in two ways:

  • Public data set/ Your own data for 60 days storage
Explore public datasets for testing.

To play with the public dataset, you can find it under "ADD DATA". Then search for the public dataset you need. In this case, I will select GA Sample Data for this use case.

Select the public datasets you need.

You may want to create your own datasets in BigQuery, which take you two main reasons:

  • Test the data sets to understand further if it can be helpful.
  • Need some temporary analysis with SQL but don't want to sign on for a free trial

Now, here you can find your GA sample data sets show in the schema. You can rename your project, but here we had a random name assigned. To give a name to your dataset, hit CREATE DATASET.

Create Dataset

You will have a window open on your RHS, give it a name and hit CREATE DATASET to save it.

And here, you'll find your newly created dataset under the project. Your schema is empty at the moment because you'll need to create the table and input some data, which we'll talk about in more detail in the following article. But for now, you can explore BigQuery's public dataset without entering your credit card and activating the free trial.

Wrapping up

Google BigQuery is a powerful tool for marketers looking to get the most out of their data. It provides easy, high-speed querying and analysis of large datasets in seconds. Google BigQuery helps you get insights from your data quickly, without having to worry about managing servers or getting slowed down by analytics software.

I hope you are inspired by the BigQuery use case and discover its best practices in any possible way. If you want to improve your knowledge with valuable resources, here is where you can check out:

--

--

janet wong

“Everything is theoretically impossible, until it is done.” Robert A. Heinlein.