Cart-to-Checkout Rate

{
  "digitalData": {
    "pageInstanceID": "checkout-step-1-prod",
    "page": {
      "pageInfo": {
        "pageID": "checkout:step1",
        "pageName": "Checkout - Shipping",
        "destinationURL": "https://www.example.com/checkout",
        "referringURL": "https://www.example.com/cart",
        "sysEnv": "web",
        "language": "en-US"
      },
      "category": {
        "primaryCategory": "Checkout",
        "subCategory1": "Checkout Initiation"
      },
      "attributes": {
        "checkoutStep": 1,
        "checkoutType": "guest",
        "expressCheckout": false,
        "sessionHasCart": true,
        "cartEntryEvent": "add_to_cart"
      }
    },
    "cart": {
      "cartID": "CART-8Y3K2P",
      "price": {
        "basePrice": 179.98,
        "currency": "USD",
        "shipping": 9.99,
        "cartTotal": 189.97
      },
      "attributes": {
        "cartSize": 3,
        "cartSizeBand": "3-5 items",
        "cartValueBand": "100-249"
      },
      "item": [
        {
          "productInfo": {
            "productID": "SKU-1042",
            "productName": "Merino Crew Sweater",
            "productBrand": "Northbound",
            "productVariant": "Charcoal / M"
          },
          "category": {
            "primaryCategory": "Apparel",
            "subCategory1": "Knitwear"
          },
          "quantity": 2,
          "price": {
            "basePrice": 64.99,
            "currency": "USD"
          }
        },
        {
          "productInfo": {
            "productID": "SKU-7781",
            "productName": "Wool Blend Scarf",
            "productBrand": "Northbound",
            "productVariant": "Oatmeal"
          },
          "category": {
            "primaryCategory": "Accessories",
            "subCategory1": "Scarves"
          },
          "quantity": 1,
          "price": {
            "basePrice": 50,
            "currency": "USD"
          }
        }
      ]
    },
    "event": [
      {
        "eventInfo": {
          "eventName": "add_to_cart",
          "eventAction": "Add To Cart",
          "type": "commerce",
          "timeStamp": "2026-09-17T14:16:41Z"
        },
        "category": {
          "primaryCategory": "Engagement"
        },
        "attributes": {
          "cartID": "CART-8Y3K2P",
          "cartSize": 3,
          "cartValue": 189.97,
          "currency": "USD"
        }
      },
      {
        "eventInfo": {
          "eventName": "view_cart",
          "eventAction": "View Cart",
          "type": "commerce",
          "timeStamp": "2026-09-17T14:20:55Z"
        },
        "category": {
          "primaryCategory": "Engagement"
        },
        "attributes": {
          "cartID": "CART-8Y3K2P",
          "cartSize": 3,
          "cartValue": 189.97,
          "currency": "USD"
        }
      },
      {
        "eventInfo": {
          "eventName": "begin_checkout",
          "eventAction": "Checkout Initiated",
          "type": "commerce",
          "timeStamp": "2026-09-17T14:22:09Z"
        },
        "category": {
          "primaryCategory": "Conversion"
        },
        "attributes": {
          "cartID": "CART-8Y3K2P",
          "checkoutStep": 1,
          "checkoutType": "guest",
          "cartSize": 3,
          "cartValue": 189.97,
          "currency": "USD",
          "expressCheckout": false
        }
      }
    ],
    "version": "1.0"
  }
}

{
  "event": "begin_checkout",
  "ecommerce": {
    "currency": "USD",
    "value": 189.97,
    "coupon": "FREESHIP50",
    "items": [
      {
        "item_id": "SKU-1042",
        "item_name": "Merino Crew Sweater",
        "item_brand": "Northbound",
        "item_category": "Apparel",
        "item_category2": "Knitwear",
        "item_variant": "Charcoal / M",
        "price": 64.99,
        "quantity": 2,
        "index": 1
      },
      {
        "item_id": "SKU-7781",
        "item_name": "Wool Blend Scarf",
        "item_brand": "Northbound",
        "item_category": "Accessories",
        "item_category2": "Scarves",
        "item_variant": "Oatmeal",
        "price": 50,
        "quantity": 1,
        "index": 2
      }
    ]
  },
  "cart_id": "CART-8Y3K2P",
  "cart_size": 3,
  "cart_value": 189.97,
  "cart_size_band": "3-5 items",
  "cart_entry_event": "add_to_cart",
  "session_has_cart": true,
  "checkout_step": 1,
  "checkout_type": "guest",
  "express_checkout": false,
  "page_type": "checkout"
}

