Guide to BigQuery Cost Optimization

Last Updated: January 3, 2023

bigquery cost 1

If you do not want BigQuery to send you a bill of several hundred or thousand dollars a month, then you are at the right place.

Contrary to popular belief, Google BigQuery is a cost-effective solution.

However, it can get expensive pretty fast in the hands of a rookie.

If you regularly find yourself testing the limits of Google Sheets or MS excel, you are not ready for BigQuery.

Because that means you have the habit of mindlessly processing a large amount of data.

You do not have clearly defined data analysis objectives. You do not have clearly defined business questions.

I use BigQuery to leverage the power of SQL. Formulas and functions can never compete with SQL.

Most people download a large chunk of data and then decide what to do with it.

You can get away with this bad habit when using Google Sheets/Excel. What’s the worst that could happen? Your application will freeze.

But what’s the worst that could happen when you bring your bad habit to BigQuery?

BigQuery will charge your company dearly for mindless data processing. You could end up paying hundreds or thousands of dollars to Google.

Your monthly cost of using BigQuery depends upon the following factors:

#1 The amount of data you stored in BigQuery (i.e. the storage cost)

#2 The amount of data you processed by each query you run (i.e. the query cost).

#3 The cost of connecting your Google Analytics account to BigQuery

The first 10 GB of active storage is free each month. After that, you would be charged $0.020 per GB of active storage.

The first 1 terabyte of data processed is free each month. After that, you would be charged $5 per terabyte (TB) of data processed. 

But before you even start processing a large amount of data, you should ask yourself why you are even collecting that much data.

Is it even necessary? Do you even have permission?

Because collecting unnecessary data about your website users and customers are against GDPR.

GDPR compliance requires practising data minimization.

Data minimization is the practice of collecting, storing and using only that personal data, which you absolutely need, for the purpose you have specified in your privacy policy.

Now when I hear someone bragging about processing millions or billions of rows of data, my first thought (unless proven wrong) is that their company is not GDPR compliant.

They are a sitting duck and just waiting to be sued by some fanatic privacy lawyer.

If you do not question your data practices, if you process the data as you please, you could find yourself explaining to a supervisory authority why you should not be fined $12.5 million or 2% of your annual global turnover (whichever is higher).

Google BigQuery can get expensive pretty fast if you are dealing with terabytes or petabytes of data every day and you do not construct your queries properly or pull too much data too frequently.

Cost of using BigQuery for Google Analytics 360 users

Every Google Analytics 360 property comes with a free connection to BigQuery.

So you can connect your Google Analytics account to the BigQuery account for free. 

However, you could still be charged based on your data storage and processing. 

If you are using Google Analytics 360, you get $500 of free credit each month, giving you up to 25 terabytes of data storage and 100 terabytes of data to query each month. 

You will be charged only when you exceed this free usage limit (which is very unlikely).

Most GA360 users generally don’t pay anything extra for using BigQuery.

Cost of using BigQuery for Google Analytics 3 (or earlier version) users

Google Analytics 3 (or earlier versions) does not come with a free connection to BigQuery. 

In order to connect your Google Analytics account with BigQuery, you would need to use a third-party solution which will cost you a little bit of money. 

However, it is unlikely to cost you even 1/100 of $150k / year, which all GA360 users pay to access GA data in BigQuery. 

You could also be charged extra based on your data storage and processing. 

However, your credit card will not be charged if you remain within 10 GB of data storage and one terabyte of queries per month.

Once you exceed this free usage limit, your credit card will be charged.

Long story short, 

BigQuery is not completely free to use with Google Analytics unless you are using Google Analytics 4 (GA4) and you stay within the free usage limit every month. 

Cost of using BigQuery for Google Analytics 4 users

Google Analytics 4 comes with a free connection to the BigQuery.

So you won’t need a third-party solution to connect your Google Analytics account with BigQuery. 

However, you could still be charged based on your data storage and processing. 

As long as you remain within 10 GB of data storage and one terabyte of queries per month limit, your credit card will not be charged.

Once you exceed this free usage limit, your credit card will be charged.

Note: If you start querying gigabytes or terabytes of data daily in BigQuery, you need to be mentally and financially prepared to pay a considerable amount of storage and/or processing fees each month.

For more details on BigQuery pricing, check out the official resource: https://cloud.google.com/bigquery/pricing 

