Dev GuideAPI Reference
Dev GuideAPI ReferenceUser GuideGitHubNuGetDev CommunitySubmit a ticketLog In
GitHubNuGetDev CommunitySubmit a ticket

Snowflake Integration

How to access your Optimizely Experimentation Events Export data through Snowflake, a third-party data warehouse.

Overview

Snowflake is a data warehouse many companies use to store and analyze data. To analyze Optimizely’s experiment data within Snowflake, you have, until now, needed to write a custom ETL to pull that data from Optimizely’s AWS S3 buckets into your own data warehouses.

With a direct Snowflake integration, Experimentation Events Export decisions and conversions display automatically in your Snowflake instance:

Get started

  • Check with your Snowflake representative that Secure Data Sharing is a feature available in your Snowflake plan.
  • Send Optimizely your Snowflake account name and region.
  • Verify you can query your Experimentation Events Export datasets with Snowflake when Optimizely confirms the share is enabled. This typically takes a few days.

Usage examples

How many new visitors saw the updated call to action on my subscription page last week?
Exposure to the new call to action (CTA) should be aligned with lifetime value predictions generated for visitors to my site. This query looks at decision events for my experiment last week to find new visitors and counts unique visitors who converted after a decision event, using the conversion event name ‘CTA_entered_viewport’.

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
)

How many times per day did visitors who saw the new call to action click on it?
Clicks on the new CTA should be joined with user-level revenue averages for visitors to my site. This query looks at all ‘CTA_clicked’ events for my experiment and CTA variation grouped by date.

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

🚧

Important

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

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

Monthly totals

You can send up to 1 billion monthly events. Each conversion or decision event logged is considered an event in the calculation. Contact your Account Executive for further details.