{
  "event": "commerce.checkouts",
  "eventInfo": {
    "eventName": "begin_checkout",
    "eventAction": "Checkout Initiated",
    "timestamp": "2026-09-17T14:22:09Z"
  },
  "commerce": {
    "checkouts": {
      "id": "CHK-8Y3K2P-1",
      "value": 1
    },
    "cart": {
      "cartID": "CART-8Y3K2P",
      "cartSize": 3,
      "cartValue": 189.97,
      "cartSizeBand": "3-5 items",
      "cartEntryEvent": "add_to_cart",
      "sessionHasCart": true
    },
    "order": {
      "currencyCode": "USD",
      "priceTotal": 189.97
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-1042",
      "name": "Merino Crew Sweater",
      "product": "Apparel|Knitwear",
      "priceTotal": 129.98,
      "quantity": 2,
      "currencyCode": "USD"
    },
    {
      "SKU": "SKU-7781",
      "name": "Wool Blend Scarf",
      "product": "Accessories|Scarves",
      "priceTotal": 50,
      "quantity": 1,
      "currencyCode": "USD"
    }
  ],
  "checkout": {
    "step": 1,
    "stepName": "Shipping",
    "type": "guest",
    "expressCheckout": false
  },
  "web": {
    "webPageDetails": {
      "name": "Checkout - Shipping",
      "siteSection": "Checkout",
      "pageType": "checkout",
      "URL": "https://www.example.com/checkout"
    },
    "webReferrer": {
      "URL": "https://www.example.com/cart"
    }
  }
}