Keep the following points in mind when doing BigQuery cost optimization:

#1 Before you query the data from a table, check the size of the table

If the size of the data table is just a few kilobytes (KB) or megabytes (MB), you don’t need to worry: 

check the size of the table bigquery

But if the table size is in gigabytes (GB), terabytes (TB) or petabytes (PB), you should be careful how you query your data:

table size is in gigabytes or terabytes bigquery

#2 Before you query the data from a table, preview the table 

Many people, especially new users, run queries just to preview the data in a data table.

This could considerably cost you if you accidentally queried gigabytes or terabytes of data. 

Instead of running queries just to preview the data in a data table, click on the ‘Preview’ tab to preview the table.

There is no cost for previewing the data table. 

The table preview will give you an idea of what type of data is available in the table without querying the table:

previewing the data table bigquery

#3 Always keep an eye on how much data your query will process before you run your query

If your query is going to process only kilobytes or megabytes of data, then you don’t need to worry:

this query will process when run bigquery

However, if your query is going to process gigabytes or terabytes of data, it could considerably cost you. 

If that’s the case, query only that data which is absolutely necessary.

#4 Your query cost depends on the number and/or size of the returned columns, not the rows.

Returning 10 rows/records is going to cost you the same as returning 10,000 records of data:

Returning 10 rows records is going to cost you the same as returning 10000 records of data
Returning 10 rows records is going to cost you the same as returning 10000 records of data 2

The number of rows/records your query returns doesn’t affect your query cost.

Your query cost is affected by the number of columns your query returns: 

Following is an example of a query which would return one column named ‘id’:

query cost is also affected by the size of each column

Following is an example of a query which would return two columns named ‘id’ and ‘creation_date’:

example of a query which would return two columns 1

Note how adding a second column increases the query size from 236 to 473 MIB (megabytes).

Now, what would happen if we wrote a query that returns all the table columns?

Avoid using SELECT 2

So if we try to return all the columns of this data table, 29.4 GB of the data would be processed.

So only query the columns you really need. 

Your query cost is also affected by the size of each column.

The query below returns one column named ‘id’: 

query cost is also affected by the size of each column 1

The query below returns one column named ‘body’:

The query below returns one column

Note how the size of the query increased from 236 MB to 26.3 GB.

So you must be very careful about the size of the column you want to retrieve. 

#5 Avoid using SELECT *

Avoid using SELECT 3

SELECT * means returns all the columns of the data table. 

Now, if your data table contains a lot of columns and some of the columns are very big in size (maybe in GB or TB), using SELECT * could considerably increase your query cost.

So the best practice is to avoid using SELECT *

#6 Applying a LIMIT clause to a SELECT * query does not affect the query cost

This is because the LIMIT clause controls the number of rows/records your query returns.

But as you know by now, the number of rows/records your query returns doesn’t affect your query cost.

With the LIMIT clause:

Applying a LIMIT clause to a SELECT query

Without the LIMIT clause:

using a SELECT query without limit clause

#7 Set up Budget alerts

Set up cloud billing budgets and budget alerts which trigger email notifications to billing admins and/or project managers when your costs (actual costs or forecasted costs) exceed a percentage of your budget (based on the threshold rules you set).

These email alerts inform you of your usage costs trending over time.

Note: Setting up a budget does not automatically cap Google Cloud usage or spending.

For more information on setting up cloud billing budgets and budget alerts, check out the official help documentation from Google: https://cloud.google.com/billing/docs/how-to/budgets

#8 Set up Quota limits

You can turn on cost control at a project level or user level by setting up/customizing quota limits. 

That way, you can cap the maximum number of bytes processed per day by a given user or project.

When the user/project exceeds their quota limit, the query will not be processed, and a “quota exceeded” error message will be displayed.

To learn more about working with Quotas, check out the official help documentation from Google: https://cloud.google.com/docs/quota

#9 Regularly monitor your spending

At least once a week, visit the ‘Billing‘ section of your Google Cloud Platform account to see how much you have spent so far:

Regularly monitor your spending bigquery

#10 Use the Google Cloud pricing calculator

The Google Cloud pricing calculator estimates the storage cost and/or the cost of running your desired query before you actually run it.

However, this calculator works only when you are querying the data in terabytes or petabytes.

To learn more about the pricing calculator, check out this article: Using Google Cloud pricing calculator for BigQuery

#11 Use the “BigQuery Mate” chrome extension

