Store / ZIP Code / Local Market

{
  "pageInstanceID": "order-confirmation-production",
  "page": {
    "pageInfo": {
      "pageID": "order-confirmation",
      "pageName": "checkout:order confirmation"
    },
    "category": {
      "primaryCategory": "checkout"
    }
  },
  "transaction": {
    "transactionID": "ORD-20260915-004817",
    "total": {
      "basePrice": 124.48,
      "currency": "USD",
      "shipping": 0,
      "transactionTotal": 124.48
    },
    "attributes": {
      "selectedStoreID": "S0142",
      "storeSelectionSource": "user_selected",
      "fulfillmentMethod": "bopis"
    },
    "item": [
      {
        "productInfo": {
          "productID": "SKU-88213",
          "sku": "SKU-88213",
          "productName": "Cordless Drill Driver 20V"
        },
        "category": {
          "primaryCategory": "Tools"
        },
        "quantity": 2,
        "price": {
          "basePrice": 49.99,
          "currency": "USD"
        },
        "attributes": {
          "fulfillingStoreID": "S0142",
          "fulfillmentMethod": "bopis"
        }
      },
      {
        "productInfo": {
          "productID": "SKU-10457",
          "sku": "SKU-10457",
          "productName": "Drill Bit Set 30-Piece"
        },
        "category": {
          "primaryCategory": "Tools"
        },
        "quantity": 1,
        "price": {
          "basePrice": 24.5,
          "currency": "USD"
        },
        "attributes": {
          "fulfillingStoreID": "S0142",
          "fulfillmentMethod": "bopis"
        }
      }
    ]
  },
  "event": [
    {
      "eventInfo": {
        "eventName": "purchase",
        "eventAction": "purchase"
      },
      "category": {
        "primaryCategory": "commerce"
      }
    }
  ]
}

{
  "event": "purchase",
  "selected_store_id": "S0142",
  "store_selection_source": "user_selected",
  "fulfillment_method": "bopis",
  "ecommerce": {
    "transaction_id": "ORD-20260915-004817",
    "value": 124.48,
    "shipping": 0,
    "currency": "USD",
    "items": [
      {
        "item_id": "SKU-88213",
        "item_name": "Cordless Drill Driver 20V",
        "item_category": "Tools",
        "price": 49.99,
        "quantity": 2,
        "location_id": "S0142",
        "fulfillment_method": "bopis"
      },
      {
        "item_id": "SKU-10457",
        "item_name": "Drill Bit Set 30-Piece",
        "item_category": "Tools",
        "price": 24.5,
        "quantity": 1,
        "location_id": "S0142",
        "fulfillment_method": "bopis"
      }
    ]
  }
}

{
  "event": "purchase",
  "eventInfo": {
    "eventName": "purchase"
  },
  "store": {
    "selectedStoreID": "S0142",
    "storeSelectionSource": "user_selected"
  },
  "transaction": {
    "transactionID": "ORD-20260915-004817",
    "currency": "USD",
    "shipping": 0,
    "transactionTotal": 124.48,
    "fulfillmentMethod": "bopis",
    "item": [
      {
        "sku": "SKU-88213",
        "productName": "Cordless Drill Driver 20V",
        "primaryCategory": "Tools",
        "quantity": 2,
        "basePrice": 49.99,
        "priceTotal": 99.98,
        "fulfillingStoreID": "S0142",
        "fulfillmentMethod": "bopis"
      },
      {
        "sku": "SKU-10457",
        "productName": "Drill Bit Set 30-Piece",
        "primaryCategory": "Tools",
        "quantity": 1,
        "basePrice": 24.5,
        "priceTotal": 24.5,
        "fulfillingStoreID": "S0142",
        "fulfillmentMethod": "bopis"
      }
    ]
  }
}

{
  "_id": "7f3c2a9e-5b1d-4e8a-9c64-2d0f8b1e4a37",
  "eventType": "commerce.purchases",
  "timestamp": "2026-09-15T19:14:22.000Z",
  "identityMap": {
    "ECID": [
      {
        "id": "45120843711254301391867250113298447913",
        "authenticatedState": "ambiguous",
        "primary": true
      }
    ]
  },
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "ORD-20260915-004817",
      "priceTotal": 124.48,
      "currencyCode": "USD"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-88213",
      "name": "Cordless Drill Driver 20V",
      "quantity": 2,
      "priceTotal": 99.98,
      "currencyCode": "USD",
      "_experience": {
        "analytics": {
          "customDimensions": {
            "eVars": {
              "eVar40": "S0142",
              "eVar41": "bopis"
            }
          }
        }
      }
    },
    {
      "SKU": "SKU-10457",
      "name": "Drill Bit Set 30-Piece",
      "quantity": 1,
      "priceTotal": 24.5,
      "currencyCode": "USD",
      "_experience": {
        "analytics": {
          "customDimensions": {
            "eVars": {
              "eVar40": "S0142",
              "eVar41": "bopis"
            }
          }
        }
      }
    }
  ],
  "_tenant": {
    "fulfillment": {
      "storeID": "S0142",
      "method": "bopis",
      "selectedStoreID": "S0142",
      "storeSelectionSource": "user_selected"
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Store / ZIP Code / Local Market: order lines and store-scoped browsing events per reporting date,
-- store local date, Store ID, store ZIP, Local Market, fulfillment method and attribution_basis.
-- Sources: GA4 export, Salesforce OMS order tables (restricted), store master (SCD Type 2),
-- postal-to-market crosswalk and trade-area ZIP lookup. Shopper ZIPs are resolved server-side and never emitted.
-- Re-run daily over the 35-day restatement window so OMS fulfilling-store reassignments are picked up; downstream loads replace open-period rows only and closed periods stay frozen.
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 35 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 1 DAY);
DECLARE reporting_tz STRING DEFAULT 'America/New_York';
DECLARE min_market_day_orders INT64 DEFAULT 10;
DECLARE unattributed_alert_share FLOAT64 DEFAULT 0.05;

WITH events_base AS (
  SELECT
    e.user_pseudo_id,
    e.event_name,
    e.event_timestamp,
    TIMESTAMP_MICROS(e.event_timestamp) AS event_ts,
    DATE(TIMESTAMP_MICROS(e.event_timestamp), reporting_tz) AS reporting_date,
    e.ecommerce.transaction_id,
    e.items,
    (SELECT MAX(IF(ep.key = 'selected_store_id', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL))
       FROM UNNEST(e.event_params) AS ep) AS selected_store_id,
    (SELECT MAX(IF(ep.key = 'store_selection_source', ep.value.string_value, NULL))
       FROM UNNEST(e.event_params) AS ep) AS store_selection_source,
    (SELECT MAX(IF(ep.key = 'fulfillment_method', ep.value.string_value, NULL))
       FROM UNNEST(e.event_params) AS ep) AS order_fulfillment_method
  FROM `your-project.analytics_123456789.events_*` AS e
  WHERE e._TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(start_date, INTERVAL 1 DAY))
                            AND FORMAT_DATE('%Y%m%d', DATE_ADD(end_date, INTERVAL 1 DAY))
    AND e.event_name IN ('page_view', 'view_item', 'add_to_cart', 'purchase')
),

