Selecting Data from Multiple Tables

As you already know, GA4 creates multiple tables in BigQuery. We have been focused on the Events table so far.

However, GA4 also creates two more tables along with the events table.

Additionally, as you dive into BigQuery and explore its complete capabilities, you might bring in data from other data sources such as CRM, Email automation tools, etc.

As a result, you will often need to combine the data from various tables to get the right data set.

In this post, I will show you how to achieve that.

Note: If you are already familiar with table joins, you can skip this lesson.

Since we haven’t yet looked at other tables or brought in other data sources, most of this post is going to be theoretical but will revisit it as needed in the future using GA4

Let’s assume you have two tables in BigQuery with the following structure

Now if we want the result set that includes that data from the “Customer” table combined with the data from the “State” table, so that we can know the actual name of the state along with customer information then we will need to “join” the two tables.

To join the two tables, you have to first identify a column in both tables that you will use to join them. In our case, that seems to be the “StateCode” column in both tables.

Now I am going to explain the SQL and then show you the result that you will get.

Here is the SQL: