My Step-By-Step Blueprint For Learning Google Analytics BigQuery

Get the only ebook on Google Analytics BigQuery ever published (60+ Pages)

Yes I want the FREE ebook

Last Updated: January 3, 2023

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.

My Step-By-Step Blueprint For Learning Google Analytics BigQuery

Get the only ebook on Google Analytics BigQuery ever published (60+ Pages)

Yes I want the FREE ebook

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).

My Step-By-Step Blueprint For Learning Google Analytics BigQuery

Get the only ebook on Google Analytics BigQuery ever published (60+ Pages)

Yes I want the FREE ebook

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: 

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

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

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

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

Get this 70 page detailed checklist containing screenshots, step-by-step instructions and links to articles

Do you want to set up Google Analytics 4 (GA4) fast and correctly?

Yes I want the ebook

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 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?

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

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 must 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), 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 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:

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

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:

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