Google BigQuery

OneSignal and BigQuery integration overview

Overview

The OneSignal + BigQuery integration supports two powerful data pipelines:

Export: Automatically send messaging event data (push, email, SMS, in-app) from OneSignal to BigQuery for analysis and reporting.
Import: Sync custom user events from your BigQuery datasets to OneSignal to trigger automated Journeys and personalized messaging.

Together, these integrations give you complete control over user engagement data—powering advanced analytics and real-time behavior-driven messaging.

Export OneSignal events to BigQuery

Send messaging performance and engagement events (e.g., sends, opens, clicks) to BigQuery to:

Build custom dashboards and reports
Track delivery and engagement trends across channels
Combine OneSignal data with other business data for analysis

Requirements

Access to Custom Events (currently in beta)
Updated Account Plan (not available on free apps).

Google Cloud Platform project with billing enabled
BigQuery enabled in your GCP project
Service account with BigQuery write permissions

Setup Steps

1. Create a Service Account

After you login, ensure the proper project is selected.

Create a service account

Visit the Create Service Account page and click Create Service Account.

Fill out the fields.

Give it any name and service account ID you choose.

Assign the 'BigQuery User' role

Give the service account the “BigQuery User” role.

Create a JSON key for this account

Go to your new service account > Keys > Add Key > Create new key > select JSON. Save the file.

You’ll paste the entire contents of this JSON key file into OneSignal to activate the integration.

2. Activate the Integration in OneSignal

Go to OneSignal > Data > Integrations > BigQuery

Paste your service account JSON key

Configure settings

Sync Frequency: As often as every 15 minutes
Dataset/Table Names: Customize if needed
Event Types: Select specific message events (e.g., sent, opened, clicked)
- Note: You can select multiple event types or update selected events at a later time.

Click Save and wait for confirmation

Initial data sync can take 15–30 minutes to appear in BigQuery.While you wait, send messages via push, email, in-app, or SMS to trigger the events selected.

3. View Data in BigQuery

Open your BigQuery console and locate the dataset (e.g., onesignal_events_<app-id>) to explore synced message events.

BigQuery dataset containing exported message events

Message 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).

Message Event (OneSignal)	`event_kind`	Description
Push Sent	`message.push.sent`	Push notification successfully sent.
Push Received	`message.push.received`	Delivered push (see Confirmed Delivery).
Push Clicked	`message.push.clicked`	User clicked the push.
Push Failed	`message.push.failed`	Delivery failure. See message reports.
Push Unsubscribed	`message.push.unsubscribed`	User unsubscribed from push.
In-App Impression	`message.iam.displayed`	In-App message shown.
In-App Clicked	`message.iam.clicked`	In-App message clicked.
In-App Page Viewed	`message.iam.pagedisplayed`	In-App page shown.
Email Sent	`message.email.sent`	Email delivered.
Email Received	`message.email.received`	Email accepted by recipient’s mail server.
Email Opened	`message.email.opened`	Email opened. See Email Reports.
Email Link Clicked	`message.email.clicked`	Link in email clicked.
Email Unsubscribed	`message.email.unsubscribed`	Recipient unsubscribed.
Email Marked Spam	`message.email.resporedasspam`	Marked as spam. See Email Deliverability.
Email Bounced	`message.email.hardbounced`	Bounce due to permanent delivery failure.
Email Failed	`message.email.failed`	Delivery failed.
Email Suppressed	`message.email.supressed`	Suppressed due to suppression list.
SMS Sent	`message.sms.sent`	SMS sent.
SMS Delivered	`message.sms.delivered`	SMS successfully delivered.
SMS Failed	`message.sms.failed`	SMS failed to deliver.
SMS Undelivered	`message.sms.undelivered`	SMS rejected or unreachable.

Event data schema

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

