Guides
Submit Documentation FeedbackJoin Developer CommunityLog In

SQL Examples

❗️

Deprecation Notice

Enriched Events Export replaces Results Export and Raw Events Export as Optimizely's source-of-truth events dataset. Results Export and Raw Events Export will no longer be supported beginning November 15, 2020. Please refer to the Enriched Events Export documentation for how to get started.

SQL examples for common metrics calculations

This section provides examples in SQL for some common metrics you might want to calculate for your A/B Experiments or Personalization campaigns. The examples assume the records have been imported to a SQL table called table.

A/B experiments

This example shows how to calculate impression usage for A/B experiments. Query Impressions by variation for experimentId 11818160790 and eventId 11351730352:

📘

Note

By convention, impression events have eventId equal to experimentId.

SELECT SUM(count), variationId FROM table
WHERE experimentId = 11818160790 and timestamp between 1554152247000 and 1556657847000
AND eventId = experimentId
GROUP BY variationId

Query Unique Visitors by variation for experimentId 11818160790:

SELECT COUNT(distinct visitorId), variationId FROM table
WHERE experimentId = 11818160790 and experimentId=eventId and timestamp between 1555599262169 and 1555743600000
GROUP BY variationId

Query Unique Conversions by variation for experimentId 11818160790 and eventId 11351730352:

SELECT COUNT(distinct visitorId), variationId FROM table
WHERE experimentId = 11818160790 and timestamp between 1554152247000 and 1556657847000
AND eventId = 11351730352
GROUP BY variationId

Query Total Conversions by variation for experimentId 11818160790 and eventId 11351730352:

SELECT SUM(count), variationId FROM table
WHERE experimentId = 11818160790 and timestamp between 1554152247000 and 1556657847000
AND eventId = 11351730352
GROUP BY variationId

Query Total Revenue by variation for experimentId 11818160790 and eventId 11351730352:

SELECT SUM(revenue), variationId FROM table
WHERE experimentId = 11818160790 and timestamp between 1554152247000 and 1556657847000
AND eventId = 11351730352
GROUP BY variationId

Query Total (Numeric) Value by variation for experimentId 11818160790 and eventId 11351730352:

SELECT SUM(value), variationId FROM table
WHERE experimentId = 11818160790 and timestamp between 1554152247000 and 1556657847000
AND eventId = 11351730352
GROUP BY variationId

Personalization campaigns

This example shows how to calculate impression usage for Personalization campaigns. Query Impressions by Personalization experience for campaignId 11818160790 and eventId 11351730352:

📘

Note

By convention, impression events have eventId equal to experimentId.

SELECT SUM(count), experimentId FROM table
WHERE campaignId = 11818160790 and timestamp between 1554152247000 and 1556657847000
AND eventId = experimentId
GROUP BY experimentId

Query Unique Sessions by Personalization experience for campaignId 11818160790 within a specified time frame:

SELECT COUNT(distinct sessionId), experimentId FROM table
WHERE campaignId = 11818160790 and experimentId=eventId and timestamp between 1554152247000 and 1556657847000
GROUP BY experimentId

Query Unique Conversions by Personalization experience for campaignId 11818160790 and eventId 11351730352:

SELECT COUNT(distinct sessionId), experimentId FROM table
WHERE campaignId = 11818160790 and timestamp between 1554152247000 and 1556657847000
AND eventId = 11351730352
GROUP BY experimentId

Query Total Conversions by Personalization experience for campaignId 11818160790 and eventId 11351730352:

SELECT SUM(count), experimentId FROM table
WHERE campaignId = 11818160790 and timestamp between 1554152247000 and 1556657847000
AND eventId = 11351730352
GROUP BY experimentId

Query Total Revenue by Personalization experience for campaignId 11818160790 and eventId 11351730352:

SELECT SUM(revenue), experimentId FROM table
WHERE campaignId = 11818160790 and timestamp between 1554152247000 and 1556657847000
AND eventId = 11351730352
GROUP BY experimentId

Query Total (Numeric) Value by Personalization experience for campaignId 11818160790 and eventId 11351730352:

SELECT SUM(value), experimentId FROM table
WHERE campaignId = 11818160790 and timestamp between 1554152247000 and 1556657847000
AND eventId = 11351730352
GROUP BY experimentId