Configure your BigQuery warehouse

  • Updated

Connect your Google BigQuery warehouse to Optimizely Analytics to unify customer and product data, create advanced segments, and measure key outcomes like revenue and retention. This direct connection keeps your analytics accurate, eliminates manual exports, and ensures experimentation insights align with a single source of truth.

Perform the following steps to configure your BigQuery warehouse for Optimizely Analytics:

Create a custom role 

To let Analytics connect to and query your database, you must create a custom role with specific permissions in BigQuery and assign it to the dedicated Analytics service account. 

You must have administrator privileges in your BigQuery project to create a custom role.

Configure your service account and create JSON keys

Creating a service account is a key component in configuring your BigQuery warehouse for Optimizely Analytics. This ensures secure, efficient, and reliable data operations. If you already have a service account, ensure the following permissions are granted to your account: 

  • BigQuery Data Viewer permissions for the datasets you want to analyze with Analytics.
  • Permissions to run jobs in the project through the BigQuery Job User role. 

After you set up your service account, you must create a JSON key and download it.

Share your dataset

Sharing your dataset in BigQuery when configuring your warehouse for Optimizely Analytics ensures seamless data integration, collaboration, and centralized insights, enhancing analysis and efficiency. This practice supports better data-driven decision-making while maintaining security and control over data access. 

IP allowlisting (conditional)

Ensure the warehouse accepts incoming Optimizely Analytics requests over the public internet. This action is conditional and only required if the warehouse cluster is guarded by a security group (such as a firewall) that prevents access to the cluster from Optimizely Analytics. 

Following is the IP allowlist: 

  • 35.196.71.222
  • 34.73.142.185
  • 34.148.77.115
  • 34.73.63.141
  • 34.74.199.69
  • 34.74.109.219
  • 34.139.128.201
  • 35.243.168.58

JSON indexing for BigQuery structs

When you work with BigQuery structs, you need a specialized configuration. Unlike other JSON-like types with direct JSON exploration, BigQuery structs require a derived column that annotates the struct with additional information.

The following is an example of a derived column:

bq_property_list(
Events.event_params,
'key',
'value',
[
['int_val', 'DATA_TYPE_INT64'],
['float_val', 'DATA_TYPE_DOUBLE'],
['double_val', 'DATA_TYPE_DOUBLE'],
['string_val', 'DATA_TYPE_STRING']
])

You can use this derived column like any other JSON-like column. The following are arguments to this new bq_property_list function, in order:

  • The struct column.
  • String literal, name of the key field in the struct entries. This is often just 'key'.
  • String literal, name of the value field in the struct entries. This is often just 'value'.
  • List of fields that display within each struct item's value, and the data type of each. The three listed data types are the only ones that should be used.

Performance guidance

The following techniques are various approaches you can take to ensure that Optimizely Analytics runs at an optimal cost and performance profile:

  1. Ensure your events/conversions table is clustered by event date (not time), and event type (the same column selected in the Semantics tab of the dataset in Optimizely Analytics), in that order.
  2. (Experimentation Analytics only) Check if the warehouse is clustered by the experiment ID column and decision date (not time) if it has a separate decisions table.
  3. Ensure that you have created a new schema in your warehouse, give Optimizely Analytics read and write access to it, and then enter the name of that schema in the Optimizely Analytics app settings, in the Materialization section. This enables the materialization of repeated queries, which is a large cost/performance boost.
  4. Ensure your warehouse instance size is appropriate.
  5. Consider creating a new warehouse for Optimizely Analytics, if this same warehouse instance is also used by other workloads, so that Optimizely Analytics queries are isolated from other workloads.

Create a writable schema in BigQuery

After you configure the warehouse and grant your account permissions to access the dataset as the BigQuery Data Owner, create a BigQuery dataset with read/write permissions for Optimizely Analytics. This space optimizes internal operations, enhancing performance, resource utilization, and user experience, making it essential for advanced analytics in BigQuery. 

Setting up the writable schema is required to use Analytics's bq_property_list commands.

Following are the required permissions:

roles/bigquery.resourceViewer
`bigquery.jobs.get`
`bigquery.jobs.listAll`

Add details of the schema in the Optimizely Analytics application:

Go to Settings > Materialization > General Settings and specify the following details:

  • Database – The database name in the data warehouse where the materialized tables are created. This corresponds to a project in BigQuery.
  • Schema – The schema's name within the database where the materialized tables are created. This value is any name of your choice. The name of the object you reference in BigQuery is a dataset.
  • Refresh Cron Schedule – The refresh periodicity of the materialized tables using the cron syntax.

When this is done, Optimizely Analytics creates materialized tables within the data warehouse that contain intermediate results to improve performance.