Self-Service Resolution Rate

{
  "page": {
    "pageInfo": {
      "pageID": "ART-1",
      "pageName": "article",
      "destinationURL": "https://example.com/help/article-1"
    },
    "category": {
      "pageType": "help_center"
    },
    "attributes": {
      "articleId": "ART-1",
      "trafficType": "external"
    }
  },
  "user": [
    {
      "profile": [
        {
          "profileInfo": {
            "profileID": "USER-1"
          }
        }
      ]
    }
  ],
  "event": [
    {
      "eventInfo": {
        "eventName": "pageView"
      }
    },
    {
      "eventInfo": {
        "eventName": "form_submit"
      },
      "attributes": {
        "formType": "support_contact",
        "supportChannel": "chat",
        "trafficType": "external"
      }
    }
  ]
}

[
  {
    "event": "page_view",
    "page_type": "help_center",
    "page_location": "https://example.com/help/article-1",
    "article_id": "ART-1",
    "article_title": "article",
    "traffic_type": "external",
    "user_id": "USER-1"
  },
  {
    "event": "submit_form",
    "form_type": "support_contact",
    "support_channel": "chat",
    "traffic_type": "external",
    "user_id": "USER-1"
  }
]

[
  {
    "event": "pageView",
    "web": {
      "webPageDetails": {
        "name": "article",
        "URL": "https://example.com/help/article-1",
        "siteSection": "help_center"
      }
    },
    "identityMap": {
      "CRMID": [
        {
          "id": "USER-1",
          "authenticatedState": "authenticated",
          "primary": false
        }
      ]
    },
    "_tenantId": {
      "articleId": "ART-1",
      "trafficType": "external"
    }
  },
  {
    "event": "form_submit",
    "identityMap": {
      "CRMID": [
        {
          "id": "USER-1",
          "authenticatedState": "authenticated",
          "primary": false
        }
      ]
    },
    "_tenantId": {
      "formType": "support_contact",
      "supportChannel": "chat",
      "trafficType": "external"
    }
  }
]

[
  {
    "eventType": "web.webpagedetails.pageViews",
    "web": {
      "webPageDetails": {
        "name": "article",
        "URL": "https://example.com/help/article-1",
        "siteSection": "help_center",
        "pageViews": {
          "value": 1
        }
      }
    },
    "_tenantId": {
      "articleId": "ART-1",
      "trafficType": "external"
    },
    "identityMap": {
      "ECID": [
        {
          "id": "ECID-1",
          "authenticatedState": "ambiguous",
          "primary": true
        }
      ],
      "CRMID": [
        {
          "id": "USER-1",
          "authenticatedState": "authenticated",
          "primary": false
        }
      ]
    }
  },
  {
    "eventType": "web.formFilledOut",
    "_tenantId": {
      "formType": "support_contact",
      "supportChannel": "chat",
      "trafficType": "external"
    },
    "identityMap": {
      "ECID": [
        {
          "id": "ECID-1",
          "authenticatedState": "ambiguous",
          "primary": true
        }
      ],
      "CRMID": [
        {
          "id": "USER-1",
          "authenticatedState": "authenticated",
          "primary": false
        }
      ]
    }
  }
]

-- ==========================================
-- 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);
DECLARE contact_page_type STRING DEFAULT 'contact';

-- Page views and support contacts in range plus one day for the 24h window; internal and agent traffic (traffic_type = internal) removed
WITH events AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS event_day,
    event_name,
    event_timestamp,
    user_id,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_type') AS page_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'form_type') AS form_type
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date)
                          AND FORMAT_DATE('%Y%m%d', DATE_ADD(end_date, INTERVAL 1 DAY))
    AND event_name IN ('page_view', 'submit_form')
    AND COALESCE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'traffic_type'), '') != 'internal'
),
-- Per session: landing page type, first help_center view and its day, user_id if the user signed in
sessions AS (
  SELECT
    user_pseudo_id,
    ga_session_id,
    MAX(user_id) AS user_id,
    MIN_BY(COALESCE(page_type, ''), event_timestamp) AS landing_page_type,
    MIN(IF(page_type = 'help_center', event_timestamp, NULL)) AS first_help_ts,
    MIN(IF(page_type = 'help_center', event_day, NULL)) AS help_date
  FROM events
  WHERE event_name = 'page_view'
  GROUP BY user_pseudo_id, ga_session_id
),
-- Denominator: help-center sessions credited to the day of the first help view; sessions landing on the contact page excluded
help_sessions AS (
  SELECT *
  FROM sessions
  WHERE first_help_ts IS NOT NULL
    AND landing_page_type != contact_page_type
    AND help_date BETWEEN start_date AND end_date
),
-- Support contacts: submit_form with form_type = support_contact, every support_channel (form, chat, phone)
contacts AS (
  SELECT user_id, user_pseudo_id, event_timestamp AS contact_ts
  FROM events
  WHERE event_name = 'submit_form'
    AND form_type = 'support_contact'
),
-- Unresolved: a contact by the same device or signed-in user within 24h of the first help view, across sessions and devices
contacted AS (
  SELECT DISTINCT h.user_pseudo_id, h.ga_session_id
  FROM help_sessions h
  JOIN contacts c
    ON (c.user_pseudo_id = h.user_pseudo_id OR (c.user_id IS NOT NULL AND c.user_id = h.user_id))
   AND c.contact_ts >= h.first_help_ts
   AND c.contact_ts < h.first_help_ts + 24 * 3600 * 1000000
)
-- Rate per help-session date: sessions with no contact in the window over all help-center sessions
SELECT
  h.help_date,
  COUNT(*) AS help_center_sessions,
  COUNT(c.ga_session_id) AS sessions_followed_by_contact,
  ROUND(SAFE_DIVIDE(COUNT(*) - COUNT(c.ga_session_id), COUNT(*)) * 100, 2) AS self_service_resolution_rate
