Item Category

{
  "product": [
    {
      "productInfo": {
        "productID": "SKU-48213",
        "productName": "Trail Runner GTX Women's",
        "manufacturer": "Northpeak"
      },
      "category": {
        "primaryCategory": "Women",
        "subCategory1": "Shoes",
        "subCategory2": "Running",
        "subCategory3": "Trail",
        "subCategory4": "Waterproof"
      },
      "attributes": {
        "variant": "Grey / 7.5"
      },
      "price": {
        "basePrice": 129.99,
        "currency": "USD"
      },
      "quantity": 1
    }
  ],
  "event": [
    {
      "eventInfo": {
        "eventName": "view_item",
        "eventAction": "Product View"
      },
      "category": {
        "primaryCategory": "Commerce"
      }
    }
  ]
}

{
  "event": "view_item",
  "ecommerce": {
    "currency": "USD",
    "value": 129.99,
    "items": [
      {
        "item_id": "SKU-48213",
        "item_name": "Trail Runner GTX Women's",
        "item_brand": "Northpeak",
        "item_category": "Women",
        "item_category2": "Shoes",
        "item_category3": "Running",
        "item_category4": "Trail",
        "item_category5": "Waterproof",
        "item_variant": "Grey / 7.5",
        "price": 129.99,
        "quantity": 1
      }
    ]
  }
}

{
  "event": "prodView",
  "eventInfo": {
    "path": "product.SKU-48213"
  },
  "product": {
    "SKU-48213": {
      "productID": "SKU-48213",
      "productName": "Trail Runner GTX Women's",
      "brand": "Northpeak",
      "category": {
        "primaryCategory": "Women",
        "subCategory1": "Shoes",
        "subCategory2": "Running",
        "subCategory3": "Trail",
        "subCategory4": "Waterproof"
      },
      "variant": "Grey / 7.5",
      "price": 129.99,
      "currency": "USD",
      "quantity": 1,
      "merchandisingCategory": "Women"
    }
  }
}

