Elevate your data analytics game with BigQuery

What is BigQuery?

What is BigQuery? BigQuery is a powerful data warehousing tool within the Google Cloud Platform designed to store, manage and analyse large datasets using SQL.

BigQuery is Google’s data warehouse. You feed it tables of data, any data, all the data you have. These can be uploaded manually, accessed through built in connectors or served via APIs and scheduled services.

It uses a language called Structured Query Language (SQL) to work with the data, to merge tables, to select only bits of tables, to transform data from one table to another. As long as there is a common unique identifier to the tables, SQL can do the job.

As a Google product it works really well for connecting to Google Analytics 4 (GA4), Looker Studio and Google Workspace. It has experienced a boom in awareness and popularity recently as the GA4 raw data exports to BigQuery are now available for free.

Previously with Universal Analytics you needed a paid account (GA360) to do so. Now everyone can store and use their data in a data warehouse. But BigQuery can do so much more than store your GA4 data.

Who should use BigQuery for data analytics?

BigQuery is an enterprise-level data analytics platform for businesses and teams who have outgrown the analytics constraints of Excel, Sheets, or GA4’s user interface. See why you should start importing GA4 to Bigquery.

If you use complex formulas in Excel to speed up repetitive reporting tasks, if you’ve ever spent 2 hours trying to get something to work because you know it will save you time in the long run. If you’re the person that people come to with questions about data. If you like to plan ahead, and know in advance which fields and which datasets you’re going to need for reporting then BigQuery might be for you.

Even if you don’t directly work with marketing analytics data, BigQuery could help you automate any task where you have to transform data to report on it.

Is BigQuery Expensive?

Like any enterprise-level software, there are costs involved. Google will charge you on the amount of data you store and the size of the queries you process. The interesting thing though is that it doesn’t have to be expensive, Google has a threshold of storage and processing, if your work falls under that, you won’t be charged.

This is where the skill and the art of a SQL analyst that knows BigQuery really comes to the fore. Knowing exactly what data you need in the output, and writing a SQL query that picks up and combines the tables to extract only that data, will reduce processing time and reduce storage for the output. Our experts share their tips for keeping the costs down.

How to master working in BigQuery

SQL queries, if grammatically correct, will run, but may output more than you expect. There is no sense check other than your own eyeballs for what a query will output. The only error that will flag is if something is wrong in the syntax of the query itself, not with what the query might create.

One way to work in BigQuery, especially if you are just getting started, is to work with datasets where you already have a manually derived end point. This way, as you write your SQL, you have something to check against to make sure you are generating exactly what you expect to see.

Think about starting with the endpoint, try to visualise what the result should look like. Choose only the data that you need to get there and try to be as accurate and concise in the arguments to get to that point.

Always review the output, sense checking results can be daunting – especially if the data sets are big – but it is worth it. Knowing what data is in the result, and how you came to that point gives confidence in the data, and confidence in any reporting or analysis built from it.

You can follow this guide from Matthew Hooson on how to start your own BigQuery project:

Differences between BigQuery SQL and other SQL dialects

Much like in GA4 or Looker Studio, where the syntax of regular expression (regex) is different to other instances of regex. BigQuery has its own dialect of the SQL language – GoogleSQL.

Like the regex example, the similarities outweigh the differences, knowing one form of SQL will definitely be an advantage, even if it isn’t the exact form that BigQuery uses.

Using BigQuery without mastering SQL

AI tools and internet resources are a good start. Sites like GA4 SQL do a really good job of helping you build the basic SQL queries you’ll need to get over the most common pitfalls in querying GA4 data in BigQuery.

Generative AI tools can take natural language input and deliver SQL queries (NL2SQL) that will run. ChatGPT will not create perfect queries, and there are idiosyncrasies in data sets that blog resources and code generation tools just cannot account for.

A good starting place for AI SQL sidekicks is this guide from Google on prompting best practices NL2SQL in BigQuery (via BigQuery data canvas) to get the best results. Within the Google Cloud Platform there is a service called Vertex AI, based on Google’s Gemini model.

Otherwise in the OpenAI ChatGPT space, there are various GPTs such as BigQuery SQL Copilot from Widenex that promises “precise and highly efficient GA4 BigQuery SQL queries with this assistant”.

Or for a more bespoke approach, contact the team here at Measurelab to see if our in-house bespoke generative AI assistant Brian is a good fit for you.

But in both cases it is really important that you as the user sense check the SQL, understand what the query is doing, where it is getting the data from and what it is doing to the data. AI especially is prone to hallucinations, and you will need to check if you are providing the tools with sensitive or confidential data. See how to debug your queries.

Visualising BigQuery data

Since BigQuery is part of the Google stack, generating data visualisations in Looker Studio is really easy, equally you can ask it to push the results into a Google Sheet through Connected Sheets. BigQuery also supports APIs for most of the major players in visualisation. And if there isn’t a native connector then the tables can be exported to a cloud storage “bucket” and accessed from there.

Once you’re happy with the results of your SQL, and your visualisations of the data, you can then schedule the query so that your reports stay up to date. Scheduling is one of the powerful tools you can use to automate your data transformation and exporting work.

Be wary of giving report users too many options or parameters for updating reports, as each parameter change or update will take from your processing and storage budget, especially if you have multiple report users.

Securing managerial buy-in

The cost is minimal. You are getting enterprise-level tools for non-enterprise prices, and for small or medium sized businesses, chances are you wont be exceeding the free budget threshold that Google has set.

Demonstrate the workload opportunity savings. If you are someone who doesn’t know SQL, but does use Excel, then the leap is not massive. SQL as a language says exactly what it is going to do, it will take time to learn, and focus to use well but the logic and the language is familiar.

A good starting point is from Google’s own training platform, where you can work through modules using pre-existing BigQuery projects. The argument for investing in the upfront learning time delivering time savings down the line is compelling.

Show how BigQuery is an efficiency multiplier. Once you master the learning curve, BigQuery and SQL can take repetitive tasks and automate them, freeing yourself and team members to focus on producing analysis and insights that directly impact business decisions.

Show how BigQuery can answer questions that no other platform can. SQL has the flexibility to let you create top-level highlights, interrogate granular user behaviour, and stitch together data from different sources to unify your business data for a level of analysis that cannot be performed in Excel alone.

Conclusion

BigQuery is a versatile tool that can transform your data analytics capabilities. Whether you’re an analyst or a marketer, the efficiencies gained from using BigQuery are substantial.

If you still have some question about BigQuery, or if you have a problem that you think BigQuery can help with but don’t know where to start, then see if us here at Measurelab can help. Feel free to get in touch for a no strings attached call with one of our consultants who can help!