How to Query a Specific Date Range of GA4 Events

So far we have looked at querying a particular table in a dataset. Which means we have only querying a particular date of data. However, for most of your practical uses, you will need to query the data for a date range instead of just a day. That’s what we will look into in this post.

But before we dive into it, let’s take a look again at one of the queries we have written in the past.

SELECT count(*)
FROM `optizent-test-project.analytics_289314775.events_20231006`

The above query will return the count of records from the daily events table of 20231006 i.e. October 6th, 2023.

Now, let’s say that instead of just looking at the data for October 6th, we want to look at the data from September 7th to October 6th. In that case, you will need to learn and use two concepts:

1. A wild card to query a range of tables

2. BETWEEN operator to specify the dates of the table

So let’s see how to use these concepts. First I am going to write a query and then explain it.

SELECT count(*) 
FROM `optizent-test-project.analytics_289314775.events_*`

Explanation

As you might have already noticed, in the above query, I used a * instead of the date of the table.

* is a wild card.

In this case, it instructs BigQuery to select the data from all the tables that match anything after events_. Since the Events table creates a new table for each day and appends the data of that day after events_, the above SQL will select the count of all the records that you have in the Events table.

However, we want to only do a count of the records for a specific date range. In that case, we will use an additional condition to narrow down the result set to the desired date range.

That condition is the use of BETWEEN Operator and a pseudo column called _TABLE_SUFFIX.

Here is what your query will look like

SELECT count(*) 
FROM `optizent-test-project.analytics_289314775.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20230907' AND '20231006'

Explanation

Building on the previous query, we have added a WHERE clause to narrow down the result to a certain date range using the _TABLE_SUFFIX column. _TABLE_SUFFIX is system system-defined column that stores the values matched by the table wildcard.

Using the BETWEEN Operator, we specified two values separated by AND, the SQL will now only look at the event tables that end in values that fall between the two values that are specified, including both values.

Advanced Notes

Subscribe to Premium Membership to read the rest.