SQL Examples

Here are some frequently-used queries to compute simple experiment measures using Enriched Events Export.

Unique Visitors

Count of Unique Visitors from Decision Events (Full-Stack Methodology)

-- Count of Unique Visitors (Full-Stack Methodology)
SELECT COUNT(distinct visitor_id)
FROM decisions
WHERE experiment_id = '17287133690'
AND timestamp between 1579039200000 AND 1579644000000
AND is_holdback = false

Count of Unique Visitors from all Events (Web A/B Methodology)

-- Count of Unique Visitors (Web A/B Methodology)
SELECT COUNT (distinct visitor_id)
FROM 
(
     SELECT visitor_id
     FROM events
     CROSS JOIN UNNEST(experiments) as t(exp)
     WHERE exp.experiment_id='17287133690'
     AND timestamp between 1579039200000 AND 1579644000000
     UNION
     SELECT visitor_id
     FROM decisions
     WHERE experiment_id = '17287133690'
     AND timestamp between 1579039200000 AND 1579644000000
     AND is_holdback = false
)

Unique Conversions

--Unique Conversions on event_id

SELECT COUNT(distinct visitor_id)
FROM events
CROSS JOIN UNNEST(experiments) as t(exp)
WHERE exp.experiment_id='10757480886'
   AND timestamp between 1579039200000 and 1579644000000
   AND entity_id = '11795515443'

Total Conversions

--Total Conversions on event_id

SELECT COUNT(*)  
FROM events
CROSS JOIN UNNEST(experiments) as t(exp)
WHERE 
exp.experiment_id='17287133690'
AND timestamp between 1579039200000 and 1579644000000
AND entity_id = '11099710611'

Total Revenue

--Total Revenue for event_id

SELECT 
   SUM(revenue)  
FROM events
CROSS JOIN UNNEST(experiments) as t(exp)
WHERE 
exp.experiment_id='17287133690'
AND timestamp between 1579039200000 and 1579644000000
AND entity_id = '17281432226'

Total Value

--Total Value for event_id

SELECT 
   SUM(value)  
FROM events
CROSS JOIN UNNEST(experiments) as t(exp)
WHERE 
exp.experiment_id='17287133690'
AND timestamp between 1579039200000 and 1579644000000
AND entity_id = '17281432226'