-- Selected store in effect: only the event's own selected_store_id when store_selection_source = 'user_selected' (purchases included); a missing parameter means no store is selected.
-- Never filled forward or backfilled from other events; the 90-day expiry is enforced client-side, which then stops sending selected_store_id; geo_inferred ignored.
store_pref AS (
  SELECT
    b.*,
    IF(LOWER(b.store_selection_source) = 'user_selected', NULLIF(TRIM(b.selected_store_id), ''), NULL) AS selected_store_in_effect
  FROM events_base AS b
),

-- Store master as of event date: excludes test / training / pop-up demo stores, distribution centers, dark stores and vendor locations.
eligible_stores AS (
  SELECT
    TRIM(store_id) AS store_id,
    store_name,
    IF(UPPER(TRIM(country_code)) = 'US',
       LPAD(TRIM(CAST(postal_code AS STRING)), 5, '0'),
       UPPER(TRIM(CAST(postal_code AS STRING)))) AS store_postal_code,
    state_province,
    market_code,
    market_name,
    market_type,
    time_zone AS store_time_zone,
    valid_from,
    COALESCE(valid_to, DATE '9999-12-31') AS valid_to,
    closed_date
  FROM `your-project.store_master.store_location_history`
  WHERE location_type NOT IN ('distribution_center', 'dark_store', 'marketplace_vendor', 'drop_ship_vendor', 'popup_demo', 'training', 'test')
    AND COALESCE(is_test_store, FALSE) = FALSE
),

purchases AS (
  SELECT *
  FROM store_pref
  WHERE event_name = 'purchase'
    AND NULLIF(TRIM(transaction_id), '') IS NOT NULL
    AND transaction_id <> '(not set)'
  QUALIFY ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_timestamp) = 1
),

order_lines AS (
  SELECT
    p.transaction_id,
    p.event_ts,
    p.reporting_date,
    p.selected_store_in_effect,
    i.item_id,
    COALESCE(i.quantity, 0) AS units,
    COALESCE(i.item_revenue_in_usd, i.price_in_usd * i.quantity, 0) AS line_revenue,
    NULLIF(TRIM(i.location_id), '') AS hit_fulfilling_store_id,
    LOWER(COALESCE(
      (SELECT MAX(IF(ip.key = 'fulfillment_method', ip.value.string_value, NULL)) FROM UNNEST(i.item_params) AS ip),
      p.order_fulfillment_method
    )) AS hit_fulfillment_method
  FROM purchases AS p
  CROSS JOIN UNNEST(p.items) AS i
  WHERE p.reporting_date BETWEEN start_date AND end_date
),

-- Current fulfilling node per order line from OMS (reassignments win over the value captured at checkout).
-- Cancelled fulfillment lines are excluded, so they never earn store credit; the order line keeps its checkout revenue, so reconcile net-of-cancellation revenue against OMS.
-- Limitation: grain is order x SKU (latest active fulfillment line wins), so a SKU split across two fulfilling stores credits the whole line to one store; split lines need a fulfillment-order-line grain with quantity allocation.
oms_line_fulfillment AS (
  SELECT
    order_number,
    sku,
    NULLIF(TRIM(fulfilled_from_location_id), '') AS oms_fulfilling_store_id,
    LOWER(fulfillment_method) AS oms_fulfillment_method
  FROM `your-project.salesforce_oms.fulfillment_order_item`
  WHERE COALESCE(status, '') <> 'Cancelled'
  QUALIFY ROW_NUMBER() OVER (PARTITION BY order_number, sku ORDER BY last_modified_date DESC) = 1
),