BigQuery Mate is a Google Chrome extension through which you can estimate query costs within the query validator. However, this extension works only on the old user interface of BigQuery.

Unlike the Google Cloud pricing calculator, this extension works even when you query less than 1 terabyte of data.

To use this extension, follow the steps below:

Step-1: Download the BigQuery Mate chrome extension.

Step-2: Make sure that you switch to the old user interface of BigQuery by clicking on the ‘HIDE PREVIEW FEATURES‘ button:

HIDE PREVIEW FEATURES

Note: If you are already using the old user interface, refresh your Big Query console by clicking on the browser refresh button.

Step-3: Type your query in the ‘Query Editor. You should now be able to see the query cost estimates on the right-hand side of your query validator:

BigQuery Mate is a Google Chrome extension through which you can estimate query cost

Other articles on Google Analytics BigQuery

  1. Advantages of using Google BigQuery for Google Analytics
  2. Cost of using BigQuery for Google Analytics
  3. What is Google BigQuery Sandbox and how to use it
  4. Understanding the BigQuery User Interface
  5. Sending data from Google Analytics to BigQuery without 360
  6.  How to connect GA4 (Google Analytics 4) with BigQuery
  7. events_& events_intraday_ tables in BigQuery for GA4 (Google Analytics 4)
  8. Using Google Cloud pricing calculator for BigQuery
  9. How to access BigQuery Public Data Sets
  10. How to use Google Analytics sample dataset for BigQuery
  11. Connect and transfer data from Google Sheets to BigQuery
  12. How to query Google Analytics data in BigQuery
  13. How to send data from Google Ads to BigQuery
  14. What is BigQuery Data Transfer Service & how it works.
  15. How to send data from Facebook ads to BigQuery
  16. How to send data from Google Search Console to BigQuery
  17. How to pull custom data from Google Analytics to BigQuery
  18. Best Supermetrics Alternative – Dataddo
  19. Google Analytics BigQuery Tutorial
  20. How to backfill Google Analytics data in BigQuery
  21. How to connect and export data from GA4 to BigQuery

Register for the FREE TRAINING...

"How to use Digital Analytics to generate floods of new Sales and Customers without spending years figuring everything out on your own."



Here’s what we’re going to cover in this training…

#1 Why digital analytics is the key to online business success.

​#2 The number 1 reason why most marketers are not able to scale their advertising and maximize sales.

#3 Why Google and Facebook ads don’t work for most businesses & how to make them work.

#4 ​Why you won’t get any competitive advantage in the marketplace just by knowing Google Analytics.

#5 The number 1 reason why conversion optimization is not working for your business.

#6 How to advertise on any marketing platform for FREE with an unlimited budget.

​#7 How to learn and master digital analytics and conversion optimization in record time.



   

My best selling books on Digital Analytics and Conversion Optimization

Maths and Stats for Web Analytics and Conversion Optimization
This expert guide will teach you how to leverage the knowledge of maths and statistics in order to accurately interpret data and take actions, which can quickly improve the bottom-line of your online business.

Master the Essentials of Email Marketing Analytics
This book focuses solely on the ‘analytics’ that power your email marketing optimization program and will help you dramatically reduce your cost per acquisition and increase marketing ROI by tracking the performance of the various KPIs and metrics used for email marketing.

Attribution Modelling in Google Analytics and BeyondSECOND EDITION OUT NOW!
Attribution modelling is the process of determining the most effective marketing channels for investment. This book has been written to help you implement attribution modelling. It will teach you how to leverage the knowledge of attribution modelling in order to allocate marketing budget and understand buying behaviour.

Attribution Modelling in Google Ads and Facebook
This book has been written to help you implement attribution modelling in Google Ads (Google AdWords) and Facebook. It will teach you, how to leverage the knowledge of attribution modelling in order to understand the customer purchasing journey and determine the most effective marketing channels for investment.

About the Author

Himanshu Sharma

  • Founder, OptimizeSmart.com
  • Over 15 years of experience in digital analytics and marketing
  • Author of four best-selling books on digital analytics and conversion optimization
  • Nominated for Digital Analytics Association Awards for Excellence
  • Runs one of the most popular blogs in the world on digital analytics
  • Consultant to countless small and big businesses over the decade

Learn Google Analytics BigQuery (63 pages ebook)

X
error: Alert: Content is protected !!