Viewing Method

{
  "digitalData": {
    "event": [
      {
        "eventInfo": {
          "eventName": "tour:request",
          "eventAction": "generate_lead"
        },
        "category": {
          "primaryCategory": "lead",
          "leadType": "tour_request"
        },
        "listing": {
          "listingId": "LST-104233",
          "viewingMethod": "private_in_person_tour",
          "bookingId": "BKG-558120"
        }
      }
    ]
  }
}

{
  "event": "generate_lead",
  "lead_type": "tour_request",
  "viewing_method": "private_in_person_tour",
  "booking_id": "BKG-558120",
  "ecommerce": {
    "items": [
      {
        "item_id": "LST-104233"
      }
    ]
  }
}

{
  "event": "tour:request",
  "eventInfo": {
    "leadType": "tour_request"
  },
  "listing": {
    "listingId": "LST-104233",
    "viewingMethod": "private_in_person_tour",
    "bookingId": "BKG-558120"
  }
}

{
  "eventType": "leadOperation.newLead",
  "productListItems": [
    {
      "SKU": "LST-104233"
    }
  ],
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar49": "private_in_person_tour",
          "eVar50": "BKG-558120",
          "eVar51": "tour_request",
          "eVar52": "external"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Viewing Method: deduplicated tour requests (generate_lead, lead_type = tour_request) by requested and delivered viewing method,
-- with attendance, no-show and offer outcomes from the showing scheduling table joined on booking_id.
-- Set before running: start_date / end_date (inclusive), project, GA4 dataset and showing scheduling table names.
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

WITH tour_requests AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS request_date,
    event_timestamp,
    user_pseudo_id,
    items[SAFE_OFFSET(0)].item_id AS listing_id,
    (SELECT MAX(IF(ep.key = 'lead_type', ep.value.string_value, NULL)) FROM UNNEST(event_params) AS ep) AS lead_type,
    (SELECT MAX(IF(ep.key = 'booking_id', ep.value.string_value, NULL)) FROM UNNEST(event_params) AS ep) AS booking_id,
    REGEXP_REPLACE(LOWER(TRIM((SELECT MAX(IF(ep.key = 'viewing_method', ep.value.string_value, NULL)) FROM UNNEST(event_params) AS ep))), r'[ -]+', '_') AS viewing_method_raw,
    (SELECT MAX(IF(ep.key = 'traffic_type', ep.value.string_value, NULL)) FROM UNNEST(event_params) AS ep) AS traffic_type
  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)
    AND event_name = 'generate_lead'
),
deduplicated_requests AS (
  SELECT *
  FROM tour_requests
  WHERE lead_type = 'tour_request'
    AND booking_id IS NOT NULL
    AND IFNULL(traffic_type, '') <> 'internal'
  QUALIFY ROW_NUMBER() OVER (PARTITION BY booking_id ORDER BY event_timestamp DESC) = 1
),
showings AS (
  SELECT
    booking_id,
    listing_id AS showing_listing_id,
    REGEXP_REPLACE(LOWER(TRIM(delivered_viewing_method)), r'[ -]+', '_') AS delivered_raw,
    appointment_type,
    showing_status,
    appointment_start_utc,
    listing_status_at_appointment,
    offer_submitted,
    property_type
  FROM `your-project.showing_scheduling.showing_appointments`
  WHERE DATE(appointment_start_utc) >= start_date
  QUALIFY ROW_NUMBER() OVER (PARTITION BY booking_id ORDER BY updated_at DESC) = 1
),
joined AS (
  SELECT
    r.request_date,
    r.booking_id,
    r.user_pseudo_id,
    COALESCE(r.listing_id, s.showing_listing_id) AS listing_id,
    IFNULL(s.property_type, 'unknown') AS property_type,
    CASE WHEN r.viewing_method_raw IN ('private_in_person_tour', 'open_house', 'live_video_tour', 'recorded_video_tour', '3d_virtual_tour', 'self_guided_tour') THEN r.viewing_method_raw ELSE 'unknown' END AS requested_viewing_method,
    s.delivered_raw,
    s.showing_status,
    IFNULL(s.offer_submitted, FALSE) AS offer_submitted,
    IFNULL(s.appointment_start_utc <= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 72 HOUR), FALSE) AS outcome_window_closed
  FROM deduplicated_requests AS r
  LEFT JOIN showings AS s
    ON r.booking_id = s.booking_id
  WHERE IFNULL(s.appointment_type, '') NOT IN ('agent_preview', 'broker_caravan', 'inspection', 'photography')
    AND IFNULL(s.listing_status_at_appointment, '') NOT IN ('withdrawn', 'sold')
),
classified AS (
  SELECT
    *,
    CASE WHEN delivered_raw IN ('private_in_person_tour', 'open_house', 'live_video_tour', 'recorded_video_tour', '3d_virtual_tour', 'self_guided_tour') THEN delivered_raw ELSE requested_viewing_method END AS delivered_viewing_method
  FROM joined
)
SELECT
  request_date,
  requested_viewing_method,
  delivered_viewing_method,
  property_type,
  COUNT(*) AS tour_requests,
  COUNT(DISTINCT user_pseudo_id) AS requesting_users,
  COUNT(DISTINCT listing_id) AS listings_requested,
  COUNTIF(showing_status = 'attended') AS attended_tours,
  COUNTIF(showing_status = 'no_show' AND outcome_window_closed) AS no_show_tours,
  COUNTIF(showing_status = 'attended' AND offer_submitted) AS offers_after_tour,
  ROUND(SAFE_DIVIDE(COUNTIF(showing_status = 'attended'), COUNT(*)), 4) AS request_to_attendance_rate,
  ROUND(SAFE_DIVIDE(COUNTIF(showing_status = 'no_show' AND outcome_window_closed), COUNTIF(outcome_window_closed AND showing_status IN ('attended', 'no_show'))), 4) AS no_show_rate,
  ROUND(SAFE_DIVIDE(COUNTIF(showing_status = 'attended' AND offer_submitted), COUNTIF(showing_status = 'attended')), 4) AS tour_to_offer_rate