-- Shipping postal code normalised to the market key: US ZIP5 (ZIP+4 stripped; only 4-digit values that lost a leading zero are padded; anything else stays unresolved), UK outward code, Canadian FSA.
oms_ship_to AS (
  SELECT
    order_number,
    UPPER(TRIM(deliver_to_country)) AS ship_country,
    CASE UPPER(TRIM(deliver_to_country))
      WHEN 'US' THEN CASE
        WHEN REGEXP_CONTAINS(TRIM(deliver_to_postal_code), r'^[0-9]{5}') THEN REGEXP_EXTRACT(TRIM(deliver_to_postal_code), r'^([0-9]{5})')
        WHEN REGEXP_CONTAINS(TRIM(deliver_to_postal_code), r'^[0-9]{4}$') THEN LPAD(TRIM(deliver_to_postal_code), 5, '0')
      END
      WHEN 'GB' THEN REGEXP_EXTRACT(UPPER(REPLACE(deliver_to_postal_code, ' ', '')), r'^([A-Z]{1,2}[0-9][A-Z0-9]?)[0-9][A-Z]{2}$')
      WHEN 'CA' THEN SUBSTR(UPPER(REPLACE(deliver_to_postal_code, ' ', '')), 1, 3)
      ELSE UPPER(REPLACE(deliver_to_postal_code, ' ', ''))
    END AS ship_postal_key
  FROM `your-project.salesforce_oms.order_delivery_group_summary`
  WHERE NULLIF(TRIM(deliver_to_postal_code), '') IS NOT NULL
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY order_number
    ORDER BY IF(LOWER(delivery_method_type) = 'ship_to_home', 0, 1), last_modified_date DESC
  ) = 1
),

-- Market from the ZIP crosswalk; a store only when exactly one eligible trade area explicitly contains the ZIP.
-- The postal-to-market crosswalk holds one market per country x postal key per validity period, so the join never fans out order lines.
ship_fallback AS (
  SELECT
    o.transaction_id,
    cw.market_code AS fallback_market_code,
    cw.market_name AS fallback_market_name,
    cw.market_type AS fallback_market_type,
    IF(COUNT(DISTINCT ts.store_id) = 1, MAX(ts.store_id), NULL) AS trade_area_store_id
  FROM (SELECT DISTINCT transaction_id, reporting_date FROM order_lines) AS o
  JOIN oms_ship_to AS st
    ON st.order_number = o.transaction_id
  JOIN `your-project.store_master.postal_market_crosswalk` AS cw
    ON cw.country_code = st.ship_country
   AND cw.postal_key = st.ship_postal_key
   AND o.reporting_date >= cw.valid_from
   AND o.reporting_date < COALESCE(cw.valid_to, DATE '9999-12-31')
  LEFT JOIN `your-project.store_master.trade_area_postal_codes` AS ta
    ON ta.country_code = st.ship_country
   AND ta.postal_key = st.ship_postal_key
   AND o.reporting_date >= ta.valid_from
   AND o.reporting_date < COALESCE(ta.valid_to, DATE '9999-12-31')
  LEFT JOIN eligible_stores AS ts
    ON ts.store_id = ta.store_id
   AND o.reporting_date >= ts.valid_from
   AND o.reporting_date < ts.valid_to
   AND (ts.closed_date IS NULL OR ts.closed_date >= o.reporting_date)
  GROUP BY o.transaction_id, cw.market_code, cw.market_name, cw.market_type
),

resolved_lines AS (
  SELECT
    l.transaction_id,
    l.event_ts,
    l.reporting_date,
    l.units,
    l.line_revenue,
    COALESCE(olf.oms_fulfillment_method, l.hit_fulfillment_method, '(not set)') AS fulfillment_method,
    fs.store_id AS fulfilling_store_id,
    ss.store_id AS selected_store_id,
    (sf.transaction_id IS NOT NULL
      AND COALESCE(olf.oms_fulfillment_method, l.hit_fulfillment_method, 'ship_to_home') NOT IN ('bopis', 'curbside', 'ship_to_store')) AS fallback_eligible,
    sf.trade_area_store_id,
    sf.fallback_market_code,
    sf.fallback_market_name,
    sf.fallback_market_type
  FROM order_lines AS l
  LEFT JOIN oms_line_fulfillment AS olf
    ON olf.order_number = l.transaction_id
   AND olf.sku = l.item_id
  LEFT JOIN eligible_stores AS fs
    ON fs.store_id = COALESCE(olf.oms_fulfilling_store_id, l.hit_fulfilling_store_id)
   AND l.reporting_date >= fs.valid_from
   AND l.reporting_date < fs.valid_to
   AND (fs.closed_date IS NULL OR fs.closed_date >= l.reporting_date)
  LEFT JOIN eligible_stores AS ss
    ON ss.store_id = l.selected_store_in_effect
   AND l.reporting_date >= ss.valid_from
   AND l.reporting_date < ss.valid_to
   AND (ss.closed_date IS NULL OR ss.closed_date >= l.reporting_date)
  LEFT JOIN ship_fallback AS sf
    ON sf.transaction_id = l.transaction_id
),

