Snowflake integration

Overview

Snowflake is a cloud-based data warehouse for storing and analyzing large datasets.

📘

Note

If your data is hosted in the EU, Optimizely loads it only into EU-hosted Snowflake instances (eu-central-1 or eu-west-1).

With the Snowflake integration, Experimentation Events Export decisions and conversions are automatically available to query in Snowflake:

• Optimizely loads Experimentation Events Export data into Snowflake daily and makes it available through Secure Data Sharing. Data from the previous day is typically available the next day.

📘

Note

The Snowflake integration is available only in the following AWS regions:

Region	Account name
ap-southeast-2	tg19960-optimizely_ap_southeast_2
eu-central-1	tg19960-optimizely_eu_central
eu-west-1	tg19960-optimizely_eu_west
us-east-1	tg19960-optimizely_laa60418east
us-east-2	tg19960-optimizely_us_east_2
us-west-2	tg19960-laa60418

Event volume limits

The integration accepts up to 1 billion events per month. Each conversion or decision event counts toward this limit.

Prerequisites

To use the Snowflake integration, you need the following:

• A Snowflake account in one of the supported AWS regions listed in the Overview section.
• A Snowflake plan that includes Secure Data Sharing. Confirm availability with your Snowflake representative if you are unsure.
• Your Snowflake account identifier and AWS region.
• Experimentation Events Export enabled on your Optimizely account.

Get started

To enable the Snowflake integration:

1. Provide Optimizely Support with your Snowflake account identifier and AWS region.
2. When Optimizely confirms sharing is enabled, verify that you can query your Experimentation Events Export datasets in Snowflake.

Usage examples

Count new visitors exposed to a variation

Align exposure to the call to action (CTA) with the lifetime-value predictions for visitors to your site. This query counts unique new visitors who converted on CTA_entered_viewport after a decision event for the experiment, over the week shown in the date range.

SELECT COUNT (distinct visitor_id) as visitor_count
FROM (
     SELECT c.visitor_id
     FROM conversions c
     INNER JOIN
     (
        SELECT visitor_id, MIN(timestamp) as decision_timestamp
        FROM decisions
        WHERE experiment_id = '10728121502'
        AND variation_id = '38495823'
        AND timestamp between '2020-08-20 00:00:00.000'
        AND '2020-08-27 00:00:00.000'
        AND is_holdback = false
        GROUP BY visitor_id
     ) d
     ON c.visitor_id = d.visitor_id
     WHERE parse_json(experiments[0]):list[0]['element']:experiment_id = '10728121502'
     AND parse_json(experiments[0]):list[0]['element']:variation_id = '38495823'
     AND c.timestamp  between '2020-08-20 00:00:00.000'
        AND '2020-08-27 00:00:00.000'
     AND c.event_name = 'CTA_entered_viewport'
     AND c.timestamp >= d.decision_timestamp
)

Count daily clicks for a variation

This query counts daily CTA_clicked conversion events for visitors bucketed into a specific experiment variation over a one-week period.

SELECT to_date(timestamp) as timestamp, COUNT(*) as click_count
FROM conversions
WHERE parse_json(experiments[0]):list[0]['element']:experiment_id ='10728121502'
  AND parse_json(experiments[0]):list[0]['element']:variation_id = '38495823'
  AND timestamp  between '2020-08-20 00:00:00.000'
      AND '2020-08-27 00:00:00.000'
   AND event_name = 'CTA_clicked'
GROUP BY to_date(timestamp)
ORDER BY to_date(timestamp) asc