Sample Queries

1. How to query total visitors by variation for an experiment?

   SELECT variation_type, COUNT(DISTINCT end_user_id)
   FROM (
     SELECT
       CASE WHEN variation_id = '<variation_id>' THEN 'control' ELSE 'variation A' END AS variation_type
     , end_user_id
     , MIN(timestamp)  AS min_timestamp
     FROM fivetran.optimizely.visitor_action
     WHERE experiment_id = '<experiment_id>'
     GROUP BY 1,2 ) a
   GROUP BY 1;
   

2. How to get variations's of all the experiments for a project?

   SELECT id,name
   FROM fivetran.optimizely.experiment_variation_history
   WHERE experiment_id IN(Select id
                           FROM fivetran.optimizely.experiment_history
                           WHERE project_id='<project_id>')