{
  "eventType": "commerce.productViews",
  "commerce": {
    "productViews": {
      "value": 1
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-48213",
      "name": "Trail Runner GTX Women's",
      "priceTotal": 129.99,
      "currencyCode": "USD",
      "quantity": 1,
      "productCategories": [
        {
          "categoryID": "CAT-1001",
          "categoryName": "Women",
          "categoryPath": "Women/Shoes/Running/Trail/Waterproof"
        }
      ],
      "_experience": {
        "analytics": {
          "customDimensions": {
            "eVars": {
              "eVar10": "Women"
            }
          }
        }
      }
    }
  ]
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Item Category: item-scoped funnel by collected category path for the last 7 complete days.
-- Only daily export tables (events_YYYYMMDD) are read; events_intraday_* tables are provisional and excluded.
-- Internal, QA and known-bot traffic is assumed removed by the property's active data filters.
-- Reporting time zone: America/New_York stands in for the property time zone; replace it with your own.
-- item_revenue is USD: item_revenue_in_usd, falling back to price_in_usd x quantity.
-- Assumed PIM table: `your-project.pim.product_category_history`
--   (item_id STRING, category_level1 STRING, valid_from DATE, valid_to DATE; NULL valid_to = current mapping).
-- pim_mismatch_item_revenue compares the as-collected level 1 with the PIM value valid on the event date (as-was).
-- For a current-hierarchy restatement, join on valid_to IS NULL instead and group by the PIM value.
WITH item_rows AS (
  SELECT
    PARSE_DATE('%Y%m%d', e.event_date) AS event_date,
    e.event_name,
    e.event_timestamp,
    e.user_pseudo_id,
    COALESCE(
      NULLIF(e.ecommerce.transaction_id, '(not set)'),
      (SELECT MAX(IF(ep.key = 'transaction_id', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep)
    ) AS transaction_id,
    it.item_id,
    it.item_variant,
    COALESCE(NULLIF(TRIM(it.item_category), ''), '(not set)') AS item_category,
    NULLIF(TRIM(it.item_category2), '') AS item_category2,
    ARRAY_TO_STRING([
      COALESCE(NULLIF(TRIM(it.item_category), ''), '(not set)'),
      NULLIF(TRIM(it.item_category2), ''),
      NULLIF(TRIM(it.item_category3), ''),
      NULLIF(TRIM(it.item_category4), ''),
      NULLIF(TRIM(it.item_category5), '')
    ], ' / ') AS category_path,
    COALESCE(it.quantity, 1) AS quantity,
    -- USD
    COALESCE(it.item_revenue_in_usd, it.price_in_usd * COALESCE(it.quantity, 1), 0) AS item_revenue
  FROM `your-project.analytics_123456789.events_*` AS e,
    UNNEST(e.items) AS it
  WHERE _TABLE_SUFFIX NOT LIKE 'intraday%'
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 7 DAY))
      AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 1 DAY))
    AND e.event_name IN ('view_item', 'add_to_cart', 'purchase')
),
-- Purchased items are counted once per distinct transaction x item x variant (duplicate purchase hits removed).
purchase_items AS (
  SELECT
    p.event_name,
    p.item_category,
    p.item_category2,
    p.category_path,
    p.quantity,
    p.item_revenue,
    m.category_level1 AS pim_category_as_was
  FROM (
    SELECT *
    FROM item_rows
    WHERE event_name = 'purchase'
    QUALIFY ROW_NUMBER() OVER (
      PARTITION BY
        COALESCE(transaction_id, CONCAT(IFNULL(user_pseudo_id, ''), '|', CAST(event_timestamp AS STRING))),
        IFNULL(item_id, ''),
        IFNULL(item_variant, '')
      ORDER BY event_timestamp
    ) = 1
  ) AS p
  LEFT JOIN `your-project.pim.product_category_history` AS m
    ON m.item_id = p.item_id
    AND p.event_date >= m.valid_from
    AND (m.valid_to IS NULL OR p.event_date < m.valid_to)
),
unified AS (
  SELECT event_name, item_category, item_category2, category_path, quantity, item_revenue,
    CAST(NULL AS STRING) AS pim_category_as_was
  FROM item_rows
  WHERE event_name IN ('view_item', 'add_to_cart')
  UNION ALL
  SELECT event_name, item_category, item_category2, category_path, quantity, item_revenue, pim_category_as_was
  FROM purchase_items
),
by_category AS (
  SELECT
    item_category,
    item_category2,
    category_path,
    SUM(IF(event_name = 'view_item', quantity, 0)) AS item_views,
    SUM(IF(event_name = 'add_to_cart', quantity, 0)) AS items_added_to_cart,
    SUM(IF(event_name = 'purchase', quantity, 0)) AS items_purchased,
    SUM(IF(event_name = 'purchase', item_revenue, 0)) AS item_revenue,
    SUM(IF(event_name = 'purchase' AND pim_category_as_was IS NOT NULL AND pim_category_as_was <> item_category, item_revenue, 0)) AS pim_mismatch_item_revenue
  FROM unified
  GROUP BY item_category, item_category2, category_path
)
-- view_to_cart_rate_pct denominator: all item units viewed in the category path.
-- not_set_share_above_threshold flags a weekly (not set) share of item revenue above 2% (top of the 1-2% tolerance).
SELECT
  item_category,
  item_category2,
  category_path,
  item_views,
  items_added_to_cart,
  ROUND(100 * SAFE_DIVIDE(items_added_to_cart, item_views), 2) AS view_to_cart_rate_pct,
  items_purchased,
  ROUND(item_revenue, 2) AS item_revenue,
  ROUND(100 * SAFE_DIVIDE(item_revenue, SUM(item_revenue) OVER ()), 2) AS item_revenue_share_pct,
  ROUND(pim_mismatch_item_revenue, 2) AS pim_mismatch_item_revenue,
  ROUND(100 * SAFE_DIVIDE(SUM(IF(item_category = '(not set)', item_revenue, 0)) OVER (), SUM(item_revenue) OVER ()), 2) AS not_set_item_revenue_share_pct,
  COALESCE(SAFE_DIVIDE(SUM(IF(item_category = '(not set)', item_revenue, 0)) OVER (), SUM(item_revenue) OVER ()) > 0.02, FALSE) AS not_set_share_above_threshold
