Querying TrackLayer's identity graph in your warehouse

DECLARE target_merchant STRING DEFAULT 'merchant_42';
DECLARE target_canonical STRING DEFAULT 'can_123';

SELECT
  e.event_timestamp,
  e.event_name,
  e.event_id,
  e.session_id,
  e.order_id,
  e.revenue,
  i.type AS identifier_type,
  i.value AS identifier_value
FROM `warehouse.events` e
LEFT JOIN `warehouse.identities` i
  ON e.merchant_id = target_merchant
 AND i.merchant_id = target_merchant
 AND i.canonical_id = e.canonical_user_id
WHERE e.merchant_id = target_merchant
  AND e.canonical_user_id = target_canonical
ORDER BY e.event_timestamp;

SET target_merchant = 'merchant_42';
SET target_canonical = 'can_123';

SELECT
  e.event_timestamp,
  e.event_name,
  e.event_id,
  e.session_id,
  e.order_id,
  e.revenue,
  i.type AS identifier_type,
  i.value AS identifier_value
FROM warehouse.events e
LEFT JOIN warehouse.identities i
  ON i.merchant_id = $target_merchant
 AND i.canonical_id = e.canonical_user_id
WHERE e.merchant_id = $target_merchant
  AND e.canonical_user_id = $target_canonical
ORDER BY e.event_timestamp;

WITH user_devices AS (
  SELECT
    merchant_id,
    canonical_user_id,
    COUNT(DISTINCT COALESCE(device_type, 'unknown')) AS device_count
  FROM `warehouse.events`
  WHERE canonical_user_id IS NOT NULL
  GROUP BY 1, 2
)
SELECT
  merchant_id,
  COUNT(*) AS resolved_users,
  COUNTIF(device_count > 1) AS cross_device_users,
  ROUND(
    100 * SAFE_DIVIDE(COUNTIF(device_count > 1), COUNT(*)),
    2
  ) AS cross_device_coverage_pct
FROM user_devices
GROUP BY 1
ORDER BY cross_device_coverage_pct DESC;

WITH user_devices AS (
  SELECT
    merchant_id,
    canonical_user_id,
    COUNT(DISTINCT COALESCE(device_type, 'unknown')) AS device_count
  FROM warehouse.events
  WHERE canonical_user_id IS NOT NULL
  GROUP BY 1, 2
)
SELECT
  merchant_id,
  COUNT(*) AS resolved_users,
  COUNT_IF(device_count > 1) AS cross_device_users,
  ROUND(
    100 * COUNT_IF(device_count > 1) / NULLIF(COUNT(*), 0),
    2
  ) AS cross_device_coverage_pct
FROM user_devices
GROUP BY 1
ORDER BY cross_device_coverage_pct DESC;

SELECT
  merchant_id,
  canonical_id,
  COUNT(DISTINCT identity_id) AS identity_count,
  MAX(last_seen) AS cluster_last_seen,
  AVG(confidence) AS avg_identity_confidence
FROM `warehouse.identities`
GROUP BY 1, 2
HAVING COUNT(DISTINCT identity_id) > 10
ORDER BY identity_count DESC, cluster_last_seen DESC;

SELECT
  merchant_id,
  canonical_id,
  COUNT(DISTINCT identity_id) AS identity_count,
  MAX(last_seen) AS cluster_last_seen,
  AVG(confidence) AS avg_identity_confidence
FROM warehouse.identities
GROUP BY 1, 2
HAVING COUNT(DISTINCT identity_id) > 10
ORDER BY identity_count DESC, cluster_last_seen DESC;

WITH stitched_users AS (
  SELECT
    merchant_id,
    canonical_id
  FROM `warehouse.identities`
  GROUP BY 1, 2
  HAVING COUNTIF(type = 'cookie') > 0
     AND COUNTIF(type = 'email_hash') > 0
),
first_touch AS (
  SELECT
    e.merchant_id,
    e.canonical_user_id,
    ARRAY_AGG(
      IF(e.traffic_source IS NOT NULL, e.traffic_source, NULL)
      IGNORE NULLS
      ORDER BY e.event_timestamp
      LIMIT 1
    )[OFFSET(0)] AS first_known_source
  FROM `warehouse.events` e
  JOIN stitched_users s
    ON s.merchant_id = e.merchant_id
   AND s.canonical_id = e.canonical_user_id
  GROUP BY 1, 2
)
SELECT
  DATE(e.event_timestamp) AS purchase_date,
  COALESCE(f.first_known_source, 'direct_or_unknown') AS stitched_source,
  SUM(e.revenue) AS attributed_revenue
