Skip to main content

Preparing Google Analytics 4 data for a BigQuery – PowerMetrics integration

Updated

Before creating data warehouse metrics in PowerMetrics for GA4 data that’s stored in BigQuery, you need to:

  1. Configure a GA4 BigQuery Export Integration to export your GA4 data to BigQuery.
  2. Create a view in BigQuery to use for metric creation in PowerMetrics.
  3. Enable the required set of permissions for your Google Cloud service account.

Exporting Google Analytics 4 data to BigQuery

To export GA4 data to BigQuery, you need to configure a GA4 BigQuery Export Integration. The process is described here and includes:

  • Creating a new Google Cloud Console project
  • Preparing your project for BigQuery Export
  • Linking BigQuery to Google Analytics 4 properties

Creating a view in BigQuery for metric creation

PowerMetrics requires each metric to be defined against a single table or view (the example here uses a view). Also, each table or view must include at least one column that has the data type timestamp, date, or datetime.

The GA4 data structure, when exported into BigQuery, doesn’t meet these requirements. Instead, the data is spread across multiple tables/views within a dataset (one table/view for each day of data). In addition, instead of a single date/time column, the exported data in BigQuery usually includes two date columns, one called “event_date”, which is formatted as “String”, and one called “event_timestamp”, which is formatted as “integer”. Either column can be used as a date dimension when creating a metric. In the following example, we use the “event_date” column to prepare the view.

To prepare GA4 data for data warehouse metrics, you need to create a view in BigQuery that combines multiple tables and adds a new column that maps the string date to a BigQuery date (see the following example):

CREATE VIEW `<ProjectId>.<DatasetId>.<View>` AS

SELECT

  *,

  PARSE_DATE('%Y%m%d', event_date) AS parsed_event_date

FROM

  `<SourceProjectId>.<SourceDatasetId>.events_*`;

Where ProjectId.DatasetId.View is where you want to put the view, SourceProjectId.DatasetId.events_* points to where the Google Analytics 4 data was exported, and parsed_event_date is the name of the date column for the metric (in this example, it’s called “parsed_event_date” but you can use whatever name you prefer, for example, ).

Before connecting BigQuery to PowerMetrics

Before connecting to PowerMetrics to create BigQuery data warehouse metrics, make sure your Google Cloud service account has the right set of permissions. We recommend enabling these predefined IAM roles: BigQuery Data Viewer, BigQuery Job User, and BigQuery Read Session User. Learn more about Identity and Access Management (IAM) roles and permissions for BigQuery.

You’re ready to connect to PowerMetrics and query the data! Go here to learn how.

Related articles