-- Precedence per line: fulfilling store, then selected store, then shipping-ZIP fallback, then unattributed.
located AS (
  SELECT
    'order' AS record_type,
    r.transaction_id,
    CAST(NULL AS STRING) AS user_pseudo_id,
    CAST(NULL AS STRING) AS event_name,
    r.event_ts,
    r.reporting_date,
    r.units,
    CAST(r.line_revenue AS FLOAT64) AS line_revenue,
    r.fulfillment_method,
    CASE
      WHEN r.fulfilling_store_id IS NOT NULL THEN 'fulfilling'
      WHEN r.selected_store_id IS NOT NULL THEN 'selected'
      WHEN r.fallback_eligible THEN 'shipping_zip_fallback'
      ELSE 'unattributed'
    END AS attribution_basis,
    CASE
      WHEN r.fulfilling_store_id IS NOT NULL THEN r.fulfilling_store_id
      WHEN r.selected_store_id IS NOT NULL THEN r.selected_store_id
      WHEN r.fallback_eligible THEN r.trade_area_store_id
    END AS resolved_store_id,
    r.fallback_market_code,
    r.fallback_market_name,
    r.fallback_market_type
  FROM resolved_lines AS r

  UNION ALL

  SELECT
    'browse' AS record_type,
    CAST(NULL AS STRING) AS transaction_id,
    b.user_pseudo_id,
    b.event_name,
    b.event_ts,
    b.reporting_date,
    0 AS units,
    0.0 AS line_revenue,
    '(not applicable)' AS fulfillment_method,
    IF(ss.store_id IS NOT NULL, 'selected', 'unattributed') AS attribution_basis,
    ss.store_id AS resolved_store_id,
    CAST(NULL AS STRING) AS fallback_market_code,
    CAST(NULL AS STRING) AS fallback_market_name,
    CAST(NULL AS STRING) AS fallback_market_type
  FROM store_pref AS b
  LEFT JOIN eligible_stores AS ss
    ON ss.store_id = b.selected_store_in_effect
   AND b.reporting_date >= ss.valid_from
   AND b.reporting_date < ss.valid_to
   AND (ss.closed_date IS NULL OR ss.closed_date >= b.reporting_date)
  WHERE b.event_name IN ('page_view', 'view_item', 'add_to_cart')
    AND b.reporting_date BETWEEN start_date AND end_date
),

located_dims AS (
  SELECT
    x.record_type,
    x.transaction_id,
    x.user_pseudo_id,
    x.event_name,
    x.reporting_date,
    IF(st.store_id IS NOT NULL, DATE(x.event_ts, st.store_time_zone), x.reporting_date) AS store_local_date,
    x.units,
    x.line_revenue,
    x.fulfillment_method,
    x.attribution_basis,
    CASE
      WHEN st.store_id IS NOT NULL THEN st.store_id
      WHEN x.attribution_basis = 'shipping_zip_fallback' THEN '(market level only)'
      ELSE '(unattributed)'
    END AS store_id,
    CASE
      WHEN st.store_id IS NOT NULL THEN COALESCE(st.store_name, '(not set)')
      WHEN x.attribution_basis = 'shipping_zip_fallback' THEN '(market level only)'
      ELSE '(unattributed)'
    END AS store_name,
    CASE
      WHEN st.store_id IS NOT NULL THEN COALESCE(st.store_postal_code, '(not set)')
      WHEN x.attribution_basis = 'shipping_zip_fallback' THEN '(market level only)'
      ELSE '(unattributed)'
    END AS store_postal_code,
    CASE
      WHEN st.store_id IS NOT NULL THEN COALESCE(st.state_province, '(not set)')
      WHEN x.attribution_basis = 'shipping_zip_fallback' THEN '(market level only)'
      ELSE '(unattributed)'
    END AS state_province,
    CASE
      WHEN st.store_id IS NOT NULL THEN COALESCE(st.market_code, '(not set)')
      WHEN x.attribution_basis = 'shipping_zip_fallback' THEN x.fallback_market_code
      ELSE '(unattributed)'
    END AS local_market_code,
    CASE
      WHEN st.store_id IS NOT NULL THEN COALESCE(st.market_name, '(not set)')
      WHEN x.attribution_basis = 'shipping_zip_fallback' THEN COALESCE(x.fallback_market_name, '(not set)')
      ELSE '(unattributed)'
    END AS local_market_name,
    CASE
      WHEN st.store_id IS NOT NULL THEN COALESCE(st.market_type, '(not set)')
      WHEN x.attribution_basis = 'shipping_zip_fallback' THEN COALESCE(x.fallback_market_type, '(not set)')
      ELSE '(unattributed)'
    END AS local_market_type
  FROM located AS x
  LEFT JOIN eligible_stores AS st
    ON st.store_id = x.resolved_store_id
   AND x.reporting_date >= st.valid_from
   AND x.reporting_date < st.valid_to
   AND (st.closed_date IS NULL OR st.closed_date >= x.reporting_date)
),

cells AS (
  SELECT
    reporting_date,
    store_local_date,
    store_id,
    store_name,
    store_postal_code,
    state_province,
    local_market_code,
    local_market_name,
    local_market_type,
    fulfillment_method,
    attribution_basis,
    COUNT(DISTINCT IF(record_type = 'order', transaction_id, NULL)) AS orders,
    COUNTIF(record_type = 'order') AS order_lines,
    SUM(IF(record_type = 'order', units, 0)) AS units,
    ROUND(SUM(IF(record_type = 'order', line_revenue, 0)), 2) AS line_revenue,
    COUNTIF(record_type = 'browse') AS browsing_events,
    COUNTIF(record_type = 'browse' AND event_name = 'view_item') AS view_item_events,
    COUNTIF(record_type = 'browse' AND event_name = 'add_to_cart') AS add_to_cart_events,
    COUNT(DISTINCT IF(record_type = 'browse', user_pseudo_id, NULL)) AS browsing_users
  FROM located_dims
  GROUP BY
    reporting_date, store_local_date, store_id, store_name, store_postal_code, state_province,
    local_market_code, local_market_name, local_market_type, fulfillment_method, attribution_basis
),

market_day AS (
  SELECT
    reporting_date,
    local_market_code,
    COUNT(DISTINCT IF(record_type = 'order', transaction_id, NULL)) AS market_day_orders
  FROM located_dims
  GROUP BY reporting_date, local_market_code
),

