Integrating OneSignal Message Event Data with Snowflake
.jpg "OneSignal Message Event Data with Snowflake") |
| OneSignal Message Event Data with Snowflake |
Snowflake is a leading cloud-based data warehousing platform that offers robust, scalable solutions for storing and analyzing large volumes of structured and semi-structured data. It empowers businesses to process, query, and derive insights from their data with ease.
 |
| OneSignal Message Event Data with Snowflake |
By integrating Snowflake with OneSignal, businesses can sync message event data—such as email, push notifications, in-app messages, and SMS events—directly into their Snowflake database. This enables real-time querying and reporting on user-generated event data.
Prerequisites
Snowflake Account:
- Ensure you have a Snowflake account with the necessary roles (
IMPORT SHAREandCREATE DATABASE). TheACCOUNTADMINrole is recommended for smooth integration.
OneSignal Account:
- This feature is available exclusively for OneSignal customers on an annual plan.
Steps to Set Up the Integration
1. Request Access:
Start by requesting access through the OneSignal Marketplace under the "Message Events Data" listing.
2. Provide Account Details:
Once approved, provide your Snowflake Account ID and Organization ID to OneSignal..
This information is available in the Snowflake dashboard via the "Copy account identifier" option.
3. Accept the Data Share:
Look for an email from Snowflake confirming the data share.
- Log in to Snowflake and navigate to the Data → Private Sharing section.
 |
- Locate the shared listing and click the "Get" button to initiate data replication.
- If the "Get" button is unavailable, ensure your role includes the required permissions (
CREATE DATABASEandIMPORT SHARE).
.jpg "Snowflake Organization ID")
4. Wait for Replication:
The replication process might take some time. Once completed, Snowflake will notify you via email. You can then name your database and configure roles as needed.
Managing the Data Share
If you ever need to deactivate the data share, simply contact OneSignal at snowflake-data-sharing@onesignal.com.
Benefits of the Integration
This integration offers a seamless way to analyze message event data using Snowflake's advanced querying capabilities. Businesses can generate actionable insights and improve communication strategies, all while leveraging the power of Snowflake's data warehousing features.
Metadata Overview
| Column Name | Type | Description | Example |
|---|---|---|---|
| event_id | UUID | Unique identifier for the event | dc76df03-287c-403a-aabd-b2d4fe44e5c6 |
| event_kind | string | Type of event that occurred | message.email.opened |
| event_impression_timestamp | unix_timestamp | Time OneSignal processed the event | 1684951205 |
| subscription_id | UUID | Unique ID for the device or subscription | 6e8b6978-75a4-4882-878c-2d15a78c2409 |
| subscription_language | string | Language of the subscription | en |
| subscription_timezone | integer | Timezone of the subscription | -25200 |
| subscription_device_type | string | Device type used by the subscription | SMS |
| onesignal_id | UUID | ID of the user generating the event | dc76df03-287c-403a-aabd-b2d4fe44e5c6 |
| external_id | string | External ID of the user | MyExternalId |
| message_id | UUID | Unique ID for the message | dc76df03-287c-403a-aabd-b2d4fe44e5c6 |
| message_name | string | Human-readable identifier for the message | Campaign 1 |
| message_title | string | Title of the notification (English only) | Bonus 50 coins when you play! |
| message_body | string | Body of the message (truncated, English only) | Play against your friends and earn... |
Available Event Kinds
| Event Kind | Event Name |
|---|---|
| Push Sent | message.push.sent |
| Push Clicked | message.push.clicked |
| Push Received | message.push.received |
| In-App Message Displayed | message.iam.displayed |
| In-App Message Clicked | message.iam.clicked |
| In-App Message Page Displayed | message.iam.pagedisplayed |
| Email Sent | message.email.sent |
| Email Opened | message.email.opened |
| Email Unsubscribed | message.email.unsubscribed |
| Email Reported as Spam | message.email.reportedasspam |
| Email Hardbounced | message.email.hardbounced |
| Email Failed | message.email.failed |
| SMS Sent | message.sms.sent |
| SMS Delivered | message.sms.delivered |
| SMS Failed | message.sms.failed |
| SMS Undelivered | message.sms.undelivered |
Data Retention Policy
| Detail | Description |
|---|---|
| Retention Period | Data is retained for 30 days. |
| Recommendation | Copy data to your own tables to preserve it beyond the retention period. |
| Contact for Additional Data Categories | Reach out to snowflake-data-sharing@onesignal.com for requests on new data types. |