FROM `warehouse.events` e
JOIN first_touch f
  ON f.merchant_id = e.merchant_id
 AND f.canonical_user_id = e.canonical_user_id
WHERE e.event_name = 'purchase'
GROUP BY 1, 2
ORDER BY purchase_date DESC, attributed_revenue DESC;

WITH stitched_users AS (
  SELECT
    merchant_id,
    canonical_id
  FROM warehouse.identities
  GROUP BY 1, 2
  HAVING COUNT_IF(type = 'cookie') > 0
     AND COUNT_IF(type = 'email_hash') > 0
),
first_touch AS (
  SELECT
    e.merchant_id,
    e.canonical_user_id,
    e.traffic_source AS first_known_source
  FROM warehouse.events e
  JOIN stitched_users s
    ON s.merchant_id = e.merchant_id
   AND s.canonical_id = e.canonical_user_id
  WHERE e.traffic_source IS NOT NULL
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY e.merchant_id, e.canonical_user_id
    ORDER BY e.event_timestamp
  ) = 1
)
SELECT
  DATE(e.event_timestamp) AS purchase_date,
  COALESCE(f.first_known_source, 'direct_or_unknown') AS stitched_source,
  SUM(e.revenue) AS attributed_revenue
FROM warehouse.events e
JOIN first_touch f
  ON f.merchant_id = e.merchant_id
 AND f.canonical_user_id = e.canonical_user_id
WHERE e.event_name = 'purchase'
GROUP BY 1, 2
ORDER BY purchase_date DESC, attributed_revenue DESC;

WITH current_unresolved AS (
  SELECT
    merchant_id,
    event_id,
    maid_hash
  FROM `staging.hypothetical_identifiers`
  WHERE maid_hash IS NOT NULL
),
candidate_matches AS (
  SELECT
    u.merchant_id,
    u.event_id,
    i.canonical_id,
    i.confidence
  FROM current_unresolved u
  LEFT JOIN `warehouse.identities` i
    ON i.merchant_id = u.merchant_id
   AND i.type = 'maid'
   AND i.value = u.maid_hash
)
SELECT
  merchant_id,
  COUNT(*) AS unresolved_events_tested,
  COUNTIF(canonical_id IS NOT NULL) AS events_that_would_resolve,
  ROUND(
    100 * SAFE_DIVIDE(COUNTIF(canonical_id IS NOT NULL), COUNT(*)),
    2
  ) AS hypothetical_resolution_lift_pct,
  AVG(confidence) AS avg_candidate_confidence
FROM candidate_matches
GROUP BY 1
ORDER BY hypothetical_resolution_lift_pct DESC;

WITH current_unresolved AS (
  SELECT
    merchant_id,
    event_id,
    maid_hash
  FROM staging.hypothetical_identifiers
  WHERE maid_hash IS NOT NULL
),
candidate_matches AS (
  SELECT
    u.merchant_id,
    u.event_id,
    i.canonical_id,
    i.confidence
  FROM current_unresolved u
  LEFT JOIN warehouse.identities i
    ON i.merchant_id = u.merchant_id
   AND i.type = 'maid'
   AND i.value = u.maid_hash
)
SELECT
  merchant_id,
  COUNT(*) AS unresolved_events_tested,
  COUNT_IF(canonical_id IS NOT NULL) AS events_that_would_resolve,
  ROUND(
    100 * COUNT_IF(canonical_id IS NOT NULL) / NULLIF(COUNT(*), 0),
    2
  ) AS hypothetical_resolution_lift_pct,
  AVG(confidence) AS avg_candidate_confidence
FROM candidate_matches
GROUP BY 1
ORDER BY hypothetical_resolution_lift_pct DESC;