How to get the GA4 property id from the BigQuery export metadata and join it with your event data

If you have multiple GA4 properties linked to BigQuery, you'll often find yourself in situations where you need to identify the GA4 property id associated with specific BigQuery export tables.

The raw event tables do contain a stream_id, but the GA4 property id is not included. Luckily, there is a way to retrieve this identifier in an automatic way and use it further downstream in your data processes.

In this tutorial we'll show you:

• how to get the GA4 property id from BigQuery's INFORMATION_SCHEMA
• how to join this property id with the stream id and other GA4 event data

What is INFORMATION_SCHEMA?

The INFORMATION_SCHEMA in Google BigQuery is a special set of tables that provide metadata about your dataset. Think of it as a data dictionary that describes the structure, types, and properties of your tables, columns, and other elements within a BigQuery dataset.

You can query INFORMATION_SCHEMA just like any other BigQuery table to gain insights into your data schema, which is extremely helpful for tasks like data validation, exploration, and of course, for our specific use case—identifying the GA4 property id.

GA4 export naming convention

As you probably know the GA4 property id is always part of the automatically generated data set when you activate the GA4 BigQuery export. Here's an example of the way the metadata is structured:

• Project: ga4bigquery
• Data set: analytics_250794857
• Event table: events_20230907

In this case 250794857 is the property id of our GA4 export. This is the value we want to retrieve using SQL.