daily_unattributed AS (
  SELECT
    reporting_date,
    SAFE_DIVIDE(
      SUM(IF(record_type = 'order' AND attribution_basis = 'unattributed', line_revenue, 0)),
      SUM(IF(record_type = 'order', line_revenue, 0))
    ) AS unattributed_revenue_share
  FROM located_dims
  GROUP BY reporting_date
)

SELECT
  c.reporting_date,
  c.store_local_date,
  c.store_id,
  c.store_name,
  c.store_postal_code,
  c.state_province,
  c.local_market_code,
  c.local_market_name,
  c.local_market_type,
  c.fulfillment_method,
  c.attribution_basis,
  'USD' AS currency_code,
  c.orders,
  c.order_lines,
  c.units,
  c.line_revenue,
  c.browsing_events,
  c.view_item_events,
  c.add_to_cart_events,
  c.browsing_users,
  md.market_day_orders,
  COALESCE(md.market_day_orders, 0) < min_market_day_orders AS suppress_market_day_cell,
  ROUND(du.unattributed_revenue_share, 4) AS daily_unattributed_revenue_share,
  COALESCE(du.unattributed_revenue_share, 0) > unattributed_alert_share AS unattributed_revenue_alert
FROM cells AS c
LEFT JOIN market_day AS md
  ON md.reporting_date = c.reporting_date
 AND md.local_market_code = c.local_market_code
LEFT JOIN daily_unattributed AS du
  ON du.reporting_date = c.reporting_date
ORDER BY c.reporting_date, c.local_market_code, c.store_id, c.fulfillment_method, c.attribution_basis;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Store / ZIP Code / Local Market: order lines and store-scoped browsing events per reporting date,
-- store local date, Store ID, store ZIP, Local Market, fulfillment method, currency and attribution_basis.
-- Sources: web ExperienceEvent dataset, Salesforce OMS order datasets (restricted), store master (SCD Type 2),
-- postal-to-market crosswalk and trade-area ZIP lookup. Replace _tenant with the organisation tenant namespace.
-- productListItems eVar40 = fulfilling Store ID and eVar41 = fulfillment method are merchandising eVars (product syntax, purchase binding); replace with the eVars allocated in your report suite.
-- Re-run daily over the 35-day restatement window so OMS fulfilling-store reassignments are picked up; downstream loads replace open-period rows only and closed periods stay frozen.
WITH params AS (
  SELECT
    date_sub(current_date(), 35) AS start_date,
    date_sub(current_date(), 1) AS end_date,
    'America/New_York' AS reporting_tz,
    10 AS min_market_day_orders,
    0.05 AS unattributed_alert_share
),

events_base AS (
  SELECT
    e._id AS event_id,
    e.identityMap['ECID'][0].id AS ecid,
    CASE
      WHEN e.eventType = 'commerce.purchases' THEN 'purchase'
      WHEN e.eventType = 'commerce.productListAdds' THEN 'add_to_cart'
      WHEN e.eventType = 'commerce.productViews' THEN 'view_item'
      WHEN e.eventType = 'web.webpagedetails.pageViews' THEN 'page_view'
      ELSE e.eventType
    END AS event_name,
    e.timestamp AS event_ts,
    to_date(from_utc_timestamp(e.timestamp, p.reporting_tz)) AS reporting_date,
    e.commerce.order.purchaseID AS purchase_id,
    COALESCE(e.commerce.order.currencyCode, '(not set)') AS currency_code,
    e.productListItems AS product_list_items,
    NULLIF(TRIM(e._tenant.fulfillment.selectedStoreID), '') AS selected_store_id,
    LOWER(e._tenant.fulfillment.storeSelectionSource) AS store_selection_source,
    LOWER(NULLIF(TRIM(e._tenant.fulfillment.method), '')) AS order_fulfillment_method
  FROM web_commerce_experience_events e
  CROSS JOIN params p
  WHERE e.timestamp >= to_timestamp(date_sub(p.start_date, 1))
    AND e.timestamp < to_timestamp(date_add(p.end_date, 2))
    AND e.eventType IN ('web.webpagedetails.pageViews', 'commerce.productViews', 'commerce.productListAdds', 'commerce.purchases')
),

-- Selected store in effect: only the event's own selected_store_id when store_selection_source = 'user_selected' (purchases included); a missing parameter means no store is selected.
-- Never filled forward or backfilled from other events; the 90-day expiry is enforced client-side, which then stops sending selected_store_id; geo_inferred ignored.
store_pref AS (
  SELECT
    b.*,
    CASE WHEN b.store_selection_source = 'user_selected' THEN b.selected_store_id END AS selected_store_in_effect
  FROM events_base b
),

eligible_stores AS (
  SELECT
    TRIM(store_id) AS store_id,
    store_name,
    CASE
      WHEN UPPER(TRIM(country_code)) = 'US' THEN lpad(TRIM(CAST(postal_code AS STRING)), 5, '0')
      ELSE UPPER(TRIM(CAST(postal_code AS STRING)))
    END AS store_postal_code,
    state_province,
    market_code,
    market_name,
    market_type,
    time_zone AS store_time_zone,
    valid_from,
    COALESCE(valid_to, DATE '9999-12-31') AS valid_to,
    closed_date
  FROM store_master_location_history
  WHERE location_type NOT IN ('distribution_center', 'dark_store', 'marketplace_vendor', 'drop_ship_vendor', 'popup_demo', 'training', 'test')
    AND COALESCE(is_test_store, false) = false
),

