Snowflake Secure Data Sharing

Using Iterable's integration with Snowflake Secure Data Sharing, you can send campaign-related data from your Iterable project to your Snowflake account. This gives you another way to analyze your Iterable campaign data and can help you gain more insights from it.

For example, imagine that you're preparing a new marketing campaign to encourage one-time buyers to make an additional purchase. The campaign takes two approaches: one group of customers receives a standard discount, and another is incentivized by a "mystery gift" to leave a product review. Information about your customers' engagement with these campaigns is stored in Iterable, as usual. But with Snowflake Secure Data Sharing, that data is also sent to your Snowflake account.

In Snowflake, you might join your Iterable data with session-related events from your website (or any other data you have stored in Snowflake), comparing how your different campaign approaches affect sign-ins, conversions, etc. You might also use demographic data stored in Snowflake to further analyze your results, or create visualizations and dashboards in your favorite business intelligence tools. Iterable's integration with Snowflake makes all these things possible, and more.

This guide looks at how Snowflake Secure Data Sharing works, some of the benefits of using this integration, how to set it up, and some sample queries you can use to inspect your Iterable data in Snowflake.

# Table of contents

# Early access conditions & limitations

To use this early access release:

• You must be a customer of both Iterable and Snowflake.
• Your Snowflake instance must be on AWS-US-EAST-1.
• You acknowledge that Iterable's integration with Snowflake Secure Data Sharing:
  • Is an early access feature. During early access, it is excluded from Iterable's SLAs.
  • Is a one-way share of data from Iterable to Snowflake.
  • Shares only a subset of Iterable's system event data (campaign-related data such as email clicks, opens, unsubscribes, push events, and more). We'll add more system events over time.

# Benefits of sharing Iterable data with Snowflake

Sharing Iterable data with Snowflake allows you to:

• Spend time and money where it matters most. With Snowflake Secure Data Sharing, you can send data from Iterable to Snowflake without setting up or maintaining an ETL.

• Save on data storage costs. Since Iterable shares data to your Snowflake account rather than copying it, you'll avoid extra data storage costs. (However, you will have to pay for compute resources you use to query the shared data.)

• Gain valuable customer insights. With your Iterable data in Snowflake, you can join it with any other data you store there—and use your favorite business intelligence tools—to gain a deeper understanding of your customers.

# How does Snowflake Secure Data Sharing work?

Sharing data from Iterable to Snowflake is a straightforward process:

• After you ask your Iterable customer success manager to enable the feature, Iterable will share, from its Snowflake account to yours, a database that contains your Iterable system event data (for example, blast and triggered sends, email opens, clicks, bounces, and more).
• In your Snowflake account, you'll accept this share and use it to create a read-only database, using standard Snowflake role-based access controls to grant access as needed.

# Setting up Iterable's integration with Snowflake

To share your project's Iterable system event data with Snowflake, follow these steps:

1. Tell your Iterable customer success manager that you'd like to use Snowflake Secure Data Sharing and give them your Snowflake URL and Snowflake Account ID. We'll use a portion of the URL to determine where to send the shared data.

   TIP

   To find your Snowflake Account ID, expand the drop down menu on the top right corner of your Snowflake instance. The ID is right next to "Organization" under your business name.

2. When you receive an email stating that we’ve shared data with you, have your Snowflake account administrator sign in to Snowflake and complete these steps:

   • On Snowflake's top navigation bar, click Shares:

     

   • Select the secure share from Iterable.

   • Click Create Database from Secure Share. You'll see this window:

     

3. Give the share a Database Name.

4. In Grant access to, use standard Snowflake role-based access controls to indicate which roles should have access to the shared data.

5. (optional) Leave any comments.

6. Click Create Database

Snowflake users with access to the shared database can now navigate to the Worksheets tab and work with the data.

# Ways to use Iterable data in Snowflake

Once your Iterable data is in Snowflake, you can use it in all sorts of interesting ways. You might already have some ideas you'd like to explore, but for now, let’s consider some common ways to evaluate campaigns:

