Product Area Interest

{
  "page": {
    "pageInfo": {
      "pageID": "pdp:SKU-48213",
      "pageName": "product:footwear:running-shoes:trail-runner-gtx",
      "destinationURL": "https://www.example-shop.com/p/trail-runner-gtx/SKU-48213"
    },
    "category": {
      "pageType": "product",
      "primaryCategory": "Footwear",
      "subCategory1": "Running Shoes"
    }
  },
  "product": [
    {
      "productInfo": {
        "productID": "SKU-48213",
        "productName": "Trail Runner GTX",
        "manufacturer": "Northpeak"
      },
      "category": {
        "primaryCategory": "Footwear",
        "subCategory1": "Running Shoes"
      },
      "price": {
        "basePrice": 129,
        "currency": "USD"
      },
      "quantity": 1
    }
  ],
  "event": [
    {
      "eventInfo": {
        "eventName": "productView",
        "eventAction": "view_item",
        "timeStamp": "2026-09-14T15:42:07.000Z"
      }
    }
  ],
  "user": [
    {
      "profile": [
        {
          "profileInfo": {
            "profileID": "9f2c7b1e4a6d8053c1e7f94b2a0d6e3857c4b19f0a2e6d7c3b8f5a1e9d4c2b70"
          },
          "attributes": {
            "loginStatus": "authenticated",
            "idHashAlgorithm": "SHA-256"
          }
        }
      ]
    }
  ],
  "privacy": {
    "accessCategories": [
      {
        "categoryName": "analytics",
        "domains": [
          "www.example-shop.com"
        ]
      },
      {
        "categoryName": "ad_personalization",
        "domains": [
          "www.example-shop.com"
        ]
      }
    ]
  }
}

{
  "event": "view_item",
  "user_id": "9f2c7b1e4a6d8053c1e7f94b2a0d6e3857c4b19f0a2e6d7c3b8f5a1e9d4c2b70",
  "page_type": "product",
  "ecommerce": {
    "currency": "USD",
    "value": 129,
    "items": [
      {
        "item_id": "SKU-48213",
        "item_name": "Trail Runner GTX",
        "item_brand": "Northpeak",
        "item_category": "Footwear",
        "item_category2": "Running Shoes",
        "item_variant": "Grey / US 10",
        "price": 129,
        "quantity": 1,
        "index": 0
      }
    ]
  }
}

{
  "event": "productView",
  "eventInfo": {
    "path": "product.SKU-48213"
  },
  "page": {
    "pageName": "product:footwear:running-shoes:trail-runner-gtx",
    "pageType": "product",
    "siteSection": "Footwear"
  },
  "product": {
    "SKU-48213": {
      "productID": "SKU-48213",
      "productName": "Trail Runner GTX",
      "brand": "Northpeak",
      "productCategory": "Footwear",
      "productSubCategory": "Running Shoes",
      "variant": "Grey / US 10",
      "price": 129,
      "currency": "USD",
      "quantity": 1
    }
  },
  "user": {
    "authState": "authenticated",
    "crmIdHashed": "9f2c7b1e4a6d8053c1e7f94b2a0d6e3857c4b19f0a2e6d7c3b8f5a1e9d4c2b70"
  },
  "consent": {
    "analytics": "granted",
    "adPersonalization": "granted"
  }
}