FROM help_sessions h
LEFT JOIN contacted c
  ON c.user_pseudo_id = h.user_pseudo_id
 AND c.ga_session_id = h.ga_session_id
GROUP BY h.help_date
ORDER BY h.help_date;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Window: help sessions from 90 days ago to yesterday; events read through today for the 24h contact window; contact page type = 'contact'

-- Page views and support contacts in range plus one day for the 24h window; internal and agent traffic (trafficType = internal) removed; 30-minute sessions per ECID
WITH events AS (
  SELECT
    timestamp,
    eventType,
    identityMap['ECID'][0].id AS ecid,
    identityMap['CRMID'][0].id AS user_id,
    web.webPageDetails.siteSection AS page_type,
    _tenantId.formType AS form_type,
    SESS_TIMEOUT(timestamp, 60 * 30) OVER (
      PARTITION BY identityMap['ECID'][0].id ORDER BY timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ).num AS session_num
  FROM aep_web_events_dataset
  WHERE TO_DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), 90) AND CURRENT_DATE()
    AND eventType IN ('web.webpagedetails.pageViews', 'web.formFilledOut')
    AND COALESCE(_tenantId.trafficType, '') <> 'internal'
),
-- Per session: landing page type, first help_center view and its day, user ID if the user signed in
sessions AS (
  SELECT
    ecid,
    session_num,
    MAX(user_id) AS user_id,
    MIN_BY(COALESCE(page_type, ''), timestamp) AS landing_page_type,
    MIN(CASE WHEN page_type = 'help_center' THEN timestamp END) AS first_help_ts,
    TO_DATE(MIN(CASE WHEN page_type = 'help_center' THEN timestamp END)) AS help_date
  FROM events
  WHERE eventType = 'web.webpagedetails.pageViews'
  GROUP BY ecid, session_num
),
-- Denominator: help-center sessions credited to the day of the first help view; sessions landing on the contact page excluded
help_sessions AS (
  SELECT *
  FROM sessions
  WHERE first_help_ts IS NOT NULL
    AND landing_page_type <> 'contact'
    AND help_date BETWEEN DATE_SUB(CURRENT_DATE(), 90) AND DATE_SUB(CURRENT_DATE(), 1)
),
-- Support contacts: web.formFilledOut with formType = support_contact, every supportChannel (form, chat, phone)
contacts AS (
  SELECT ecid, user_id, timestamp AS contact_ts
  FROM events
  WHERE eventType = 'web.formFilledOut'
    AND form_type = 'support_contact'
),
-- Unresolved: a contact by the same device or signed-in user within 24h of the first help view, across sessions and devices
contacted AS (
  SELECT DISTINCT h.ecid, h.session_num
  FROM help_sessions h
  JOIN contacts c
    ON (c.ecid = h.ecid OR (c.user_id IS NOT NULL AND c.user_id = h.user_id))
   AND c.contact_ts >= h.first_help_ts
   AND c.contact_ts < h.first_help_ts + INTERVAL 24 HOURS
)
-- Rate per help-session date: sessions with no contact in the window over all help-center sessions
SELECT
  h.help_date,
  COUNT(*) AS help_center_sessions,
  COUNT(c.session_num) AS sessions_followed_by_contact,
  ROUND((COUNT(*) - COUNT(c.session_num)) * 100.0 / NULLIF(COUNT(*), 0), 2) AS self_service_resolution_rate
FROM help_sessions h
LEFT JOIN contacted c
  ON c.ecid = h.ecid
 AND c.session_num = h.session_num
GROUP BY h.help_date
ORDER BY h.help_date;