Zero-Results Searches

{
  "digitalData": {
    "event": [
      {
        "eventInfo": {
          "eventName": "search"
        },
        "attributes": {
          "searchTerm": "blue boots",
          "searchSubmissionId": "synthetic-search-001",
          "searchSubmittedAt": "2026-10-14T12:00:00Z",
          "searchResultsCount": 0,
          "searchType": "product",
          "submissionKind": "query",
          "trafficType": "external",
          "responseStatus": "completed"
        }
      }
    ]
  }
}

{
  "event": "search",
  "search_term": "blue boots",
  "search_submission_id": "synthetic-search-001",
  "search_submitted_at": "2026-10-14T12:00:00Z",
  "search_results_count": 0,
  "search_type": "product",
  "submission_kind": "query",
  "traffic_type": "external",
  "response_status": "completed"
}

[
  {
    "event": "search",
    "eventInfo": {
      "searchTerm": "blue boots",
      "searchSubmissionId": "synthetic-search-001",
      "searchSubmittedAt": "2026-10-14T12:00:00Z",
      "searchResultsCount": 0,
      "searchType": "product",
      "submissionKind": "query",
      "trafficType": "external",
      "responseStatus": "completed"
    }
  }
]

{
  "_id": "synthetic-response-001",
  "timestamp": "2026-10-14T12:00:01Z",
  "eventType": "search",
  "identityMap": {
    "ECID": [
      {
        "id": "synthetic-ecid-001",
        "authenticatedState": "ambiguous",
        "primary": true
      }
    ]
  },
  "_tenantId": {
    "search": {
      "searchTerm": "blue boots",
      "submissionId": "synthetic-search-001",
      "submittedAt": "2026-10-14T12:00:00Z",
      "searchResultsCount": 0,
      "searchType": "product",
      "submissionKind": "query",
      "trafficType": "external",
      "responseStatus": "completed"
    }
  }
}

-- Bind {{ga4_export_dataset}} to the GA4 export dataset; query finalized daily events_* tables.
-- Parameters: @report_days INT64 > 0, @completion_lag_days INT64 >= 0, @report_tz STRING IANA zone, @coverage_complete BOOL.
-- Coverage must include every final response for the selected submissions, complete exports, and upstream conflict quarantine.
-- Submission IDs are globally unique; one immutable final response per ID; identical retries retain all values.
-- Collection excludes navigation/refinement emissions; query means deliberate submission, including a submitted suggestion.
-- Final counts exclude unrelated recommendations; contradictory payloads, duplicate keys, and ambiguous value slots are quarantined upstream.
-- Completion event time is at or after submission time. Search terms are sanitized before collection; raw access is restricted.
WITH bounds AS (
  SELECT
    TIMESTAMP(DATE_SUB(DATE_SUB(CURRENT_DATE(@report_tz), INTERVAL @completion_lag_days DAY), INTERVAL @report_days DAY), @report_tz) AS start_ts,
    TIMESTAMP(DATE_SUB(CURRENT_DATE(@report_tz), INTERVAL @completion_lag_days DAY), @report_tz) AS end_ts
), extracted AS (
  SELECT
    TIMESTAMP_MICROS(event_timestamp) AS completed_at,
    (SELECT IF(COUNT(*) = 1, ANY_VALUE(value.string_value), NULL) FROM UNNEST(event_params) WHERE key = 'search_submission_id') AS submission_id,
    SAFE_CAST((SELECT IF(COUNT(*) = 1, ANY_VALUE(value.string_value), NULL) FROM UNNEST(event_params) WHERE key = 'search_submitted_at') AS TIMESTAMP) AS submitted_at,
    (SELECT IF(COUNT(*) = 1, ANY_VALUE(value.string_value), NULL) FROM UNNEST(event_params) WHERE key = 'search_type') AS search_type,
    (SELECT IF(COUNT(*) = 1, ANY_VALUE(value.string_value), NULL) FROM UNNEST(event_params) WHERE key = 'submission_kind') AS submission_kind,
    (SELECT IF(COUNT(*) = 1, ANY_VALUE(value.string_value), NULL) FROM UNNEST(event_params) WHERE key = 'traffic_type') AS traffic_type,
    (SELECT IF(COUNT(*) = 1, ANY_VALUE(value.string_value), NULL) FROM UNNEST(event_params) WHERE key = 'response_status') AS response_status,
    (SELECT COUNT(*) = 1 AND COUNTIF(
      IF(value.int_value IS NULL, 0, 1) + IF(value.double_value IS NULL, 0, 1) + IF(value.float_value IS NULL, 0, 1) + IF(value.string_value IS NULL, 0, 1) = 1
      AND (
        value.int_value = 0 OR value.double_value = 0 OR value.float_value = 0
        OR REGEXP_CONTAINS(value.string_value, r'^[+-]?0+(\.0+)?$')
      )
    ) = 1 FROM UNNEST(event_params) WHERE key = 'search_results_count') AS valid_zero
  FROM `{{ga4_export_dataset}}.events_*`
  CROSS JOIN bounds
  WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, r'^\d{8}$')
    AND event_name = 'search'
    AND TIMESTAMP_MICROS(event_timestamp) >= bounds.start_ts
    AND TIMESTAMP_MICROS(event_timestamp) < CURRENT_TIMESTAMP()
), submissions AS (
  SELECT submission_id, MIN(submitted_at) AS submitted_at
  FROM extracted
  WHERE submission_id IS NOT NULL AND TRIM(submission_id) != ''
  GROUP BY submission_id
  HAVING MIN(submitted_at) = MAX(submitted_at)
    AND LOGICAL_AND(COALESCE(
      submitted_at IS NOT NULL
      AND completed_at >= submitted_at
      AND search_type = 'product'
      AND submission_kind = 'query'
      AND traffic_type = 'external'
      AND response_status = 'completed'
      AND valid_zero,
      FALSE
    ))
)
SELECT IF(@coverage_complete, COUNT(*), CAST(NULL AS INT64)) AS zero_results_searches
FROM submissions
CROSS JOIN bounds
WHERE submitted_at >= bounds.start_ts AND submitted_at < bounds.end_ts;