FROM by_category
ORDER BY item_revenue DESC

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Item Category: item-scoped funnel by collected category path for the last 7 complete days.
-- Assumed datasets: web_experience_events (XDM ExperienceEvent with Commerce details and productListItems)
--   and pim_product_category_history (item_id, category_level1, valid_from DATE, valid_to DATE; NULL valid_to = current mapping).
-- The current day is excluded while streaming ingestion is still landing (the equivalent of the GA4 intraday table).
-- Reporting time zone: America/New_York stands in for the property time zone; replace it in every date expression.
-- Assumed FX table: fx_daily_rates (rate_date DATE, currency_code STRING, usd_rate DOUBLE, loaded_at TIMESTAMP).
-- item_revenue is USD: priceTotal x the daily usd_rate for the line currency and event date (one rate per day x currency, latest load).
-- Non-USD lines with no rate for the day contribute 0 revenue until the rate lands.
-- Level 1 comes from the first categoryPath segment, then productCategories[0].categoryName, then merchandising eVar10.
-- SKU is the variant-level identifier, so purchases are deduplicated per order x SKU.
WITH raw_items AS (
  SELECT
    to_date(from_utc_timestamp(e.timestamp, 'America/New_York')) AS event_date,
    CASE
      WHEN e.eventType = 'commerce.productViews' OR e.commerce.productViews.value > 0 THEN 'view_item'
      WHEN e.eventType = 'commerce.productListAdds' OR e.commerce.productListAdds.value > 0 THEN 'add_to_cart'
      WHEN e.eventType = 'commerce.purchases' OR e.commerce.purchases.value > 0 THEN 'purchase'
    END AS event_name,
    e.timestamp AS event_ts,
    COALESCE(NULLIF(e.commerce.order.purchaseID, ''), e._id) AS order_key,
    pli.SKU AS item_id,
    CASE WHEN size(pli.productCategories) > 0 THEN pli.productCategories[0].categoryName END AS category_name_l1,
    CASE WHEN size(pli.productCategories) > 0 THEN split(pli.productCategories[0].categoryPath, '/') END AS path_parts,
    pli._experience.analytics.customDimensions.eVars.eVar10 AS merch_evar10,
    COALESCE(pli.quantity, 1) AS quantity,
    COALESCE(pli.priceTotal, 0) AS item_revenue_local,
    COALESCE(NULLIF(pli.currencyCode, ''), e.commerce.order.currencyCode) AS currency_code
  FROM web_experience_events e
  LATERAL VIEW explode(e.productListItems) pli_t AS pli
  WHERE e.timestamp >= to_timestamp(date_sub(to_date(from_utc_timestamp(current_timestamp(), 'America/New_York')), 8))
    AND e.timestamp < to_timestamp(date_add(to_date(from_utc_timestamp(current_timestamp(), 'America/New_York')), 1))
),
leveled AS (
  SELECT
    event_date,
    event_name,
    event_ts,
    order_key,
    item_id,
    quantity,
    item_revenue_local,
    currency_code,
    COALESCE(
      NULLIF(trim(CASE WHEN size(path_parts) > 0 THEN path_parts[0] END), ''),
      NULLIF(trim(category_name_l1), ''),
      NULLIF(trim(merch_evar10), ''),
      '(not set)'
    ) AS item_category,
    NULLIF(trim(CASE WHEN size(path_parts) > 1 THEN path_parts[1] END), '') AS item_category2,
    NULLIF(trim(CASE WHEN size(path_parts) > 2 THEN path_parts[2] END), '') AS item_category3,
    NULLIF(trim(CASE WHEN size(path_parts) > 3 THEN path_parts[3] END), '') AS item_category4,
    NULLIF(trim(CASE WHEN size(path_parts) > 4 THEN path_parts[4] END), '') AS item_category5
  FROM raw_items
  WHERE event_name IS NOT NULL
    AND event_date BETWEEN date_sub(to_date(from_utc_timestamp(current_timestamp(), 'America/New_York')), 7) AND date_sub(to_date(from_utc_timestamp(current_timestamp(), 'America/New_York')), 1)
),
-- One FX rate per rate_date x currency (latest load), so the item-line join cannot multiply lines.
fx_rates AS (
  SELECT rate_date, currency_code, usd_rate
  FROM (
    SELECT
      r.rate_date,
      r.currency_code,
      r.usd_rate,
      ROW_NUMBER() OVER (PARTITION BY r.rate_date, r.currency_code ORDER BY r.loaded_at DESC) AS rn
    FROM fx_daily_rates r
  ) fr
  WHERE fr.rn = 1
),
item_rows AS (
  SELECT
    l.event_date,
    l.event_name,
    l.event_ts,
    l.order_key,
    l.item_id,
    l.quantity,
    -- USD
    l.item_revenue_local
      * COALESCE(fx.usd_rate, CASE WHEN COALESCE(l.currency_code, 'USD') = 'USD' THEN 1 END, 0) AS item_revenue,
    l.item_category,
    l.item_category2,
    concat_ws(' / ', l.item_category, l.item_category2, l.item_category3, l.item_category4, l.item_category5) AS category_path
  FROM leveled l
  LEFT JOIN fx_rates fx
    ON fx.rate_date = l.event_date
    AND fx.currency_code = l.currency_code
),
purchase_items AS (
  SELECT
    p.event_name,
    p.item_category,
    p.item_category2,
    p.category_path,
    p.quantity,
    p.item_revenue,
    m.category_level1 AS pim_category_as_was
  FROM (
    SELECT
      r.*,
      ROW_NUMBER() OVER (PARTITION BY r.order_key, COALESCE(r.item_id, '') ORDER BY r.event_ts) AS rn
    FROM item_rows r
    WHERE r.event_name = 'purchase'
  ) p
  LEFT JOIN pim_product_category_history m
    ON m.item_id = p.item_id
    AND p.event_date >= m.valid_from
    AND (m.valid_to IS NULL OR p.event_date < m.valid_to)
  WHERE p.rn = 1
),
unified AS (
  SELECT event_name, item_category, item_category2, category_path, quantity, item_revenue,
    CAST(NULL AS STRING) AS pim_category_as_was
  FROM item_rows
  WHERE event_name IN ('view_item', 'add_to_cart')
  UNION ALL
  SELECT event_name, item_category, item_category2, category_path, quantity, item_revenue, pim_category_as_was
  FROM purchase_items
),
by_category AS (
  SELECT
    item_category,
    item_category2,
    category_path,
    SUM(CASE WHEN event_name = 'view_item' THEN quantity ELSE 0 END) AS item_views,
    SUM(CASE WHEN event_name = 'add_to_cart' THEN quantity ELSE 0 END) AS items_added_to_cart,
    SUM(CASE WHEN event_name = 'purchase' THEN quantity ELSE 0 END) AS items_purchased,
    SUM(CASE WHEN event_name = 'purchase' THEN item_revenue ELSE 0 END) AS item_revenue,
    SUM(CASE WHEN event_name = 'purchase' AND pim_category_as_was IS NOT NULL AND pim_category_as_was <> item_category THEN item_revenue ELSE 0 END) AS pim_mismatch_item_revenue
  FROM unified
  GROUP BY item_category, item_category2, category_path
)
-- view_to_cart_rate_pct denominator: all item units viewed in the category path.
-- not_set_share_above_threshold flags a weekly (not set) share of item revenue above 2% (top of the 1-2% tolerance).
SELECT
  item_category,
  item_category2,
  category_path,
  item_views,
  items_added_to_cart,
  ROUND(100 * items_added_to_cart / NULLIF(item_views, 0), 2) AS view_to_cart_rate_pct,
  items_purchased,
  ROUND(item_revenue, 2) AS item_revenue,
  ROUND(100 * item_revenue / NULLIF(SUM(item_revenue) OVER (), 0), 2) AS item_revenue_share_pct,
  ROUND(pim_mismatch_item_revenue, 2) AS pim_mismatch_item_revenue,
  ROUND(100 * SUM(CASE WHEN item_category = '(not set)' THEN item_revenue ELSE 0 END) OVER () / NULLIF(SUM(item_revenue) OVER (), 0), 2) AS not_set_item_revenue_share_pct,
  COALESCE(SUM(CASE WHEN item_category = '(not set)' THEN item_revenue ELSE 0 END) OVER () / NULLIF(SUM(item_revenue) OVER (), 0) > 0.02, false) AS not_set_share_above_threshold
FROM by_category
ORDER BY item_revenue DESC