{
  "eventType": "commerce.checkouts",
  "timestamp": "2026-09-17T14:22:09Z",
  "commerce": {
    "checkouts": {
      "id": "CHK-8Y3K2P-1",
      "value": 1
    },
    "productListAdds": {
      "value": 0
    },
    "productListViews": {
      "value": 0
    },
    "order": {
      "currencyCode": "USD",
      "priceTotal": 189.97
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-1042",
      "name": "Merino Crew Sweater",
      "priceTotal": 129.98,
      "quantity": 2,
      "currencyCode": "USD",
      "productCategories": [
        {
          "categoryID": "apparel-knitwear"
        }
      ]
    },
    {
      "SKU": "SKU-7781",
      "name": "Wool Blend Scarf",
      "priceTotal": 50,
      "quantity": 1,
      "currencyCode": "USD",
      "productCategories": [
        {
          "categoryID": "accessories-scarves"
        }
      ]
    }
  ],
  "web": {
    "webPageDetails": {
      "name": "Checkout - Shipping",
      "siteSection": "Checkout",
      "URL": "https://www.example.com/checkout",
      "pageViews": {
        "value": 0
      }
    },
    "webReferrer": {
      "URL": "https://www.example.com/cart"
    }
  },
  "device": {
    "type": "mobile"
  },
  "environment": {
    "type": "browser",
    "browserDetails": {
      "userAgent": "Mozilla/5.0 (iPhone; CPU iPhone OS 17_5 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/17.5 Mobile/15E148 Safari/604.1"
    }
  },
  "consents": {
    "collect": {
      "val": "y"
    }
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar1": {
            "value": "CART-8Y3K2P"
          },
          "eVar2": {
            "value": "3-5 items"
          },
          "eVar3": {
            "value": "guest"
          },
          "eVar4": {
            "value": "add_to_cart"
          }
        },
        "props": {
          "prop1": {
            "value": "checkout:step1"
          },
          "prop2": {
            "value": "cart-holding session"
          },
          "prop3": {
            "value": "external"
          }
        }
      },
      "session": {
        "depth": 7
      }
    }
  },
  "identityMap": {
    "ECID": [
      {
        "id": "79352374837478925037226990396212",
        "primary": true
      }
    ]
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Cart-to-Checkout Rate = sessions initiating checkout / sessions holding a cart x 100.
-- Session grain = (user_pseudo_id, ga_session_id) pivoted out of event_params, the same session key as Order Conversion Rate (Visit);
-- GA4 closes a session after 30 minutes of inactivity, at midnight in the property time zone and on a campaign change, so a cart built
-- on one day and checked out on another correctly produces two sessions.
-- Denominator = every qualified session with at least one add_to_cart or view_cart (the full cart-holding base population).
-- Numerator  = the subset of those same sessions that also fire begin_checkout at or after the first cart event.
-- Express (buy-now / wallet) entries that fire begin_checkout with no preceding cart event never enter either side, because the session
-- carries no qualified cart event; they belong on the separate express checkout view.
-- Window = 28 complete days, ending 4 days back so late-arriving hits have landed.
WITH base_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', e.event_date) AS event_date,
    e.event_name,
    e.event_timestamp,
    e.user_pseudo_id,
    e.device.category AS device_category,
    -- Session-scoped last-click source / medium: the export stamps the same session_traffic_source_last_click value on every event of
    -- the session, so it is read here rather than event-level collected_traffic_source or first-touch traffic_source.
    LOWER(COALESCE(NULLIF(e.session_traffic_source_last_click.manual_campaign.source, ''), '(direct)')) AS traffic_source,
    LOWER(COALESCE(NULLIF(e.session_traffic_source_last_click.manual_campaign.medium, ''), '(none)')) AS traffic_medium,
    e.privacy_info.analytics_storage AS analytics_storage,
    p.ga_session_id,
    p.cart_size,
    p.cart_size_band,
    p.cart_value,
    p.express_checkout_flag,
    p.debug_mode,
    p.traffic_type,
    (
      SELECT COUNTIF(i.item_id IS NOT NULL
                     AND NOT STARTS_WITH(UPPER(i.item_id), 'TEST')
                     AND COALESCE(i.price, 0) > 0)
      FROM UNNEST(e.items) AS i
    ) AS sellable_items
  FROM `your-project.analytics_123456789.events_*` AS e,
  UNNEST([(
    SELECT AS STRUCT
      MAX(IF(ep.key = 'ga_session_id', ep.value.int_value, NULL)) AS ga_session_id,
      MAX(IF(ep.key = 'cart_size', COALESCE(ep.value.int_value, SAFE_CAST(ep.value.string_value AS INT64)), NULL)) AS cart_size,
      MAX(IF(ep.key = 'cart_size_band', ep.value.string_value, NULL)) AS cart_size_band,
      MAX(IF(ep.key = 'cart_value', COALESCE(ep.value.double_value, ep.value.float_value, SAFE_CAST(ep.value.int_value AS FLOAT64)), NULL)) AS cart_value,
      MAX(IF(ep.key = 'express_checkout', CASE WHEN LOWER(COALESCE(ep.value.string_value, '')) = 'true' OR ep.value.int_value = 1 THEN 1 ELSE 0 END, NULL)) AS express_checkout_flag,
      MAX(IF(ep.key = 'debug_mode', COALESCE(ep.value.int_value, SAFE_CAST(ep.value.string_value AS INT64)), NULL)) AS debug_mode,
      MAX(IF(ep.key = 'traffic_type', LOWER(ep.value.string_value), NULL)) AS traffic_type
    FROM UNNEST(e.event_params) AS ep
  )]) AS p
  WHERE e._TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY))
                            AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
    AND e.event_name IN ('add_to_cart', 'view_cart', 'begin_checkout')
    AND COALESCE(e.device.web_info.hostname, '') NOT LIKE '%staging%'
),
clean_events AS (
  -- Exclusions applied before the ratio (mirrored in the AEP build): internal / QA / load-test / monitoring traffic, debug hits and
  -- consent-denied measurement. Known bots and crawlers are already filtered out of the GA4 export by the property.
  SELECT
    CONCAT(user_pseudo_id, '.', CAST(ga_session_id AS STRING)) AS session_key,
    *
  FROM base_events
  WHERE ga_session_id IS NOT NULL
    AND COALESCE(debug_mode, 0) = 0
    AND COALESCE(traffic_type, '') NOT IN ('internal', 'qa', 'load_test', 'monitoring', 'debug')
    AND COALESCE(analytics_storage, 'Yes') <> 'No'
),
session_context AS (
  SELECT
    session_key,
    MIN(event_date) AS session_date,
    MAX(cart_size) AS max_cart_size,
    MAX(COALESCE(express_checkout_flag, 0)) AS express_flag,
    ARRAY_AGG(cart_size_band IGNORE NULLS ORDER BY event_timestamp DESC LIMIT 1)[SAFE_OFFSET(0)] AS last_cart_size_band,
    ARRAY_AGG(STRUCT(device_category, traffic_source, traffic_medium) ORDER BY event_timestamp LIMIT 1)[SAFE_OFFSET(0)] AS session_entry
  FROM clean_events
  GROUP BY session_key
),
cart_sessions AS (
  -- Denominator population: one row per session that created or viewed a cart holding at least one sellable, non-test item.
  SELECT
    session_key,
    MIN(event_timestamp) AS first_cart_ts
  FROM clean_events
  WHERE event_name IN ('add_to_cart', 'view_cart')
    AND sellable_items > 0
  GROUP BY session_key
),
checkout_sessions AS (
  -- Numerator population: the SAME sessions, deduplicated to one row, whose begin_checkout fires at or after the first cart event.
  SELECT
    c.session_key,
    MIN(c.event_timestamp) AS first_checkout_ts
  FROM clean_events AS c
  JOIN cart_sessions AS s USING (session_key)
  WHERE c.event_name = 'begin_checkout'
    AND c.event_timestamp >= s.first_cart_ts
  GROUP BY c.session_key
),
session_outcome AS (
  SELECT
    cs.session_key,
    ctx.session_date,
    COALESCE(ctx.session_entry.device_category, 'unknown') AS device_category,
    COALESCE(
      ctx.last_cart_size_band,
      CASE
        WHEN ctx.max_cart_size IS NULL THEN 'Unknown'
        WHEN ctx.max_cart_size <= 2 THEN '1-2 items'
        WHEN ctx.max_cart_size <= 5 THEN '3-5 items'
        WHEN ctx.max_cart_size <= 9 THEN '6-9 items'
        ELSE '10+ items'
      END
    ) AS cart_size_band,
    -- Default Channel Grouping on the session-scoped last-click source / medium; the AEP build applies the identical rule set and labels
    -- to the source / medium of each session's entry hit.
    CASE
      WHEN ctx.session_entry.traffic_source IN ('(direct)', '') AND ctx.session_entry.traffic_medium IN ('(none)', '(not set)', '') THEN 'Direct'
      WHEN REGEXP_CONTAINS(ctx.session_entry.traffic_medium, r'^(.*cp.*|ppc|retargeting|paid.*)$')
           AND REGEXP_CONTAINS(ctx.session_entry.traffic_source, r'google|bing|yahoo|duckduckgo|baidu|yandex|ecosia') THEN 'Paid Search'
      WHEN REGEXP_CONTAINS(ctx.session_entry.traffic_medium, r'^(.*cp.*|ppc|retargeting|paid.*)$')
           AND REGEXP_CONTAINS(ctx.session_entry.traffic_source, r'facebook|instagram|tiktok|pinterest|linkedin|twitter|reddit|snapchat') THEN 'Paid Social'
      WHEN ctx.session_entry.traffic_medium IN ('display', 'banner', 'expandable', 'interstitial', 'cpm') THEN 'Display'
      WHEN REGEXP_CONTAINS(ctx.session_entry.traffic_medium, r'^(.*cp.*|ppc|retargeting|paid.*)$') THEN 'Paid Other'
      WHEN ctx.session_entry.traffic_medium IN ('social', 'social-network', 'social-media', 'sm', 'social network', 'social media')
           OR REGEXP_CONTAINS(ctx.session_entry.traffic_source, r'facebook|instagram|tiktok|pinterest|linkedin|twitter|reddit|snapchat') THEN 'Organic Social'
      WHEN ctx.session_entry.traffic_medium = 'organic'
           OR REGEXP_CONTAINS(ctx.session_entry.traffic_source, r'google|bing|yahoo|duckduckgo|baidu|yandex|ecosia') THEN 'Organic Search'
      WHEN ctx.session_entry.traffic_medium IN ('email', 'e-mail', 'e_mail', 'e mail', 'newsletter') THEN 'Email'
      WHEN ctx.session_entry.traffic_medium = 'affiliate' THEN 'Affiliates'
      WHEN ctx.session_entry.traffic_medium IN ('referral', 'app', 'link') THEN 'Referral'
      ELSE 'Unassigned'
    END AS channel_group,
    ctx.express_flag,
    IF(co.session_key IS NULL, 0, 1) AS initiated_checkout
  FROM cart_sessions AS cs
  JOIN session_context AS ctx USING (session_key)
  LEFT JOIN checkout_sessions AS co USING (session_key)
)
SELECT
  session_date,
  device_category,
  channel_group,
  cart_size_band,
  COUNT(*) AS cart_sessions,
  COUNTIF(initiated_checkout = 1) AS sessions_initiating_checkout,
  COUNTIF(express_flag = 1) AS cart_sessions_with_express_entry,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(initiated_checkout = 1), COUNT(*)), 1) AS cart_to_checkout_rate_pct,
  COUNT(*) >= 200 AS meets_minimum_base