purchases AS (
  SELECT *
  FROM (
    SELECT
      s.*,
      ROW_NUMBER() OVER (PARTITION BY s.purchase_id ORDER BY s.event_ts, s.event_id) AS purchase_rank
    FROM store_pref s
    WHERE s.event_name = 'purchase'
      AND NULLIF(TRIM(s.purchase_id), '') IS NOT NULL
  ) d
  WHERE d.purchase_rank = 1
),

order_lines AS (
  SELECT
    x.purchase_id,
    x.event_ts,
    x.reporting_date,
    x.currency_code,
    x.selected_store_in_effect,
    x.pli.SKU AS sku,
    COALESCE(x.pli.quantity, 0) AS units,
    CAST(COALESCE(x.pli.priceTotal, 0) AS DOUBLE) AS line_revenue,
    NULLIF(TRIM(x.pli._experience.analytics.customDimensions.eVars.eVar40), '') AS hit_fulfilling_store_id,
    LOWER(COALESCE(NULLIF(TRIM(x.pli._experience.analytics.customDimensions.eVars.eVar41), ''), x.order_fulfillment_method)) AS hit_fulfillment_method
  FROM (
    SELECT
      p.purchase_id,
      p.event_ts,
      p.reporting_date,
      p.currency_code,
      p.selected_store_in_effect,
      p.order_fulfillment_method,
      explode(p.product_list_items) AS pli
    FROM purchases p
  ) x
  CROSS JOIN params pr
  WHERE x.reporting_date BETWEEN pr.start_date AND pr.end_date
),

-- Current fulfilling node per order line from OMS (reassignments win over the value captured at checkout).
-- Cancelled fulfillment lines are excluded, so they never earn store credit; the order line keeps its checkout revenue, so reconcile net-of-cancellation revenue against OMS.
-- Limitation: grain is order x SKU (latest active fulfillment line wins), so a SKU split across two fulfilling stores credits the whole line to one store; split lines need a fulfillment-order-line grain with quantity allocation.
oms_line_fulfillment AS (
  SELECT order_number, sku, oms_fulfilling_store_id, oms_fulfillment_method
  FROM (
    SELECT
      order_number,
      sku,
      NULLIF(TRIM(fulfilled_from_location_id), '') AS oms_fulfilling_store_id,
      LOWER(fulfillment_method) AS oms_fulfillment_method,
      ROW_NUMBER() OVER (PARTITION BY order_number, sku ORDER BY last_modified_date DESC) AS rn
    FROM salesforce_oms_fulfillment_order_item
    WHERE COALESCE(status, '') <> 'Cancelled'
  ) f
  WHERE f.rn = 1
),

oms_ship_to AS (
  SELECT order_number, ship_country, ship_postal_key
  FROM (
    SELECT
      order_number,
      UPPER(TRIM(deliver_to_country)) AS ship_country,
      CASE UPPER(TRIM(deliver_to_country))
        WHEN 'US' THEN CASE
          WHEN TRIM(deliver_to_postal_code) RLIKE '^[0-9]{5}' THEN regexp_extract(TRIM(deliver_to_postal_code), '^([0-9]{5})', 1)
          WHEN TRIM(deliver_to_postal_code) RLIKE '^[0-9]{4}$' THEN lpad(TRIM(deliver_to_postal_code), 5, '0')
        END
        WHEN 'GB' THEN NULLIF(regexp_extract(UPPER(regexp_replace(deliver_to_postal_code, ' ', '')), '^([A-Z]{1,2}[0-9][A-Z0-9]?)[0-9][A-Z]{2}$', 1), '')
        WHEN 'CA' THEN substr(UPPER(regexp_replace(deliver_to_postal_code, ' ', '')), 1, 3)
        ELSE UPPER(regexp_replace(deliver_to_postal_code, ' ', ''))
      END AS ship_postal_key,
      ROW_NUMBER() OVER (
        PARTITION BY order_number
        ORDER BY CASE WHEN LOWER(delivery_method_type) = 'ship_to_home' THEN 0 ELSE 1 END, last_modified_date DESC
      ) AS rn
    FROM salesforce_oms_order_delivery_group_summary
    WHERE NULLIF(TRIM(deliver_to_postal_code), '') IS NOT NULL
  ) g
  WHERE g.rn = 1
),

-- Market from the ZIP crosswalk; a store only when exactly one eligible trade area explicitly contains the ZIP.
-- The postal-to-market crosswalk holds one market per country x postal key per validity period, so the join never fans out order lines.
ship_fallback AS (
  SELECT
    o.purchase_id,
    cw.market_code AS fallback_market_code,
    cw.market_name AS fallback_market_name,
    cw.market_type AS fallback_market_type,
    CASE WHEN COUNT(DISTINCT ts.store_id) = 1 THEN MAX(ts.store_id) END AS trade_area_store_id
  FROM (SELECT DISTINCT purchase_id, reporting_date FROM order_lines) o
  JOIN oms_ship_to st
    ON st.order_number = o.purchase_id
  JOIN store_master_postal_market_crosswalk cw
    ON cw.country_code = st.ship_country
   AND cw.postal_key = st.ship_postal_key
   AND o.reporting_date >= cw.valid_from
   AND o.reporting_date < COALESCE(cw.valid_to, DATE '9999-12-31')
  LEFT JOIN store_master_trade_area_postal_codes ta
    ON ta.country_code = st.ship_country
   AND ta.postal_key = st.ship_postal_key
   AND o.reporting_date >= ta.valid_from
   AND o.reporting_date < COALESCE(ta.valid_to, DATE '9999-12-31')
  LEFT JOIN eligible_stores ts
    ON ts.store_id = ta.store_id
   AND o.reporting_date >= ts.valid_from
   AND o.reporting_date < ts.valid_to
   AND (ts.closed_date IS NULL OR ts.closed_date >= o.reporting_date)
  GROUP BY o.purchase_id, cw.market_code, cw.market_name, cw.market_type
),