Column Name	Type	Description
`event_id`	UUID	Unique identifier for the event
`event_timestamp`	Timestamp	Time of event occurrence
`event_kind`	String	The Event Kind
`subscription_device_type`	String	Device type (e.g., iOS, Android, Web, Email, SMS)
`language`	String	Subscription language code
`version`	String	Integration version
`device_os`	String	Device operating system version
`device_type`	Number	Numeric device type
`token`	String	Push token, phone number, or email
`subscription_id`	UUID	Subscription ID
`subscribed`	Boolean	Subscription status
`onesignal_id`	UUID	OneSignal user ID
`last_active`	String	Last active timestamp
`sdk`	String	OneSignal SDK version
`external_id`	String	External user ID that should match the integration user ID
`app_id`	UUID	App ID from OneSignal
`template_id`	UUID	Template ID (if applicable)
`message_id`	UUID	Message batch/request ID
`message_name`	String	Name of the message
`message_title`	String	Message title (English only)
`message_contents`	String	Truncated message body (English only)
`_created`, `_id`, `_index`, `_fivetran_synced`	Internal use	Fivetran sync metadata

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

Import events from BigQuery

Send behavioral event data from BigQuery to OneSignal to:

Trigger Journeys based on user activity
Personalize messaging based on behavioral data

Requirements

Access to Custom Events (currently in beta)
Updated Account Plan (not available on free apps).

GCP project with BigQuery and event data tables
Service account with read permissions
Event data tables containing behavioral data in BigQuery datasets

Setup Steps

Create BigQuery service account

OneSignal will generate a service account automatically when you create the connection. Alternatively, you can provide your own service account key JSON file.If creating your own service account, ensure it has the required permissions listed below.

Grant required permissions

The OneSignal service account needs these BigQuery IAM roles:

bigquery.dataViewer - Read access to datasets and tables containing event data
bigquery.jobUser - Permission to create jobs for data queries
bigquery.metadataViewer - Project-level metadata access (recommended)

Grant permissions using the Google Cloud Console or CLI:

gcloud projects add-iam-policy-binding YOUR_PROJECT_ID \
  --member serviceAccount:ONESIGNAL_SERVICE_ACCOUNT_EMAIL \
  --role roles/bigquery.dataViewer

gcloud projects add-iam-policy-binding YOUR_PROJECT_ID \
  --member serviceAccount:ONESIGNAL_SERVICE_ACCOUNT_EMAIL \
  --role roles/bigquery.jobUser

Add integration in OneSignal

In OneSignal, go to Data > Integrations and click Add Integration.Select Google BigQuery and provide:

Google Cloud Project ID: Your GCP project containing BigQuery datasets
Dataset Region: Location where your BigQuery datasets are stored
Service Account Key (optional): JSON key file if using your own service account

Configure event data source

Specify the BigQuery dataset and table containing your event data:

Dataset: BigQuery dataset name (e.g., analytics_events)
Table/View: Table or view containing event records
Event Query: Optional SQL query to filter or transform event data

Your event table should contain columns for:

Event name/type
User identifier
Event timestamp
Additional event properties

Test the connection

Click Test Connection to verify OneSignal can access your BigQuery project and read event data.

Event data mapping

Map your to OneSignal’s custom events format:

OneSignal Field	Description	Required
`name`	`event_name`	Event identifier	Yes
`external_id`	`user_id`	User identifier	Yes
`timestamp`	`event_timestamp`	When event occurred	No
`properties`	`event_data`	No

Advanced configuration

Custom SQL Queries

Use custom SQL to filter or transform event data before syncing to OneSignal:

SELECT
  event_name,
  user_id,
  event_timestamp,
  STRUCT(
    product_id,
    purchase_amount,
    category
  ) as payload
FROM `project.dataset.events`
WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

Cross-Project Access

If your event data spans multiple BigQuery projects, grant the OneSignal service account access to each project containing referenced tables or views.

Your BigQuery connection region must match the specific table region for optimal performance.

FAQ

Why do I see multiple message IDs for the same content?

This typically occurs when a message template is reused across multiple sends or triggered flows.

How often does OneSignal sync data?

Both export and import integrations can sync as frequently as every 15 minutes.

Can I use BigQuery views?

Yes. Just make sure the service account has access to all referenced tables in the view.

First steps & settings

Message channels

Journeys

Audience & custom events

Analytics & Integrations

Privacy & Security

Overview