Email Click-to-Purchase Conversion Rate

Also known as: Conversion Rate, Email Conversion Rate, Email Click Conversion Rate, Email Placed Order Rate (Clickers), Email Click-to-Order Rate

ConversionkpiE-commerce: Top-of-Funnel & Marketing AcquisitionE-commerce

E-commerce specialisation of Conversion Rate: the share of unique email clickers in a period whose click-through session (utm_medium=email, matched to the sending campaign) leads to a completed purchase, or to a click-attributed order on the ESP order feed, within the attribution window, with every unique clicker in the period, taken from the ESP click feed, as the denominator. It separates post-click commercial performance from open and click engagement, showing whether campaign content, offer and landing experience turn email traffic into orders at the rate reported by the ESP (Klaviyo, Braze, Salesforce Marketing Cloud) and reconciled in GA4.

Business Use Case

1️⃣ The CRM & Email Marketing Manager reviews the rate per campaign in the weekly send review; when a campaign converts below the trailing 8-week median despite an above-median click-through rate, the team audits the landing page, offer terms and product availability before the next send to that segment. 2️⃣ Lifecycle Marketing compares automated flows (abandoned cart, browse abandonment, win-back) against broadcast campaigns each month and moves discount budget from flows whose rate does not exceed the broadcast baseline to those that do. 3️⃣ The Growth team uses the rate as the primary success metric in subject-line, creative and send-time A/B tests, promoting a variant only when its uplift in click-to-purchase conversion is statistically significant rather than on clicks alone. 4️⃣ Marketing Leadership reconciles ESP-reported and GA4-attributed values monthly; when the gap exceeds 15 percentage points, the email operations lead fixes UTM tagging or the hashed-ID handoff before the figures are used in channel budget planning.

Business Criticality

High

Journey / Navigation Area

Discovery

Owning Function

Marketing

Scope

User

Formula

(Unique Email Clickers with ≥1 Attributed Purchase within the Attribution Window / Total Unique Email Clickers) × 100

Calculation Notes

1️⃣ Denominator is every unique clicking recipient (ESP subscriber/profile ID) with at least one tracked click on a campaign or flow email in the period, whether or not the click produced a measurable GA4 session; the click-level variant (attributed purchasing clicks / total clicks) must be labelled separately and never blended with the recipient-level rate. 2️⃣ Numerator counts a clicker once when a purchase event with a valid transaction_id occurs in a session whose session_source_medium is email and whose session_campaign maps to the clicked send, or when a click-attributed order on the ESP order feed ties to the same profile and campaign, within the attribution window (default 5 days from click; state the window when comparing against Klaviyo or Braze defaults); every source row is matched to clicks first and only then deduplicated on transaction_id, with the GA4 / web session record winning over the ESP feed row when both match. 3️⃣ Stitch ESP click data to GA4 on a hashed subscriber ID passed in the dedicated landing-URL parameter `esub_id`, separate from the UTM campaign parameters that carry the campaign id used as the join key, captured as the event parameter `email_subscriber_id_hashed` in GA4 and `eVar12` in Adobe, and falls back to the logged-in user_id (GA4) or CRMID (Adobe) only when that id is hashed the same way as the ESP subscriber id; raw email addresses never enter the analytics join, and unmatched clicks from the ESP click feed stay in the denominator so the rate is not inflated by stitching gaps. 4️⃣ Exclude bot and security-scanner clicks (clicks within 2 seconds of delivery, clicks on every link in the message, or known link-scanner user agents), internal and seed-list recipients, test sends and transactional messages (order confirmations, shipping notifications). 5️⃣ GA4 sessions expire after 30 minutes of inactivity; a purchase in a later session still qualifies when that session is attributed to the same email campaign within the window, while a later session from a different source (paid search, direct with a new campaign) is credited to that source under last non-direct click rules; ESP-feed orders are used only when they are click-attributed and the analytics data holds no purchase for that transaction_id in a non-email, non-direct session, so the ESP's own attribution never overrides this rule. 6️⃣ Deduplicate orders on transaction_id across web, app and the ESP order feed; orders fully refunded or cancelled before the reporting close are removed from the numerator, using GA4 full refund events (refund with no items), Adobe order-cancelled events and full credit memos (full credit memo = credited amount equals order total, i.e. the summed credit-memo commerce.order.priceTotal is at least the original commerce.purchases priceTotal for the same purchaseID), and ESP refunded or cancelled order status in both SQL variants. 7️⃣ Attribute the conversion to the click date (the send cohort), not the order date, and hold the latest period open until the attribution window has fully elapsed before publishing the final value; weekly and monthly values recount unique clickers over the whole period (reporting_grain parameter in both SQLs) and are never summed from daily rows; the requested click window is snapped to whole periods of the chosen grain (ISO weeks starting Monday) so no partial week or month is reported.