resolved_lines AS (
  SELECT
    l.purchase_id,
    l.event_ts,
    l.reporting_date,
    l.currency_code,
    l.units,
    l.line_revenue,
    COALESCE(olf.oms_fulfillment_method, l.hit_fulfillment_method, '(not set)') AS fulfillment_method,
    fs.store_id AS fulfilling_store_id,
    ss.store_id AS selected_store_id,
    (sf.purchase_id IS NOT NULL
      AND COALESCE(olf.oms_fulfillment_method, l.hit_fulfillment_method, 'ship_to_home') NOT IN ('bopis', 'curbside', 'ship_to_store')) AS fallback_eligible,
    sf.trade_area_store_id,
    sf.fallback_market_code,
    sf.fallback_market_name,
    sf.fallback_market_type
  FROM order_lines l
  LEFT JOIN oms_line_fulfillment olf
    ON olf.order_number = l.purchase_id
   AND olf.sku = l.sku
  LEFT JOIN eligible_stores fs
    ON fs.store_id = COALESCE(olf.oms_fulfilling_store_id, l.hit_fulfilling_store_id)
   AND l.reporting_date >= fs.valid_from
   AND l.reporting_date < fs.valid_to
   AND (fs.closed_date IS NULL OR fs.closed_date >= l.reporting_date)
  LEFT JOIN eligible_stores ss
    ON ss.store_id = l.selected_store_in_effect
   AND l.reporting_date >= ss.valid_from
   AND l.reporting_date < ss.valid_to
   AND (ss.closed_date IS NULL OR ss.closed_date >= l.reporting_date)
  LEFT JOIN ship_fallback sf
    ON sf.purchase_id = l.purchase_id
),

located AS (
  SELECT
    'order' AS record_type,
    r.purchase_id,
    CAST(NULL AS STRING) AS ecid,
    CAST(NULL AS STRING) AS event_name,
    r.event_ts,
    r.reporting_date,
    r.currency_code,
    CAST(r.units AS BIGINT) AS units,
    r.line_revenue,
    r.fulfillment_method,
    CASE
      WHEN r.fulfilling_store_id IS NOT NULL THEN 'fulfilling'
      WHEN r.selected_store_id IS NOT NULL THEN 'selected'
      WHEN r.fallback_eligible THEN 'shipping_zip_fallback'
      ELSE 'unattributed'
    END AS attribution_basis,
    CASE
      WHEN r.fulfilling_store_id IS NOT NULL THEN r.fulfilling_store_id
      WHEN r.selected_store_id IS NOT NULL THEN r.selected_store_id
      WHEN r.fallback_eligible THEN r.trade_area_store_id
    END AS resolved_store_id,
    r.fallback_market_code,
    r.fallback_market_name,
    r.fallback_market_type
  FROM resolved_lines r

  UNION ALL

  SELECT
    'browse' AS record_type,
    CAST(NULL AS STRING) AS purchase_id,
    b.ecid,
    b.event_name,
    b.event_ts,
    b.reporting_date,
    '(not applicable)' AS currency_code,
    CAST(0 AS BIGINT) AS units,
    CAST(0 AS DOUBLE) AS line_revenue,
    '(not applicable)' AS fulfillment_method,
    CASE WHEN ss.store_id IS NOT NULL THEN 'selected' ELSE 'unattributed' END AS attribution_basis,
    ss.store_id AS resolved_store_id,
    CAST(NULL AS STRING) AS fallback_market_code,
    CAST(NULL AS STRING) AS fallback_market_name,
    CAST(NULL AS STRING) AS fallback_market_type
  FROM store_pref b
  CROSS JOIN params pr
  LEFT JOIN eligible_stores ss
    ON ss.store_id = b.selected_store_in_effect
   AND b.reporting_date >= ss.valid_from
   AND b.reporting_date < ss.valid_to
   AND (ss.closed_date IS NULL OR ss.closed_date >= b.reporting_date)
  WHERE b.event_name IN ('page_view', 'view_item', 'add_to_cart')
    AND b.reporting_date BETWEEN pr.start_date AND pr.end_date
),

