Search-to-Booking Conversion Rate

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
WITH parameters AS (
  SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AS start_date, CURRENT_TIMESTAMP() AS as_of_timestamp
),
ranked_search_sessions AS (
  SELECT s.*, ROW_NUMBER() OVER (PARTITION BY s.session_key ORDER BY s.record_updated_at DESC, s.source_ingested_at DESC) AS record_rank
  FROM `your-project.travel_analytics.search_session_fact` AS s CROSS JOIN parameters AS p
  WHERE NULLIF(TRIM(CAST(s.session_key AS STRING)), '') IS NOT NULL
    AND s.first_search_at >= TIMESTAMP(p.start_date) AND s.first_search_at <= p.as_of_timestamp
),
eligible_sessions AS (
  SELECT session_key, DATE(first_search_at) AS search_date, first_search_at, session_end_at,
    COALESCE(NULLIF(origin_code, ''), 'unknown') AS origin_code, COALESCE(NULLIF(destination_code, ''), 'unknown') AS destination_code,
    COALESCE(NULLIF(LOWER(search_type), ''), 'unknown') AS search_type,
    CASE WHEN results_count = 0 THEN 'zero_results'
      WHEN results_count > 0 THEN 'results_available' ELSE 'unknown' END AS results_availability
  FROM ranked_search_sessions CROSS JOIN parameters AS p
  WHERE record_rank = 1 AND NULLIF(TRIM(CAST(session_key AS STRING)), '') IS NOT NULL AND first_search_at IS NOT NULL
    AND session_end_at IS NOT NULL AND session_end_at <= p.as_of_timestamp
    AND LOWER(TRIM(COALESCE(session_status, ''))) IN ('completed', 'expired')
    AND session_eligible = TRUE AND is_bot = FALSE AND is_test = FALSE AND is_internal = FALSE
),
ranked_bookings AS (
  SELECT b.*, ROW_NUMBER() OVER (PARTITION BY b.booking_id ORDER BY b.record_updated_at DESC, b.source_ingested_at DESC) AS record_rank
  FROM `your-project.travel_analytics.booking_attribution_fact` AS b CROSS JOIN parameters AS p
  WHERE NULLIF(TRIM(CAST(b.booking_id AS STRING)), '') IS NOT NULL
    AND NULLIF(TRIM(CAST(b.session_key AS STRING)), '') IS NOT NULL
    AND b.confirmed_at >= TIMESTAMP(p.start_date) AND b.confirmed_at <= p.as_of_timestamp
),
confirmed_bookings AS (
  SELECT booking_id, session_key, confirmed_at FROM ranked_bookings
  WHERE record_rank = 1 AND NULLIF(TRIM(CAST(booking_id AS STRING)), '') IS NOT NULL
    AND NULLIF(TRIM(CAST(session_key AS STRING)), '') IS NOT NULL
    AND LOWER(booking_status) IN ('confirmed', 'completed', 'ticketed')
    AND is_test = FALSE AND is_internal = FALSE AND is_fraudulent = FALSE
),
scored AS (
  SELECT s.*, EXISTS (SELECT 1 FROM confirmed_bookings AS b WHERE b.session_key = s.session_key AND b.confirmed_at > s.first_search_at AND b.confirmed_at <= s.session_end_at) AS converted
  FROM eligible_sessions AS s
)
SELECT search_date, origin_code, destination_code, search_type, results_availability,
  COUNT(*) AS eligible_search_sessions, COUNTIF(converted) AS converted_search_sessions,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(converted), COUNT(*)), 2) AS search_to_booking_conversion_rate_pct
FROM scored GROUP BY search_date, origin_code, destination_code, search_type, results_availability
ORDER BY search_date DESC, origin_code, destination_code, search_type, results_availability;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH parameters AS (
  SELECT CURRENT_DATE - INTERVAL '90' DAY AS start_date, CURRENT_TIMESTAMP AS as_of_timestamp
),
ranked_search_sessions AS (
  SELECT s.*, ROW_NUMBER() OVER (PARTITION BY s.session_key ORDER BY s.record_updated_at DESC, s.source_ingested_at DESC) AS record_rank
  FROM search_session_fact_backend_dataset AS s CROSS JOIN parameters AS p
  WHERE NULLIF(TRIM(CAST(s.session_key AS VARCHAR)), '') IS NOT NULL
    AND s.first_search_at >= CAST(p.start_date AS TIMESTAMP) AND s.first_search_at <= p.as_of_timestamp
),
eligible_sessions AS (
  SELECT session_key, CAST(first_search_at AS DATE) AS search_date, first_search_at, session_end_at,
    COALESCE(NULLIF(origin_code, ''), 'unknown') AS origin_code, COALESCE(NULLIF(destination_code, ''), 'unknown') AS destination_code,
    COALESCE(NULLIF(LOWER(search_type), ''), 'unknown') AS search_type,
    CASE WHEN results_count = 0 THEN 'zero_results'
      WHEN results_count > 0 THEN 'results_available' ELSE 'unknown' END AS results_availability
  FROM ranked_search_sessions CROSS JOIN parameters AS p
  WHERE record_rank = 1 AND NULLIF(TRIM(CAST(session_key AS VARCHAR)), '') IS NOT NULL AND first_search_at IS NOT NULL
    AND session_end_at IS NOT NULL AND session_end_at <= p.as_of_timestamp
    AND LOWER(TRIM(COALESCE(session_status, ''))) IN ('completed', 'expired')
    AND session_eligible = TRUE AND is_bot = FALSE AND is_test = FALSE AND is_internal = FALSE
),
ranked_bookings AS (
  SELECT b.*, ROW_NUMBER() OVER (PARTITION BY b.booking_id ORDER BY b.record_updated_at DESC, b.source_ingested_at DESC) AS record_rank
  FROM booking_attribution_fact_backend_dataset AS b CROSS JOIN parameters AS p
  WHERE NULLIF(TRIM(CAST(b.booking_id AS VARCHAR)), '') IS NOT NULL
    AND NULLIF(TRIM(CAST(b.session_key AS VARCHAR)), '') IS NOT NULL
    AND b.confirmed_at >= CAST(p.start_date AS TIMESTAMP) AND b.confirmed_at <= p.as_of_timestamp
),
confirmed_bookings AS (
  SELECT booking_id, session_key, confirmed_at FROM ranked_bookings
  WHERE record_rank = 1 AND NULLIF(TRIM(CAST(booking_id AS VARCHAR)), '') IS NOT NULL
    AND NULLIF(TRIM(CAST(session_key AS VARCHAR)), '') IS NOT NULL
    AND LOWER(booking_status) IN ('confirmed', 'completed', 'ticketed')
    AND is_test = FALSE AND is_internal = FALSE AND is_fraudulent = FALSE
),
scored AS (
  SELECT s.*, CASE WHEN EXISTS (SELECT 1 FROM confirmed_bookings AS b WHERE b.session_key = s.session_key AND b.confirmed_at > s.first_search_at AND b.confirmed_at <= s.session_end_at) THEN 1 ELSE 0 END AS converted
  FROM eligible_sessions AS s
)
SELECT search_date, origin_code, destination_code, search_type, results_availability,
  COUNT(*) AS eligible_search_sessions, SUM(converted) AS converted_search_sessions,
  ROUND(100.0 * SUM(converted) / NULLIF(COUNT(*), 0), 2) AS search_to_booking_conversion_rate_pct
FROM scored GROUP BY search_date, origin_code, destination_code, search_type, results_availability
ORDER BY search_date DESC, origin_code, destination_code, search_type, results_availability;