Google BigQuery

Export messaging event data to BigQuery and import custom events from BigQuery to trigger personalized campaigns.

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

1. Log in to your Google Cloud Platform account

   After you login, ensure the proper project is selected.

   
2. Create a service account

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

   
3. Fill out the fields.

   Give it any name and service account ID you choose.

   
4. Assign the 'BigQuery User' role

   Give the service account the “BigQuery User” role.

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

1. Go to OneSignal > Data > Integrations > BigQuery
2. Paste your service account JSON key
3. 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.
4. 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

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

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

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

---

Related Resources

• Creating service account keys in GCP
• OneSignal Journeys documentation
• OneSignal Data Export documentation

---