Site Searches

{
  "digitalData": {
    "page": {
      "pageInfo": {
        "pageName": "search results",
        "pageType": "search results"
      },
      "category": {
        "primaryCategory": "search"
      }
    },
    "event": [
      {
        "eventInfo": {
          "eventName": "search",
          "eventAction": "submit",
          "type": "search"
        },
        "category": {
          "primaryCategory": "site search"
        },
        "attributes": {
          "searchTerm": "running shoes",
          "searchResultsCount": 128,
          "searchType": "submitted",
          "searchSource": "header",
          "searchScope": "product_catalog",
          "pageNumber": 1,
          "isRefire": false
        }
      }
    ]
  }
}

{
  "event": "search",
  "search_term": "running shoes",
  "search_results_count": 128,
  "search_type": "submitted",
  "search_source": "header",
  "search_scope": "product_catalog",
  "page_number": 1,
  "is_refire": false
}

{
  "event": "search",
  "eventInfo": {
    "eventName": "search",
    "eventAction": "submit"
  },
  "search": {
    "term": "running shoes",
    "resultsCount": 128,
    "type": "submitted",
    "source": "header",
    "scope": "product_catalog",
    "pageNumber": 1,
    "isRefire": false
  },
  "page": {
    "pageInfo": {
      "pageName": "search results",
      "pageType": "search results"
    }
  }
}

