Using Date Parameters in a GA4 BigQuery query

This article is for Analytics Canvas users who are accessing GA4 data through the BigQuery export.

What are Data Parameters?

A parameter is a variable or a value in a query that can be assigned. A Date Parameter is a date value that can be assigned before running a query. For example, when using a visual date control in a tool like Analytics Canvas, Data Studio, PowerBI, or Tableau, the date parameters control the start and end dates of the query.

Why do you need date parameters with the GA4 BigQuery export tables?

By including date parameters in your SQL query, it is possible to do an incremental load on BigQuery sharded tables, such as the GA4 BigQuery export event_ tables.

In other words, instead of scanning across the raw GA4 BigQuery export tables, you create a report table with the dimensions and metrics you want, then refresh it by scanning over only the new raw event tables that were generated since the last refresh.

Since report tables are much smaller than the raw export, creating report tables saves significant BigQuery charges by reducing the number of bytes processed whenever the SQL is run against it. This could be in Canvas, Data Studio, Tableau, PowerBI, Looker, etc.

Canvas will ingest data from GA4 sharded tables into date-partitioned "smart tables" within your Analytics Canvas account. This has the added benefit of further reducing costs when filtering on these "smart report tables" when you are in Canvas or when using the Analytics Canvas Data Studio Partner Data Connector.

How to use Data Parameters to Create GA4 Report Tables

Examples below are for Analytics Canvas Online users. Desktop / On-prem users should replace @ac_start_date and @ac_end_date with:

'{DateToText(DateOnly($AC_StartDate), "yyyyMMdd")}' and '{DateToText(DateOnly($AC_EndDate), "yyyyMMdd")}'

To add date parameters, you need to include the start and end date in the query, by including the following:

_table_suffix between @ac_start_date and @ac_end_date

In addition, you need to specify the event table with the wild card character- events_* rather than including a specific date.

FROM`YourProject.analytics_YourProfileID.events_*`

For example, the following is a simple query that returns the sessions by country and uses the start and end date parameters. When you tell Canvas what historical period and how much of the recent data to refresh, it will edit the SQL appropriately.

SELECT PARSE_DATE('%Y%m%d',`event_date`) as `date`, geo.country, count(1) as sessions
FROM `YourProject.analytics_YourProfileID.events_*`
WHERE `event_name`='session_start' and _table_suffix between @ac_start_date and @ac_end_date
GROUP BY 1,2

How to use Date Parameters in a GA4 BigQuery SQL Query

Of course these parameters only work in Analytics Canvas. To run this query in the BigQuery SQL editor just put in actual dates:

How to use Date Parameters in an Analytics Canvas GA4 BigQuery SQL Query

Within Analytics Canvas, use the BigQuery SQL Import block and add your SQL accordingly. You can then control the date range using the visual date selectors for each import.

Start by adding a BigQuery SQL block to the Canvas.

Adding an SQL Import block to the Canvas

If you haven't already connected your BigQuery account, follow the instructions here.

Next, add your SQL using the date parameters described above.

how to enter the date parameters in a BigQuery SQL query

Since the query includes date parameters, the option to perform an incremental load is available:

how to configure the table for an incremental load

Pressing Submit will store the settings and run the query. Each time the table is refreshed, either through an automation run or through refreshing the Canvas manually, an incremental load will be performed that overlaps the refresh period defined in the last step.

If you need further assistance with querying your GA4 data in BigQuery, contact us at support@analyticscanvas.com