• Email bounces. It’s important to know when Iterable sends emails that bounce, or can’t be delivered. A bounce event might indicate that the recipient’s inbox is full, that the message content was perceived to be spam, or even that an email was sent to a misspelled or outdated email address.

• Email clicks. When testing different templates to see which is most effective, you’ll want to be able to determine which template generates the most unique clicks during a certain period of time. This information helps you expand on what’s working and pull back from what’s not.

• Email unsubscribes. Knowing how many unique users have unsubscribed during a certain period of time can be a valuable indicator of a campaign's success, and of any opportunities you may have for improvement.

• Push sends and bounces. Knowing how many of your push notifications have been successfully delivered (sends minus bounces) can help you identify users to remove from your contact lists so you can focus your efforts on those who are actively receiving your messages.

Now, let’s take a look at how to query this Iterable data in your Snowflake account:

1. Sign in to Snowflake, using an account that has permission to see your Iterable data.

2. Navigate to the Worksheets tab, and use the menu in the upper-right corner to select a role, warehouse, database, and schema.

3. Create your first query. We’ll start by evaluating how many email bounces there were in all of your campaigns over the past 30 days, and the most common reasons they happened:

   SELECT RECIPIENT_STATE, COUNT(DISTINCT MESSAGE_ID) AS UNIQUE_BOUNCES
   FROM "EMAIL_BOUNCES_VIEW"
   WHERE CREATED_AT >= dateadd('day', -30, current_date())
   GROUP BY RECIPIENT_STATE
   ORDER BY UNIQUE_BOUNCES DESC;
   

4. Create another query, this time to determine which email template generated the most unique clicks in the past 30 days:

   SELECT TEMPLATE_ID, COUNT(DISTINCT EMAIL) AS UNIQUE_CLICKS
   FROM "EMAIL_CLICKS_VIEW"
   WHERE CREATED_AT >= dateadd('day', -30, current_date())
   GROUP BY TEMPLATE_ID
   ORDER BY UNIQUE_CLICKS DESC;
   

5. Finally, let’s create a query that tells us how many unique email users unsubscribed every day, before a certain date:

   SELECT DATE_TRUNC('DAY',CREATED_AT) AS DD, COUNT(DISTINCT EMAIL) AS TOTAL_USERS_UNSUBCRIBED
   FROM "EMAIL_UNSUBSCRIBES_VIEW"
   WHERE CREATED_AT >= '2021-08-01'
   GROUP BY 1
   ORDER BY 1 DESC;
   

# Frequently Asked Questions (FAQ)

# What data does Iterable share with Snowflake?

For a full list of the data Iterable shares with Snowflake, talk to your Iterable customer success manager.

# Is this a one-way sync?

Yes. This feature syncs data from Iterable to Snowflake, not from Snowflake to Iterable.

# Does accepting shared data from Iterable increase my Snowflake costs?

This integration uses Snowflake's Secure Data Sharing feature, which shares data rather than copying it. Because of this, the data doesn't affect your Snowflake data storage costs. However, you will have to pay for compute resources you use to query the shared data. See Snowflake's Introduction to Secure Data Sharing document.

# How long does it take Iterable data to appear in Snowflake?

Data appears in Snowflake within 24 hours of its creation in Iterable.

# Does Iterable backfill old system event data to Snowflake?

Iterable does not backfill system event data to Snowflake. Sharing starts with data created close to the creation of the share.

# Does this feature copy Iterable data to my Snowflake account?

No. This feature uses Snowflake's Secure Data Sharing feature, which does not copy data. However, you can copy the data if you need to (this may increase your Snowflake costs).

# Does the early access release of Iterable's integration with Snowflake support GDPR or CCPA?

Yes. To learn how to respond to GDPR and CCPA requests in Iterable, read Responding to GDPR Requests.

# In Snowflake, can I edit the data that Iterable has shared?

No. Data shared via Secure Data Sharing is read-only. To edit the data, you'll need to make a copy of it (which may increase your Snowflake costs).

# If I update a system event in Iterable, does it re-sync to Snowflake?

It's not possible to manually update a system event in Iterable (by making an API call or otherwise).

# Can I control access to my Iterable data in Snowflake?

To control access to shared data in Snowflake, you can use its role-based access controls.