How to Query a Specific Date Range of GA4 Events

BigQuery With GA4

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.

Subscribe to keep reading

This content is free, but you must be subscribed to GA4 BigQuery Newsletter For Marketing Analytics to continue reading.

Already a subscriber?Sign In.Not now

Reply

or to participate.