Technical Details

Measure TypePercentage

Aggregation WindowDaily, Weekly, Monthly

Events

Platform	Event
Google Analytics 4	purchase
Adobe	purchase

Data Mappings

W3 Data Layer

{
  "digitalData": {
    "page": {
      "pageInfo": {
        "pageID": "checkout-confirmation",
        "pageName": "Order Confirmation",
        "destinationURL": "https://www.example.com/checkout/confirmation"
      },
      "category": {
        "primaryCategory": "Checkout"
      }
    },
    "transaction": {
      "transactionID": "T-100245",
      "total": {
        "basePrice": 114.9,
        "voucherCode": "EMAIL15",
        "voucherDiscount": 15,
        "currency": "USD",
        "taxRate": 0.09,
        "shipping": 5,
        "shippingMethod": "Standard",
        "priceWithTax": 108.89,
        "transactionTotal": 113.89
      },
      "item": [
        {
          "productInfo": {
            "productID": "SKU-48213",
            "productName": "Merino Crew Sweater"
          },
          "category": {
            "primaryCategory": "Apparel"
          },
          "quantity": 1,
          "price": {
            "basePrice": 114.9,
            "currency": "USD"
          }
        }
      ],
      "attributes": {
        "trafficSource": "klaviyo",
        "trafficMedium": "email",
        "campaignName": "fall_launch_2026_09",
        "campaignID": "kl_cmp_01J8Z3",
        "campaignContent": "hero_cta",
        "messageType": "campaign",
        "subjectLineVariant": "B",
        "emailClickTimestamp": "2026-09-12T14:05:31Z"
      }
    },
    "user": [
      {
        "profile": [
          {
            "profileInfo": {
              "profileID": "a3f5c9e1b27d4e8f9c0b6d2e7a1f4c8b",
              "returningStatus": "returning"
            }
          }
        ]
      }
    ]
  }
}

GA4 Data Layer

{
  "event": "purchase",
  "user_id": "a3f5c9e1b27d4e8f9c0b6d2e7a1f4c8b",
  "campaign_source": "klaviyo",
  "campaign_medium": "email",
  "campaign_name": "fall_launch_2026_09",
  "campaign_id": "kl_cmp_01J8Z3",
  "campaign_content": "hero_cta",
  "email_message_type": "campaign",
  "email_subject_variant": "B",
  "email_subscriber_id_hashed": "a3f5c9e1b27d4e8f9c0b6d2e7a1f4c8b",
  "ecommerce": {
    "transaction_id": "T-100245",
    "value": 113.89,
    "tax": 8.99,
    "shipping": 5,
    "currency": "USD",
    "coupon": "EMAIL15",
    "items": [
      {
        "item_id": "SKU-48213",
        "item_name": "Merino Crew Sweater",
        "item_category": "Apparel",
        "price": 99.9,
        "discount": 15,
        "quantity": 1
      }
    ]
  }
}

Adobe Client Data Layer

{
  "event": "purchase",
  "eventInfo": {
    "eventName": "purchase",
    "timestamp": "2026-09-12T14:21:08Z"
  },
  "page": {
    "pageName": "Order Confirmation",
    "siteSection": "Checkout"
  },
  "marketing": {
    "trackingCode": "email:klaviyo:fall_launch_2026_09:hero_cta",
    "campaignName": "fall_launch_2026_09",
    "campaignID": "kl_cmp_01J8Z3",
    "channel": "email",
    "messageType": "campaign",
    "subjectLineVariant": "B"
  },
  "transaction": {
    "purchaseID": "T-100245",
    "total": 113.89,
    "tax": 8.99,
    "shipping": 5,
    "currency": "USD",
    "couponCode": "EMAIL15",
    "products": [
      {
        "SKU": "SKU-48213",
        "name": "Merino Crew Sweater",
        "category": "Apparel",
        "quantity": 1,
        "priceTotal": 99.9
      }
    ]
  },
  "user": {
    "subscriberIdHashed": "a3f5c9e1b27d4e8f9c0b6d2e7a1f4c8b",
    "loginStatus": "logged-in"
  }
}

XDM Mapping