{
  "eventType": "commerce.productViews",
  "timestamp": "2026-09-14T15:42:07.000Z",
  "identityMap": {
    "ECID": [
      {
        "id": "45729385616230459802281456632901448113",
        "authenticatedState": "ambiguous",
        "primary": true
      }
    ],
    "CRMID": [
      {
        "id": "9f2c7b1e4a6d8053c1e7f94b2a0d6e3857c4b19f0a2e6d7c3b8f5a1e9d4c2b70",
        "authenticatedState": "authenticated",
        "primary": false
      }
    ]
  },
  "web": {
    "webPageDetails": {
      "name": "product:footwear:running-shoes:trail-runner-gtx",
      "URL": "https://www.example-shop.com/p/trail-runner-gtx/SKU-48213",
      "siteSection": "Footwear"
    }
  },
  "commerce": {
    "productViews": {
      "value": 1
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-48213",
      "name": "Trail Runner GTX",
      "productCategories": [
        {
          "categoryID": "footwear",
          "categoryName": "Footwear",
          "hierarchy": "Footwear/Running Shoes"
        }
      ],
      "priceTotal": 129,
      "currencyCode": "USD",
      "quantity": 1
    }
  ],
  "consents": {
    "collect": {
      "val": "y"
    },
    "personalize": {
      "content": {
        "val": "y"
      }
    },
    "share": {
      "val": "y"
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Product Area Interest (Category Affinity): one row per user per snapshot date.
-- Window: snapshot_date - 29 days .. snapshot_date (30 days, property reporting time zone); the daily run uses snapshot_date = yesterday.
-- Backfill: widen the params range and move both _TABLE_SUFFIX lower bounds to snapshot_start - 29 days.
-- Ad consent: ad_activation_eligible uses privacy_info.ads_storage as the proxy for ad_personalization, because the GA4 BigQuery export does not carry ad_personalization; where a consent-log table exists, a join on it replaces this proxy.
-- Consent data absent: properties without Consent Mode have NULL privacy_info fields and return no rows under the strict analytics_storage = 'Yes' filter.
-- reporting_tz must equal the GA4 property reporting time zone (calculation note 3).
DECLARE reporting_tz STRING DEFAULT 'America/New_York';
WITH params AS (
  SELECT
    DATE_SUB(CURRENT_DATE(reporting_tz), INTERVAL 1 DAY) AS snapshot_start,
    DATE_SUB(CURRENT_DATE(reporting_tz), INTERVAL 1 DAY) AS snapshot_end
),
snapshots AS (
  SELECT snapshot_date
  FROM params, UNNEST(GENERATE_DATE_ARRAY(snapshot_start, snapshot_end)) AS snapshot_date
),
-- Catalog master: current level-1 category per item_id (renames apply retroactively); gift-card and service SKUs flagged for exclusion.
catalog AS (
  SELECT
    TRIM(item_id) AS item_id,
    NULLIF(INITCAP(TRIM(item_category)), '') AS product_area,
    COALESCE(LOWER(TRIM(product_type)) IN ('gift_card', 'service', 'warranty', 'shipping_protection'), FALSE) AS is_excluded_sku
  FROM `your-project.reference.product_catalog`
  WHERE item_id IS NOT NULL
  QUALIFY ROW_NUMBER() OVER (PARTITION BY TRIM(item_id) ORDER BY updated_at DESC) = 1
),
-- Staff, internal and known bot identities (user_id or user_pseudo_id).
excluded_users AS (
  SELECT DISTINCT user_key
  FROM `your-project.reference.excluded_users`
),
test_orders AS (
  SELECT DISTINCT transaction_id
  FROM `your-project.reference.test_orders`
),
raw_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', e.event_date) AS event_date,
    e.event_timestamp,
    e.event_name,
    e.user_pseudo_id,
    NULLIF(TRIM(e.user_id), '') AS user_id,
    e.privacy_info.ads_storage AS ads_storage,
    e.ecommerce.transaction_id,
    e.ecommerce.purchase_revenue,
    e.items,
    (SELECT MAX(IF(ep.key = 'ga_session_id', ep.value.int_value, NULL)) FROM UNNEST(e.event_params) AS ep) AS ga_session_id,
    (SELECT MAX(IF(ep.key = 'traffic_type', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep) AS traffic_type,
    (SELECT MAX(IF(ep.key = 'debug_mode', COALESCE(ep.value.int_value, SAFE_CAST(ep.value.string_value AS INT64)), NULL)) FROM UNNEST(e.event_params) AS ep) AS debug_mode,
    (SELECT MAX(IF(ep.key = 'value', COALESCE(ep.value.double_value, ep.value.float_value, CAST(ep.value.int_value AS FLOAT64)), NULL)) FROM UNNEST(e.event_params) AS ep) AS event_value
  FROM `your-project.analytics_123456789.events_*` AS e
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(reporting_tz), INTERVAL 30 DAY))
                          AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(reporting_tz), INTERVAL 1 DAY))
    AND e.event_name IN ('view_item', 'add_to_cart', 'purchase')
    AND e.privacy_info.analytics_storage = 'Yes'
    AND e.user_pseudo_id IS NOT NULL
),
-- Internal / developer traffic, test orders and zero-value replacement orders removed; refunded purchases are kept.
clean_events AS (
  SELECT r.*
  FROM raw_events AS r
  LEFT JOIN test_orders AS t
    ON r.event_name = 'purchase' AND t.transaction_id = r.transaction_id
  WHERE t.transaction_id IS NULL
    AND COALESCE(LOWER(r.traffic_type), '') NOT IN ('internal', 'developer')
    AND COALESCE(r.debug_mode, 0) = 0
    AND NOT (r.event_name = 'purchase' AND COALESCE(r.purchase_revenue, r.event_value, -1) = 0)
),
item_rows AS (
  SELECT
    c.event_date,
    c.event_timestamp,
    c.event_name,
    c.user_pseudo_id,
    c.user_id,
    c.ads_storage,
    CONCAT(c.user_pseudo_id, '.', COALESCE(CAST(c.ga_session_id AS STRING), CONCAT('nosession-', CAST(c.event_timestamp AS STRING)))) AS session_key,
    COALESCE(NULLIF(TRIM(it.item_id), ''), '(not set)') AS item_id,
    NULLIF(INITCAP(TRIM(it.item_category)), '') AS datalayer_category
  FROM clean_events AS c, UNNEST(c.items) AS it
),
-- Device stitching: each user_pseudo_id inherits the most recent user_id it carried inside the snapshot window.
stitch_map AS (
  SELECT
    s.snapshot_date,
    e.user_pseudo_id,
    ARRAY_AGG(TRIM(e.user_id) ORDER BY e.event_timestamp DESC LIMIT 1)[OFFSET(0)] AS stitched_user_id
  FROM `your-project.analytics_123456789.events_*` AS e
  JOIN snapshots AS s
    ON PARSE_DATE('%Y%m%d', e.event_date) BETWEEN DATE_SUB(s.snapshot_date, INTERVAL 29 DAY) AND s.snapshot_date
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(reporting_tz), INTERVAL 30 DAY))
                          AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(reporting_tz), INTERVAL 1 DAY))
    AND e.user_id IS NOT NULL
    AND TRIM(e.user_id) <> ''
    AND e.privacy_info.analytics_storage = 'Yes'
  GROUP BY s.snapshot_date, e.user_pseudo_id
),
windowed AS (
  SELECT
    s.snapshot_date,
    i.*,
    COALESCE(i.user_id, m.stitched_user_id, i.user_pseudo_id) AS user_key,
    IF(COALESCE(i.user_id, m.stitched_user_id) IS NULL, 'user_pseudo_id', 'user_id') AS identity_type
  FROM item_rows AS i
  JOIN snapshots AS s
    ON i.event_date BETWEEN DATE_SUB(s.snapshot_date, INTERVAL 29 DAY) AND s.snapshot_date
  LEFT JOIN stitch_map AS m
    ON m.snapshot_date = s.snapshot_date AND m.user_pseudo_id = i.user_pseudo_id
),
filtered AS (
  SELECT w.*
  FROM windowed AS w
  LEFT JOIN excluded_users AS x1 ON x1.user_key = w.user_key
  LEFT JOIN excluded_users AS x2 ON x2.user_key = w.user_pseudo_id
  WHERE x1.user_key IS NULL
    AND x2.user_key IS NULL
),
-- One interaction per item: view_item once per item_id per session; add_to_cart / purchase once per item_id per event.
interactions AS (
  SELECT
    f.snapshot_date,
    f.user_key,
    f.identity_type,
    f.event_name,
    f.item_id,
    MAX(f.event_timestamp) AS last_ts,
    ANY_VALUE(f.datalayer_category) AS datalayer_category
  FROM filtered AS f
  GROUP BY
    f.snapshot_date, f.user_key, f.identity_type, f.event_name, f.item_id,
    IF(f.event_name = 'view_item', f.session_key, CONCAT(f.session_key, '.', CAST(f.event_timestamp AS STRING)))
),
categorised AS (
  SELECT
    i.*,
    COALESCE(c.product_area, '(not set)') AS product_area
  FROM interactions AS i
  LEFT JOIN catalog AS c
    ON c.item_id = i.item_id
  WHERE NOT COALESCE(c.is_excluded_sku, FALSE)
),
category_counts AS (
  SELECT
    snapshot_date,
    user_key,
    product_area,
    COUNT(*) AS category_interactions,
    COUNTIF(event_name = 'purchase') > 0 AS has_purchase,
    COUNTIF(event_name = 'add_to_cart') > 0 AS has_add_to_cart,
    MAX(last_ts) AS last_ts
  FROM categorised
  WHERE product_area <> '(not set)'
  GROUP BY snapshot_date, user_key, product_area
),
-- Ranking: interactions, then purchase, then add_to_cart, then most recent, then alphabetical; (not set) never ranks.
top_category AS (
  SELECT *
  FROM category_counts
  WHERE TRUE
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY snapshot_date, user_key
    ORDER BY category_interactions DESC, has_purchase DESC, has_add_to_cart DESC, last_ts DESC, product_area ASC
  ) = 1
),
user_totals AS (
  SELECT
    snapshot_date,
    user_key,
    MAX(identity_type) AS identity_type,
    COUNT(*) AS qualifying_interactions,
    COUNTIF(product_area <> '(not set)') AS mapped_interactions,
    COUNTIF(product_area = '(not set)') AS unmapped_interactions,
    COUNTIF(event_name = 'view_item') AS view_item_interactions,
    COUNTIF(event_name = 'add_to_cart') AS add_to_cart_interactions,
    COUNTIF(event_name = 'purchase') AS purchase_interactions,
    COUNT(DISTINCT NULLIF(product_area, '(not set)')) AS distinct_product_areas,
    COUNTIF(product_area <> '(not set)' AND datalayer_category IS NOT NULL AND datalayer_category <> product_area) AS datalayer_category_mismatches,
    MAX(last_ts) AS last_ts
  FROM categorised
  GROUP BY snapshot_date, user_key
),
ads_consent AS (
  SELECT
    snapshot_date,
    user_key,
    ARRAY_AGG(ads_storage IGNORE NULLS ORDER BY event_timestamp DESC LIMIT 1)[SAFE_OFFSET(0)] AS latest_ads_storage
  FROM filtered
  GROUP BY snapshot_date, user_key
)
SELECT
  t.snapshot_date,
  t.user_key,
  t.identity_type,
  IF(t.mapped_interactions >= 2 AND tc.product_area IS NOT NULL, tc.product_area, '(not set)') AS product_area_interest,
  t.qualifying_interactions,
  IF(t.mapped_interactions >= 2 AND tc.product_area IS NOT NULL, tc.category_interactions, 0) AS top_product_area_interactions,
  t.mapped_interactions,
  t.unmapped_interactions,
  t.view_item_interactions,
  t.add_to_cart_interactions,
  t.purchase_interactions,
  t.distinct_product_areas,
  t.datalayer_category_mismatches,
  TIMESTAMP_MICROS(t.last_ts) AS last_interaction_at,
  COALESCE(a.latest_ads_storage = 'Yes', FALSE) AS ad_activation_eligible
