Skip to main content

Google BigQuery

​
Google BigQuery

DirectionBidirectional
Environment
WebiOSAndroidCTVAPI Direct
Capability
ConnectivityRouting
SDK RequiredNo
Product(s) Required
Core PlatformRouting

Google BigQuery enables publishers to analyze large datasets for audience insights, campaign performance, and ad optimization.

Setup

Troubleshooting

​
Overview

The BigQuery integration enables publishers to leverage Permutive’s bi-directional data capabilities with their BigQuery data warehouse. This integration operates in two modes: Routing (Destination): Stream first-party event data from Permutive to BigQuery. Read more in Routing documentation.
Routing capability requires the Routing package in addition to Core Platform. Contact your Customer Success Manager to enable Routing.
Connectivity (Source): Import audience data from your BigQuery warehouse into Permutive for cohort building and activation across your publisher inventory. Key Routing capabilities include:
  • Automatic schema generation and updates for new event types and properties
  • Day-partitioned tables for efficient querying
  • Support for all Permutive event data including user events, identities, and segment metadata
  • Self-service setup through the Permutive Dashboard

​
Environment Compatibility

EnvironmentSupportedNotes
WebYes—
iOSYes—
AndroidYes—
CTVYes—
API DirectYes—

​
Prerequisites

For Routing (exporting data to BigQuery):
  • Google Cloud Platform project: An active GCP project where BigQuery will store your data
  • BigQuery API enabled: The BigQuery API must be enabled on your GCP project (enabled by default for new projects)
  • IAM permissions: Ability to grant IAM roles to service accounts at the project level in Google Cloud Console
  • Project-level access: Permissions to add service accounts with the BigQuery User role to your GCP project
Do NOT manually create the BigQuery dataset before configuring the integration. Permutive will automatically create the dataset during setup. If you manually create the dataset, you must grant the Permutive service account the BigQuery Data Owner role instead of BigQuery User.

​
Setup

​
Overview

BigQuery Routing enables self-service setup through the Permutive Dashboard. The setup involves configuring a BigQuery destination in the Dashboard and granting a Permutive service account the BigQuery User role at the project level in your GCP IAM settings. Permutive automatically creates the dataset and tables — no manual dataset creation is required.

​
Prerequisites

  • An active Google Cloud Platform project with the BigQuery API enabled
  • Your GCP Project ID, a unique dataset name, and your preferred data location (US or EU)
  • Permissions to grant IAM roles to service accounts at the project level in Google Cloud Console
Do NOT manually create the BigQuery dataset before configuring the integration. If you do, you must grant the Permutive service account the BigQuery Data Owner role instead of BigQuery User.
For complete setup steps, see Setting up BigQuery Routing.

​
What Happens After Setup

Once routing is active:
  1. Tables are created automatically for each event type (e.g., Pageview_events, VideoView_events)
  2. Events stream in near real-time with approximately 5-minute latency
  3. Schemas update automatically when you add new event types or properties
  4. Daily partitions organize data by event date for efficient querying

​
Data Types

The BigQuery Routing integration creates the following tables in your dataset to store different types of data:
Permutive creates separate tables for each event type. Tables are named {event_name}_events and contain daily partitions based on event date.Example tables:
  • Pageview_events
  • VideoView_events
  • AffiliateLinkClick_events
Schema:
​
time
TIMESTAMP
Event timestamp in UTC
​
event_id
STRING
Unique identifier for the event
​
user_id
STRING
Permutive user identifier
​
session_id
STRING
Session identifier
​
view_id
STRING
Page view identifier
​
segments
ARRAY<INTEGER>
Array of segment IDs the user belongs to at the time of the event
​
properties
RECORD
Event-specific properties stored as a JSON record. Schema varies by event type and is automatically updated when new properties are added.
​
workspace_id
STRING
Workspace identifier
Stores identity resolution data. The table is named identities and contains daily partitions.Schema:
​
time
TIMESTAMP
required
Timestamp when the identity was captured
​
event_type
STRING
Type of identity event
​
permutive_id
STRING
required
Permutive user identifier
​
id
STRING
required
External identity value
​
tag
STRING
required
Identity tag or namespace
​
workspace_id
STRING
Workspace identifier
Stores segment definitions and metadata. Permutive creates two objects:
  • Table: segment_metadata_snapshots - Raw snapshots of segment metadata
  • View: segment_metadata - Deduplicated view of the latest segment metadata
Schema:
​
number
INT64
required
Segment ID number
​
name
STRING
required
Segment name
​
tags
ARRAY<STRING>
Array of tags associated with the segment
​
metadata
STRING
JSON string containing segment configuration and metadata
​
workspace_id
STRING
Workspace identifier
Use the segment_metadata view for queries to automatically get deduplicated, up-to-date segment information.
Key Characteristics:
  • Automatic Schema Management: New event types and properties are automatically added to tables without manual intervention
  • Partitioning: Event and identity tables are partitioned by day for efficient querying and cost optimization

​
Troubleshooting

Cause: The Permutive service account was not granted the correct permissions, or permissions were granted after clicking “Confirm account access granted”.Solution:
  1. Verify the service account has been granted the BigQuery User role at the PROJECT level in the Google Cloud IAM console
  2. Ensure you selected the correct GCP project
  3. If permissions were granted incorrectly, you will need to restart the integration configuration from the beginning
  4. Contact Support if the issue persists after verifying permissions
Cause: A dataset with the same name already exists in your BigQuery project.Solution:
  • Option 1 (Recommended): Use a different dataset name that doesn’t already exist in your project
  • Option 2: If you must use an existing dataset, grant the Permutive service account the BigQuery Data Owner role at the dataset level (not just BigQuery User at project level)
Cause: Your organization’s IAM policies may restrict adding external service accounts, or permissions were not granted at the correct level.Solution:
  1. Check with your GCP administrator about organization policies that may block external service accounts
  2. Ensure the BigQuery User role was granted at the PROJECT level, not the dataset level
  3. Verify your user account has permissions to grant IAM roles in the GCP project
  4. If organization policies block external service accounts, work with your security team to add an exception for @permutive-routing-production.iam.gserviceaccount.com domains

​
Changelog

No changes listed yet. For detailed changelog information, visit our Changelog.