Average Delivery Time

{
  "transaction": {
    "averageDeliveryTime": 3.5
  }
}

{
  "event": "purchase",
  "ecommerce": {
    "average_delivery_time": 3.5
  }
}

{
  "event": "purchase",
  "ecommerce": {
    "averageDeliveryTime": 3.5
  }
}

{
  "_experience": {
    "analytics": {
      "customDimensions": {
        "averageDeliveryTime": 3.5
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL (GA4 Export)
-- ==========================================
SELECT AVG(TIMESTAMP_DIFF(delivery_time, order_time, MINUTE)) AS average_delivery_time
FROM (
  SELECT
    TIMESTAMP_MICROS(MAX(CASE WHEN event_name = 'purchase' THEN event_timestamp END)) AS order_time,
    TIMESTAMP_MICROS(MAX(CASE WHEN event_name = 'delivery' THEN event_timestamp END)) AS delivery_time
  FROM `your-project.analytics_123456789.events_*`, UNNEST(event_params) AS params
  WHERE event_name IN ('purchase', 'delivery')
  GROUP BY user_pseudo_id
)
WHERE delivery_time IS NOT NULL AND order_time IS NOT NULL;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL (XDM Dataset)
-- ==========================================
SELECT AVG(DATEDIFF(delivery_time, order_time, 'minute')) AS average_delivery_time
FROM (
  SELECT
    MAX(CASE WHEN xdm:eventType = 'commerce.purchases' THEN xdm:timestamp END) AS order_time,
    MAX(CASE WHEN xdm:eventType = 'commerce.deliveries' THEN xdm:timestamp END) AS delivery_time
  FROM your_dataset.your_table
  WHERE xdm:eventType IN ('commerce.purchases', 'commerce.deliveries')
  GROUP BY xdm:identityMap.ecid.id
)
WHERE delivery_time IS NOT NULL AND order_time IS NOT NULL;