Device category

{
  "page": {
    "pageInfo": {
      "pageID": "plp-womens-running-shoes",
      "pageName": "category:womens:running-shoes",
      "destinationURL": "https://www.example-store.com/c/womens/running-shoes",
      "referringURL": "https://www.google.com/",
      "language": "en-US"
    },
    "category": {
      "primaryCategory": "womens",
      "subCategory1": "running-shoes",
      "pageType": "category"
    }
  }
}

{
  "event": "page_view",
  "page_location": "https://www.example-store.com/c/womens/running-shoes",
  "page_referrer": "https://www.google.com/",
  "page_title": "Women's Running Shoes",
  "page_type": "category",
  "language": "en-US"
}

{
  "event": "pageView",
  "page": {
    "pageInfo": {
      "pageName": "category:womens:running-shoes",
      "pageType": "category",
      "siteSection": "womens",
      "url": "https://www.example-store.com/c/womens/running-shoes",
      "referrer": "https://www.google.com/",
      "language": "en-US"
    }
  }
}

{
  "eventType": "web.webpagedetails.pageViews",
  "web": {
    "webPageDetails": {
      "name": "category:womens:running-shoes",
      "URL": "https://www.example-store.com/c/womens/running-shoes",
      "siteSection": "womens",
      "pageViews": {
        "value": 1
      }
    },
    "webReferrer": {
      "URL": "https://www.google.com/"
    }
  },
  "environment": {
    "type": "browser",
    "operatingSystem": "Android",
    "operatingSystemVersion": "14.0.0",
    "browserDetails": {
      "name": "Chrome",
      "userAgent": "Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/128.0.0.0 Mobile Safari/537.36",
      "userAgentClientHints": {
        "mobile": true,
        "platform": "Android",
        "platformVersion": "14.0.0",
        "model": "SM-S921B"
      }
    }
  },
  "device": {
    "type": "mobile",
    "manufacturer": "Samsung",
    "model": "SM-S921B",
    "screenWidth": 384,
    "screenHeight": 832,
    "screenOrientation": "portrait"
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Device category by session date x platform x device category: sessions, users, page views, orders and session conversion rate.
-- Session = user_pseudo_id + ga_session_id. Device and platform come from the first event of the session by event_timestamp;
-- hits without device data (Measurement Protocol, server-side requests without user_agent) rank after collected hits, as the value is constant within a session.
-- Known bots are removed by GA4 (IAB list) before export; internal traffic (traffic_type = internal) is removed at session level.
-- Backend purchases without device data are credited to the session where the order was placed, joined on transaction_id.
-- users_non_additive: one person can appear in several device categories; never sum this column across rows.
-- The suffix range reads daily tables only; use events_intraday_* for same-day checks.
WITH events AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS event_date,
    event_timestamp,
    event_name,
    user_pseudo_id,
    UPPER(platform) AS platform,
    NULLIF(LOWER(TRIM(device.category)), '') AS device_category_raw,
    NULLIF(NULLIF(ecommerce.transaction_id, ''), '(not set)') AS ecommerce_transaction_id,
    ecommerce.purchase_revenue AS purchase_revenue,
    (SELECT MAX(IF(ep.key = 'ga_session_id', COALESCE(ep.value.int_value, SAFE_CAST(ep.value.string_value AS INT64)), NULL)) FROM UNNEST(event_params) AS ep) AS ga_session_id,
    (SELECT MAX(IF(ep.key = 'traffic_type', ep.value.string_value, NULL)) FROM UNNEST(event_params) AS ep) AS traffic_type,
    (SELECT MAX(IF(ep.key = 'transaction_id', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) FROM UNNEST(event_params) AS ep) AS param_transaction_id
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260801' AND '20260831'
),
keyed AS (
  SELECT
    *,
    CONCAT(user_pseudo_id, '.', CAST(ga_session_id AS STRING)) AS session_key,
    COALESCE(ecommerce_transaction_id, NULLIF(NULLIF(param_transaction_id, ''), '(not set)')) AS transaction_id
  FROM events
),
sessions AS (
  SELECT
    session_key,
    user_pseudo_id,
    MIN(event_date) AS session_date,
    ARRAY_AGG(STRUCT(platform, device_category_raw) ORDER BY IF(device_category_raw IS NULL, 1, 0), event_timestamp LIMIT 1)[OFFSET(0)] AS first_hit,
    COUNTIF(event_name = 'page_view') AS page_views,
    COALESCE(LOGICAL_OR(traffic_type = 'internal'), FALSE) AS is_internal
  FROM keyed
  WHERE user_pseudo_id IS NOT NULL
    AND ga_session_id IS NOT NULL
  GROUP BY session_key, user_pseudo_id
),
session_device AS (
  SELECT
    session_key,
    user_pseudo_id,
    session_date,
    COALESCE(first_hit.platform, '(not set)') AS platform,
    CASE first_hit.device_category_raw
      WHEN 'desktop' THEN 'desktop'
      WHEN 'mobile' THEN 'mobile'
      WHEN 'tablet' THEN 'tablet'
      WHEN 'smart tv' THEN 'smart tv'
      ELSE '(not set)'
    END AS device_category,
    page_views
  FROM sessions
  WHERE NOT is_internal
),
transaction_session AS (
  SELECT
    transaction_id,
    ARRAY_AGG(session_key ORDER BY event_timestamp LIMIT 1)[OFFSET(0)] AS placed_session_key
  FROM keyed
  WHERE transaction_id IS NOT NULL
    AND ga_session_id IS NOT NULL
    AND device_category_raw IS NOT NULL
  GROUP BY transaction_id
),
purchase_events AS (
  SELECT
    COALESCE(p.transaction_id, CONCAT(p.session_key, '.', CAST(p.event_timestamp AS STRING))) AS order_key,
    COALESCE(ts.placed_session_key, IF(p.ga_session_id IS NOT NULL, p.session_key, NULL)) AS credited_session_key,
    p.purchase_revenue
  FROM keyed AS p
  LEFT JOIN transaction_session AS ts
    ON ts.transaction_id = p.transaction_id
  WHERE p.event_name = 'purchase'
),
orders AS (
  SELECT
    order_key,
    ARRAY_AGG(credited_session_key IGNORE NULLS LIMIT 1)[SAFE_OFFSET(0)] AS session_key,
    MAX(purchase_revenue) AS purchase_revenue
  FROM purchase_events
  GROUP BY order_key
),
session_orders AS (
  SELECT
    session_key,
    COUNT(*) AS transactions,
    SUM(COALESCE(purchase_revenue, 0)) AS purchase_revenue
  FROM orders
  WHERE session_key IS NOT NULL
  GROUP BY session_key
),
device_daily AS (
  SELECT
    sd.session_date,
    sd.platform,
    sd.device_category,
    COUNT(DISTINCT sd.session_key) AS sessions,
    COUNT(DISTINCT sd.user_pseudo_id) AS users_non_additive,
    SUM(sd.page_views) AS page_views,
    COUNT(DISTINCT IF(so.transactions > 0, sd.session_key, NULL)) AS purchase_sessions,
    SUM(COALESCE(so.transactions, 0)) AS transactions,
    ROUND(SUM(COALESCE(so.purchase_revenue, 0)), 2) AS purchase_revenue
  FROM session_device AS sd
  LEFT JOIN session_orders AS so
    ON so.session_key = sd.session_key
  GROUP BY sd.session_date, sd.platform, sd.device_category
)
SELECT
  session_date,
  platform,
  device_category,
  sessions,
  users_non_additive,
  page_views,
  purchase_sessions,
  transactions,
  purchase_revenue,
  SAFE_DIVIDE(purchase_sessions, sessions) AS session_conversion_rate,
  SAFE_DIVIDE(sessions, SUM(sessions) OVER (PARTITION BY session_date, platform)) AS session_share_of_platform,
  device_category = '(not set)' AND SAFE_DIVIDE(sessions, SUM(sessions) OVER (PARTITION BY session_date, platform)) > 0.01 AS not_set_share_above_1pct
FROM device_daily
ORDER BY session_date, platform, device_category;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Device category by session date (UTC) x platform x device category from the web and mobile ExperienceEvent dataset.
-- XDM device.type is mapped to the GA4 buckets: desktop; mobile; tablet + ereader -> tablet; television, settop, mediaplayer, gaming -> smart tv;
-- a missing or unrecognised type is treated as desktop only for browser hits on Windows, macOS, Linux or ChromeOS, otherwise (not set).
-- Session = ECID + 30-minute inactivity (SESS_TIMEOUT); device and platform come from the session's first event by timestamp.
-- Backend events without an environment are not sessionised; their orders are credited to the placing session via commerce.order.purchaseID.
-- Internal traffic (tenant field trafficType = internal) and bot user agents are removed at session level, in addition to datastream bot detection.
-- users_non_additive: one ECID or stitched person can appear in several device categories; never sum this column across rows.
WITH events AS (
  SELECT
    timestamp AS event_ts,
    eventType AS event_type,
    identityMap['ECID'][0].id AS ecid,
    LOWER(TRIM(device.type)) AS device_type_raw,
    LOWER(environment.type) AS environment_type,
    LOWER(environment.operatingSystem) AS operating_system,
    LOWER(COALESCE(environment.browserDetails.userAgent, '')) AS user_agent,
    NULLIF(TRIM(commerce.order.purchaseID), '') AS purchase_id,
    commerce.order.priceTotal AS order_total,
    LOWER(_yourtenant.trafficType) AS traffic_type
  FROM ecom_web_experience_events
  WHERE timestamp >= TIMESTAMP '2026-08-01 00:00:00'
    AND timestamp < TIMESTAMP '2026-09-01 00:00:00'
),
mapped AS (
  SELECT
    *,
    CASE
      WHEN environment_type = 'browser' THEN 'WEB'
      WHEN environment_type = 'application' AND operating_system RLIKE 'ios|ipados' THEN 'IOS'
      WHEN environment_type = 'application' AND operating_system LIKE '%android%' THEN 'ANDROID'
      ELSE '(not set)'
    END AS platform,
    CASE
      WHEN device_type_raw = 'desktop' THEN 'desktop'
      WHEN device_type_raw = 'mobile' THEN 'mobile'
      WHEN device_type_raw IN ('tablet', 'ereader') THEN 'tablet'
      WHEN device_type_raw IN ('television', 'settop', 'mediaplayer', 'gaming') THEN 'smart tv'
      WHEN environment_type = 'browser' AND operating_system RLIKE 'windows|mac ?os|os x|linux|chrome ?os' THEN 'desktop'
      ELSE NULL
    END AS device_category_known
  FROM events
),
sessionised AS (
  SELECT
    m.*,
    SESS_TIMEOUT(event_ts, 60 * 30) OVER (PARTITION BY ecid ORDER BY event_ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sess
  FROM mapped AS m
  WHERE ecid IS NOT NULL
    AND environment_type IS NOT NULL
),
keyed AS (
  SELECT
    *,
    CONCAT(ecid, '-', CAST(sess.num AS STRING)) AS session_key
  FROM sessionised
),
ranked AS (
  SELECT
    k.*,
    ROW_NUMBER() OVER (PARTITION BY session_key ORDER BY CASE WHEN device_category_known IS NULL THEN 1 ELSE 0 END, event_ts) AS hit_rank,
    MIN(event_ts) OVER (PARTITION BY session_key) AS session_start_ts,
    SUM(CASE WHEN event_type = 'web.webpagedetails.pageViews' THEN 1 ELSE 0 END) OVER (PARTITION BY session_key) AS page_views,
    MAX(CASE WHEN traffic_type = 'internal' THEN 1 ELSE 0 END) OVER (PARTITION BY session_key) AS is_internal,
    MAX(CASE WHEN user_agent RLIKE 'bot|crawl|spider|slurp|headlesschrome|lighthouse|pingdom' THEN 1 ELSE 0 END) OVER (PARTITION BY session_key) AS is_bot
  FROM keyed AS k
),
session_device AS (
  SELECT
    session_key,
    ecid,
    TO_DATE(session_start_ts) AS session_date,
    platform,
    COALESCE(device_category_known, '(not set)') AS device_category,
    page_views
  FROM ranked
  WHERE hit_rank = 1
    AND is_internal = 0
    AND is_bot = 0
),
transaction_session AS (
  SELECT purchase_id, session_key AS placed_session_key
  FROM (
    SELECT
      purchase_id,
      session_key,
      ROW_NUMBER() OVER (PARTITION BY purchase_id ORDER BY event_ts) AS rn
    FROM keyed
    WHERE purchase_id IS NOT NULL
  ) AS t
  WHERE rn = 1
),
order_events AS (
  SELECT
    m.purchase_id AS order_key,
    ts.placed_session_key AS session_key,
    m.order_total
  FROM mapped AS m
  JOIN transaction_session AS ts
    ON ts.purchase_id = m.purchase_id
  WHERE m.event_type = 'commerce.purchases'
  UNION ALL
  SELECT
    CONCAT(session_key, '-', CAST(event_ts AS STRING)) AS order_key,
    session_key,
    order_total
  FROM keyed
  WHERE event_type = 'commerce.purchases'
    AND purchase_id IS NULL
),
session_orders AS (
  SELECT
    session_key,
    COUNT(*) AS transactions,
    SUM(COALESCE(order_total, 0)) AS purchase_revenue
  FROM (
    SELECT order_key, MAX(session_key) AS session_key, MAX(order_total) AS order_total
    FROM order_events
    GROUP BY order_key
  ) AS o
  GROUP BY session_key
),
device_daily AS (
  SELECT
    sd.session_date,
    sd.platform,
    sd.device_category,
    COUNT(DISTINCT sd.session_key) AS sessions,
    COUNT(DISTINCT sd.ecid) AS users_non_additive,
    SUM(sd.page_views) AS page_views,
    COUNT(DISTINCT CASE WHEN so.transactions > 0 THEN sd.session_key END) AS purchase_sessions,
    SUM(COALESCE(so.transactions, 0)) AS transactions,
    ROUND(SUM(COALESCE(so.purchase_revenue, 0)), 2) AS purchase_revenue
  FROM session_device AS sd
  LEFT JOIN session_orders AS so
    ON so.session_key = sd.session_key
  GROUP BY sd.session_date, sd.platform, sd.device_category
)
SELECT
  session_date,
  platform,
  device_category,
  sessions,
  users_non_additive,
  page_views,
  purchase_sessions,
  transactions,
  purchase_revenue,
  CAST(purchase_sessions AS DOUBLE) / NULLIF(sessions, 0) AS session_conversion_rate,
  CAST(sessions AS DOUBLE) / NULLIF(SUM(sessions) OVER (PARTITION BY session_date, platform), 0) AS session_share_of_platform,
  CASE WHEN device_category = '(not set)' AND CAST(sessions AS DOUBLE) / NULLIF(SUM(sessions) OVER (PARTITION BY session_date, platform), 0) > 0.01 THEN TRUE ELSE FALSE END AS not_set_share_above_1pct
FROM device_daily
ORDER BY session_date, platform, device_category;