Marketing-Sourced New Customer Rate

{
  "digitalData": {
    "user": [
      {
        "profile": [
          {
            "profileInfo": {
              "profileID": "<pseudonymous_user_id>",
              "returningStatus": "new"
            }
          }
        ]
      }
    ],
    "transaction": {
      "transactionID": "<order_id>",
      "total": {
        "basePrice": "<order_value>",
        "currency": "USD"
      }
    },
    "page": {
      "pageInfo": {
        "pageID": "order-confirmation"
      },
      "attributes": {
        "firstTouchChannel": "<channel_grouping>",
        "firstTouchSourceMedium": "<source/medium>"
      }
    }
  }
}

{
  "event": "purchase",
  "user_id": "<pseudonymous_user_id>",
  "customer_type": "new",
  "first_touch_channel": "<channel_grouping>",
  "first_touch_source_medium": "<source/medium>",
  "ecommerce": {
    "transaction_id": "<order_id>",
    "value": "<order_value>",
    "currency": "USD",
    "items": [
      {
        "item_id": "<sku>",
        "item_name": "<product_name>",
        "price": "<unit_price>",
        "quantity": 1
      }
    ]
  }
}

{
  "event": "purchase",
  "eventInfo": {
    "eventName": "Purchase Confirmation"
  },
  "commerce": {
    "order": {
      "purchaseID": "<order_id>",
      "priceTotal": "<order_value>",
      "currencyCode": "USD"
    }
  },
  "user": {
    "customerType": "new",
    "firstTouchChannel": "<channel_grouping>",
    "firstTouchSourceMedium": "<source/medium>"
  }
}

{
  "eventType": "commerce.purchases",
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "<order_id>",
      "priceTotal": "<order_value>",
      "currencyCode": "USD"
    }
  },
  "marketing": {
    "trackingCode": "<campaign_tracking_code>"
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar10": "<first_touch_channel_grouping>",
          "eVar11": "new"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
WITH first_purchase AS (
  SELECT
    user_pseudo_id,
    MIN(event_timestamp) AS first_purchase_ts,
    MIN(DATE(TIMESTAMP_MICROS(event_timestamp))) AS first_purchase_date
  FROM `your-project.analytics_123456789.events_*`
  WHERE event_name = 'purchase'
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(@report_start, INTERVAL 730 DAY)) AND FORMAT_DATE('%Y%m%d', @report_end)
  GROUP BY user_pseudo_id
),
new_customers AS (
  SELECT user_pseudo_id, first_purchase_ts, first_purchase_date
  FROM first_purchase
  WHERE first_purchase_date BETWEEN @report_start AND @report_end
),
first_touch AS (
  SELECT
    e.user_pseudo_id,
    ARRAY_AGG(
      STRUCT(
        LOWER(COALESCE(e.traffic_source.medium, '(none)')) AS medium,
        LOWER(COALESCE(e.traffic_source.source, '(direct)')) AS source
      )
      ORDER BY e.event_timestamp ASC LIMIT 1
    )[OFFSET(0)] AS ft
  FROM `your-project.analytics_123456789.events_*` e
  JOIN new_customers n USING (user_pseudo_id)
  WHERE e.event_name = 'session_start'
    AND e.event_timestamp <= n.first_purchase_ts
    AND e.event_timestamp >= n.first_purchase_ts - 90 * 24 * 60 * 60 * 1000000
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(@report_start, INTERVAL 90 DAY)) AND FORMAT_DATE('%Y%m%d', @report_end)
  GROUP BY e.user_pseudo_id
)
SELECT
  n.first_purchase_date AS purchase_date,
  COUNT(DISTINCT n.user_pseudo_id) AS first_time_purchasers,
  COUNT(DISTINCT IF(ft.ft.medium IN ('cpc', 'ppc', 'paidsocial', 'paid_social', 'display', 'affiliate', 'email', 'sms'), n.user_pseudo_id, NULL)) AS marketing_sourced_first_time_purchasers,
  SAFE_DIVIDE(
    COUNT(DISTINCT IF(ft.ft.medium IN ('cpc', 'ppc', 'paidsocial', 'paid_social', 'display', 'affiliate', 'email', 'sms'), n.user_pseudo_id, NULL)),
    COUNT(DISTINCT n.user_pseudo_id)
  ) * 100 AS marketing_sourced_new_customer_rate
FROM new_customers n
LEFT JOIN first_touch ft USING (user_pseudo_id)
GROUP BY purchase_date
ORDER BY purchase_date;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH first_purchase AS (
  SELECT
    endUserIDs._experience.mcid.id AS user_id,
    MIN(timestamp) AS first_purchase_ts,
    MIN(DATE(timestamp)) AS first_purchase_date
  FROM web_experience_events
  WHERE eventType = 'commerce.purchases'
    AND commerce.purchases.value > 0
    AND timestamp >= DATE_SUB(CAST(@report_start AS DATE), INTERVAL 730 DAY)
  GROUP BY 1
),
new_customers AS (
  SELECT user_id, first_purchase_ts, first_purchase_date
  FROM first_purchase
  WHERE first_purchase_date BETWEEN CAST(@report_start AS DATE) AND CAST(@report_end AS DATE)
),
first_touch AS (
  SELECT
    user_id,
    medium
  FROM (
    SELECT
      e.endUserIDs._experience.mcid.id AS user_id,
      LOWER(COALESCE(e.marketing.trackingCode, e.channel._type, 'direct')) AS medium,
      ROW_NUMBER() OVER (PARTITION BY e.endUserIDs._experience.mcid.id ORDER BY e.timestamp ASC) AS rn
    FROM web_experience_events e
    JOIN new_customers n ON n.user_id = e.endUserIDs._experience.mcid.id
    WHERE e.eventType = 'web.webpagedetails.pageViews'
      AND e.timestamp <= n.first_purchase_ts
      AND e.timestamp >= DATE_SUB(n.first_purchase_ts, INTERVAL 90 DAY)
  )
  WHERE rn = 1
)
SELECT
  n.first_purchase_date AS purchase_date,
  COUNT(DISTINCT n.user_id) AS first_time_purchasers,
  COUNT(DISTINCT CASE WHEN ft.medium LIKE '%paid%' OR ft.medium LIKE '%cpc%' OR ft.medium LIKE '%display%' OR ft.medium LIKE '%affiliate%' OR ft.medium LIKE '%email%' OR ft.medium LIKE '%sms%' THEN n.user_id END) AS marketing_sourced_first_time_purchasers,
  COUNT(DISTINCT CASE WHEN ft.medium LIKE '%paid%' OR ft.medium LIKE '%cpc%' OR ft.medium LIKE '%display%' OR ft.medium LIKE '%affiliate%' OR ft.medium LIKE '%email%' OR ft.medium LIKE '%sms%' THEN n.user_id END) * 100.0
    / NULLIF(COUNT(DISTINCT n.user_id), 0) AS marketing_sourced_new_customer_rate
FROM new_customers n
LEFT JOIN first_touch ft ON ft.user_id = n.user_id
GROUP BY 1
ORDER BY 1;