Narrowing Down The Result Set in BigQuery

In the previous post, we looked at the SELECT SQL command.

This command is used to select or view data from a given table or set of tables.

In the previous lesson, I showed you how to pull the data from the entire table or limit the result set by using a LIMIT command.

However, most of the time that alone is not going to be enough. 

For example, if you only want to select the data that has “first_visit” GA4 event then the SELECT along with LIMIT is not going to work. You need a way to filter our result set to only look at the rows (data) that contain the “first_visit”.event.

That’s what I am going to cover in this post.

Let’s first rewrite the SQL statement that we wrote in the past lesson:

SELECT * FROM ‘optizent-test-project.analytics_219941990.events_20221204' 

Note: I have removed the LIMIT command for now and also selected all the columns, hence the *. 

Now, continuing with what I mentioned above, let’s say we only want to look at the rows that have the “first_visit” event. In that case, you will use the “WHERE” clause in your statement to filter the result set.

The modified SQL will be

SELECT * FROM ‘optizent-test-project.analytics_219941990.events_20221204’

WHERE event_name = 'first_visit'

WHERE clause goes after the table's name, followed by the name of the column that you want to use for the filter, in our example the column is event_name  and then you write the value that you want to use for the filter.  (Note: if you are not familiar with the names of the column then check out, https://bigquery.beehiiv.com/p/ga-events-table-schema-in-bigquery)

Please note that I have used single quotes around the value i.e. ‘first_visits’, you will always need to use single quotes when using a STRING value in your SQL statement. 

Now go ahead and execute this statement in BigQuery. If you are not familiar with how to executive the SQL then check the previous lesson.  Before executing the statement, make sure to use your own table name instead of the one I am using in this example. 

Here is the screenshot of the BigQuery console.

Advanced Tips

Subscribe to Premium to read the rest.