{
  "eventType": "siteSearch",
  "web": {
    "webPageDetails": {
      "name": "search results",
      "siteSection": "search"
    }
  },
  "_tenant": {
    "siteSearch": {
      "keywords": "running shoes",
      "resultsCount": 128,
      "searchType": "submitted",
      "searchSource": "header",
      "searchScope": "product_catalog",
      "pageNumber": 1,
      "isRefire": false,
      "submissions": {
        "value": 1
      }
    }
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar1": "running shoes",
          "eVar2": "header",
          "eVar3": "submitted"
        },
        "props": {
          "prop1": "running shoes",
          "prop2": "128"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
WITH search_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', e.event_date) AS event_date,
    e.user_pseudo_id,
    e.event_timestamp,
    e.device.category AS device_category,
    (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 = 'search_term', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep) AS search_term_raw,
    (SELECT MAX(IF(ep.key = 'search_results_count', COALESCE(ep.value.int_value, SAFE_CAST(ep.value.string_value AS INT64), SAFE_CAST(ep.value.double_value AS INT64)), NULL)) FROM UNNEST(e.event_params) AS ep) AS search_results_count,
    (SELECT MAX(IF(ep.key = 'search_type', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep) AS search_type,
    (SELECT MAX(IF(ep.key = 'search_scope', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep) AS search_scope,
    (SELECT MAX(IF(ep.key = 'page_number', COALESCE(ep.value.int_value, SAFE_CAST(ep.value.string_value AS INT64)), NULL)) FROM UNNEST(e.event_params) AS ep) AS page_number,
    (SELECT MAX(IF(ep.key = 'is_refire', LOWER(COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING))), NULL)) FROM UNNEST(e.event_params) AS ep) AS is_refire_flag,
    (SELECT MAX(IF(ep.key = 'traffic_type', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep) AS traffic_type
  FROM `your-project.analytics_123456789.events_*` AS e
  WHERE e._TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                            AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND e.event_name = 'search'
),
normalised AS (
  SELECT
    *,
    NULLIF(REGEXP_REPLACE(LOWER(TRIM(search_term_raw)), r'\s+', ' '), '') AS search_term_norm
  FROM search_events
  WHERE COALESCE(search_scope, 'product_catalog') = 'product_catalog'
    AND COALESCE(traffic_type, 'external') <> 'internal'
),
sequenced AS (
  SELECT
    *,
    IF(ga_session_id IS NULL, NULL,
       LAG(search_term_norm) OVER (PARTITION BY user_pseudo_id, ga_session_id ORDER BY event_timestamp)) AS prev_term_norm
  FROM normalised
  WHERE search_term_norm IS NOT NULL
),
counted AS (
  SELECT
    *,
    CASE
      WHEN is_refire_flag IN ('true', '1') THEN TRUE
      WHEN search_term_norm = prev_term_norm AND COALESCE(page_number, 1) > 1 THEN TRUE
      ELSE FALSE
    END AS is_refire
  FROM sequenced
)
SELECT
  event_date,
  device_category,
  COUNT(*) AS site_searches,
  COUNT(DISTINCT CONCAT(user_pseudo_id, '.', CAST(ga_session_id AS STRING))) AS sessions_with_search,
  COUNTIF(search_results_count = 0) AS zero_result_searches,
  COUNTIF(search_results_count IS NULL) AS null_results_count_searches,
  SAFE_DIVIDE(COUNTIF(search_results_count IS NULL), COUNT(*)) AS null_results_count_share
FROM counted
WHERE is_refire = FALSE
GROUP BY event_date, device_category
ORDER BY event_date, device_category;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Time zone: replace 'America/New_York' with the property reporting time zone (IANA name) so event_date matches GA4 event_date.
WITH all_hits AS (
  SELECT
    timestamp,
    eventType,
    identityMap['ECID'][0].id AS ecid,
    device.type AS device_type,
    _yourtenant.siteSearch.keywords AS search_term_raw,
    CAST(_yourtenant.siteSearch.resultsCount AS INT) AS search_results_count,
    _yourtenant.siteSearch.searchScope AS search_scope,
    CAST(_yourtenant.siteSearch.pageNumber AS INT) AS page_number,
    _yourtenant.siteSearch.isRefire AS is_refire_flag,
    SESS_TIMEOUT(timestamp, 60 * 30) OVER (
      PARTITION BY identityMap['ECID'][0].id
      ORDER BY timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session
  FROM your_xdm_web_experience_event_dataset
  -- UTC bounds padded by one day on each side; the event_date filter below sets the exact reporting window.
  WHERE timestamp >= to_timestamp(date_sub(current_date(), 31))
    AND timestamp < to_timestamp(date_add(current_date(), 1))
    AND identityMap['ECID'][0].id IS NOT NULL
),
search_events AS (
  SELECT
    CAST(from_utc_timestamp(timestamp, 'America/New_York') AS DATE) AS event_date, -- set to the property reporting time zone
    timestamp,
    ecid,
    session.num AS session_num,
    device_type,
    search_results_count,
    page_number,
    is_refire_flag,
    NULLIF(regexp_replace(LOWER(TRIM(search_term_raw)), '\\s+', ' '), '') AS search_term_norm
  FROM all_hits
  WHERE eventType = 'siteSearch'
    AND COALESCE(search_scope, 'product_catalog') = 'product_catalog'
),
sequenced AS (
  SELECT
    *,
    LAG(search_term_norm) OVER (PARTITION BY ecid, session_num ORDER BY timestamp) AS prev_term_norm
  FROM search_events
  WHERE search_term_norm IS NOT NULL
    AND event_date BETWEEN date_sub(current_date(), 30) AND date_sub(current_date(), 1)
),
counted AS (
  SELECT
    *,
    CASE
      WHEN COALESCE(is_refire_flag, FALSE) = TRUE THEN TRUE
      WHEN search_term_norm = prev_term_norm AND COALESCE(page_number, 1) > 1 THEN TRUE
      ELSE FALSE
    END AS is_refire
  FROM sequenced
)
SELECT
  event_date,
  device_type,
  COUNT(*) AS site_searches,
  COUNT(DISTINCT CONCAT(ecid, '.', CAST(session_num AS STRING))) AS sessions_with_search,
  SUM(CASE WHEN search_results_count = 0 THEN 1 ELSE 0 END) AS zero_result_searches,
  SUM(CASE WHEN search_results_count IS NULL THEN 1 ELSE 0 END) AS null_results_count_searches,
  CAST(SUM(CASE WHEN search_results_count IS NULL THEN 1 ELSE 0 END) AS DOUBLE) / NULLIF(COUNT(*), 0) AS null_results_count_share
FROM counted
WHERE is_refire = FALSE
GROUP BY event_date, device_type
ORDER BY event_date, device_type;