conversion_rates.sql

WITH birth_events AS
 (SELECT identity_id, MIN(timestamp) AS first_timestamp
 FROM fact_experiment_birth_events
 -- Experiment name chosen
 WHERE experiment_name LIKE '%experiment-1%'
 GROUP BY identity_id),

death_events AS
 (SELECT identity_id, MIN(timestamp) AS first_timestamp
 -- Table with chosen metric
 FROM fact_zap_created
 GROUP BY identity_id),

conversion_data AS
(SELECT
  birth_events.identity_id,
  fact_experiment_birth_events.variant,
 (CASE
   WHEN dim_user.is_employee = TRUE THEN 'Excluded from Experiment'
   WHEN dim_user.is_spam = TRUE THEN 'Excluded from Experiment'
   -- Timeframe chosen (e.g., 1 day)
   WHEN birth_events.first_timestamp > DATEADD('day', -1.0, GETDATE()) THEN 'Excluded from Experiment'
   WHEN death_events.first_timestamp < birth_events.first_timestamp THEN 'Excluded from Experiment'
   -- Timeframe chosen (e.g., 1 day)
   WHEN date_diff('day', birth_events.first_timestamp, death_events.first_timestamp) BETWEEN 0 AND 1.0 THEN 'Converted'
   ELSE 'Did Not Convert'
 END) AS did_they_convert
   FROM birth_events
   LEFT JOIN fact_experiment_birth_events
   ON
     birth_events.identity_id = fact_experiment_birth_events.identity_id AND
     birth_events.first_timestamp = fact_experiment_birth_events.timestamp AND
     -- Experiment name chosen
    fact_experiment_birth_events.experiment_name LIKE '%experiment-1%'
   LEFT JOIN death_events
   ON birth_events.identity_id = death_events.identity_id
   LEFT JOIN dim_user
   ON birth_events.identity_id = dim_user.identity_id AND dim_user.current_version)

SELECT did_they_convert, variant, COUNT(DISTINCT identity_id)
FROM conversion_data
GROUP BY 1, 2