Time on Property Page

{
  "digitalData": {
    "page": {
      "pageInfo": {
        "pageID": "listing_detail:LST-48213",
        "pageName": "listings:listing_detail:LST-48213",
        "destinationURL": "https://www.example-realty.com/listings/austin-tx/lst-48213",
        "language": "en-US",
        "sysEnv": "web"
      },
      "category": {
        "primaryCategory": "listings",
        "subCategory1": "for_sale",
        "pageType": "listing_detail"
      },
      "attributes": {
        "listingID": "LST-48213",
        "propertyType": "condo",
        "listingType": "for_sale",
        "listingCity": "Austin",
        "listingRegion": "TX",
        "priceBand": "400k-500k",
        "listingStatus": "active"
      }
    },
    "product": [
      {
        "productInfo": {
          "productID": "LST-48213",
          "productName": "2 Bed Condo - Downtown Austin"
        },
        "category": {
          "primaryCategory": "listings",
          "productType": "condo"
        },
        "attributes": {
          "listingType": "for_sale",
          "listingCity": "Austin",
          "listingRegion": "TX",
          "priceBand": "400k-500k",
          "listingStatus": "active"
        }
      }
    ],
    "event": [
      {
        "eventInfo": {
          "eventName": "pageView",
          "eventAction": "page_view",
          "type": "page"
        }
      }
    ]
  }
}

{
  "event": "page_view",
  "page_type": "listing_detail",
  "content_group": "listing_detail",
  "page_location": "https://www.example-realty.com/listings/austin-tx/lst-48213",
  "page_title": "2 Bed Condo - Downtown Austin",
  "listing_id": "LST-48213",
  "property_type": "condo",
  "listing_type": "for_sale",
  "listing_city": "Austin",
  "listing_region": "TX",
  "price_band": "400k-500k",
  "listing_status": "active"
}

{
  "event": "pageView",
  "page": {
    "pageInfo": {
      "pageName": "listings:listing_detail:LST-48213",
      "pageURL": "https://www.example-realty.com/listings/austin-tx/lst-48213",
      "siteSection": "listings",
      "pageType": "listing_detail",
      "language": "en-US"
    }
  },
  "listing": {
    "listingID": "LST-48213",
    "listingName": "2 Bed Condo - Downtown Austin",
    "propertyType": "condo",
    "listingType": "for_sale",
    "listingCity": "Austin",
    "listingRegion": "TX",
    "priceBand": "400k-500k",
    "listingStatus": "active"
  },
  "trafficType": "external"
}

{
  "eventType": "web.webpagedetails.pageViews",
  "timestamp": "2026-09-16T14:48:00.000Z",
  "web": {
    "webPageDetails": {
      "name": "listings:listing_detail:LST-48213",
      "URL": "https://www.example-realty.com/listings/austin-tx/lst-48213",
      "siteSection": "listings",
      "pageViews": {
        "value": 1
      }
    }
  },
  "productListItems": [
    {
      "SKU": "LST-48213",
      "name": "2 Bed Condo - Downtown Austin"
    }
  ],
  "_tenant": {
    "pageType": "listing_detail",
    "listing": {
      "listingID": "LST-48213",
      "propertyType": "condo",
      "listingType": "for_sale",
      "listingCity": "Austin",
      "listingRegion": "TX",
      "priceBand": "400k-500k",
      "listingStatus": "active"
    },
    "trafficType": "external"
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Time on Property Page: average foreground engaged seconds per listing detail page view (ratio of sums).
-- Engaged time comes only from the engagement_time_msec parameter; timestamps are used for ordering and
-- attribution, never to compute durations. GA4 known-bot filtering is applied before export.
-- event_date in the export is already in the property reporting time zone.
WITH base AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS event_date,
    event_name,
    event_timestamp,
    user_pseudo_id,
    (SELECT MAX(IF(key = 'page_type', value.string_value, NULL)) FROM UNNEST(event_params)) AS page_type,
    (SELECT MAX(IF(key = 'listing_id', COALESCE(value.string_value, CAST(value.int_value AS STRING)), NULL)) FROM UNNEST(event_params)) AS listing_id,
    (SELECT MAX(IF(key = 'page_location', value.string_value, NULL)) FROM UNNEST(event_params)) AS page_location,
    (SELECT MAX(IF(key = 'ga_session_id', value.int_value, NULL)) FROM UNNEST(event_params)) AS ga_session_id,
    (SELECT MAX(IF(key = 'engagement_time_msec', value.int_value, NULL)) FROM UNNEST(event_params)) AS engagement_time_msec,
    (SELECT MAX(IF(key = 'traffic_type', value.string_value, NULL)) FROM UNNEST(event_params)) AS traffic_type,
    (SELECT MAX(IF(key = 'debug_mode', COALESCE(value.int_value, SAFE_CAST(value.string_value AS INT64)), NULL)) FROM UNNEST(event_params)) AS debug_mode
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY))
                          AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
),