located_dims AS (
  SELECT
    x.record_type,
    x.purchase_id,
    x.ecid,
    x.event_name,
    x.reporting_date,
    CASE
      WHEN st.store_id IS NOT NULL THEN to_date(from_utc_timestamp(x.event_ts, st.store_time_zone))
      ELSE x.reporting_date
    END AS store_local_date,
    x.currency_code,
    x.units,
    x.line_revenue,
    x.fulfillment_method,
    x.attribution_basis,
    CASE
      WHEN st.store_id IS NOT NULL THEN st.store_id
      WHEN x.attribution_basis = 'shipping_zip_fallback' THEN '(market level only)'
      ELSE '(unattributed)'
    END AS store_id,
    CASE
      WHEN st.store_id IS NOT NULL THEN COALESCE(st.store_name, '(not set)')
      WHEN x.attribution_basis = 'shipping_zip_fallback' THEN '(market level only)'
      ELSE '(unattributed)'
    END AS store_name,
    CASE
      WHEN st.store_id IS NOT NULL THEN COALESCE(st.store_postal_code, '(not set)')
      WHEN x.attribution_basis = 'shipping_zip_fallback' THEN '(market level only)'
      ELSE '(unattributed)'
    END AS store_postal_code,
    CASE
      WHEN st.store_id IS NOT NULL THEN COALESCE(st.state_province, '(not set)')
      WHEN x.attribution_basis = 'shipping_zip_fallback' THEN '(market level only)'
      ELSE '(unattributed)'
    END AS state_province,
    CASE
      WHEN st.store_id IS NOT NULL THEN COALESCE(st.market_code, '(not set)')
      WHEN x.attribution_basis = 'shipping_zip_fallback' THEN x.fallback_market_code
      ELSE '(unattributed)'
    END AS local_market_code,
    CASE
      WHEN st.store_id IS NOT NULL THEN COALESCE(st.market_name, '(not set)')
      WHEN x.attribution_basis = 'shipping_zip_fallback' THEN COALESCE(x.fallback_market_name, '(not set)')
      ELSE '(unattributed)'
    END AS local_market_name,
    CASE
      WHEN st.store_id IS NOT NULL THEN COALESCE(st.market_type, '(not set)')
      WHEN x.attribution_basis = 'shipping_zip_fallback' THEN COALESCE(x.fallback_market_type, '(not set)')
      ELSE '(unattributed)'
    END AS local_market_type
  FROM located x
  LEFT JOIN eligible_stores st
    ON st.store_id = x.resolved_store_id
   AND x.reporting_date >= st.valid_from
   AND x.reporting_date < st.valid_to
   AND (st.closed_date IS NULL OR st.closed_date >= x.reporting_date)
),

cells AS (
  SELECT
    reporting_date,
    store_local_date,
    store_id,
    store_name,
    store_postal_code,
    state_province,
    local_market_code,
    local_market_name,
    local_market_type,
    fulfillment_method,
    attribution_basis,
    currency_code,
    COUNT(DISTINCT CASE WHEN record_type = 'order' THEN purchase_id END) AS orders,
    SUM(CASE WHEN record_type = 'order' THEN 1 ELSE 0 END) AS order_lines,
    SUM(CASE WHEN record_type = 'order' THEN units ELSE 0 END) AS units,
    ROUND(SUM(CASE WHEN record_type = 'order' THEN line_revenue ELSE 0 END), 2) AS line_revenue,
    SUM(CASE WHEN record_type = 'browse' THEN 1 ELSE 0 END) AS browsing_events,
    SUM(CASE WHEN record_type = 'browse' AND event_name = 'view_item' THEN 1 ELSE 0 END) AS view_item_events,
    SUM(CASE WHEN record_type = 'browse' AND event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS add_to_cart_events,
    COUNT(DISTINCT CASE WHEN record_type = 'browse' THEN ecid END) AS browsing_users
  FROM located_dims
  GROUP BY
    reporting_date, store_local_date, store_id, store_name, store_postal_code, state_province,
    local_market_code, local_market_name, local_market_type, fulfillment_method, attribution_basis, currency_code
),

market_day AS (
  SELECT
    reporting_date,
    local_market_code,
    COUNT(DISTINCT CASE WHEN record_type = 'order' THEN purchase_id END) AS market_day_orders
  FROM located_dims
  GROUP BY reporting_date, local_market_code
),

daily_unattributed AS (
  SELECT
    reporting_date,
    currency_code,
    SUM(CASE WHEN attribution_basis = 'unattributed' THEN line_revenue ELSE 0 END)
      / NULLIF(SUM(line_revenue), 0) AS unattributed_revenue_share
  FROM located_dims
  WHERE record_type = 'order'
  GROUP BY reporting_date, currency_code
),

-- Browse rows carry currency_code '(not applicable)', so they take the highest per-currency unattributed share of the day (one row per date, no fan-out); for a single-currency retailer this equals the GA4 daily share.
daily_unattributed_any AS (
  SELECT
    reporting_date,
    MAX(unattributed_revenue_share) AS unattributed_revenue_share
  FROM daily_unattributed
  GROUP BY reporting_date
)

SELECT
  c.reporting_date,
  c.store_local_date,
  c.store_id,
  c.store_name,
  c.store_postal_code,
  c.state_province,
  c.local_market_code,
  c.local_market_name,
  c.local_market_type,
  c.fulfillment_method,
  c.attribution_basis,
  c.currency_code,
  c.orders,
  c.order_lines,
  c.units,
  c.line_revenue,
  c.browsing_events,
  c.view_item_events,
  c.add_to_cart_events,
  c.browsing_users,
  md.market_day_orders,
  COALESCE(md.market_day_orders, 0) < p.min_market_day_orders AS suppress_market_day_cell,
  ROUND(CASE WHEN c.currency_code = '(not applicable)' THEN dua.unattributed_revenue_share ELSE du.unattributed_revenue_share END, 4) AS daily_unattributed_revenue_share,
  COALESCE(CASE WHEN c.currency_code = '(not applicable)' THEN dua.unattributed_revenue_share ELSE du.unattributed_revenue_share END, 0) > p.unattributed_alert_share AS unattributed_revenue_alert
FROM cells c
CROSS JOIN params p
LEFT JOIN market_day md
  ON md.reporting_date = c.reporting_date
 AND md.local_market_code = c.local_market_code
LEFT JOIN daily_unattributed du
  ON du.reporting_date = c.reporting_date
 AND du.currency_code = c.currency_code
LEFT JOIN daily_unattributed_any dua
  ON dua.reporting_date = c.reporting_date
ORDER BY c.reporting_date, c.local_market_code, c.store_id, c.fulfillment_method, c.attribution_basis