BigQuery - data export

​

Overview

We understand that gaining deeper insights into engagement is essential for our customers to drive successful messaging strategies. By integrating with OneSignal’s BigQuery Data Export functionality, you can automatically sync your messaging event data directly to your BigQuery project—eliminating the need for manual exports and giving you immediate access to a centralized data source for advanced analysis

This integration empowers you to perform in-depth analyses of your engagement data across key channels, including email, push notifications, in-app messages, and SMS. You can leverage this data to build custom dashboards, uncover trends, and generate actionable reports for leadership and cross-functional teams.

Currently, the integration supports data exports for events tied to sent messages and related engagement actions (such as clicks, opens, and more). We are continuously expanding our export capabilities and plan to offer additional event types in the near future to provide an even more comprehensive view of your users’ journeys.

​

Requirements

​

OneSignal

• Professional Plan

​

Google Cloud Platform

• Google Cloud Platform account
• App connected to an Organization with active billing account

​

BigQuery

• Active BigQuery account
• Credentials for your BigQuery account

​

Setup

​

Create a service account

​

Create a service account key

Create a key file for your service account. The service account key must be in JSON format. Make a note of all the contents of the JSON file. You will need it to configure the integration.

{
  "type": "service_account",
  "project_id": "random-project-12345",
  "private_key_id": "abcdefg",
  "private_key": "********",
  "client_email": "[email protected]",
  "client_id": "12345678",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/name%40project.iam.gserviceaccount.com"
}

​

Connect OneSignal

​

Generate message events

Send messages via push, email, in-app, or SMS to trigger events that will sync based on your Integration Settings. You can update which events to sync within your OneSignal dashboard Data > Integrations > BigQuery.

​

View data in BigQuery

Whenever an event is generated, OneSignal automatically sends a copy of the event details to BigQuery.

Once that time has elapsed, navigate to your BigQuery instance and search for the newly created dataset.

​

Events and properties

​

Message event kinds

Property: event_kind Type: String

The kind of message and event (e.g. message.push.received, message.push.sent).

​

Event data schema

For each message event generated by a user, the following metadata will be attached to the record.

​

Notes

• Syncs after saving/activating may take an additional 15-30 minutes to complete.
• Deactivating may still result in one final sync after deactivation.
• To ensure efficient data synchronization in your BigQuery destination, our system automatically creates and manages staging datasets. These datasets, named with a pattern like fivetran_{two random words}_staging, temporarily store data during processing before it’s integrated into your main schema. These staging datasets are essential for maintaining a streamlined workflow and should not be deleted, as they will be automatically recreated.

​

FAQ

​

Why do I see different message IDs with the same content?

This happens when the same message is sent more than once, likely via a transactional flow or message template reused across multiple sends.

​

Further reading

• Google Cloud’s documentation