Search Exits

{
  "page": {
    "pageInfo": {
      "pageName": "search results",
      "pageID": "search"
    },
    "category": {
      "primaryCategory": "search",
      "pageType": "search_results"
    },
    "attributes": {
      "search_term": "term",
      "search_results_count": 24,
      "search_type": "product",
      "page_number": 1,
      "traffic_type": "external"
    }
  }
}

{
  "event": "view_search_results",
  "search_term": "term",
  "search_results_count": 24,
  "search_type": "product",
  "page_type": "search_results",
  "page_number": 1,
  "traffic_type": "external"
}

{
  "event": "pageView",
  "eventInfo": {
    "pageType": "search_results"
  },
  "web": {
    "webPageDetails": {
      "name": "search results",
      "pageViews": {
        "value": 1
      }
    }
  },
  "_tenantId": {
    "search_term": "term",
    "search_results_count": 24,
    "search_type": "product",
    "page_number": 1,
    "page_type": "search_results",
    "traffic_type": "external"
  }
}

{
  "eventType": "web.webpagedetails.pageViews",
  "web.webPageDetails.pageViews.value": 1,
  "web.webPageDetails.name": "search results",
  "_tenantId.search_term": "term",
  "_tenantId.search_results_count": 24,
  "_tenantId.search_type": "product",
  "_tenantId.page_number": 1,
  "_tenantId.page_type": "search_results",
  "_tenantId.traffic_type": "external"
}

-- ==========================================
-- 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);

-- Page-level views (page_view and view_search_results) per session; one day either side so sessions crossing midnight are complete
WITH page_views 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,
    PARSE_DATE('%Y%m%d', event_date) AS event_day,
    event_timestamp,
    event_name,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_type') AS page_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 = 'traffic_type') AS traffic_type
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(start_date, INTERVAL 1 DAY))
                          AND FORMAT_DATE('%Y%m%d', DATE_ADD(end_date, INTERVAL 1 DAY))
    AND event_name IN ('page_view', 'view_search_results')
),
-- Last page view of each session (results-page view wins a timestamp tie), with a session-level internal/bot flag
last_views AS (
  SELECT
    *,
    LOGICAL_OR(COALESCE(traffic_type, '') IN ('internal', 'bot')) OVER (PARTITION BY session_key) AS excluded_traffic
  FROM page_views
  WHERE session_key IS NOT NULL
  QUALIFY ROW_NUMBER() OVER (PARTITION BY session_key ORDER BY event_timestamp DESC, IF(event_name = 'view_search_results', 0, 1)) = 1
)
-- Sessions whose last page view is a search results page, dated by that view, excluding store locator and internal/bot traffic
SELECT COUNT(DISTINCT session_key) AS search_exits
FROM last_views
WHERE (event_name = 'view_search_results' OR page_type = 'search_results')
  AND event_day BETWEEN start_date AND end_date
  AND COALESCE(search_type, '') != 'store_locator'
  AND NOT excluded_traffic;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Relative reporting window
WITH params AS (
  SELECT date_sub(current_date(), 90) AS start_date, date_sub(current_date(), 1) AS end_date
),
-- Every web event assigned to its 30-minute-inactivity session per ECID; one day either side so sessions crossing midnight are complete
sessionized AS (
  SELECT
    identityMap['ECID'][0].id AS ecid,
    timestamp,
    eventType,
    _tenantId.page_type AS page_type,
    _tenantId.search_type AS search_type,
    _tenantId.traffic_type AS traffic_type,
    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 aep_web_events CROSS JOIN params
  WHERE timestamp >= date_sub(params.start_date, 1)
    AND timestamp < date_add(params.end_date, 2)
    AND identityMap['ECID'][0].id IS NOT NULL
),
-- Last page view of each session (results-page view wins a timestamp tie), with a session-level internal/bot flag
last_views AS (
  SELECT
    CONCAT(ecid, '.', CAST(session.num AS STRING)) AS session_key,
    timestamp,
    page_type,
    search_type,
    MAX(CASE WHEN COALESCE(traffic_type, '') IN ('internal', 'bot') THEN 1 ELSE 0 END) OVER (PARTITION BY ecid, session.num) AS excluded_traffic,
    ROW_NUMBER() OVER (PARTITION BY ecid, session.num ORDER BY timestamp DESC, CASE WHEN page_type = 'search_results' THEN 0 ELSE 1 END) AS rn
  FROM sessionized
  WHERE eventType = 'web.webpagedetails.pageViews'
)
-- Sessions whose last page view is a search results page, dated by that view, excluding store locator and internal/bot traffic
SELECT COUNT(DISTINCT session_key) AS search_exits
FROM last_views CROSS JOIN params
WHERE rn = 1
  AND page_type = 'search_results'
  AND CAST(timestamp AS DATE) BETWEEN params.start_date AND params.end_date
  AND COALESCE(search_type, '') <> 'store_locator'
  AND excluded_traffic = 0;