-- Internal traffic, debug hits and developer/QA hosts are removed from numerator and denominator alike.
clean AS (
  SELECT *
  FROM base
  WHERE COALESCE(traffic_type, '') <> 'internal'
    AND COALESCE(debug_mode, 0) = 0
    AND ga_session_id IS NOT NULL
    AND page_location IS NOT NULL
    AND NOT REGEXP_CONTAINS(LOWER(COALESCE(NET.HOST(page_location), '')), r'(^localhost$|^127\.0\.0\.1$|staging|^dev\.|^qa\.|\.dev\.|\.qa\.)')
),

-- Denominator: every listing detail page_view, including zero-engagement and bounced views.
-- next_view_ts closes the attribution window when the same listing URL is viewed again in the session.
listing_views AS (
  SELECT
    event_date,
    user_pseudo_id,
    ga_session_id,
    page_location,
    event_timestamp AS view_ts,
    ROW_NUMBER() OVER (PARTITION BY user_pseudo_id, ga_session_id, page_location ORDER BY event_timestamp) AS view_seq,
    LEAD(event_timestamp) OVER (PARTITION BY user_pseudo_id, ga_session_id, page_location ORDER BY event_timestamp) AS next_view_ts
  FROM clean
  WHERE event_name = 'page_view'
    AND page_type = 'listing_detail'
    AND listing_id IS NOT NULL
),

-- Numerator source: engagement_time_msec from every event type (user_engagement, page_view, scroll, ...).
engagement_hits AS (
  SELECT
    user_pseudo_id,
    ga_session_id,
    page_location,
    event_timestamp,
    engagement_time_msec
  FROM clean
  WHERE engagement_time_msec > 0
),

-- Each engagement hit is attributed to the most recent preceding listing page_view on the same
-- user_pseudo_id + ga_session_id + page_location; engaged time is capped per view at 30 minutes.
per_view AS (
  SELECT
    v.event_date,
    v.user_pseudo_id,
    v.ga_session_id,
    v.page_location,
    v.view_seq,
    LEAST(COALESCE(SUM(e.engagement_time_msec), 0), 1800000) AS capped_engaged_msec
  FROM listing_views AS v
  LEFT JOIN engagement_hits AS e
    ON  e.user_pseudo_id = v.user_pseudo_id
    AND e.ga_session_id  = v.ga_session_id
    AND e.page_location  = v.page_location
    AND e.event_timestamp >= v.view_ts
    AND (v.next_view_ts IS NULL OR e.event_timestamp < v.next_view_ts)
  GROUP BY v.event_date, v.user_pseudo_id, v.ga_session_id, v.page_location, v.view_seq
)

SELECT
  event_date,
  COUNT(*) AS listing_page_views,
  ROUND(SUM(capped_engaged_msec) / 1000, 1) AS total_engaged_seconds,
  ROUND(SAFE_DIVIDE(SUM(capped_engaged_msec) / 1000, COUNT(*)), 2) AS avg_engaged_seconds_per_listing_view,
  ROUND(APPROX_QUANTILES(capped_engaged_msec, 100)[OFFSET(50)] / 1000, 2) AS median_engaged_seconds_per_listing_view
