Snowflake

Connecting Snowflake and OneSignal

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

Snowflake Account

  • If you don't have a Snowflake account, we recommend you follow the Snowflake Getting Started Tutorial.
  • Ensure your Snowflake account has the IMPORT SHARE and CREATE DATABASE roles. We recommend using the ACCOUNTADMIN role for accepting the share.

OneSignal Account & Annual Plan

  • This integration is only accessible to OneSignal customers on an annual plan.

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 (as shown below).

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

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

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

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

Set the name of the database and database roles to something you prefer. Then click Get

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 [email protected] to request to deactivate your data share with OneSignal.

Events and Properties

An overview of the data available on Snowflake's Marketplace

Message Events Data

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

Column NameTypeDescriptionExample
event_idUUIDA unique ID that represents the eventdc76df03-287c-403a-aabd-b2d4fe44e5c6
event_kindstringThe type of event that occurred. The list of event kinds can be found here.message.email.opened
event_impression_timestampunix_timestampThe time that Onesignal processes the event1684951205
subscription_idUUIDA unique ID that represents the device/subscription6e8b6978-75a4-4882-878c-2d15a78c2409
subscription_languagestringThe language of this subscriptionen
subscription_timezoneintegerThe specified timezone of this subscription-25200
subscription_device_typestringThe type of device this subscription uses.SMS
onesignal_idUUIDThe onesignal_id of the user that made the impressiondc76df03-287c-403a-aabd-b2d4fe44e5c6
external_idstringThe external_id of the user that made the impressionMyExternalId
message_idUUIDA unique ID that represents the messagedc76df03-287c-403a-aabd-b2d4fe44e5c6
message_namestringThe human-readable id of the Notification/MessageCampaign 1
message_titlestringThe Title of the Notification/Message.
English only at this time
Bonus 50 coins when you play!
message_bodystringThe Body of the message. (Truncated) English only at this time. Play against your friends and earn an additional...

Message Event Kinds

The message event kinds that are currently available through the Snowflake Integration are:

Event KindEvent Name
Push Sentmessage.push.sent
Push Clickedmessage.push.clicked
Push Receivedmessage.push.recieved
In-App Message Displayedmessage.iam.displayed
In-App Message Clickedmessage.iam.clicked
In-App Message Page is Displayedmessage.iam.pagedisplayed
Email Sentmessage.email.sent
Email Openedmessage.email.opened
Email Unsubscribedmessage.email.unsubscribed
Email Reported As Spammessage.email.reportedasspam
Email Hardbouncedmessage.email.hardbounced
Email Failedmessage.email.failed
SMS Sentmessage.sms.sent
SMS Deliveredmessage.sms.delivered
SMS Failedmessage.sms.failed
SMS Undeliveredmessage.sms.undelivered

Message Events are the first category of data that we'll be offering on Snowflake's Marketplace. Please reach out to us at [email protected] 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.