Counting GA4 Events and Other Records in BigQuery

The most common SQL that you will write in BigQuery will be related to counting events or other records in the BigQuery tables.

For example, you might want to know

1. The total number of events on a given day

2. The total number of specific events e.g. ‘first_visit’ on a given day.

These are the two examples that we will look at in this post.  These types of queries will form the basis of what you will see in future posts. Let’s take a look at these one by one.

The total number of events in a given day

The function that will need to perform the total is called “COUNT”.  

Here is the query that you will use to count the number of events on a given day

SELECT COUNT(*) FROM optizent-test-project.analytics_219941990.events_20221204

Use the count function within SELECT and FROM. 

COUNT(*) is the call that you need to count all the records

The table name is the table for the particular day that you want to count the records for.

Here is the output from the console:

Pretty simple, right?

Now let’s look at the next example.

The total number of specific events e.g. ‘first_visit’ on a given day.

I am sure, you now know how to do it.

SELECT COUNT(*) FROM optizent-test-project.analytics_219941990.events_20221204
WHERE event_name = 'first_visit'

The SQL code is similar to what we did previously, the only thing we added is the WHERE clause to limit the record set to only the ‘first_visit’ event. 

You can do the same for any event that you want to do the count for.

Now let’s take a look at another example.

The total count of each type of event on a given day

What we are looking for is a result set that looks like the following:

For this, we will be using one SQL statement instead of writing the above statement for each event.  

Subscribe to Premium Membership to read the rest.