FROM classified
GROUP BY request_date, requested_viewing_method, delivered_viewing_method, property_type
ORDER BY request_date, tour_requests DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Viewing Method: deduplicated tour requests (leadOperation.newLead, eVar51 = tour_request) by requested and delivered viewing method,
-- with attendance, no-show and offer outcomes from the showing scheduling dataset joined on booking_id (eVar50).
-- Set before running: $start_date and $end_date query parameters (YYYY-MM-DD, both inclusive) and dataset names.
WITH tour_requests AS (
  SELECT
    CAST(timestamp AS DATE) AS request_date,
    timestamp,
    endUserIDs._experience.ecid.id AS ecid,
    productListItems[0].SKU AS listing_id,
    _experience.analytics.customDimensions.eVars.eVar50 AS booking_id,
    REGEXP_REPLACE(LOWER(TRIM(_experience.analytics.customDimensions.eVars.eVar49)), '[ -]+', '_') AS viewing_method_raw,
    ROW_NUMBER() OVER (PARTITION BY _experience.analytics.customDimensions.eVars.eVar50 ORDER BY timestamp DESC) AS request_rank
  FROM web_experience_events
  WHERE timestamp >= TO_TIMESTAMP('$start_date')
    AND timestamp < TO_TIMESTAMP(DATE_ADD(TO_DATE('$end_date'), 1))
    AND eventType = 'leadOperation.newLead'
    AND _experience.analytics.customDimensions.eVars.eVar51 = 'tour_request'
    AND _experience.analytics.customDimensions.eVars.eVar50 IS NOT NULL
    AND COALESCE(_experience.analytics.customDimensions.eVars.eVar52, '') <> 'internal'
),
showings AS (
  SELECT
    booking_id,
    listing_id AS showing_listing_id,
    REGEXP_REPLACE(LOWER(TRIM(delivered_viewing_method)), '[ -]+', '_') AS delivered_raw,
    appointment_type,
    showing_status,
    appointment_start_utc,
    listing_status_at_appointment,
    offer_submitted,
    property_type,
    ROW_NUMBER() OVER (PARTITION BY booking_id ORDER BY updated_at DESC) AS version_rank
  FROM showing_appointments
  WHERE CAST(appointment_start_utc AS DATE) >= TO_DATE('$start_date')
),
joined AS (
  SELECT
    r.request_date,
    r.booking_id,
    r.ecid,
    COALESCE(r.listing_id, s.showing_listing_id) AS listing_id,
    COALESCE(s.property_type, 'unknown') AS property_type,
    CASE WHEN r.viewing_method_raw IN ('private_in_person_tour', 'open_house', 'live_video_tour', 'recorded_video_tour', '3d_virtual_tour', 'self_guided_tour') THEN r.viewing_method_raw ELSE 'unknown' END AS requested_viewing_method,
    s.delivered_raw,
    s.showing_status,
    COALESCE(s.offer_submitted, FALSE) AS offer_submitted,
    COALESCE(s.appointment_start_utc <= current_timestamp() - INTERVAL 72 HOURS, FALSE) AS outcome_window_closed
  FROM tour_requests AS r
  LEFT JOIN showings AS s
    ON r.booking_id = s.booking_id
   AND s.version_rank = 1
  WHERE r.request_rank = 1
    AND COALESCE(s.appointment_type, '') NOT IN ('agent_preview', 'broker_caravan', 'inspection', 'photography')
    AND COALESCE(s.listing_status_at_appointment, '') NOT IN ('withdrawn', 'sold')
),
classified AS (
  SELECT
    *,
    CASE WHEN delivered_raw IN ('private_in_person_tour', 'open_house', 'live_video_tour', 'recorded_video_tour', '3d_virtual_tour', 'self_guided_tour') THEN delivered_raw ELSE requested_viewing_method END AS delivered_viewing_method
  FROM joined
)
SELECT
  request_date,
  requested_viewing_method,
  delivered_viewing_method,
  property_type,
  COUNT(*) AS tour_requests,
  COUNT(DISTINCT ecid) AS requesting_users,
  COUNT(DISTINCT listing_id) AS listings_requested,
  SUM(CASE WHEN showing_status = 'attended' THEN 1 ELSE 0 END) AS attended_tours,
  SUM(CASE WHEN showing_status = 'no_show' AND outcome_window_closed THEN 1 ELSE 0 END) AS no_show_tours,
  SUM(CASE WHEN showing_status = 'attended' AND offer_submitted THEN 1 ELSE 0 END) AS offers_after_tour,
  ROUND(SUM(CASE WHEN showing_status = 'attended' THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 4) AS request_to_attendance_rate,
  ROUND(SUM(CASE WHEN showing_status = 'no_show' AND outcome_window_closed THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN outcome_window_closed AND showing_status IN ('attended', 'no_show') THEN 1 ELSE 0 END), 0), 4) AS no_show_rate,
  ROUND(SUM(CASE WHEN showing_status = 'attended' AND offer_submitted THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN showing_status = 'attended' THEN 1 ELSE 0 END), 0), 4) AS tour_to_offer_rate
FROM classified
GROUP BY request_date, requested_viewing_method, delivered_viewing_method, property_type
ORDER BY request_date, tour_requests DESC;