Net Promoter Score

{
  "event": [
    {
      "eventInfo": {
        "eventName": "NPS Survey Submitted",
        "eventAction": "submit_form",
        "type": "other"
      },
      "attributes": {
        "formName": "NPS Survey",
        "formID": "nps_survey_form",
        "formType": "nps_survey",
        "rating": 9,
        "ratingScaleMax": 10,
        "surveyType": "relationship",
        "trafficType": "external"
      }
    }
  ],
  "user": [
    {
      "profile": [
        {
          "profileInfo": {
            "profileID": "USER_ID"
          }
        }
      ]
    }
  ]
}

{
  "event": "submit_form",
  "form_type": "nps_survey",
  "form_id": "nps_survey_form",
  "form_name": "NPS Survey",
  "rating": 9,
  "rating_scale_max": 10,
  "survey_type": "relationship",
  "traffic_type": "external",
  "user_id": "USER_ID"
}

{
  "event": "form_submit",
  "formData": {
    "formType": "nps_survey",
    "formId": "nps_survey_form",
    "formName": "NPS Survey",
    "rating": 9,
    "ratingScaleMax": 10,
    "surveyType": "relationship",
    "trafficType": "external"
  },
  "user": {
    "userId": "USER_ID"
  }
}

{
  "eventType": "web.formFilledOut",
  "web": {
    "formDetails": {
      "formType": "nps_survey",
      "formID": "nps_survey_form",
      "formName": "NPS Survey"
    }
  },
  "identityMap": {
    "ECID": [
      {
        "id": "ECID-1",
        "authenticatedState": "authenticated",
        "primary": true
      }
    ]
  },
  "_tenant": {
    "formData": {
      "rating": 9,
      "ratingScaleMax": 10,
      "surveyType": "relationship",
      "trafficType": "external"
    },
    "user": {
      "userId": "USER_ID"
    }
  }
}

-- ==========================================
-- 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);

-- NPS survey submissions, dated by submission; the survey wave is the submission month (no wave id is collected)
WITH nps_submissions AS (
  SELECT
    COALESCE(user_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'user_id')) AS user_id,
    event_timestamp,
    DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), MONTH) AS survey_month,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'survey_type') AS survey_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'traffic_type') AS traffic_type,
    (SELECT COALESCE(CAST(value.int_value AS FLOAT64), value.double_value, value.float_value, SAFE_CAST(value.string_value AS FLOAT64))
       FROM UNNEST(event_params) WHERE key = 'rating') AS rating
  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 = 'submit_form'
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'form_type') = 'nps_survey'
),
-- Valid integer 0-10 ratings from non-internal users; latest response per user, survey type and wave
latest_responses AS (
  SELECT survey_month, survey_type, rating
  FROM nps_submissions
  WHERE user_id IS NOT NULL
    AND rating IS NOT NULL
    AND rating = TRUNC(rating)
    AND rating BETWEEN 0 AND 10
    AND COALESCE(traffic_type, '') NOT IN ('internal', 'employee', 'test')
  QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id, survey_type, survey_month ORDER BY event_timestamp DESC) = 1
)
-- NPS per wave and survey type (relationship and transactional kept apart), suppressed below 100 valid responses
SELECT
  survey_month,
  survey_type,
  IF(COUNT(*) < 100, NULL,
     CAST(ROUND(100 * (COUNTIF(rating >= 9) - COUNTIF(rating <= 6)) / COUNT(*)) AS INT64)) AS net_promoter_score
FROM latest_responses
GROUP BY survey_month, survey_type
ORDER BY survey_month, survey_type;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- NPS survey submissions (last 90 days to yesterday), dated by submission; the survey wave is the submission month (no wave id is collected)
WITH nps_submissions AS (
  SELECT
    _tenant.user.userId AS user_id,
    timestamp AS event_timestamp,
    DATE_TRUNC('month', timestamp) AS survey_month,
    _tenant.formData.surveyType AS survey_type,
    _tenant.formData.trafficType AS traffic_type,
    CAST(_tenant.formData.rating AS DOUBLE) AS rating
  FROM aep_web_events_dataset
  WHERE CAST(timestamp AS DATE) BETWEEN DATE_SUB(CURRENT_DATE, 90) AND DATE_SUB(CURRENT_DATE, 1)
    AND eventType = 'web.formFilledOut'
    AND web.formDetails.formType = 'nps_survey'
),
-- Valid integer 0-10 ratings from non-internal users; latest response per user, survey type and wave
latest_responses AS (
  SELECT survey_month, survey_type, rating
  FROM (
    SELECT
      survey_month,
      survey_type,
      rating,
      ROW_NUMBER() OVER (PARTITION BY user_id, survey_type, survey_month ORDER BY event_timestamp DESC) AS rn
    FROM nps_submissions
    WHERE user_id IS NOT NULL
      AND rating IS NOT NULL
      AND rating = FLOOR(rating)
      AND rating BETWEEN 0 AND 10
      AND COALESCE(traffic_type, '') NOT IN ('internal', 'employee', 'test')
  ) AS ranked
  WHERE rn = 1
)
-- NPS per wave and survey type (relationship and transactional kept apart), suppressed below 100 valid responses
SELECT
  survey_month,
  survey_type,
  CASE WHEN COUNT(*) < 100 THEN NULL
       ELSE CAST(ROUND(100.0 * (SUM(CASE WHEN rating >= 9 THEN 1 ELSE 0 END) - SUM(CASE WHEN rating <= 6 THEN 1 ELSE 0 END)) / COUNT(*)) AS INT)
  END AS net_promoter_score
FROM latest_responses
GROUP BY survey_month, survey_type
ORDER BY survey_month, survey_type;