Email Opens
Also known as: Total Email Opens, Total Opens (Email), ESP Opens, Email Open Count
Total opens of delivered marketing emails, as logged by the email service provider from tracking-pixel loads. Repeat opens count; unique opens is the one-per-recipient variant.
Business Use Case
1️⃣ CRM Marketing compares opens by subject line in an A/B send and rolls the winner out to the rest of the list.,2️⃣ Marketing sees non-MPP opens fall at one mailbox provider and checks sender reputation and inbox placement.,3️⃣ Marketing suppresses recipients with no non-MPP open in 180 days to protect deliverability.,4️⃣ Marketing pairs opens with Email Clicks per campaign and rewrites content that is opened but not clicked.
Business Criticality
Medium
Journey / Navigation Area
Engagement
Owning Function
Marketing
Scope
Event
Formula
COUNT(open events) on delivered marketing emails, by open timestamp in the reporting period, excluding test, seed and internal sends; unique opens = COUNT(DISTINCT recipient_id, send_id)Calculation Notes
1️⃣ One open is one tracking-pixel load logged by the ESP (Klaviyo Opened Email, Mailchimp open, SFMC _Open); every repeat open counts.,2️⃣ Unique opens count each recipient once per send: COUNT(DISTINCT recipient_id, send_id). Report both, as Klaviyo and Mailchimp do.,3️⃣ Apple Mail Privacy Protection prefetches pixels: flag machine opens, report opens excluding them, and keep them out of engagement segments.,4️⃣ Marketing campaigns and flows only; transactional mail (order and shipping notices) is excluded and sits in Delivery Notifications Opened.,5️⃣ Dated by open timestamp, not send date; one campaign's opens can fall across several reporting days.,6️⃣ Image-blocking clients fire no pixel; implied opens logged on click are excluded, so only pixel opens count across every ESP.,7️⃣ Opens from test sends, seed lists, internal domains and ESP bot-filtered events are excluded.
Technical Details
Events
SQL Queries
Google Analytics 4 (BigQuery)
-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Relative reporting window: last 90 complete days
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
-- Pixel opens (implied opens on click excluded) of delivered marketing campaign and flow emails, dated by open timestamp; test, seed, internal-domain and ESP bot-filtered opens dropped
WITH opens AS (
SELECT
DATE(o.opened_at) AS open_date,
o.recipient_id,
o.send_id,
o.is_machine_open
FROM `your-project.crm.email_open_events` AS o
JOIN `your-project.crm.email_sends` AS s -- one row per send_id x recipient_id
ON s.send_id = o.send_id
AND s.recipient_id = o.recipient_id
WHERE DATE(o.opened_at) BETWEEN start_date AND end_date
AND s.delivery_status = 'delivered'
AND s.message_category IN ('campaign', 'flow')
AND s.is_test_send IS NOT TRUE
AND s.is_seed_recipient IS NOT TRUE
AND s.is_internal_domain IS NOT TRUE
AND o.is_bot_filtered IS NOT TRUE
AND o.is_implied_open IS NOT TRUE
)
-- Daily total opens (repeats count), opens excluding machine (Apple MPP) opens, and unique opens per recipient per send
SELECT
open_date,
COUNT(*) AS email_opens,
COUNTIF(is_machine_open IS NOT TRUE) AS email_opens_excl_machine,
COUNT(DISTINCT CONCAT(CAST(recipient_id AS STRING), '|', CAST(send_id AS STRING))) AS unique_email_opens
FROM opens
GROUP BY open_date
ORDER BY open_date;Adobe Experience Platform (AEP Query Service)
-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Relative reporting window: last 90 complete days
WITH params AS (
SELECT date_sub(current_date(), 90) AS start_date,
date_sub(current_date(), 1) AS end_date
),
-- Pixel opens (implied opens on click excluded) of delivered marketing campaign and flow emails, dated by open timestamp; test, seed, internal-domain and ESP bot-filtered opens dropped
opens AS (
SELECT
to_date(o.opened_at) AS open_date,
o.recipient_id,
o.send_id,
o.is_machine_open
FROM crm_email_open_events AS o
JOIN crm_email_sends AS s -- one row per send_id x recipient_id
ON s.send_id = o.send_id
AND s.recipient_id = o.recipient_id
CROSS JOIN params
WHERE to_date(o.opened_at) BETWEEN params.start_date AND params.end_date
AND s.delivery_status = 'delivered'
AND s.message_category IN ('campaign', 'flow')
AND s.is_test_send IS NOT TRUE
AND s.is_seed_recipient IS NOT TRUE
AND s.is_internal_domain IS NOT TRUE
AND o.is_bot_filtered IS NOT TRUE
AND o.is_implied_open IS NOT TRUE
)
-- Daily total opens (repeats count), opens excluding machine (Apple MPP) opens, and unique opens per recipient per send
SELECT
open_date,
COUNT(*) AS email_opens,
SUM(CASE WHEN is_machine_open IS NOT TRUE THEN 1 ELSE 0 END) AS email_opens_excl_machine,
COUNT(DISTINCT recipient_id, send_id) AS unique_email_opens
FROM opens
GROUP BY open_date
ORDER BY open_date;Source Data
CRM
Dependencies
Prerequisite: List the dependencies required for this Metric
Dimensions:
Report Attributes
Campaign Flow / Automation Send Date Subject Line Variant Mailbox Provider Device Category Machine Open Flag
Recommended Reporting Views
Segment Eligibility
true
Governance
Governance
GitHub
catalog/metrics/email-opens.yml