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

• All OneSignal customers can try out this integration regardless of the plan they are on
• Only customers on an Annual OneSignal plan can purchase the add-on for this integration

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. In OneSignal, navigate to Data > Integrations > BigQuery.
2. 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.

3. OneSignal Settings
   1. Set up Sync Frequency, the most frequent setting is 15 minutes
   2. Dataset and Destination Table names will be pre set
      1. Dataset is named as onesignal_events_your-appid
      2. Destination Table is names as message_events
      3. you can update these names if you need to
   3. Select the events you care to receive in your BigQuery dataset. Feel free to select them all.

4. 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

Type: String

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

Message Events Properties (columns)

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.

Further Reading

• Google Cloud's documentation