📘

This Integration is in Private Beta

If you are interested in participating in this beta, please contact your OneSignal Account Representative.

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

Google Cloud Platform

• Find a project or create a new one
• Organization with Billing enabled
• Ensure there’s an active billing account. If not, create one. It will require valid credit card information.
• Make sure that the billing account is added to the project

BigQuery Account

• You'll need an active BigQuery account
• Credentials for your BigQuery account

OneSignal Account & Annual Plan

• This integration is only accessible to OneSignal customers on an annual plan.

Setup

Step 1 - Create a Service Account

1. Ensure the proper project is selected.
2. Visit the Create Service Account page or from the Service Accounts page, click "+ Create Service Account." - Quick Link

3. Fill out the fields. Give it any name and service account ID you choose.

4. Give the service account the BigQuery User role.

Step 2 - 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.

1. Navigate to Service Accounts in the GPC Dashboard - Quick Link
2. Select your BigQuery Service Account

3. Under Keys, select Add Key → Create New Key → JSON

4. The key will download, open the file

Example of the Service Account Key:

{
  "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"
}

Step 3 - Setup BigQuery Integration

1. Copy the configuration details from the Service Account Key JSON file you downloaded in the previous step.
   ℹ️ Note: The Service Account Key text input field requires the entire JSON content.

2. Select the events you care to receive in your BigQuery dataset. Feel free to select them all.

3. Click “Save” and wait for the success confirmation

Step 4 - Send a Message

The hard part is over.

Now, we move on to the fun part: generating Message Events. You can send messages to any number of subscriptions using the channel of your choice.

Step 5 - Find Message Event Data In BigQuery

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

ℹ️ Note: After saving/activating the BigQuery integration, you might see a delay of 15-30 minutes for the initial setup.

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

Events and Properties

Below is an overview of the data available for the BigQuery Data Export Integration. We will offer Message Event data in the beta, and will plan to offer more categories of event data and summary reports in the future.

For this integration, any event data generated by a user for the given OneSignal application will be exported to a customer's BigQuery project. Customers will not be able to select specific event kinds or filter the events they receive.

Message Event Kinds

See Event Streams for more details.

The events that be:

• sent - The push, email, or sms was sent to the Subscription
• received - The push or email was received by the Subscription. See Confirmed Delivery.
• clicked - The in-app, push, or email link was clicked by the user.
• failed - The push, email, or sms failed to send. See message results for details.
• unsubscribed - The push, email, or sms Subscription became unsubscribed.
• opened - The email was opened. See Email Message Reports for details.
• spam - The user reported email as spam. See Email deliverability for details.
• bounce - The email address bounced. See Email Message Reports for details.
• suppressed - The email was not sent because the email address is on the Suppression list .
• page_displayed - A card on the in-app carousel displayed to the user.
• impression - The in-app displayed to the user.

Message Events Properties

See Event Streams for more details.

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.

Further Reading

• Google Cloud's documentation