Snowflake is a data warehouse that many companies use to store and analyze data. In order 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, Enriched Events Export decisions and conversions appear automatically in your Snowflake instance:
- Enriched Events Export data gets pushed into Optimizely’s Snowflake instance when it finishes processing each day.
- You can then query the data immediately using Snowflake’s Secure Data Sharing tool.
- Check with your Technical Account Manager (TAM) that Enriched Events Export is a feature available in your Optimizely plan.
- Check with your Snowflake representative that Secure Data Sharing is a feature available in your Snowflake plan.
- Send Optimizely your Snowflake
- Verify you can query your Enriched Events Export datasets with Snowflake, once Optimizely confirms the share is enabled. This typically takes a few days.
How many new visitors saw the updated call to action on my subscription page last week?
Exposure to the new CTA should be aligned with lifetime value predictions generated for visitors to my site. This query looks at decision events for my experiment in the 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):list['element']:experiment_id = '10728121502' AND parse_json(experiments):list['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):list['element']:experiment_id ='10728121502' AND parse_json(experiments):list['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
This integration is free to Business and Enterprise customers up to 1 billion events per month. Reach out to your AE for details.
Updated about a month ago