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.

Your monthly cost of using BigQuery depend upon the following three factors:

1. The cost of connecting your Google Analytics account to BigQuery
2. The amount of data you stored in BigQuery (i.e the storage cost)
3. The amount of data you processed by each query you run (i.e. the query cost)

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. 

 
Get the E-Book (50 Pages)

 
Get the FREE E-Book (50+ Pages)

Google Analytics 360 users

Every Google Analytics 360 property comes with a free connection to the 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 which gives 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 the BigQuery.

Google Analytics 3 (or earlier version) users

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

In order to connect your Google Analytics account with BigQuery, you would need to use a third-party solution which is going to 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, as long as you remain within 10 GB of data storage and one terabyte of queries per month, your credit card will not be charged. Once you exceed this free usage limit, only then 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 and you stay within the free usage limit every month. 

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, only then your credit card will be charged.

Note: If you start querying gigabytes or terabytes of data every day in BigQuery then 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) then you don’t need to worry: 

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

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

Many people esp. the 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:

#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:

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

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

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

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

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’:

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

Note, how just by adding a second column, the query size is increased from 236 to 473 MIB (megabytes).

Now, what would happen if we write a query which returns all the columns of the table?

So if we try to return all the columns of this data table then 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’: 

The query below returns one column named ‘body’:

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

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

#5 Avoid using SELECT *

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) then 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:

Without the 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 manager 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 how your usage costs are 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 can put a cap on 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 would 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:

#10 Use the Google Cloud pricing calculator

The Google Cloud pricing calculator is used to estimate 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 cost 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 are querying 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:

Note: If you are already using the old user interface then 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:

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