FROM session_outcome
WHERE session_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY)
                       AND DATE_SUB(CURRENT_DATE(), INTERVAL 4 DAY)
GROUP BY session_date, device_category, channel_group, cart_size_band
ORDER BY session_date, device_category, channel_group, cart_size_band;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Cart-to-Checkout Rate on the web ExperienceEvent dataset, same base population and same session grain as the BigQuery build.
-- Session key = ECID from identityMap plus a session ordinal rebuilt from _experience.analytics.session.depth, which restarts at every
-- new visit, with the 30-minute inactivity and midnight rules as the fallback boundary when depth is unavailable.
-- Page-view hits (web.webPageDetails.pageViews) are kept alongside the commerce hits so every session includes its entry hit; the
-- channel is derived from that entry hit's UTM parameters, or from its referrer only when the referrer is external to the site.
-- Denominator = sessions with a qualified cart hit (commerce.productListAdds or commerce.productListViews, the view_cart equivalent).
-- Numerator  = the subset of those sessions that also carry commerce.checkouts at or after the first cart hit.
-- Checkout hits with no preceding cart hit in the session (express / wallet entries) fall outside both sides by construction.
-- Exclusions mirror the BigQuery build: internal / QA / load-test / monitoring / debug traffic (prop3 traffic type), bots, crawlers and
-- load-test agents (user agent) and consent-denied measurement (consents.collect.val = 'n').
-- Window = the same 28 complete days as BigQuery: current_date - 31 through current_date - 4, both inclusive.
WITH base_events AS (
  SELECT
    identityMap['ECID'][0]['id'] AS ecid,
    timestamp AS event_ts,
    eventType,
    commerce.productListAdds.value AS product_list_adds,
    commerce.productListViews.value AS product_list_views,
    commerce.checkouts.value AS checkouts,
    commerce.order.priceTotal AS cart_value,
    device.type AS device_category,
    web.webPageDetails.siteSection AS site_section,
    COALESCE(web.webPageDetails.pageViews.value, 0) AS page_views,
    -- Hit-level source / medium, used only from each session's entry hit (see session_context). UTM parameters on the landing URL win;
    -- otherwise an external referrer (not the site's own domain) is mapped to a channel medium: search engines -> organic,
    -- social networks -> social, any other external host -> referral. No UTM and no external referrer -> (direct) / (none).
    lower(COALESCE(
      NULLIF(parse_url(web.webPageDetails.URL, 'QUERY', 'utm_source'), ''),
      CASE WHEN COALESCE(parse_url(web.webReferrer.URL, 'HOST'), '') = '' OR lower(parse_url(web.webReferrer.URL, 'HOST')) RLIKE '(^|\\.)example\\.com$' THEN '(direct)'
           ELSE regexp_replace(lower(parse_url(web.webReferrer.URL, 'HOST')), '^www\\.', '') END
    )) AS traffic_source,
    lower(COALESCE(
      NULLIF(parse_url(web.webPageDetails.URL, 'QUERY', 'utm_medium'), ''),
      CASE WHEN NULLIF(parse_url(web.webPageDetails.URL, 'QUERY', 'utm_source'), '') IS NOT NULL THEN '(not set)'
           WHEN COALESCE(parse_url(web.webReferrer.URL, 'HOST'), '') = '' OR lower(parse_url(web.webReferrer.URL, 'HOST')) RLIKE '(^|\\.)example\\.com$' THEN '(none)'
           WHEN lower(parse_url(web.webReferrer.URL, 'HOST')) RLIKE 'google|bing|yahoo|duckduckgo|baidu|yandex|ecosia' THEN 'organic'
           WHEN lower(parse_url(web.webReferrer.URL, 'HOST')) RLIKE 'facebook|instagram|tiktok|pinterest|linkedin|twitter|reddit|snapchat' THEN 'social'
           ELSE 'referral' END
    )) AS traffic_medium,
    _experience.analytics.session.depth AS session_depth,
    _experience.analytics.customDimensions.eVars.eVar2.value AS cart_size_band,
    _experience.analytics.customDimensions.eVars.eVar3.value AS checkout_type,
    COALESCE(size(filter(productListItems, i -> i.priceTotal > 0 AND upper(COALESCE(i.SKU, '')) NOT LIKE 'TEST%')), 0) AS sellable_items
  FROM aep_web_experience_events
  WHERE timestamp >= date_sub(current_date(), 31)
    AND timestamp < date_sub(current_date(), 3)
    AND identityMap['ECID'][0]['id'] IS NOT NULL
    AND COALESCE(web.webPageDetails.URL, '') NOT LIKE '%staging%'
    AND lower(COALESCE(_experience.analytics.customDimensions.props.prop3.value, '')) NOT IN ('internal', 'qa', 'load_test', 'monitoring', 'debug')
    AND NOT (lower(COALESCE(environment.browserDetails.userAgent, '')) RLIKE 'bot|crawler|spider|headless|lighthouse|pingdom|datadog|k6|jmeter|gatling|loadrunner')
    AND COALESCE(consents.collect.val, 'y') <> 'n'
    AND (
      eventType IN ('commerce.productListAdds', 'commerce.productListViews', 'commerce.checkouts', 'web.webpagedetails.pageViews')
      OR COALESCE(web.webPageDetails.pageViews.value, 0) > 0
      OR COALESCE(commerce.productListAdds.value, 0) > 0
      OR COALESCE(commerce.productListViews.value, 0) > 0
      OR COALESCE(commerce.checkouts.value, 0) > 0
    )
),
sessionised AS (
  SELECT
    b.*,
    CASE
      WHEN LAG(event_ts) OVER (PARTITION BY ecid ORDER BY event_ts) IS NULL THEN 1
      WHEN session_depth IS NOT NULL
           AND LAG(session_depth) OVER (PARTITION BY ecid ORDER BY event_ts) IS NOT NULL
           AND session_depth <= LAG(session_depth) OVER (PARTITION BY ecid ORDER BY event_ts) THEN 1
      WHEN to_date(event_ts) <> to_date(LAG(event_ts) OVER (PARTITION BY ecid ORDER BY event_ts)) THEN 1
      WHEN unix_timestamp(event_ts) - unix_timestamp(LAG(event_ts) OVER (PARTITION BY ecid ORDER BY event_ts)) > 1800 THEN 1
      ELSE 0
    END AS is_session_start
  FROM base_events AS b
),
keyed AS (
  SELECT
    s.*,
    CONCAT(ecid, '.', CAST(SUM(is_session_start) OVER (
      PARTITION BY ecid ORDER BY event_ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS STRING)) AS session_key
  FROM sessionised AS s
),
cart_hits AS (
  SELECT *
  FROM keyed
  WHERE (
      eventType = 'commerce.productListAdds'
      OR COALESCE(product_list_adds, 0) > 0
      OR eventType = 'commerce.productListViews'
      OR COALESCE(product_list_views, 0) > 0
    )
    AND sellable_items > 0
),
cart_sessions AS (
  SELECT session_key, MIN(event_ts) AS first_cart_ts
  FROM cart_hits
  GROUP BY session_key
),
checkout_sessions AS (
  SELECT k.session_key, MIN(k.event_ts) AS first_checkout_ts
  FROM keyed AS k
  JOIN cart_sessions AS c ON c.session_key = k.session_key
  WHERE (k.eventType = 'commerce.checkouts' OR COALESCE(k.checkouts, 0) > 0)
    AND k.event_ts >= c.first_cart_ts
  GROUP BY k.session_key
),
session_context AS (
  SELECT
    session_key,
    to_date(MIN(event_ts)) AS session_date,
    COALESCE(MIN_BY(device_category, event_ts), 'unknown') AS device_category,
    -- Session-scoped source / medium = the values on the session's entry hit (its first hit, normally the landing page view).
    MIN_BY(traffic_source, event_ts) AS entry_source,
    MIN_BY(traffic_medium, event_ts) AS entry_medium,
    COALESCE(MAX_BY(cart_size_band, CASE WHEN cart_size_band IS NOT NULL THEN event_ts END), 'Unknown') AS cart_size_band,
    MAX_BY(checkout_type, CASE WHEN checkout_type IS NOT NULL THEN event_ts END) AS checkout_type
  FROM keyed
  GROUP BY session_key
),
session_outcome AS (
  SELECT
    cs.session_key,
    ctx.session_date,
    ctx.device_category,
    ctx.cart_size_band,
    ctx.checkout_type,
    -- Default Channel Grouping on the entry hit's source / medium: identical rule set and labels to the BigQuery build, which reads the
    -- session-scoped session_traffic_source_last_click campaign fields.
    CASE
      WHEN ctx.entry_source IN ('(direct)', '') AND ctx.entry_medium IN ('(none)', '(not set)', '') THEN 'Direct'
      WHEN ctx.entry_medium RLIKE '^(.*cp.*|ppc|retargeting|paid.*)$'
           AND ctx.entry_source RLIKE 'google|bing|yahoo|duckduckgo|baidu|yandex|ecosia' THEN 'Paid Search'
      WHEN ctx.entry_medium RLIKE '^(.*cp.*|ppc|retargeting|paid.*)$'
           AND ctx.entry_source RLIKE 'facebook|instagram|tiktok|pinterest|linkedin|twitter|reddit|snapchat' THEN 'Paid Social'
      WHEN ctx.entry_medium IN ('display', 'banner', 'expandable', 'interstitial', 'cpm') THEN 'Display'
      WHEN ctx.entry_medium RLIKE '^(.*cp.*|ppc|retargeting|paid.*)$' THEN 'Paid Other'
      WHEN ctx.entry_medium IN ('social', 'social-network', 'social-media', 'sm', 'social network', 'social media')
           OR ctx.entry_source RLIKE 'facebook|instagram|tiktok|pinterest|linkedin|twitter|reddit|snapchat' THEN 'Organic Social'
      WHEN ctx.entry_medium = 'organic'
           OR ctx.entry_source RLIKE 'google|bing|yahoo|duckduckgo|baidu|yandex|ecosia' THEN 'Organic Search'
      WHEN ctx.entry_medium IN ('email', 'e-mail', 'e_mail', 'e mail', 'newsletter') THEN 'Email'
      WHEN ctx.entry_medium = 'affiliate' THEN 'Affiliates'
      WHEN ctx.entry_medium IN ('referral', 'app', 'link') THEN 'Referral'
      ELSE 'Unassigned'
    END AS channel_group,
    CASE WHEN co.session_key IS NOT NULL THEN 1 ELSE 0 END AS initiated_checkout
  FROM cart_sessions AS cs
  JOIN session_context AS ctx ON ctx.session_key = cs.session_key
  LEFT JOIN checkout_sessions AS co ON co.session_key = cs.session_key
)
SELECT
  session_date,
  device_category,
  channel_group,
  cart_size_band,
  COUNT(*) AS cart_sessions,
  SUM(initiated_checkout) AS sessions_initiating_checkout,
  SUM(CASE WHEN lower(COALESCE(checkout_type, '')) = 'guest' THEN 1 ELSE 0 END) AS guest_checkout_sessions,
  ROUND(100.0 * SUM(initiated_checkout) / NULLIF(COUNT(*), 0), 1) AS cart_to_checkout_rate_pct,
  CASE WHEN COUNT(*) >= 200 THEN TRUE ELSE FALSE END AS meets_minimum_base
FROM session_outcome
WHERE session_date BETWEEN date_sub(current_date(), 31) AND date_sub(current_date(), 4)
GROUP BY session_date, device_category, channel_group, cart_size_band
ORDER BY session_date, device_category, channel_group, cart_size_band;