Customer Segment

{
  "event": [
    {
      "eventInfo": {
        "eventName": "purchase"
      }
    }
  ],
  "user": [
    {
      "segment": {
        "customer_segment": "vip"
      },
      "profile": [
        {
          "profileInfo": {
            "profileID": "U-1"
          }
        }
      ]
    }
  ]
}

{
  "event": "purchase",
  "user_id": "U-1",
  "user_properties": {
    "customer_segment": "vip"
  },
  "ecommerce": {
    "transaction_id": "txn-1",
    "value": 100,
    "currency": "USD",
    "items": [
      {
        "item_id": "SKU-1",
        "item_name": "item",
        "price": 100,
        "quantity": 1
      }
    ]
  }
}

{
  "event": "purchase",
  "user": {
    "userID": "U-1",
    "customerSegment": "vip"
  },
  "eventInfo": {
    "transactionID": "txn-1"
  }
}

{
  "eventType": "commerce.purchases",
  "identityMap": {
    "CRMID": [
      {
        "id": "U-1",
        "authenticatedState": "authenticated",
        "primary": true
      }
    ]
  },
  "commerce": {
    "purchases": {
      "value": 1
    }
  },
  "_tenantId": {
    "customerSegment": "vip"
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Owner precedence among overlapping segment rules is applied in the CRM before the value is sent; the precedence list is not in these tables.
-- Reporting window: last 90 complete days
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

-- Segment held when each purchase fired (event-time user property); anonymous or blank values are (not set)
WITH segment_events AS (
  SELECT
    COALESCE(user_id, user_pseudo_id) AS customer_key,
    IF(
      user_id IS NULL,
      '(not set)',
      COALESCE(NULLIF((SELECT up.value.string_value FROM UNNEST(user_properties) AS up WHERE up.key = 'customer_segment'), ''), '(not set)')
    ) AS customer_segment
  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 = 'purchase'
)
-- Distinct customers per segment, (not set) kept as the identity-resolution gap
SELECT
  customer_segment,
  COUNT(DISTINCT customer_key) AS customers
FROM segment_events
GROUP BY customer_segment
ORDER BY customers DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Owner precedence among overlapping segment rules is applied in the CRM before the value is sent; the precedence list is not in these tables.
-- Reporting window: last 90 complete days
WITH params AS (
  SELECT
    CAST(DATE_SUB(CURRENT_DATE, 90) AS DATE) AS start_date,
    CAST(DATE_SUB(CURRENT_DATE, 1) AS DATE) AS end_date
),
-- Segment held when each purchase fired (event-time tenant field); anonymous or blank values are (not set)
segment_events AS (
  SELECT
    COALESCE(e.identityMap['CRMID'][0].id, e.identityMap['ECID'][0].id) AS customer_key,
    CASE
      WHEN e.identityMap['CRMID'][0].id IS NULL THEN '(not set)'
      ELSE COALESCE(NULLIF(e._tenantId.customerSegment, ''), '(not set)')
    END AS customer_segment
  FROM aep_web_events_dataset e
  CROSS JOIN params p
  WHERE CAST(e.timestamp AS DATE) BETWEEN p.start_date AND p.end_date
    AND e.eventType = 'commerce.purchases'
)
-- Distinct customers per segment, (not set) kept as the identity-resolution gap
SELECT
  customer_segment,
  COUNT(DISTINCT customer_key) AS customers
FROM segment_events
GROUP BY customer_segment
ORDER BY customers DESC;