Snowflake integration
This is a third-party integration and is not an Optimizely subprocessor. See Optimizely's Third-Party Add-Ons & Platform Integration Terms.
Overview
Snowflake is a cloud-based data warehouse for storing and analyzing large datasets.
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.
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 |
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:
- Provide Optimizely Support with your Snowflake account identifier and AWS region.
- 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