Zero-Results Search Rate

{
  "page": {
    "pageInfo": {
      "pageName": "search results",
      "destinationURL": "https://www.example.com/search?q=waterproof+trail+shoes"
    },
    "category": {
      "primaryCategory": "search",
      "pageType": "search_results"
    }
  },
  "event": [
    {
      "eventInfo": {
        "eventName": "view_search_results",
        "eventAction": "search",
        "type": "search"
      },
      "attributes": {
        "search_term": "waterproof trail shoes",
        "search_term_original": "waterproof trail shoes",
        "search_results_count": 0,
        "search_type": "standard",
        "search_method": "enter_key"
      }
    }
  ]
}

{
  "event": "view_search_results",
  "search_term": "waterproof trail shoes",
  "search_term_original": "waterproof trail shoes",
  "search_results_count": 0,
  "search_type": "standard",
  "search_method": "enter_key",
  "page_type": "search_results",
  "traffic_type": "standard"
}

{
  "event": "internalSearch",
  "eventInfo": {
    "eventName": "internalSearch",
    "eventAction": "search"
  },
  "page": {
    "pageInfo": {
      "pageName": "search results",
      "pageType": "search_results"
    }
  },
  "search": {
    "search_term": "waterproof trail shoes",
    "search_term_original": "waterproof trail shoes",
    "search_results_count": 0,
    "search_type": "standard",
    "search_method": "enter_key"
  },
  "traffic_type": "standard"
}

