Snowflake - data export
Export OneSignal message event data to Snowflake for analytics.
OneSignal Snowflake integration overview
Overview
Snowflake is a cloud-based data warehousing company that offers a fully managed and scalable solution for storing and analyzing large amounts of data. It provides a cloud-based data storage and analytics service that allows businesses to store, process, and analyze large amounts of structured and semi-structured data.
The events available are from email, push notifications, in-app messages, and SMS messages that have been sent and the subsequent events data tied with it. By integrating with OneSignal, you unlock the ability to automatically sync message event data to your Snowflake database. This allows you to run queries and generate reports based on the events your users generate.
Requirements
OneSignal
Snowflake
- Snowflake account
- Ensure your Snowflake account has the
IMPORT SHAREandCREATE DATABASEroles.- We recommend using the
ACCOUNTADMINrole for accepting the share.
- We recommend using the
Setup
1. Sign up
In order to activate the Snowflake integration, you’ll need to request access via the OneSignal Marketplace listing for Message Events Data. From there we’ll reach out to your team to obtain more information.
2. Provide Account Information
Once we connect with your team, you’ll need to provide some details to us so we can share data with you. We will need your Snowflake Account ID and your Snowflake Organization ID. The easiest way to get that info is to click the “Copy account identifier” button (as shown below).
You should be in contact with us via the OneSignal Marketplace listing. However, if you have any further questions about providing account information, you can email snowflake-data-sharing@onesignal.com
Copying Snowflake account identifier
Once you are accepted into the program, be on the lookout for a few items:
- Email from OneSignal welcoming you into the program
- Email from Snowflake notifying that the data share is complete
Snowflake data share notification email
3. Accept data share within Snowflake
Once you receive an email from Snowflake notifying you about the data share being complete, you should be able to log into the Snowflake app and accept the data share.
Navigate to Data → Private Sharing where you should be able to see a new Privately Shared Listing. Once you click Get, your data will start replicating to your region.
Snowflake private sharing interface
Note: If the Get button is not available but a Request button is, the permissions of the role attempting to make the share does not include CREATE DATABASE or IMPORT SHARE. We suggest switching to the ACCOUNTADMIN role.
It will take some time for the data to be replicated. Snowflake will follow up with an additional email once the data is available. You should see a dialog that looks like this
Data replication status dialog
Once the data is ready to use, your listing should have a new Get button available. Click the Get button and you will be greeted with a new dialog
Database configuration dialog
Set the name of the database and database roles to something you prefer. Then click Get
Data share setup completion
That’s it! Data is now being synced from OneSignal to Snowflake. The sync cadence is set to once every 24 hours, so the initial data may not be available until the next day.
Disconnect data share with OneSignal
Please email us at snowflake-data-sharing@onesignal.com to request to deactivate your data share with OneSignal.
Generate message events
Send messages via push, email, in-app, or SMS to trigger events that will sync based on your Integration Settings.
Events and Properties
An overview of the data available on Snowflake’s Marketplace
Message Events Data
See Event Streams for more details.
For each message event generated by a user, the following metadata will be attached to the record.
| Column Name | Type | Description | Example |
|---|---|---|---|
| event_id | UUID | A unique ID that represents the event | dc76df03-287c-403a-aabd-b2d4fe44e5c6 |
| event_kind | string | The type of event that occurred. The list of event kinds can be found here. | message.email.opened |
| event_impression_timestamp | unix_timestamp | The time that Onesignal processes the event | 1684951205 |
| subscription_id | UUID | A unique ID that represents the device/subscription | 6e8b6978-75a4-4882-878c-2d15a78c2409 |
| subscription_language | string | The language of this subscription | en |
| subscription_timezone | integer | The specified timezone of this subscription | -25200 |
| subscription_device_type | string | The type of device this subscription uses. | SMS |
| onesignal_id | UUID | The onesignal_id of the user that made the impression | dc76df03-287c-403a-aabd-b2d4fe44e5c6 |
| external_id | string | The external_id of the user that made the impression | MyExternalId |
| message_id | UUID | A unique ID that represents the message | dc76df03-287c-403a-aabd-b2d4fe44e5c6 |
| message_name | string | The human-readable id of the Notification/Message | Campaign 1 |
| message_title | string | The Title of the Notification/Message.English only at this time | Bonus 50 coins when you play! |
| message_body | string | The Body of the message. (Truncated) English only at this time. | Play against your friends and earn an additional... |
Message Event Kinds
See Event Streams for more details.
Message Events are the first category of data that we’ll be offering on Snowflake’s Marketplace. Please reach out to us at snowflake-data-sharing@onesignal.com with any requests for additional categories of data!
Data Retention
Data shared from OneSignal via Snowflake is retained for a period of 30 days. To ensure data preservation beyond this timeframe, we recommend you copy the relevant data to your own tables.