{
  "eventType": "commerce.purchases",
  "timestamp": "2026-09-12T14:21:08Z",
  "channel": {
    "_id": "https://ns.adobe.com/xdm/channels/email",
    "_type": "https://ns.adobe.com/xdm/channel-types/email"
  },
  "marketing": {
    "trackingCode": "email:klaviyo:fall_launch_2026_09:hero_cta",
    "campaignName": "fall_launch_2026_09",
    "campaignGroup": "email"
  },
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "T-100245",
      "priceTotal": 113.89,
      "currencyCode": "USD",
      "taxAmount": 8.99,
      "payments": [
        {
          "paymentAmount": 113.89,
          "currencyCode": "USD"
        }
      ]
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-48213",
      "name": "Merino Crew Sweater",
      "quantity": 1,
      "priceTotal": 99.9,
      "currencyCode": "USD"
    }
  ],
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar10": "kl_cmp_01J8Z3",
          "eVar11": "campaign",
          "eVar12": "a3f5c9e1b27d4e8f9c0b6d2e7a1f4c8b"
        }
      }
    }
  }
}

SQL Queries

Google Analytics 4 (BigQuery)

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Email Click-to-Purchase Conversion Rate (recipient level, click-date cohort, 5-day attribution window)
-- Requested click window: 2026-08-01 to 2026-08-31, snapped to whole periods of params.reporting_grain
--   ('WEEK' = ISO weeks 2026-07-27 to 2026-09-06; 'MONTH' = 2026-08-01 to 2026-08-31; 'DAY' = unchanged)
-- GA4 export and ESP orders scanned from the snapped start to the snapped end + 5 days
-- Unique clickers are recounted per whole period, never summed from daily rows
WITH params AS (
  SELECT
    'WEEK' AS reporting_grain,
    DATE '2026-08-01' AS requested_click_start,
    DATE '2026-08-31' AS requested_click_end
),
click_window AS (
  SELECT
    reporting_grain,
    click_start,
    click_end_exclusive,
    DATE_ADD(click_end_exclusive, INTERVAL 5 DAY) AS scan_end_exclusive
  FROM (
    SELECT
      reporting_grain,
      CASE reporting_grain
        WHEN 'MONTH' THEN DATE_TRUNC(requested_click_start, MONTH)
        WHEN 'WEEK' THEN DATE_TRUNC(requested_click_start, ISOWEEK)
        ELSE requested_click_start
      END AS click_start,
      CASE reporting_grain
        WHEN 'MONTH' THEN DATE_ADD(DATE_TRUNC(requested_click_end, MONTH), INTERVAL 1 MONTH)
        WHEN 'WEEK' THEN DATE_ADD(DATE_TRUNC(requested_click_end, ISOWEEK), INTERVAL 7 DAY)
        ELSE DATE_ADD(requested_click_end, INTERVAL 1 DAY)
      END AS click_end_exclusive
    FROM params
  ) AS snapped
),
esp_clicks AS (
  SELECT
    k.click_id,
    k.subscriber_id_hashed,
    k.campaign_id,
    k.message_type,
    k.click_timestamp AS click_ts,
    DATE(k.click_timestamp) AS click_date
  FROM `your-project.esp.email_clicks` AS k
  CROSS JOIN click_window AS w
  WHERE k.click_timestamp >= TIMESTAMP(w.click_start)
    AND k.click_timestamp < TIMESTAMP(w.click_end_exclusive)
    AND k.subscriber_id_hashed IS NOT NULL
    AND k.message_type IN ('campaign', 'flow')
    AND COALESCE(k.is_test_send, FALSE) = FALSE
    AND COALESCE(k.is_internal_or_seed_recipient, FALSE) = FALSE
    AND COALESCE(k.clicked_all_links_in_message, FALSE) = FALSE
    AND TIMESTAMP_DIFF(k.click_timestamp, k.delivered_timestamp, SECOND) > 2
    AND NOT REGEXP_CONTAINS(LOWER(COALESCE(k.user_agent, '')), r'(bot|scanner|proofpoint|mimecast|barracuda|safelinks)')
),
ga4_events AS (
  SELECT
    e.user_pseudo_id,
    e.event_name,
    TIMESTAMP_MICROS(e.event_timestamp) AS event_ts,
    e.ecommerce.transaction_id AS transaction_id,
    ARRAY_LENGTH(e.items) AS item_count,
    (SELECT MAX(IF(ep.key = 'ga_session_id', ep.value.int_value, NULL)) FROM UNNEST(e.event_params) AS ep) AS ga_session_id,
    -- Hashed ESP subscriber id from the esub_id landing parameter first; user_id only when hashed the same way
    COALESCE(
      (SELECT MAX(IF(ep.key = 'email_subscriber_id_hashed', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep),
      e.user_id
    ) AS subscriber_id_hashed,
    LOWER(COALESCE(
      e.session_traffic_source_last_click.manual_campaign.medium,
      e.collected_traffic_source.manual_medium,
      (SELECT MAX(IF(ep.key = 'medium', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep),
      (SELECT MAX(IF(ep.key = 'campaign_medium', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep)
    )) AS event_medium,
    COALESCE(
      e.session_traffic_source_last_click.manual_campaign.campaign_id,
      e.collected_traffic_source.manual_campaign_id,
      (SELECT MAX(IF(ep.key = 'campaign_id', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep)
    ) AS event_campaign_id
  FROM `your-project.analytics_123456789.events_*` AS e
  CROSS JOIN click_window AS w
  -- Static suffix range covers the widest snapped window (ISO WEEK start 2026-07-27, end 2026-09-06 + 5 days); the timestamp filter applies the exact window
  WHERE e._TABLE_SUFFIX BETWEEN '20260727' AND '20260911'
    AND TIMESTAMP_MICROS(e.event_timestamp) >= TIMESTAMP(w.click_start)
    AND TIMESTAMP_MICROS(e.event_timestamp) < TIMESTAMP(w.scan_end_exclusive)
    AND e.event_name IN ('session_start', 'page_view', 'purchase', 'refund')
),
ga4_sessions AS (
  SELECT
    user_pseudo_id,
    ga_session_id,
    MAX(subscriber_id_hashed) AS subscriber_id_hashed,
    ARRAY_AGG(event_medium IGNORE NULLS ORDER BY event_ts LIMIT 1)[SAFE_OFFSET(0)] AS session_medium,
    ARRAY_AGG(event_campaign_id IGNORE NULLS ORDER BY event_ts LIMIT 1)[SAFE_OFFSET(0)] AS session_campaign_id
  FROM ga4_events
  WHERE ga_session_id IS NOT NULL
  GROUP BY user_pseudo_id, ga_session_id
),
ga4_purchases AS (
  SELECT
    e.transaction_id,
    e.event_ts,
    s.subscriber_id_hashed,
    s.session_medium,
    s.session_campaign_id
  FROM ga4_events AS e
  JOIN ga4_sessions AS s
    ON s.user_pseudo_id = e.user_pseudo_id
   AND s.ga_session_id = e.ga_session_id
  WHERE e.event_name = 'purchase'
    AND e.transaction_id IS NOT NULL
    AND e.transaction_id NOT IN ('', '(not set)')
),
ga4_email_orders AS (
  SELECT
    transaction_id,
    subscriber_id_hashed,
    session_campaign_id AS campaign_id,
    MIN(event_ts) AS order_ts
  FROM ga4_purchases
  WHERE session_medium = 'email'
    AND subscriber_id_hashed IS NOT NULL
  GROUP BY transaction_id, subscriber_id_hashed, session_campaign_id
),
-- Orders recorded in a non-email, non-direct session belong to that source under last non-direct click rules
ga4_other_source_orders AS (
  SELECT DISTINCT transaction_id
  FROM ga4_purchases
  WHERE session_medium IS NOT NULL
    AND session_medium NOT IN ('email', '(none)', 'direct')
),
-- ESP feed restricted to click-attributed orders that analytics does not credit to another source
esp_feed_orders AS (
  SELECT
    f.order_id AS transaction_id,
    f.subscriber_id_hashed,
    f.attributed_campaign_id AS campaign_id,
    f.order_timestamp AS order_ts
  FROM `your-project.esp.email_orders` AS f
  CROSS JOIN click_window AS w
  LEFT JOIN ga4_other_source_orders AS g
    ON g.transaction_id = f.order_id
  WHERE f.order_timestamp >= TIMESTAMP(w.click_start)
    AND f.order_timestamp < TIMESTAMP(w.scan_end_exclusive)
    AND f.order_id IS NOT NULL
    AND f.subscriber_id_hashed IS NOT NULL
    AND LOWER(f.attribution_type) = 'click'
    AND g.transaction_id IS NULL
),
-- Full refunds and cancellations: GA4 refund events with no items plus ESP refunded / cancelled orders
voided_orders AS (
  SELECT DISTINCT transaction_id
  FROM ga4_events
  WHERE event_name = 'refund'
    AND transaction_id IS NOT NULL
    AND COALESCE(item_count, 0) = 0
  UNION DISTINCT
  SELECT DISTINCT order_id AS transaction_id
  FROM `your-project.esp.email_orders`
  WHERE LOWER(order_status) IN ('refunded', 'cancelled')
),
order_candidates AS (
  SELECT o.transaction_id, o.subscriber_id_hashed, o.campaign_id, o.order_ts, o.source_priority
  FROM (
    SELECT transaction_id, subscriber_id_hashed, campaign_id, order_ts, 1 AS source_priority FROM ga4_email_orders
    UNION ALL
    SELECT transaction_id, subscriber_id_hashed, campaign_id, order_ts, 2 AS source_priority FROM esp_feed_orders
  ) AS o
  LEFT JOIN voided_orders AS v
    ON v.transaction_id = o.transaction_id
  WHERE v.transaction_id IS NULL
),
-- Match every source row to clicks first, then keep one match per transaction_id (GA4 session record wins, then latest click)
converting_clicks AS (
  SELECT
    c.click_date,
    c.subscriber_id_hashed,
    o.transaction_id
  FROM esp_clicks AS c
  JOIN order_candidates AS o
    ON o.subscriber_id_hashed = c.subscriber_id_hashed
   AND o.campaign_id = c.campaign_id
   AND o.order_ts >= c.click_ts
   AND o.order_ts < TIMESTAMP_ADD(c.click_ts, INTERVAL 5 DAY)
  QUALIFY ROW_NUMBER() OVER (PARTITION BY o.transaction_id ORDER BY o.source_priority, c.click_ts DESC) = 1
),
clicks_by_period AS (
  SELECT
    CASE w.reporting_grain
      WHEN 'MONTH' THEN DATE_TRUNC(c.click_date, MONTH)
      WHEN 'WEEK' THEN DATE_TRUNC(c.click_date, ISOWEEK)
      ELSE c.click_date
    END AS period_start,
    CASE w.reporting_grain
      WHEN 'MONTH' THEN DATE_ADD(DATE_TRUNC(c.click_date, MONTH), INTERVAL 1 MONTH)
      WHEN 'WEEK' THEN DATE_ADD(DATE_TRUNC(c.click_date, ISOWEEK), INTERVAL 7 DAY)
      ELSE DATE_ADD(c.click_date, INTERVAL 1 DAY)
    END AS period_end_exclusive,
    c.subscriber_id_hashed,
    cc.subscriber_id_hashed AS converting_subscriber_id_hashed
  FROM esp_clicks AS c
  CROSS JOIN click_window AS w
  LEFT JOIN converting_clicks AS cc
    ON cc.click_date = c.click_date
   AND cc.subscriber_id_hashed = c.subscriber_id_hashed
)
SELECT
  period_start,
  DATE_SUB(period_end_exclusive, INTERVAL 1 DAY) AS period_end,
  COUNT(DISTINCT subscriber_id_hashed) AS total_unique_email_clickers,
  COUNT(DISTINCT converting_subscriber_id_hashed) AS converting_unique_email_clickers,
  ROUND(SAFE_DIVIDE(COUNT(DISTINCT converting_subscriber_id_hashed), COUNT(DISTINCT subscriber_id_hashed)) * 100, 2) AS email_click_to_purchase_conversion_rate_pct,
  TIMESTAMP_ADD(TIMESTAMP(period_end_exclusive), INTERVAL 5 DAY) <= CURRENT_TIMESTAMP() AS attribution_window_closed
FROM clicks_by_period
GROUP BY period_start, period_end_exclusive
ORDER BY period_start;

Adobe Experience Platform (AEP Query Service)

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Email Click-to-Purchase Conversion Rate (recipient level, click-date cohort, 5-day attribution window)
-- Requested click window: 2026-08-01 to 2026-08-31, snapped to whole periods of params.reporting_grain
--   ('WEEK' = ISO weeks 2026-07-27 to 2026-09-06; 'MONTH' = 2026-08-01 to 2026-08-31; 'DAY' = unchanged)
-- Web events and ESP orders scanned from the snapped start to the snapped end + 5 days
-- Unique clickers are recounted per whole period, never summed from daily rows
WITH params AS (
  SELECT
    'WEEK' AS reporting_grain,
    CAST('2026-08-01' AS DATE) AS requested_click_start,
    CAST('2026-08-31' AS DATE) AS requested_click_end
),
click_window AS (
  SELECT
    reporting_grain,
    click_start,
    click_end_exclusive,
    date_add(click_end_exclusive, 5) AS scan_end_exclusive
  FROM (
    SELECT
      reporting_grain,
      CASE reporting_grain
        WHEN 'MONTH' THEN CAST(date_trunc('MONTH', CAST(requested_click_start AS TIMESTAMP)) AS DATE)
        WHEN 'WEEK' THEN CAST(date_trunc('WEEK', CAST(requested_click_start AS TIMESTAMP)) AS DATE)
        ELSE requested_click_start
      END AS click_start,
      CASE reporting_grain
        WHEN 'MONTH' THEN add_months(CAST(date_trunc('MONTH', CAST(requested_click_end AS TIMESTAMP)) AS DATE), 1)
        WHEN 'WEEK' THEN date_add(CAST(date_trunc('WEEK', CAST(requested_click_end AS TIMESTAMP)) AS DATE), 7)
        ELSE date_add(requested_click_end, 1)
      END AS click_end_exclusive
    FROM params
  ) AS snapped
),
esp_clicks AS (
  SELECT
    k.click_id,
    k.subscriber_id_hashed,
    k.campaign_id,
    k.message_type,
    CAST(k.click_timestamp AS TIMESTAMP) AS click_ts,
    CAST(k.click_timestamp AS DATE) AS click_date
  FROM esp_email_click_dataset AS k
  CROSS JOIN click_window AS w
  WHERE k.click_timestamp >= CAST(w.click_start AS TIMESTAMP)
    AND k.click_timestamp < CAST(w.click_end_exclusive AS TIMESTAMP)
    AND k.subscriber_id_hashed IS NOT NULL
    AND k.message_type IN ('campaign', 'flow')
    AND COALESCE(k.is_test_send, FALSE) = FALSE
    AND COALESCE(k.is_internal_or_seed_recipient, FALSE) = FALSE
    AND COALESCE(k.clicked_all_links_in_message, FALSE) = FALSE
    AND (unix_timestamp(k.click_timestamp) - unix_timestamp(k.delivered_timestamp)) > 2
    AND NOT (LOWER(COALESCE(k.user_agent, '')) RLIKE '(bot|scanner|proofpoint|mimecast|barracuda|safelinks)')
),
web_events AS (
  SELECT
    x.endUserIDs._experience.mcid.id AS ecid,
    x.timestamp AS event_ts,
    x.eventType AS event_type,
    x.commerce.order.purchaseID AS transaction_id,
    -- Hashed ESP subscriber id (eVar12, from the esub_id landing parameter) first; CRMID only when hashed the same way
    COALESCE(
      x._experience.analytics.customDimensions.eVars.eVar12,
      x.identityMap['CRMID'][0].id
    ) AS subscriber_id_hashed,
    x._experience.analytics.customDimensions.eVars.eVar10 AS event_campaign_id,
    LOWER(COALESCE(
      x.marketing.campaignGroup,
      CASE WHEN x.channel._type = 'https://ns.adobe.com/xdm/channel-types/email' THEN 'email' END,
      split(x.marketing.trackingCode, ':')[0]
    )) AS event_medium,
    SESS_TIMEOUT(x.timestamp, 60 * 30) OVER (
      PARTITION BY x.endUserIDs._experience.mcid.id
      ORDER BY x.timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ).num AS session_num
  FROM aep_web_experience_event_dataset AS x
  CROSS JOIN click_window AS w
  WHERE x.timestamp >= CAST(w.click_start AS TIMESTAMP)
    AND x.timestamp < CAST(w.scan_end_exclusive AS TIMESTAMP)
    AND x.endUserIDs._experience.mcid.id IS NOT NULL
),
web_sessions AS (
  SELECT DISTINCT
    ecid,
    session_num,
    MAX(subscriber_id_hashed) OVER (PARTITION BY ecid, session_num) AS subscriber_id_hashed,
    first_value(event_medium, TRUE) OVER (
      PARTITION BY ecid, session_num ORDER BY event_ts
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS session_medium,
    first_value(event_campaign_id, TRUE) OVER (
      PARTITION BY ecid, session_num ORDER BY event_ts
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS session_campaign_id
  FROM web_events
),
web_purchases AS (
  SELECT
    e.transaction_id,
    e.event_ts,
    s.subscriber_id_hashed,
    s.session_medium,
    s.session_campaign_id
  FROM web_events AS e
  JOIN web_sessions AS s
    ON s.ecid = e.ecid
   AND s.session_num = e.session_num
  WHERE e.event_type = 'commerce.purchases'
    AND e.transaction_id IS NOT NULL
    AND e.transaction_id <> ''
),
web_email_orders AS (
  SELECT
    transaction_id,
    subscriber_id_hashed,
    session_campaign_id AS campaign_id,
    MIN(event_ts) AS order_ts
  FROM web_purchases
  WHERE session_medium = 'email'
    AND subscriber_id_hashed IS NOT NULL
  GROUP BY transaction_id, subscriber_id_hashed, session_campaign_id
),
-- Orders recorded in a non-email, non-direct session belong to that source under last non-direct click rules
web_other_source_orders AS (
  SELECT DISTINCT transaction_id
  FROM web_purchases
  WHERE session_medium IS NOT NULL
    AND session_medium NOT IN ('email', '(none)', 'direct')
),
-- ESP feed restricted to click-attributed orders that analytics does not credit to another source
esp_feed_orders AS (
  SELECT
    f.order_id AS transaction_id,
    f.subscriber_id_hashed,
    f.attributed_campaign_id AS campaign_id,
    CAST(f.order_timestamp AS TIMESTAMP) AS order_ts
  FROM esp_email_order_dataset AS f
  CROSS JOIN click_window AS w
  LEFT JOIN web_other_source_orders AS o
    ON o.transaction_id = f.order_id
  WHERE f.order_timestamp >= CAST(w.click_start AS TIMESTAMP)
    AND f.order_timestamp < CAST(w.scan_end_exclusive AS TIMESTAMP)
    AND f.order_id IS NOT NULL
    AND f.subscriber_id_hashed IS NOT NULL
    AND LOWER(f.attribution_type) = 'click'
    AND o.transaction_id IS NULL
),
-- Original order totals from commerce.purchases events
web_order_totals AS (
  SELECT
    x.commerce.order.purchaseID AS transaction_id,
    MAX(x.commerce.order.priceTotal) AS order_total
  FROM aep_web_experience_event_dataset AS x
  CROSS JOIN click_window AS w
  WHERE x.timestamp >= CAST(w.click_start AS TIMESTAMP)
    AND x.timestamp < CAST(w.scan_end_exclusive AS TIMESTAMP)
    AND x.eventType = 'commerce.purchases'
    AND x.commerce.order.purchaseID IS NOT NULL
  GROUP BY x.commerce.order.purchaseID
),
-- Credited amounts from Adobe Commerce credit memos
web_credit_memos AS (
  SELECT
    x.commerce.order.purchaseID AS transaction_id,
    SUM(x.commerce.order.priceTotal) AS credited_total
  FROM aep_web_experience_event_dataset AS x
  CROSS JOIN click_window AS w
  WHERE x.timestamp >= CAST(w.click_start AS TIMESTAMP)
    AND x.timestamp < CAST(w.scan_end_exclusive AS TIMESTAMP)
    AND x.eventType = 'commerce.backofficeCreditmemoIssued'
    AND x.commerce.order.purchaseID IS NOT NULL
  GROUP BY x.commerce.order.purchaseID
),
-- Full refunds and cancellations: XDM order-cancelled events, full credit memos (credited amount equals order total) and ESP refunded / cancelled orders
voided_orders AS (
  SELECT DISTINCT x.commerce.order.purchaseID AS transaction_id
  FROM aep_web_experience_event_dataset AS x
  CROSS JOIN click_window AS w
  WHERE x.timestamp >= CAST(w.click_start AS TIMESTAMP)
    AND x.timestamp < CAST(w.scan_end_exclusive AS TIMESTAMP)
    AND x.eventType = 'commerce.backofficeOrderCancelled'
    AND x.commerce.order.purchaseID IS NOT NULL
  UNION
  SELECT m.transaction_id
  FROM web_credit_memos AS m
  JOIN web_order_totals AS t
    ON t.transaction_id = m.transaction_id
  WHERE m.credited_total >= t.order_total
  UNION
  SELECT DISTINCT order_id AS transaction_id
  FROM esp_email_order_dataset
  WHERE LOWER(order_status) IN ('refunded', 'cancelled')
),
order_candidates AS (
  SELECT o.transaction_id, o.subscriber_id_hashed, o.campaign_id, o.order_ts, o.source_priority
  FROM (
    SELECT transaction_id, subscriber_id_hashed, campaign_id, order_ts, 1 AS source_priority FROM web_email_orders
    UNION ALL
    SELECT transaction_id, subscriber_id_hashed, campaign_id, order_ts, 2 AS source_priority FROM esp_feed_orders
  ) AS o
  LEFT JOIN voided_orders AS v
    ON v.transaction_id = o.transaction_id
  WHERE v.transaction_id IS NULL
),
-- Match every source row to clicks first, then keep one match per transaction_id (web session record wins, then latest click)
click_order_matches AS (
  SELECT
    c.click_date,
    c.subscriber_id_hashed,
    o.transaction_id,
    ROW_NUMBER() OVER (PARTITION BY o.transaction_id ORDER BY o.source_priority, c.click_ts DESC) AS rn
  FROM esp_clicks AS c
  JOIN order_candidates AS o
    ON o.subscriber_id_hashed = c.subscriber_id_hashed
   AND o.campaign_id = c.campaign_id
   AND o.order_ts >= c.click_ts
   AND unix_timestamp(o.order_ts) < unix_timestamp(c.click_ts) + 5 * 86400
),
converting_clicks AS (
  SELECT DISTINCT click_date, subscriber_id_hashed
  FROM click_order_matches
  WHERE rn = 1
),
clicks_by_period AS (
  SELECT
    CASE w.reporting_grain
      WHEN 'MONTH' THEN CAST(date_trunc('MONTH', CAST(c.click_date AS TIMESTAMP)) AS DATE)
      WHEN 'WEEK' THEN CAST(date_trunc('WEEK', CAST(c.click_date AS TIMESTAMP)) AS DATE)
      ELSE c.click_date
    END AS period_start,
    CASE w.reporting_grain
      WHEN 'MONTH' THEN add_months(CAST(date_trunc('MONTH', CAST(c.click_date AS TIMESTAMP)) AS DATE), 1)
      WHEN 'WEEK' THEN date_add(CAST(date_trunc('WEEK', CAST(c.click_date AS TIMESTAMP)) AS DATE), 7)
      ELSE date_add(c.click_date, 1)
    END AS period_end_exclusive,
    c.subscriber_id_hashed,
    cc.subscriber_id_hashed AS converting_subscriber_id_hashed
  FROM esp_clicks AS c
  CROSS JOIN click_window AS w
  LEFT JOIN converting_clicks AS cc
    ON cc.click_date = c.click_date
   AND cc.subscriber_id_hashed = c.subscriber_id_hashed
)
SELECT
  period_start,
  date_sub(period_end_exclusive, 1) AS period_end,
  COUNT(DISTINCT subscriber_id_hashed) AS total_unique_email_clickers,
  COUNT(DISTINCT converting_subscriber_id_hashed) AS converting_unique_email_clickers,
  ROUND(
    100.0 * COUNT(DISTINCT converting_subscriber_id_hashed)
    / NULLIF(COUNT(DISTINCT subscriber_id_hashed), 0), 2
  ) AS email_click_to_purchase_conversion_rate_pct,
  CAST(date_add(period_end_exclusive, 5) AS TIMESTAMP) <= current_timestamp() AS attribution_window_closed
FROM clicks_by_period
GROUP BY period_start, period_end_exclusive
ORDER BY period_start;

Source Data

Digital Analytics (GA4) + Email Service Provider (Klaviyo)

Dependencies

Prerequisite: List the dependencies required for this KPI

Events:

purchaserefund

Dimensions:

traffic-source

Report Attributes

Email Campaign Name Message Type (Campaign vs Flow) Send Date Audience Segment Subject Line Variant Landing Page Device Category New vs Returning Customer Country

Recommended Reporting Views

Views

Email & CRM Performance DashboardLifecycle Marketing ScorecardMarketing Channel Performance DashboardCampaign A/B Test Results

Segment Eligibility

true

Governance

Data SensitivityRestricted

Contains PIIYes - This KPI contains Personally Identifiable Information

Governance

Created bydevyendarm

Created on9/13/2026

Last modified bydevyendarm

Last modified on9/15/2026

Version1.0.0

Validation stateqa passed

StatusPUBLISHED

GitHub

View related Pull Requestcatalog/kpis/email-click-to-purchase-conversion-rate.yml

Email Click-to-Purchase Conversion Rate

Business Use Case

Business Criticality

Journey / Navigation Area

Owning Function

Scope

Formula

Calculation Notes

Technical Details

Events

Data Mappings

SQL Queries

Source Data

Dependencies

Events:

Dimensions:

Report Attributes

Recommended Reporting Views

Segment Eligibility

Governance

Related KPIs

Governance

GitHub

Community Discussion