{
  "eventType": "siteSearch.resultsView",
  "timestamp": "2026-09-16T14:05:12.000Z",
  "web": {
    "webPageDetails": {
      "name": "search results",
      "pageViews": {
        "value": 1
      }
    }
  },
  "siteSearch": {
    "phrase": "waterproof trail shoes"
  },
  "_tenant": {
    "traffic_type": "standard",
    "search": {
      "search_term": "waterproof trail shoes",
      "search_term_original": "waterproof trail shoes",
      "search_results_count": 0,
      "search_type": "standard",
      "search_method": "enter_key"
    }
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar1": "waterproof trail shoes",
          "eVar2": "0",
          "eVar3": "standard"
        }
      },
      "event1to100": {
        "event1": {
          "value": 1
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Zero-Results Search Rate = zero-result searches / all classifiable submitted searches x 100 (daily, property time zone)
WITH search_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS report_date,
    user_pseudo_id,
    event_timestamp,
    MAX(IF(ep.key = 'ga_session_id', ep.value.int_value, NULL)) AS ga_session_id,
    MAX(IF(ep.key = 'search_term', ep.value.string_value, NULL)) AS search_term,
    MAX(IF(ep.key = 'search_type', ep.value.string_value, NULL)) AS search_type,
    MAX(IF(ep.key = 'search_method', ep.value.string_value, NULL)) AS search_method,
    MAX(IF(ep.key = 'traffic_type', ep.value.string_value, NULL)) AS traffic_type,
    MAX(IF(ep.key = 'debug_mode', COALESCE(CAST(ep.value.int_value AS STRING), ep.value.string_value), NULL)) AS debug_mode,
    MAX(IF(ep.key = 'search_results_count',
           COALESCE(ep.value.int_value,
                    CAST(ep.value.double_value AS INT64),
                    SAFE_CAST(ep.value.string_value AS INT64)),
           NULL)) AS search_results_count
  FROM `your-project.analytics_123456789.events_*`,
    UNNEST(event_params) AS ep
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
    AND event_name = 'view_search_results'
  GROUP BY report_date, user_pseudo_id, event_timestamp, event_bundle_sequence_id
),
qualified AS (
  SELECT
    report_date,
    user_pseudo_id,
    ga_session_id,
    event_timestamp,
    REGEXP_REPLACE(LOWER(TRIM(search_term)), r'\s+', ' ') AS search_term_norm,
    search_results_count
  FROM search_events
  WHERE search_term IS NOT NULL
    AND TRIM(search_term) != ''
    AND COALESCE(search_type, 'standard') != 'redirect'
    AND search_method IN ('enter_key', 'search_button', 'suggestion_select')
    AND COALESCE(traffic_type, '') NOT IN ('internal', 'qa', 'synthetic')
    AND COALESCE(debug_mode, '0') = '0'
),
deduplicated AS (
  SELECT
    *,
    LAG(event_timestamp) OVER (
      PARTITION BY user_pseudo_id, ga_session_id, search_term_norm
      ORDER BY event_timestamp
    ) AS prev_same_term_ts
  FROM qualified
),
searches AS (
  SELECT *
  FROM deduplicated
  WHERE prev_same_term_ts IS NULL
     OR event_timestamp - prev_same_term_ts > 30 * 1000000
)
SELECT
  report_date,
  COUNT(*) AS total_search_events,
  COUNTIF(search_results_count IS NOT NULL) AS total_searches,
  COUNTIF(search_results_count = 0) AS zero_result_searches,
  ROUND(SAFE_DIVIDE(COUNTIF(search_results_count = 0),
                    COUNTIF(search_results_count IS NOT NULL)) * 100, 2) AS zero_results_search_rate_pct,
  ROUND(SAFE_DIVIDE(COUNTIF(search_results_count IS NOT NULL), COUNT(*)) * 100, 2) AS results_count_coverage_pct,
  report_date > DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AS is_provisional
FROM searches
GROUP BY report_date
ORDER BY report_date;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Zero-Results Search Rate = zero-result searches / all classifiable submitted searches x 100 (daily, reporting time zone)
WITH search_events AS (
  SELECT
    to_date(from_utc_timestamp(timestamp, 'America/New_York')) AS report_date,
    identityMap['ECID'][0].id AS ecid,
    COALESCE(_experience.analytics.session.num, 0) AS session_num,
    timestamp,
    _tenant.search.search_term AS search_term,
    _tenant.search.search_type AS search_type,
    _tenant.search.search_method AS search_method,
    CAST(_tenant.search.search_results_count AS INT) AS search_results_count
  FROM aep_web_events_dataset
  WHERE eventType = 'siteSearch.resultsView'
    AND timestamp >= date_sub(current_date(), 90)
    AND NOT (lower(COALESCE(environment.browserDetails.userAgent, '')) RLIKE 'bot|crawler|spider|slurp|headless|lighthouse')
    AND COALESCE(_tenant.traffic_type, '') NOT IN ('internal', 'qa', 'synthetic')
),
qualified AS (
  SELECT
    report_date,
    ecid,
    session_num,
    timestamp,
    regexp_replace(lower(trim(search_term)), '\\s+', ' ') AS search_term_norm,
    search_results_count
  FROM search_events
  WHERE search_term IS NOT NULL
    AND trim(search_term) != ''
    AND COALESCE(search_type, 'standard') != 'redirect'
    AND search_method IN ('enter_key', 'search_button', 'suggestion_select')
),
deduplicated AS (
  SELECT
    *,
    LAG(timestamp) OVER (
      PARTITION BY ecid, session_num, search_term_norm
      ORDER BY timestamp
    ) AS prev_same_term_ts
  FROM qualified
),
searches AS (
  SELECT *
  FROM deduplicated
  WHERE prev_same_term_ts IS NULL
     OR unix_timestamp(timestamp) - unix_timestamp(prev_same_term_ts) > 30
)
SELECT
  report_date,
  COUNT(*) AS total_search_events,
  SUM(CASE WHEN search_results_count IS NOT NULL THEN 1 ELSE 0 END) AS total_searches,
  SUM(CASE WHEN search_results_count = 0 THEN 1 ELSE 0 END) AS zero_result_searches,
  ROUND(
    SUM(CASE WHEN search_results_count = 0 THEN 1 ELSE 0 END) * 100.0
    / NULLIF(SUM(CASE WHEN search_results_count IS NOT NULL THEN 1 ELSE 0 END), 0), 2) AS zero_results_search_rate_pct,
  ROUND(
    SUM(CASE WHEN search_results_count IS NOT NULL THEN 1 ELSE 0 END) * 100.0
    / NULLIF(COUNT(*), 0), 2) AS results_count_coverage_pct,
  report_date > date_sub(current_date(), 3) AS is_provisional
FROM searches
GROUP BY report_date
ORDER BY report_date;