FROM per_view
GROUP BY event_date
ORDER BY event_date

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Time on Property Page, Adobe methodology: average seconds spent per listing detail page view (ratio of sums).
-- Adobe does not collect a foreground engagement timer, so this query follows Adobe Analytics hit-gap
-- time-spent logic: time on a page view = seconds until the next hit (any eventType, including pings and
-- link clicks) from the same ECID in the same session. The last hit of a session receives 0 seconds, as
-- Adobe records no time for an exit page without a following ping. Results therefore differ from the
-- GA4 foreground engagement figure and must not be compared one-to-one.
-- _tenant is the organisation tenant namespace in the XDM schema; replace it with your tenant id.
-- Replace your_aep_web_events_dataset with the name of the web ExperienceEvent dataset.
WITH hits AS (
  SELECT
    _id AS hit_id,
    identityMap['ECID'][0].id AS ecid,
    timestamp AS hit_ts,
    eventType AS event_type,
    _tenant.pageType AS page_type,
    _tenant.listing.listingID AS listing_id,
    _tenant.trafficType AS traffic_type,
    LOWER(regexp_extract(COALESCE(web.webPageDetails.URL, ''), '^[a-zA-Z]+://([^/:?#]+)', 1)) AS page_host,
    environment.browserDetails.userAgent AS user_agent
  FROM your_aep_web_events_dataset
  -- UTC prefilter widened by one day on each side for partition pruning; the exact window is applied on the local date below.
  WHERE timestamp >= date_sub(current_date(), 29)
    AND timestamp <  date_add(current_date(), 1)
    AND identityMap['ECID'][0].id IS NOT NULL
),

-- Internal traffic, developer/QA hosts and bot user agents are removed before sessionisation so they never create gaps.
clean AS (
  SELECT *
  FROM hits
  WHERE COALESCE(traffic_type, '') <> 'internal'
    AND NOT (page_host RLIKE '(^localhost$|^127\\.0\\.0\\.1$|staging|^dev\\.|^qa\\.|\\.dev\\.|\\.qa\\.)')
    AND NOT (LOWER(COALESCE(user_agent, '')) RLIKE 'bot|crawl|spider|slurp|headless|lighthouse|pingdom|monitor')
),

-- Sessions: a new session starts after more than 30 minutes of inactivity for the same ECID.
flagged AS (
  SELECT
    *,
    CASE
      WHEN LAG(hit_ts) OVER (PARTITION BY ecid ORDER BY hit_ts, hit_id) IS NULL THEN 1
      WHEN unix_timestamp(hit_ts) - unix_timestamp(LAG(hit_ts) OVER (PARTITION BY ecid ORDER BY hit_ts, hit_id)) > 1800 THEN 1
      ELSE 0
    END AS is_new_session
  FROM clean
),

sessionized AS (
  SELECT
    *,
    SUM(is_new_session) OVER (PARTITION BY ecid ORDER BY hit_ts, hit_id
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_seq
  FROM flagged
),

-- Seconds until the next hit of any eventType in the same session; NULL for the last hit of a session.
with_next AS (
  SELECT
    *,
    LEAD(hit_ts) OVER (PARTITION BY ecid, session_seq ORDER BY hit_ts, hit_id) AS next_hit_ts
  FROM sessionized
),

-- Denominator: every listing detail page view; exit views keep 0 seconds; each view is capped at 1800 s.
-- Replace America/New_York with the reporting time zone of the property.
listing_views AS (
  SELECT
    to_date(from_utc_timestamp(hit_ts, 'America/New_York')) AS event_date,
    LEAST(COALESCE(unix_timestamp(next_hit_ts) - unix_timestamp(hit_ts), 0), 1800) AS seconds_spent
  FROM with_next
  WHERE event_type = 'web.webpagedetails.pageViews'
    AND page_type = 'listing_detail'
    AND listing_id IS NOT NULL
    AND to_date(from_utc_timestamp(hit_ts, 'America/New_York')) BETWEEN date_sub(current_date(), 28) AND date_sub(current_date(), 1)
)

SELECT
  event_date,
  COUNT(*) AS listing_page_views,
  SUM(seconds_spent) AS total_seconds_spent,
  ROUND(CAST(SUM(seconds_spent) AS DOUBLE) / NULLIF(COUNT(*), 0), 2) AS avg_seconds_spent_per_listing_view,
  percentile_approx(seconds_spent, 0.5) AS median_seconds_spent_per_listing_view
FROM listing_views
GROUP BY event_date
ORDER BY event_date