-- Bind {{aep_dataset}} to the ExperienceEvent dataset and {{tenant_namespace}} to the namespace represented by _tenantId in XDM.
-- Bind {{report_days}} positive integer, {{completion_lag_days}} nonnegative integer, {{report_tz}} validated IANA zone, {{coverage_complete}} boolean literal.
-- Coverage requires every final response for selected submissions, complete ingestion, and upstream conflict quarantine.
-- Submission IDs are globally unique; one immutable final response per ID; identical retries retain all values.
-- XDM searchResultsCount is double, submittedAt is date-time, and other search fields are strings; reject invalid source values before ingestion.
-- Collection excludes navigation/refinement emissions; query means deliberate submission, including a submitted suggestion.
-- Final counts exclude unrelated recommendations. Quarantine conflicting payloads; never coerce malformed or tiny nonzero values to zero.
-- Completion timestamp is at or after submission time. Search terms are sanitized before collection; raw access is restricted.
WITH settings AS (
  SELECT
    {{report_days}} AS report_days,
    {{completion_lag_days}} AS completion_lag_days,
    '{{report_tz}}' AS report_tz,
    {{coverage_complete}} AS coverage_complete
), bounds AS (
  SELECT
    to_utc_timestamp(CAST(date_sub(date_sub(to_date(from_utc_timestamp(current_timestamp(), report_tz)), completion_lag_days), report_days) AS TIMESTAMP), report_tz) AS start_ts,
    to_utc_timestamp(CAST(date_sub(to_date(from_utc_timestamp(current_timestamp(), report_tz)), completion_lag_days) AS TIMESTAMP), report_tz) AS end_ts,
    coverage_complete
  FROM settings
), extracted AS (
  SELECT
    e.timestamp AS completed_at,
    e.{{tenant_namespace}}.search.submissionId AS submission_id,
    e.{{tenant_namespace}}.search.submittedAt AS submitted_at,
    e.{{tenant_namespace}}.search.searchResultsCount AS result_count,
    e.{{tenant_namespace}}.search.searchType AS search_type,
    e.{{tenant_namespace}}.search.submissionKind AS submission_kind,
    e.{{tenant_namespace}}.search.trafficType AS traffic_type,
    e.{{tenant_namespace}}.search.responseStatus AS response_status
  FROM {{aep_dataset}} e
  CROSS JOIN bounds b
  WHERE e.eventType = 'search'
    AND e.timestamp >= b.start_ts
    AND e.timestamp < current_timestamp()
), submissions AS (
  SELECT submission_id, MIN(submitted_at) AS submitted_at
  FROM extracted
  WHERE submission_id IS NOT NULL AND TRIM(submission_id) <> ''
  GROUP BY submission_id
  HAVING MIN(submitted_at) = MAX(submitted_at)
    AND SUM(CASE WHEN
      submitted_at IS NOT NULL
      AND completed_at >= submitted_at
      AND result_count = 0
      AND search_type = 'product'
      AND submission_kind = 'query'
      AND traffic_type = 'external'
      AND response_status = 'completed'
      THEN 0 ELSE 1 END) = 0
)
SELECT CASE WHEN (SELECT coverage_complete FROM bounds)
  THEN COUNT(*) ELSE CAST(NULL AS BIGINT) END AS zero_results_searches
FROM submissions s
CROSS JOIN bounds b
WHERE s.submitted_at >= b.start_ts AND s.submitted_at < b.end_ts;