Order Status Checks

{
  "page": {
    "pageInfo": {
      "pageName": "Order Status",
      "pageType": "order_status",
      "destinationURL": "https://www.example.com/account/orders/status?transaction_id=1029384"
    }
  },
  "user": [
    {
      "profile": [
        {
          "profileInfo": {
            "loginStatus": "logged_in"
          }
        }
      ]
    }
  ],
  "event": [
    {
      "eventInfo": {
        "eventName": "page_view",
        "session_id": "{{session_id}}",
        "transaction_id": "{{transaction_id}}",
        "order_status": "{{order_status}}",
        "login_status": "logged_in",
        "lookup_success": true,
        "entry_source": "account_menu",
        "traffic_type": "external"
      }
    }
  ]
}

{
  "event": "page_view",
  "page_title": "Order Status",
  "page_location": "https://www.example.com/account/orders/status?transaction_id=1029384",
  "page_path": "/account/orders/status",
  "page_referrer": "https://www.example.com/account/orders",
  "page_type": "order_status",
  "session_id": "{{session_id}}",
  "transaction_id": "{{transaction_id}}",
  "order_status": "{{order_status}}",
  "login_status": "logged_in",
  "lookup_success": true,
  "entry_source": "account_menu",
  "traffic_type": "external"
}

{
  "event": "pageView",
  "page": {
    "pageName": "Order Status",
    "pageType": "order_status"
  },
  "session_id": "{{session_id}}",
  "transaction_id": "{{transaction_id}}",
  "order_status": "{{order_status}}",
  "login_status": "logged_in",
  "lookup_success": true,
  "entry_source": "account_menu",
  "traffic_type": "external"
}

{
  "eventType": "web.webpagedetails.pageViews",
  "web": {
    "webPageDetails": {
      "name": "Order Status",
      "pageViews": {
        "value": 1
      }
    }
  },
  "_experience": {
    "analytics": {
      "session_id": "{{session_id}}",
      "transaction_id": "{{transaction_id}}",
      "order_status": "{{order_status}}",
      "login_status": "logged_in",
      "lookup_success": true,
      "entry_source": "account_menu",
      "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);

-- Order status page views with their parameters pivoted to one row per event
WITH status_views AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS check_date,
    MAX(IF(ep.key = 'page_type', ep.value.string_value, NULL)) AS page_type,
    MAX(IF(ep.key = 'session_id', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS session_id,
    MAX(IF(ep.key = 'transaction_id', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS transaction_id,
    MAX(IF(ep.key = 'lookup_success', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS lookup_success,
    MAX(IF(ep.key = 'traffic_type', ep.value.string_value, NULL)) AS traffic_type
  FROM `your-project.analytics_123456789.events_*`,
    UNNEST(event_params) AS ep
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
    AND event_name = 'page_view'
  GROUP BY event_date, user_pseudo_id, event_timestamp, event_bundle_sequence_id
)
-- One check per order per session, successful lookups only, external traffic with a transaction_id, attributed to the check date
SELECT
  check_date,
  COUNT(DISTINCT CONCAT(session_id, '|', transaction_id)) AS order_status_checks
FROM status_views
WHERE page_type = 'order_status'
  AND lookup_success IN ('true', '1')
  AND COALESCE(traffic_type, 'external') != 'internal'
  AND transaction_id IS NOT NULL AND transaction_id != ''
  AND session_id IS NOT NULL
GROUP BY check_date
ORDER BY check_date;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Relative reporting window: the last 90 complete days
WITH params AS (
  SELECT date_sub(current_date(), 90) AS start_date, date_sub(current_date(), 1) AS end_date
),
-- Order status page views with the lookup fields captured on each event
status_views AS (
  SELECT
    to_date(e.timestamp) AS check_date,
    CAST(e._experience.analytics.session_id AS STRING) AS session_id,
    CAST(e._experience.analytics.transaction_id AS STRING) AS transaction_id,
    lower(CAST(e._experience.analytics.lookup_success AS STRING)) AS lookup_success,
    e._experience.analytics.traffic_type AS traffic_type
  FROM aep_web_events_dataset e
  CROSS JOIN params p
  WHERE e.eventType = 'web.webpagedetails.pageViews'
    AND e.web.webPageDetails.name = 'Order Status'
    AND to_date(e.timestamp) BETWEEN p.start_date AND p.end_date
)
-- One check per order per session, successful lookups only, external traffic with a transaction_id, attributed to the check date
SELECT
  check_date,
  COUNT(DISTINCT concat(session_id, '|', transaction_id)) AS order_status_checks
FROM status_views
WHERE lookup_success IN ('true', '1')
  AND COALESCE(traffic_type, 'external') <> 'internal'
  AND transaction_id IS NOT NULL AND transaction_id <> ''
  AND session_id IS NOT NULL
GROUP BY check_date
ORDER BY check_date;