Site Search Usage Rate

{
  "event": [
    {
      "eventInfo": {
        "eventName": "search",
        "eventAction": "site_search_submitted"
      }
    }
  ],
  "search": {
    "searchTerm": "wireless headphones",
    "searchType": "product",
    "resultsCount": 24,
    "searchSource": "header_search_bar",
    "isRefire": false
  },
  "page": {
    "pageInfo": {
      "pageType": "search_results"
    }
  },
  "user": {
    "trafficType": "customer"
  }
}

{
  "event": "search",
  "search_term": "wireless headphones",
  "search_type": "product",
  "search_results_count": 24,
  "search_source": "header_search_bar",
  "is_refire": false,
  "page_type": "search_results",
  "traffic_type": "customer"
}

{
  "event": "search",
  "search_term": "wireless headphones",
  "search_type": "product",
  "search_results_count": 24,
  "search_source": "header_search_bar",
  "is_refire": false,
  "page_type": "search_results",
  "traffic_type": "customer"
}

{
  "eventType": "search",
  "search": {
    "term": "search_term",
    "type": "search_type",
    "resultsCount": "search_results_count",
    "source": "search_source",
    "isRefire": "is_refire"
  },
  "web": {
    "webPageDetails": {
      "type": "search_results"
    }
  },
  "_tenant": {
    "traffic_type": "traffic_type"
  },
  "_experience": {
    "analytics": {
      "event101": "1"
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

-- One row per event with its session key and the search parameters pivoted out of event_params
WITH events AS (
  SELECT
    CONCAT(user_pseudo_id, '.', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS session_key,
    event_name,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'traffic_type') AS traffic_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'search_type') AS search_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'search_term') AS search_term,
    (SELECT LOWER(COALESCE(value.string_value, CAST(value.int_value AS STRING))) FROM UNNEST(event_params) WHERE key = 'is_refire') IN ('true', '1') AS is_refire
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
),
-- Flag each session: a qualifying product search (non-empty term, not a results-page refire) and bot or internal traffic
sessions AS (
  SELECT
    session_key,
    LOGICAL_OR(event_name = 'search' AND search_type = 'product' AND TRIM(IFNULL(search_term, '')) != '' AND IFNULL(is_refire, FALSE) = FALSE) AS has_search,
    LOGICAL_OR(traffic_type IN ('internal', 'bot')) AS is_excluded
  FROM events
  WHERE session_key IS NOT NULL
  GROUP BY session_key
)
-- Sessions with at least one qualifying search over all eligible sessions
SELECT
  COUNTIF(has_search) AS sessions_with_search,
  COUNT(*) AS total_sessions,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(has_search), COUNT(*)), 2) AS site_search_usage_rate
FROM sessions
WHERE NOT IFNULL(is_excluded, FALSE);

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Events in the window (90 days back to yesterday) with a 30-minute inactivity session number per visitor
WITH events AS (
  SELECT
    endUserIDs._experience.ecid.id AS visitor_id,
    eventType,
    _tenant.traffic_type AS traffic_type,
    search.type AS search_type,
    search.term AS search_term,
    search.isRefire AS is_refire,
    SESS_TIMEOUT(timestamp, 60 * 30) OVER (PARTITION BY endUserIDs._experience.ecid.id ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session
  FROM aep_web_experience_event_dataset
  WHERE timestamp >= DATE_SUB(CURRENT_DATE, 90)
    AND timestamp < CURRENT_DATE
),
-- Flag each session: a qualifying product search (non-empty term, not a results-page refire) and bot or internal traffic
sessions AS (
  SELECT
    visitor_id,
    session.num AS session_num,
    BOOL_OR(eventType = 'search' AND search_type = 'product' AND TRIM(COALESCE(search_term, '')) != '' AND COALESCE(is_refire, FALSE) = FALSE) AS has_search,
    BOOL_OR(traffic_type IN ('internal', 'bot')) AS is_excluded
  FROM events
  WHERE visitor_id IS NOT NULL
  GROUP BY visitor_id, session.num
)
-- Sessions with at least one qualifying search over all eligible sessions
SELECT
  COUNT(CASE WHEN has_search THEN 1 END) AS sessions_with_search,
  COUNT(*) AS total_sessions,
  ROUND(100.0 * COUNT(CASE WHEN has_search THEN 1 END) / NULLIF(COUNT(*), 0), 2) AS site_search_usage_rate
FROM sessions
WHERE NOT COALESCE(is_excluded, FALSE);