FROM user_totals AS t
LEFT JOIN top_category AS tc
  ON tc.snapshot_date = t.snapshot_date AND tc.user_key = t.user_key
LEFT JOIN ads_consent AS a
  ON a.snapshot_date = t.snapshot_date AND a.user_key = t.user_key

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Product Area Interest (Category Affinity): one row per user per snapshot date.
-- Window: snapshot_date - 29 days .. snapshot_date (30 days, reporting time zone); the daily run uses snapshot_date = yesterday.
-- Sessions are rebuilt with SESS_TIMEOUT (30-minute inactivity) over every web hit of the ECID.
-- Ad consent: ad_activation_eligible is set from the latest consents.share.val (ad-platform data sharing) = 'y'.
-- Consent data absent: datasets without an XDM consents object return no rows under the strict consents.collect.val = 'y' filter.
WITH tz AS (
  -- reporting_tz must equal the report-suite / property reporting time zone (calculation note 3).
  SELECT 'America/New_York' AS reporting_tz
),
params AS (
  SELECT
    reporting_tz,
    date_sub(to_date(from_utc_timestamp(current_timestamp(), reporting_tz)), 1) AS snapshot_start,
    date_sub(to_date(from_utc_timestamp(current_timestamp(), reporting_tz)), 1) AS snapshot_end
  FROM tz
),
snapshots AS (
  SELECT explode(sequence(snapshot_start, snapshot_end, interval 1 day)) AS snapshot_date
  FROM params
),
-- Catalog master: current level-1 category per SKU (renames apply retroactively); gift-card and service SKUs flagged for exclusion.
catalog AS (
  SELECT item_id, product_area, is_excluded_sku
  FROM (
    SELECT
      trim(sku) AS item_id,
      NULLIF(initcap(trim(category_level1)), '') AS product_area,
      COALESCE(lower(trim(product_type)) IN ('gift_card', 'service', 'warranty', 'shipping_protection'), FALSE) AS is_excluded_sku,
      ROW_NUMBER() OVER (PARTITION BY trim(sku) ORDER BY updated_at DESC) AS rn
    FROM product_catalog_reference
    WHERE sku IS NOT NULL
  ) AS c
  WHERE rn = 1
),
-- Staff, internal and known bot identities (CRMID or ECID).
excluded_identities AS (
  SELECT DISTINCT identity_id
  FROM excluded_identities_reference
),
test_orders AS (
  SELECT DISTINCT purchase_id
  FROM test_orders_reference
),
web_events AS (
  SELECT
    e._id AS event_id,
    e.timestamp AS event_ts,
    to_date(from_utc_timestamp(e.timestamp, p.reporting_tz)) AS event_date,
    e.eventType AS event_type,
    e.identityMap['ECID'][0].id AS ecid,
    NULLIF(trim(e.identityMap['CRMID'][0].id), '') AS crm_id,
    e.consents.collect.val AS collect_consent,
    e.consents.share.val AS share_consent,
    e.commerce.order.purchaseID AS purchase_id,
    e.commerce.order.priceTotal AS order_total,
    e.environment.browserDetails.userAgent AS user_agent,
    e.productListItems AS product_list_items,
    SESS_TIMEOUT(e.timestamp, 60 * 30) OVER (
      PARTITION BY e.identityMap['ECID'][0].id
      ORDER BY e.timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session
  FROM aep_web_experience_events AS e
  CROSS JOIN params AS p
  WHERE e.timestamp >= to_utc_timestamp(CAST(date_sub(p.snapshot_start, 30) AS TIMESTAMP), p.reporting_tz)
    AND e.timestamp < to_utc_timestamp(CAST(date_add(p.snapshot_end, 1) AS TIMESTAMP), p.reporting_tz)
    AND e.identityMap['ECID'][0].id IS NOT NULL
),
-- Analytics consent only; bots, test orders and zero-value replacement orders removed; refunded purchases are kept.
clean_events AS (
  SELECT w.*
  FROM web_events AS w
  LEFT JOIN test_orders AS t
    ON w.event_type = 'commerce.purchases' AND t.purchase_id = w.purchase_id
  WHERE w.event_type IN ('commerce.productViews', 'commerce.productListAdds', 'commerce.purchases')
    AND w.collect_consent = 'y'
    AND t.purchase_id IS NULL
    AND NOT (w.event_type = 'commerce.purchases' AND COALESCE(w.order_total, -1) = 0)
    AND NOT COALESCE(lower(w.user_agent) RLIKE '(bot|crawler|spider|headlesschrome|lighthouse)', FALSE)
),
item_rows AS (
  SELECT
    c.event_id,
    c.event_ts,
    c.event_date,
    c.event_type,
    c.ecid,
    c.crm_id,
    c.share_consent,
    concat(c.ecid, '.', CAST(c.session.num AS STRING)) AS session_key,
    COALESCE(NULLIF(trim(pli.SKU), ''), '(not set)') AS item_id,
    NULLIF(initcap(trim(COALESCE(pli.productCategories[0].categoryName, split(pli.productCategories[0].hierarchy, '/')[0]))), '') AS datalayer_category
  FROM clean_events AS c
  LATERAL VIEW explode(c.product_list_items) pli_view AS pli
),
-- Device stitching: each ECID inherits the most recent CRMID it carried inside the snapshot window.
stitch_map AS (
  SELECT snapshot_date, ecid, crm_id AS stitched_crm_id
  FROM (
    SELECT
      s.snapshot_date,
      w.ecid,
      w.crm_id,
      ROW_NUMBER() OVER (PARTITION BY s.snapshot_date, w.ecid ORDER BY w.event_ts DESC) AS rn
    FROM web_events AS w
    JOIN snapshots AS s
      ON w.event_date BETWEEN date_sub(s.snapshot_date, 29) AND s.snapshot_date
    WHERE w.crm_id IS NOT NULL
      AND w.collect_consent = 'y'
  ) AS m
  WHERE rn = 1
),
windowed AS (
  SELECT
    s.snapshot_date,
    i.*,
    COALESCE(i.crm_id, m.stitched_crm_id, i.ecid) AS user_key,
    CASE WHEN COALESCE(i.crm_id, m.stitched_crm_id) IS NULL THEN 'ECID' ELSE 'CRMID' END AS identity_type
  FROM item_rows AS i
  JOIN snapshots AS s
    ON i.event_date BETWEEN date_sub(s.snapshot_date, 29) AND s.snapshot_date
  LEFT JOIN stitch_map AS m
    ON m.snapshot_date = s.snapshot_date AND m.ecid = i.ecid
),
filtered AS (
  SELECT w.*
  FROM windowed AS w
  LEFT JOIN excluded_identities AS x1 ON x1.identity_id = w.user_key
  LEFT JOIN excluded_identities AS x2 ON x2.identity_id = w.ecid
  WHERE x1.identity_id IS NULL
    AND x2.identity_id IS NULL
),
-- One interaction per item: product view once per SKU per session; cart add / purchase once per SKU per event.
interactions AS (
  SELECT
    snapshot_date,
    user_key,
    identity_type,
    event_type,
    item_id,
    MAX(event_ts) AS last_ts,
    MAX(datalayer_category) AS datalayer_category
  FROM filtered
  GROUP BY
    snapshot_date, user_key, identity_type, event_type, item_id,
    CASE WHEN event_type = 'commerce.productViews' THEN session_key ELSE event_id END
),
categorised AS (
  SELECT
    i.*,
    COALESCE(c.product_area, '(not set)') AS product_area
  FROM interactions AS i
  LEFT JOIN catalog AS c
    ON c.item_id = i.item_id
  WHERE NOT COALESCE(c.is_excluded_sku, FALSE)
),
category_counts AS (
  SELECT
    snapshot_date,
    user_key,
    product_area,
    COUNT(*) AS category_interactions,
    MAX(CASE WHEN event_type = 'commerce.purchases' THEN 1 ELSE 0 END) AS has_purchase,
    MAX(CASE WHEN event_type = 'commerce.productListAdds' THEN 1 ELSE 0 END) AS has_add_to_cart,
    MAX(last_ts) AS last_ts
  FROM categorised
  WHERE product_area <> '(not set)'
  GROUP BY snapshot_date, user_key, product_area
),
-- Ranking: interactions, then purchase, then cart add, then most recent, then alphabetical; (not set) never ranks.
top_category AS (
  SELECT snapshot_date, user_key, product_area, category_interactions
  FROM (
    SELECT
      cc.*,
      ROW_NUMBER() OVER (
        PARTITION BY snapshot_date, user_key
        ORDER BY category_interactions DESC, has_purchase DESC, has_add_to_cart DESC, last_ts DESC, product_area ASC
      ) AS rn
    FROM category_counts AS cc
  ) AS r
  WHERE rn = 1
),
user_totals AS (
  SELECT
    snapshot_date,
    user_key,
    MAX(identity_type) AS identity_type,
    COUNT(*) AS qualifying_interactions,
    SUM(CASE WHEN product_area <> '(not set)' THEN 1 ELSE 0 END) AS mapped_interactions,
    SUM(CASE WHEN product_area = '(not set)' THEN 1 ELSE 0 END) AS unmapped_interactions,
    SUM(CASE WHEN event_type = 'commerce.productViews' THEN 1 ELSE 0 END) AS view_item_interactions,
    SUM(CASE WHEN event_type = 'commerce.productListAdds' THEN 1 ELSE 0 END) AS add_to_cart_interactions,
    SUM(CASE WHEN event_type = 'commerce.purchases' THEN 1 ELSE 0 END) AS purchase_interactions,
    COUNT(DISTINCT CASE WHEN product_area <> '(not set)' THEN product_area END) AS distinct_product_areas,
    SUM(CASE WHEN product_area <> '(not set)' AND datalayer_category IS NOT NULL AND datalayer_category <> product_area THEN 1 ELSE 0 END) AS datalayer_category_mismatches,
    MAX(last_ts) AS last_ts
  FROM categorised
  GROUP BY snapshot_date, user_key
),
share_consent_latest AS (
  SELECT snapshot_date, user_key, share_consent AS latest_share_consent
  FROM (
    SELECT
      snapshot_date,
      user_key,
      share_consent,
      ROW_NUMBER() OVER (PARTITION BY snapshot_date, user_key ORDER BY event_ts DESC) AS rn
    FROM filtered
    WHERE share_consent IS NOT NULL
  ) AS sc
  WHERE rn = 1
)
SELECT
  t.snapshot_date,
  t.user_key,
  t.identity_type,
  CASE WHEN t.mapped_interactions >= 2 AND tc.product_area IS NOT NULL THEN tc.product_area ELSE '(not set)' END AS product_area_interest,
  t.qualifying_interactions,
  CASE WHEN t.mapped_interactions >= 2 AND tc.product_area IS NOT NULL THEN tc.category_interactions ELSE 0 END AS top_product_area_interactions,
  t.mapped_interactions,
  t.unmapped_interactions,
  t.view_item_interactions,
  t.add_to_cart_interactions,
  t.purchase_interactions,
  t.distinct_product_areas,
  t.datalayer_category_mismatches,
  t.last_ts AS last_interaction_at,
  COALESCE(sc.latest_share_consent = 'y', FALSE) AS ad_activation_eligible
FROM user_totals AS t
LEFT JOIN top_category AS tc
  ON tc.snapshot_date = t.snapshot_date AND tc.user_key = t.user_key
LEFT JOIN share_consent_latest AS sc
  ON sc.snapshot_date = t.snapshot_date AND sc.user_key = t.user_key