ShipBob Fulfillment KPIs - Implementation

Status: Draft - Pending Review Spec: ShipBob Fulfillment KPIs Dashboard: eli-kpi Fulfillment Dashboard (planned)

Implementation Overview

File Locations

Type	Path
SQL queries	`eli-kpi/src/sql/bigquery/shipbob/`
TypeScript service	`eli-kpi/src/analytics-service.ts`
API routes	`eli-kpi/src/shipbob-routes.ts` (extend existing)
Dashboard HTML	`eli-kpi/src/shipbob-dashboard.ts` (new)

Common CTEs (Shared Across Queries)

All ShipBob KPI queries share these base CTEs. They follow the same patterns established in the engagement metrics.

-- Internal accounts to exclude
internal_emails AS (
  SELECT DISTINCT LOWER(email) as email
  FROM `eli-health-prod.firebase_users.auth_users`
  WHERE LOWER(email) LIKE '%@eli.health'
),

-- Valid app users (excluding internal)
valid_app_users AS (
  SELECT u.id as app_user_id, LOWER(fa.email) as email, fa.uid
  FROM `eli-health-prod.eli_health_biometricspublic.user` u
  JOIN `eli-health-prod.firebase_users.auth_users` fa ON u.accountId = fa.uid
  LEFT JOIN internal_emails ie ON LOWER(fa.email) = ie.email
  WHERE u.deleted IS NULL
    AND ie.email IS NULL
),

-- ShipBob orders with extracted fields
shipbob_deliveries AS (
  SELECT
    sb.id as shipbob_order_id,
    CAST(JSON_VALUE(sb.data, '$.reference_id') AS INT64) as shopify_order_id,
    JSON_VALUE(sb.data, '$.order_number') as shipbob_order_number,
    JSON_VALUE(sb.data, '$.status') as order_status,
    TIMESTAMP(JSON_VALUE(sb.data, '$.purchase_date')) as purchase_date,
    JSON_VALUE(sb.data, '$.recipient.email') as recipient_email,
    JSON_VALUE(sb.data, '$.shipping_method') as shipping_method,
    -- Shipment details (first shipment)
    JSON_VALUE(sb.data, '$.shipments[0].status') as shipment_status,
    SAFE.PARSE_TIMESTAMP(
      '%Y-%m-%dT%H:%M:%E*SZ',
      JSON_VALUE(sb.data, '$.shipments[0].actual_fulfillment_date')
    ) as fulfillment_date,
    SAFE.PARSE_TIMESTAMP(
      '%Y-%m-%dT%H:%M:%E*SZ',
      JSON_VALUE(sb.data, '$.shipments[0].delivery_date')
    ) as delivery_date,
    JSON_VALUE(sb.data, '$.shipments[0].tracking.tracking_number') as tracking_number,
    JSON_VALUE(sb.data, '$.shipments[0].tracking.carrier') as carrier,
    -- Product details
    JSON_VALUE(sb.data, '$.products[0].sku') as product_sku,
    CAST(JSON_VALUE(sb.data, '$.products[0].quantity') AS INT64) as product_quantity
  FROM `eli-health-prod.eli_health_shipbob.orders` sb
  WHERE JSON_VALUE(sb.data, '$.channel.name') = 'elihealth'
),

-- Link ShipBob → Shopify → Firebase → Biometrics
delivered_to_users AS (
  SELECT
    sd.shipbob_order_id,
    sd.shopify_order_id,
    sd.purchase_date,
    sd.delivery_date,
    sd.fulfillment_date,
    sd.carrier,
    sd.shipping_method,
    sd.product_sku,
    sd.product_quantity,
    sd.order_status,
    o.email as shopify_email,
    vau.app_user_id,
    vau.uid as firebase_uid
  FROM shipbob_deliveries sd
  -- Join to Shopify via reference_id (100% match)
  LEFT JOIN `eli-health-prod.eli_health_shopify.orders` o
    ON sd.shopify_order_id = o.id
  -- Join to app user via email (case-insensitive, ~94% match)
  LEFT JOIN valid_app_users vau
    ON LOWER(o.email) = vau.email
  -- Exclude internal
  LEFT JOIN internal_emails ie
    ON LOWER(o.email) = ie.email
  WHERE ie.email IS NULL
)

Join Flow Diagram

SQL Query 1: Delivery-to-First-Test Time

File: eli-kpi/src/sql/bigquery/shipbob/delivery-to-first-test.sql

-- Delivery-to-First-Test Time
-- Measures how long after kit delivery a user takes their first test
-- Requires: ShipBob delivery_date populated
-- Excludes: eli.health internal accounts
-- Parameters: none (uses current data)

WITH
internal_emails AS (
  SELECT DISTINCT LOWER(email) as email
  FROM `eli-health-prod.firebase_users.auth_users`
  WHERE LOWER(email) LIKE '%@eli.health'
),

valid_app_users AS (
  SELECT u.id as app_user_id, LOWER(fa.email) as email
  FROM `eli-health-prod.eli_health_biometricspublic.user` u
  JOIN `eli-health-prod.firebase_users.auth_users` fa ON u.accountId = fa.uid
  LEFT JOIN internal_emails ie ON LOWER(fa.email) = ie.email
  WHERE u.deleted IS NULL
    AND ie.email IS NULL
),

-- ShipBob orders with delivery dates
shipbob_delivered AS (
  SELECT
    sb.id as shipbob_order_id,
    CAST(JSON_VALUE(sb.data, '$.reference_id') AS INT64) as shopify_order_id,
    SAFE.PARSE_TIMESTAMP(
      '%Y-%m-%dT%H:%M:%E*SZ',
      JSON_VALUE(sb.data, '$.shipments[0].delivery_date')
    ) as delivery_date,
    JSON_VALUE(sb.data, '$.shipments[0].tracking.carrier') as carrier,
    JSON_VALUE(sb.data, '$.shipping_method') as shipping_method,
    JSON_VALUE(sb.data, '$.products[0].sku') as product_sku,
    JSON_VALUE(sb.data, '$.recipient.address.state') as recipient_state,
    JSON_VALUE(sb.data, '$.recipient.address.country') as recipient_country
  FROM `eli-health-prod.eli_health_shipbob.orders` sb
  WHERE JSON_VALUE(sb.data, '$.channel.name') = 'elihealth'
    -- Only orders with confirmed delivery
    AND JSON_VALUE(sb.data, '$.shipments[0].delivery_date') IS NOT NULL
),

-- Link to app users
deliveries_with_users AS (
  SELECT
    sd.shipbob_order_id,
    sd.delivery_date,
    sd.carrier,
    sd.shipping_method,
    sd.product_sku,
    sd.recipient_state,
    sd.recipient_country,
    vau.app_user_id
  FROM shipbob_delivered sd
  JOIN `eli-health-prod.eli_health_shopify.orders` o
    ON sd.shopify_order_id = o.id
  JOIN valid_app_users vau
    ON LOWER(o.email) = vau.email
),

-- First completed test per user after their delivery date
first_test_after_delivery AS (
  SELECT
    dwu.shipbob_order_id,
    dwu.delivery_date,
    dwu.carrier,
    dwu.shipping_method,
    dwu.product_sku,
    dwu.recipient_state,
    dwu.recipient_country,
    dwu.app_user_id,
    MIN(r.created) as first_test_date
  FROM deliveries_with_users dwu
  LEFT JOIN `eli-health-prod.eli_health_biometricspublic.reading` r
    ON dwu.app_user_id = r.userId
    AND r.status = 'completed'
    AND r.created >= dwu.delivery_date
  GROUP BY 1, 2, 3, 4, 5, 6, 7, 8
)

-- Summary statistics
SELECT
  -- Overall metrics
  COUNT(*) as total_delivered,
  COUNTIF(first_test_date IS NOT NULL) as users_who_tested,
  COUNTIF(first_test_date IS NULL) as users_never_tested,
  ROUND(100.0 * COUNTIF(first_test_date IS NOT NULL) / COUNT(*), 1) as tested_pct,

  -- Time to first test (only for users who tested)
  ROUND(AVG(
    CASE WHEN first_test_date IS NOT NULL
    THEN TIMESTAMP_DIFF(first_test_date, delivery_date, HOUR) / 24.0
    END
  ), 1) as avg_days_to_first_test,

  -- Percentile approximations
  ROUND(APPROX_QUANTILES(
    CASE WHEN first_test_date IS NOT NULL
    THEN TIMESTAMP_DIFF(first_test_date, delivery_date, HOUR) / 24.0
    END,
    100 IGNORE NULLS
  )[OFFSET(50)], 1) as median_days_to_first_test,

  ROUND(APPROX_QUANTILES(
    CASE WHEN first_test_date IS NOT NULL
    THEN TIMESTAMP_DIFF(first_test_date, delivery_date, HOUR) / 24.0
    END,
    100 IGNORE NULLS
  )[OFFSET(90)], 1) as p90_days_to_first_test,

  -- Activation windows
  ROUND(100.0 * COUNTIF(
    first_test_date IS NOT NULL
    AND TIMESTAMP_DIFF(first_test_date, delivery_date, DAY) <= 7
  ) / COUNT(*), 1) as tested_within_7d_pct,

  ROUND(100.0 * COUNTIF(
    first_test_date IS NOT NULL
    AND TIMESTAMP_DIFF(first_test_date, delivery_date, DAY) <= 14
  ) / COUNT(*), 1) as tested_within_14d_pct,

  ROUND(100.0 * COUNTIF(
    first_test_date IS NOT NULL
    AND TIMESTAMP_DIFF(first_test_date, delivery_date, DAY) <= 30
  ) / COUNT(*), 1) as tested_within_30d_pct

FROM first_test_after_delivery;

-- Carrier breakdown (separate query)
-- SELECT
--   carrier,
--   COUNT(*) as deliveries,
--   COUNTIF(first_test_date IS NOT NULL) as tested,
--   ROUND(AVG(CASE WHEN first_test_date IS NOT NULL
--     THEN TIMESTAMP_DIFF(first_test_date, delivery_date, HOUR) / 24.0
--   END), 1) as avg_days
-- FROM first_test_after_delivery
-- GROUP BY carrier
-- ORDER BY deliveries DESC

SQL Query 2: Post-Delivery Activation Rate

File: eli-kpi/src/sql/bigquery/shipbob/post-delivery-activation-rate.sql

-- Post-Delivery Activation Rate
-- Of kits confirmed delivered, what % resulted in at least one test?
-- Time windows: 7-day, 14-day, 30-day
-- Excludes: eli.health internal accounts, orders without delivery confirmation
-- Parameters: none

WITH
internal_emails AS (
  SELECT DISTINCT LOWER(email) as email
  FROM `eli-health-prod.firebase_users.auth_users`
  WHERE LOWER(email) LIKE '%@eli.health'
),

valid_app_users AS (
  SELECT u.id as app_user_id, LOWER(fa.email) as email
  FROM `eli-health-prod.eli_health_biometricspublic.user` u
  JOIN `eli-health-prod.firebase_users.auth_users` fa ON u.accountId = fa.uid
  LEFT JOIN internal_emails ie ON LOWER(fa.email) = ie.email
  WHERE u.deleted IS NULL
    AND ie.email IS NULL
),

-- Delivered kits linked to users
delivered_kits AS (
  SELECT
    sb.id as shipbob_order_id,
    SAFE.PARSE_TIMESTAMP(
      '%Y-%m-%dT%H:%M:%E*SZ',
      JSON_VALUE(sb.data, '$.shipments[0].delivery_date')
    ) as delivery_date,
    DATE_TRUNC(
      DATE(SAFE.PARSE_TIMESTAMP(
        '%Y-%m-%dT%H:%M:%E*SZ',
        JSON_VALUE(sb.data, '$.shipments[0].delivery_date')
      )),
      MONTH
    ) as delivery_month,
    vau.app_user_id
  FROM `eli-health-prod.eli_health_shipbob.orders` sb
  JOIN `eli-health-prod.eli_health_shopify.orders` o
    ON CAST(JSON_VALUE(sb.data, '$.reference_id') AS INT64) = o.id
  JOIN valid_app_users vau
    ON LOWER(o.email) = vau.email
  WHERE JSON_VALUE(sb.data, '$.channel.name') = 'elihealth'
    AND JSON_VALUE(sb.data, '$.shipments[0].delivery_date') IS NOT NULL
),

-- Check if user tested after delivery, within various windows
activation_check AS (
  SELECT
    dk.shipbob_order_id,
    dk.delivery_date,
    dk.delivery_month,
    dk.app_user_id,
    -- Did user test within 7 days?
    MAX(CASE
      WHEN r.status = 'completed'
        AND r.created >= dk.delivery_date
        AND r.created < TIMESTAMP_ADD(dk.delivery_date, INTERVAL 7 DAY)
      THEN 1 ELSE 0
    END) as activated_7d,
    -- Did user test within 14 days?
    MAX(CASE
      WHEN r.status = 'completed'
        AND r.created >= dk.delivery_date
        AND r.created < TIMESTAMP_ADD(dk.delivery_date, INTERVAL 14 DAY)
      THEN 1 ELSE 0
    END) as activated_14d,
    -- Did user test within 30 days?
    MAX(CASE
      WHEN r.status = 'completed'
        AND r.created >= dk.delivery_date
        AND r.created < TIMESTAMP_ADD(dk.delivery_date, INTERVAL 30 DAY)
      THEN 1 ELSE 0
    END) as activated_30d
  FROM delivered_kits dk
  LEFT JOIN `eli-health-prod.eli_health_biometricspublic.reading` r
    ON dk.app_user_id = r.userId
  GROUP BY 1, 2, 3, 4
)

-- Monthly activation rates
SELECT
  FORMAT_DATE('%Y-%m', delivery_month) as month,
  COUNT(*) as kits_delivered,
  -- 7-day activation
  COUNTIF(activated_7d = 1) as activated_7d_count,
  ROUND(100.0 * COUNTIF(activated_7d = 1) / COUNT(*), 1) as activation_rate_7d_pct,
  -- 14-day activation
  COUNTIF(activated_14d = 1) as activated_14d_count,
  ROUND(100.0 * COUNTIF(activated_14d = 1) / COUNT(*), 1) as activation_rate_14d_pct,
  -- 30-day activation
  COUNTIF(activated_30d = 1) as activated_30d_count,
  ROUND(100.0 * COUNTIF(activated_30d = 1) / COUNT(*), 1) as activation_rate_30d_pct
FROM activation_check
-- Only include months where 30-day window has elapsed (data is mature)
WHERE delivery_date <= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY delivery_month
ORDER BY delivery_month DESC

SQL Query 3: Fulfillment-Adjusted Test Completion Rate

File: eli-kpi/src/sql/bigquery/shipbob/fulfillment-adjusted-completion-rate.sql

This enhances the existing monthly-test-completion-rate-delivered.sql by replacing order dates with actual delivery dates for Shopify one-time purchases.

-- Fulfillment-Adjusted Test Completion Rate
-- Same as monthly-test-completion-rate-delivered.sql, but uses ShipBob delivery
-- date instead of Shopify order date for one-time purchases
-- Excludes: eli.health internal accounts
-- Parameters: none

WITH params AS (
  SELECT
    DATE_TRUNC(CURRENT_DATE(), MONTH) as current_month,
    DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH) as previous_month
),

internal_emails AS (
  SELECT DISTINCT LOWER(email) as email
  FROM `eli-health-prod.firebase_users.auth_users`
  WHERE LOWER(email) LIKE '%@eli.health'
),

valid_app_users AS (
  SELECT u.id as app_user_id, LOWER(fa.email) as email
  FROM `eli-health-prod.eli_health_biometricspublic.user` u
  JOIN `eli-health-prod.firebase_users.auth_users` fa ON u.accountId = fa.uid
  LEFT JOIN internal_emails ie ON LOWER(fa.email) = ie.email
  WHERE u.deleted IS NULL
    AND ie.email IS NULL
),

-- Deduplicated tests (same 30-minute window as existing metric)
deduplicated_tests AS (
  SELECT
    DATE_TRUNC(DATE(r.created), MONTH) as test_month,
    r.userId,
    TIMESTAMP_SECONDS(DIV(UNIX_SECONDS(r.created), 1800) * 1800) as half_hour_window,
    MAX(CASE WHEN r.status = 'completed' THEN 1 ELSE 0 END) as had_success
  FROM `eli-health-prod.eli_health_biometricspublic.reading` r
  JOIN valid_app_users vau ON r.userId = vau.app_user_id
  CROSS JOIN params p
  WHERE DATE(r.created) >= DATE_SUB(p.current_month, INTERVAL 1 MONTH)
  GROUP BY test_month, r.userId, half_hour_window
),

monthly_successful AS (
  SELECT test_month, COUNT(*) as successful_tests
  FROM deduplicated_tests
  WHERE had_success = 1
  GROUP BY test_month
),

-- Subscription data (unchanged from existing query)
subscription_data AS (
  SELECT
    s.id,
    DATE(PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', JSON_EXTRACT_SCALAR(s.data, '$.createdAt'))) as created_date,
    DATE(SAFE.PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', JSON_EXTRACT_SCALAR(s.data, '$.cancelledAt'))) as cancelled_date,
    COALESCE(CAST(JSON_EXTRACT_SCALAR(s.data, '$.lines[0].quantity') AS INT64), 1) as quantity,
    COALESCE(CAST(JSON_EXTRACT_SCALAR(s.data, '$.billingPolicy.intervalCount') AS INT64), 1) as interval_months,
    CASE COALESCE(JSON_EXTRACT_SCALAR(s.data, '$.billingPolicy.intervalCount'), '1')
      WHEN '6' THEN DATE_ADD(DATE(PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', JSON_EXTRACT_SCALAR(s.data, '$.createdAt'))), INTERVAL 6 MONTH)
      WHEN '12' THEN DATE_ADD(DATE(PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', JSON_EXTRACT_SCALAR(s.data, '$.createdAt'))), INTERVAL 12 MONTH)
      ELSE NULL
    END as plan_end_date
  FROM `eli-health-prod.eli_health_loopwork.subscriptions` s
  JOIN `eli-health-prod.eli_health_loopwork.customers` c
    ON CAST(JSON_EXTRACT_SCALAR(s.data, '$.customer.id') AS INT64) = c.id
  LEFT JOIN internal_emails ie
    ON LOWER(JSON_EXTRACT_SCALAR(c.data, '$.email')) = ie.email
  WHERE ie.email IS NULL
),

-- Tests from subscriptions (unchanged)
tests_from_subscriptions AS (
  SELECT
    p.current_month as target_month,
    ROUND(SUM(SAFE_DIVIDE(sd.quantity * 4.0, sd.interval_months)), 0) as tests_delivered
  FROM subscription_data sd
  CROSS JOIN params p
  WHERE sd.created_date <= LAST_DAY(p.current_month)
    AND (sd.cancelled_date IS NULL OR sd.cancelled_date >= p.current_month)
    AND (sd.plan_end_date IS NULL OR sd.plan_end_date >= p.current_month)
  GROUP BY p.current_month

  UNION ALL

  SELECT
    p.previous_month as target_month,
    ROUND(SUM(SAFE_DIVIDE(sd.quantity * 4.0, sd.interval_months)), 0) as tests_delivered
  FROM subscription_data sd
  CROSS JOIN params p
  WHERE sd.created_date <= LAST_DAY(p.previous_month)
    AND (sd.cancelled_date IS NULL OR sd.cancelled_date >= p.previous_month)
    AND (sd.plan_end_date IS NULL OR sd.plan_end_date >= p.previous_month)
  GROUP BY p.previous_month
),

-- CHANGED: Shopify one-time purchases using ShipBob delivery date
-- Falls back to Shopify order date when no ShipBob delivery date available
shopify_one_time_with_delivery AS (
  SELECT
    DATE_TRUNC(
      DATE(COALESCE(
        -- Prefer ShipBob actual delivery date
        SAFE.PARSE_TIMESTAMP(
          '%Y-%m-%dT%H:%M:%E*SZ',
          JSON_VALUE(sb.data, '$.shipments[0].delivery_date')
        ),
        -- Fallback to Shopify order date
        o.created_at
      )),
      MONTH
    ) as delivery_month,
    4 as tests_delivered  -- 1 pack = 4 tests
  FROM `eli-health-prod.eli_health_shopify.orders` o
  -- Left join to ShipBob (not all orders may have ShipBob data)
  LEFT JOIN `eli-health-prod.eli_health_shipbob.orders` sb
    ON CAST(JSON_VALUE(sb.data, '$.reference_id') AS INT64) = o.id
  LEFT JOIN internal_emails ie ON LOWER(o.email) = ie.email
  WHERE o.tags LIKE '%1_box%'
    AND o.tags NOT LIKE '%Subscription%'
    AND o.email IS NOT NULL
    AND o.email != ''
    AND o.cancelled_at IS NULL
    AND ie.email IS NULL
),

tests_from_shopify AS (
  SELECT
    delivery_month as target_month,
    SUM(tests_delivered) as tests_delivered
  FROM shopify_one_time_with_delivery
  CROSS JOIN params p
  WHERE delivery_month IN (p.current_month, p.previous_month)
  GROUP BY delivery_month
),

tests_delivered AS (
  SELECT target_month, SUM(tests_delivered) as tests_delivered
  FROM (
    SELECT * FROM tests_from_subscriptions
    UNION ALL
    SELECT * FROM tests_from_shopify
  )
  GROUP BY target_month
)

SELECT
  FORMAT_DATE('%Y-%m', p.current_month) as current_month,
  COALESCE(curr_s.successful_tests, 0) as current_successful_tests,
  COALESCE(curr_d.tests_delivered, 0) as current_tests_delivered,
  ROUND(SAFE_DIVIDE(curr_s.successful_tests * 100.0, curr_d.tests_delivered), 1)
    as current_completion_rate,
  FORMAT_DATE('%Y-%m', p.previous_month) as previous_month,
  COALESCE(prev_s.successful_tests, 0) as previous_successful_tests,
  COALESCE(prev_d.tests_delivered, 0) as previous_tests_delivered,
  ROUND(SAFE_DIVIDE(prev_s.successful_tests * 100.0, prev_d.tests_delivered), 1)
    as previous_completion_rate,
  ROUND(
    SAFE_DIVIDE(curr_s.successful_tests * 100.0, curr_d.tests_delivered) -
    SAFE_DIVIDE(prev_s.successful_tests * 100.0, prev_d.tests_delivered),
    1
  ) as month_over_month_change,
  -- NEW: Show how many Shopify orders had ShipBob delivery data
  'fulfillment-adjusted' as variant
FROM params p
LEFT JOIN monthly_successful curr_s ON curr_s.test_month = p.current_month
LEFT JOIN tests_delivered curr_d ON curr_d.target_month = p.current_month
LEFT JOIN monthly_successful prev_s ON prev_s.test_month = p.previous_month
LEFT JOIN tests_delivered prev_d ON prev_d.target_month = p.previous_month

Key Change: The shopify_one_time_with_delivery CTE now LEFT JOINs to eli_health_shipbob.orders and uses COALESCE() to prefer the actual delivery date over the order date.

SQL Query 4: Order-to-Delivery Time

File: eli-kpi/src/sql/bigquery/shipbob/order-to-delivery-time.sql

-- Order-to-Delivery Time
-- Measures fulfillment speed from Shopify order to ShipBob delivery
-- Includes breakdown by carrier, geography, and monthly trend
-- Parameters: none

WITH
shipbob_fulfilled AS (
  SELECT
    sb.id as shipbob_order_id,
    CAST(JSON_VALUE(sb.data, '$.reference_id') AS INT64) as shopify_order_id,
    TIMESTAMP(JSON_VALUE(sb.data, '$.purchase_date')) as purchase_date,
    SAFE.PARSE_TIMESTAMP(
      '%Y-%m-%dT%H:%M:%E*SZ',
      JSON_VALUE(sb.data, '$.shipments[0].actual_fulfillment_date')
    ) as fulfillment_date,
    SAFE.PARSE_TIMESTAMP(
      '%Y-%m-%dT%H:%M:%E*SZ',
      JSON_VALUE(sb.data, '$.shipments[0].delivery_date')
    ) as delivery_date,
    JSON_VALUE(sb.data, '$.shipments[0].tracking.carrier') as carrier,
    JSON_VALUE(sb.data, '$.shipping_method') as shipping_method,
    JSON_VALUE(sb.data, '$.recipient.address.state') as recipient_state,
    JSON_VALUE(sb.data, '$.recipient.address.country') as recipient_country
  FROM `eli-health-prod.eli_health_shipbob.orders` sb
  WHERE JSON_VALUE(sb.data, '$.channel.name') = 'elihealth'
    AND JSON_VALUE(sb.data, '$.shipments[0].delivery_date') IS NOT NULL
),

delivery_times AS (
  SELECT
    sf.*,
    o.created_at as shopify_order_date,
    -- Time segments
    TIMESTAMP_DIFF(sf.fulfillment_date, o.created_at, HOUR) / 24.0 as days_to_fulfill,
    TIMESTAMP_DIFF(sf.delivery_date, sf.fulfillment_date, HOUR) / 24.0 as days_in_transit,
    TIMESTAMP_DIFF(sf.delivery_date, o.created_at, HOUR) / 24.0 as days_total,
    DATE_TRUNC(DATE(o.created_at), MONTH) as order_month
  FROM shipbob_fulfilled sf
  JOIN `eli-health-prod.eli_health_shopify.orders` o
    ON sf.shopify_order_id = o.id
  WHERE o.cancelled_at IS NULL
)

-- Overall summary
SELECT
  COUNT(*) as total_orders,
  ROUND(AVG(days_total), 1) as avg_days_total,
  ROUND(APPROX_QUANTILES(days_total, 100)[OFFSET(50)], 1) as median_days_total,
  ROUND(APPROX_QUANTILES(days_total, 100)[OFFSET(90)], 1) as p90_days_total,
  ROUND(AVG(days_to_fulfill), 1) as avg_days_to_fulfill,
  ROUND(AVG(days_in_transit), 1) as avg_days_in_transit
FROM delivery_times;

-- By carrier (separate query)
-- SELECT
--   carrier,
--   COUNT(*) as orders,
--   ROUND(AVG(days_total), 1) as avg_days,
--   ROUND(APPROX_QUANTILES(days_total, 100)[OFFSET(50)], 1) as median_days
-- FROM delivery_times
-- GROUP BY carrier
-- ORDER BY orders DESC;

-- By country (separate query)
-- SELECT
--   recipient_country,
--   COUNT(*) as orders,
--   ROUND(AVG(days_total), 1) as avg_days
-- FROM delivery_times
-- GROUP BY recipient_country
-- ORDER BY orders DESC;

-- Monthly trend (separate query)
-- SELECT
--   FORMAT_DATE('%Y-%m', order_month) as month,
--   COUNT(*) as orders,
--   ROUND(AVG(days_total), 1) as avg_days,
--   ROUND(APPROX_QUANTILES(days_total, 100)[OFFSET(50)], 1) as median_days
-- FROM delivery_times
-- GROUP BY order_month
-- ORDER BY order_month DESC

SQL Query 5: Return Rate

File: eli-kpi/src/sql/bigquery/shipbob/return-rate.sql

-- Return Rate
-- What percentage of ShipBob orders are returned?
-- Includes: return reason breakdown, cost, monthly trend
-- Parameters: none

WITH
total_orders AS (
  SELECT
    COUNT(*) as order_count,
    DATE_TRUNC(DATE(TIMESTAMP(JSON_VALUE(sb.data, '$.purchase_date'))), MONTH) as order_month
  FROM `eli-health-prod.eli_health_shipbob.orders` sb
  WHERE JSON_VALUE(sb.data, '$.channel.name') = 'elihealth'
  GROUP BY order_month
),

returns_data AS (
  SELECT
    r.id as return_id,
    JSON_VALUE(r.data, '$.status') as return_status,
    CAST(JSON_VALUE(r.data, '$.invoice_amount') AS FLOAT64) as return_cost,
    JSON_VALUE(r.data, '$.fulfillment_center.name') as fulfillment_center,
    JSON_VALUE(r.data, '$.tracking_number') as tracking_number,
    -- Extract action taken (reason)
    JSON_VALUE(r.data, '$.inventory[0].action_taken[0]') as action_taken
  FROM `eli-health-prod.eli_health_shipbob.returns` r
)

SELECT
  -- Overall return metrics
  (SELECT SUM(order_count) FROM total_orders) as total_orders,
  COUNT(rd.return_id) as total_returns,
  ROUND(100.0 * COUNT(rd.return_id) /
    NULLIF((SELECT SUM(order_count) FROM total_orders), 0), 2) as return_rate_pct,

  -- Cost metrics
  ROUND(AVG(rd.return_cost), 2) as avg_return_cost,
  ROUND(SUM(rd.return_cost), 2) as total_return_cost,

  -- By status
  COUNTIF(rd.return_status = 'Completed') as returns_completed,
  COUNTIF(rd.return_status = 'Processing') as returns_processing

FROM returns_data rd;

-- By action taken / reason (separate query)
-- SELECT
--   action_taken as reason,
--   COUNT(*) as count
-- FROM returns_data
-- GROUP BY action_taken
-- ORDER BY count DESC

SQL Query 6: Inventory Health (with Lot Tracking)

File: eli-kpi/src/sql/bigquery/shipbob/inventory-health.sql

-- Inventory Health with Lot/Batch Tracking
-- Current stock levels per item and per lot, with expiration dates
-- Includes per-fulfillment-center breakdown
-- Parameters: none

WITH
-- Aggregate inventory levels per item
inventory_levels AS (
  SELECT
    i.id as inventory_id,
    JSON_VALUE(i.data, '$.name') as item_name,
    CAST(JSON_VALUE(i.data, '$.is_active') AS BOOL) as is_active,
    CAST(JSON_VALUE(i.data, '$.is_lot') AS BOOL) as is_lot_tracked,
    CAST(JSON_VALUE(i.data, '$.total_fulfillable_quantity') AS INT64) as fulfillable,
    CAST(JSON_VALUE(i.data, '$.total_onhand_quantity') AS INT64) as onhand,
    CAST(JSON_VALUE(i.data, '$.total_committed_quantity') AS INT64) as committed,
    CAST(JSON_VALUE(i.data, '$.total_sellable_quantity') AS INT64) as sellable,
    CAST(JSON_VALUE(i.data, '$.total_awaiting_quantity') AS INT64) as awaiting,
    CAST(JSON_VALUE(i.data, '$.total_backordered_quantity') AS INT64) as backordered,
    CAST(JSON_VALUE(i.data, '$.total_exception_quantity') AS INT64) as exception_qty
  FROM `eli-health-prod.eli_health_shipbob.inventory` i
),

-- Lot-level breakdown (for items with is_lot = true)
lot_levels AS (
  SELECT
    i.id as inventory_id,
    JSON_VALUE(i.data, '$.name') as item_name,
    JSON_VALUE(lot, '$.lot_number') as lot_number,
    JSON_VALUE(lot, '$.expiration_date') as expiration_date,
    CAST(JSON_VALUE(lot, '$.fulfillable_quantity') AS INT64) as lot_fulfillable,
    CAST(JSON_VALUE(lot, '$.onhand_quantity') AS INT64) as lot_onhand,
    CAST(JSON_VALUE(lot, '$.committed_quantity') AS INT64) as lot_committed,
    CAST(JSON_VALUE(lot, '$.awaiting_quantity') AS INT64) as lot_awaiting,
    -- Per-fulfillment-center for this lot
    JSON_EXTRACT(lot, '$.fulfillable_quantity_by_fulfillment_center') as lot_by_fc
  FROM `eli-health-prod.eli_health_shipbob.inventory` i,
  UNNEST(JSON_QUERY_ARRAY(i.data, '$.fulfillable_quantity_by_lot')) as lot
  WHERE CAST(JSON_VALUE(i.data, '$.is_active') AS BOOL) = true
    AND CAST(JSON_VALUE(i.data, '$.is_lot') AS BOOL) = true
),

-- Per-fulfillment-center breakdown (aggregate level)
fc_levels AS (
  SELECT
    i.id as inventory_id,
    JSON_VALUE(fc, '$.id') as fc_id,
    JSON_VALUE(fc, '$.name') as fc_name,
    CAST(JSON_VALUE(fc, '$.fulfillable_quantity') AS INT64) as fc_fulfillable,
    CAST(JSON_VALUE(fc, '$.onhand_quantity') AS INT64) as fc_onhand,
    CAST(JSON_VALUE(fc, '$.committed_quantity') AS INT64) as fc_committed,
    CAST(JSON_VALUE(fc, '$.awaiting_quantity') AS INT64) as fc_awaiting
  FROM `eli-health-prod.eli_health_shipbob.inventory` i,
  UNNEST(JSON_QUERY_ARRAY(i.data, '$.fulfillable_quantity_by_fulfillment_center')) as fc
  WHERE CAST(JSON_VALUE(i.data, '$.is_active') AS BOOL) = true
),

-- Average daily orders in last 30 days (for days-of-stock estimate)
daily_order_volume AS (
  SELECT
    ROUND(COUNT(*) / 30.0, 1) as avg_daily_orders
  FROM `eli-health-prod.eli_health_shipbob.orders` sb
  WHERE TIMESTAMP(JSON_VALUE(sb.data, '$.purchase_date'))
    >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND JSON_VALUE(sb.data, '$.channel.name') = 'elihealth'
)

SELECT
  il.inventory_id,
  il.item_name,
  il.is_active,
  il.is_lot_tracked,
  il.fulfillable,
  il.onhand,
  il.committed,
  il.sellable,
  il.awaiting,
  il.backordered,
  il.exception_qty,
  dov.avg_daily_orders,
  CASE
    WHEN dov.avg_daily_orders > 0
    THEN ROUND(SAFE_DIVIDE(il.sellable, dov.avg_daily_orders), 0)
    ELSE NULL
  END as estimated_days_of_stock
FROM inventory_levels il
CROSS JOIN daily_order_volume dov
WHERE il.is_active = TRUE
ORDER BY il.sellable ASC

SQL Query 7: Lot Inventory Detail

File: eli-kpi/src/sql/bigquery/shipbob/lot-inventory.sql

-- Lot Inventory Detail
-- Per-lot stock levels with expiration dates and fulfillment center breakdown
-- Only for items with lot tracking enabled (is_lot = true)
-- Parameters: none

SELECT
  i.id as inventory_id,
  JSON_VALUE(i.data, '$.name') as item_name,
  JSON_VALUE(lot, '$.lot_number') as lot_number,
  JSON_VALUE(lot, '$.expiration_date') as expiration_date,
  DATE_DIFF(
    DATE(SAFE.PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', JSON_VALUE(lot, '$.expiration_date'))),
    CURRENT_DATE(),
    DAY
  ) as days_until_expiry,
  CAST(JSON_VALUE(lot, '$.fulfillable_quantity') AS INT64) as fulfillable,
  CAST(JSON_VALUE(lot, '$.onhand_quantity') AS INT64) as onhand,
  CAST(JSON_VALUE(lot, '$.committed_quantity') AS INT64) as committed,
  CAST(JSON_VALUE(lot, '$.awaiting_quantity') AS INT64) as awaiting,
  -- Per-fulfillment-center for this lot
  ARRAY(
    SELECT AS STRUCT
      JSON_VALUE(fc, '$.name') as fc_name,
      CAST(JSON_VALUE(fc, '$.fulfillable_quantity') AS INT64) as fulfillable,
      CAST(JSON_VALUE(fc, '$.onhand_quantity') AS INT64) as onhand,
      CAST(JSON_VALUE(fc, '$.committed_quantity') AS INT64) as committed
    FROM UNNEST(JSON_QUERY_ARRAY(lot, '$.fulfillable_quantity_by_fulfillment_center')) as fc
  ) as by_fulfillment_center
FROM `eli-health-prod.eli_health_shipbob.inventory` i,
UNNEST(JSON_QUERY_ARRAY(i.data, '$.fulfillable_quantity_by_lot')) as lot
WHERE CAST(JSON_VALUE(i.data, '$.is_active') AS BOOL) = true
  AND CAST(JSON_VALUE(i.data, '$.is_lot') AS BOOL) = true
  AND CAST(JSON_VALUE(lot, '$.onhand_quantity') AS INT64) > 0
ORDER BY JSON_VALUE(lot, '$.expiration_date') ASC

SQL Query 8: Batch Traceability — Forward Trace (Lot → Customers)

File: eli-kpi/src/sql/bigquery/shipbob/batch-trace-forward.sql

-- Batch Traceability: Forward Trace (Lot → Customers)
-- Given a lot number, find all customers who received units from that lot
-- Used for: product recalls, quality investigations
-- Parameters: ${lot_number}

SELECT
  JSON_VALUE(sb.data, '$.order_number') as shipbob_order_number,
  CAST(JSON_VALUE(sb.data, '$.reference_id') AS INT64) as shopify_order_id,
  JSON_VALUE(sb.data, '$.recipient.name') as customer_name,
  JSON_VALUE(sb.data, '$.recipient.email') as customer_email,
  JSON_VALUE(sb.data, '$.recipient.phone_number') as customer_phone,
  JSON_VALUE(sb.data, '$.recipient.address.address1') as address1,
  JSON_VALUE(sb.data, '$.recipient.address.city') as city,
  JSON_VALUE(sb.data, '$.recipient.address.state') as state,
  JSON_VALUE(sb.data, '$.recipient.address.country') as country,
  JSON_VALUE(sb.data, '$.recipient.address.zip_code') as zip_code,
  JSON_VALUE(sb.data, '$.shipments[0].location.name') as shipped_from,
  JSON_VALUE(sb.data, '$.shipments[0].tracking.tracking_number') as tracking_number,
  JSON_VALUE(sb.data, '$.shipments[0].tracking.carrier') as carrier,
  JSON_VALUE(sb.data, '$.status') as order_status,
  JSON_VALUE(sb.data, '$.purchase_date') as purchase_date,
  JSON_VALUE(sb.data, '$.shipments[0].actual_fulfillment_date') as fulfillment_date,
  JSON_VALUE(sb.data, '$.shipments[0].delivery_date') as delivery_date,
  JSON_VALUE(inv_item, '$.lot') as lot_number,
  JSON_VALUE(inv_item, '$.expiration_date') as lot_expiration,
  CAST(JSON_VALUE(inv_item, '$.quantity') AS INT64) as units_from_lot,
  JSON_VALUE(inv_item, '$.name') as item_name
FROM `eli-health-prod.eli_health_shipbob.orders` sb,
UNNEST(JSON_QUERY_ARRAY(sb.data, '$.shipments[0].products')) as prod,
UNNEST(JSON_QUERY_ARRAY(prod, '$.inventory_items')) as inv_item
WHERE JSON_VALUE(sb.data, '$.channel.name') = 'elihealth'
  AND JSON_VALUE(inv_item, '$.lot') = ${lot_number}
ORDER BY JSON_VALUE(sb.data, '$.purchase_date') DESC

SQL Query 9: Batch Traceability — Reverse Trace (Customer → Lot)

File: eli-kpi/src/sql/bigquery/shipbob/batch-trace-reverse.sql

-- Batch Traceability: Reverse Trace (Customer → Lot)
-- Given a customer email, find which lots they received
-- Used for: customer complaints, quality investigations
-- Parameters: ${customer_email}

SELECT
  JSON_VALUE(sb.data, '$.order_number') as shipbob_order_number,
  JSON_VALUE(sb.data, '$.recipient.name') as customer_name,
  JSON_VALUE(sb.data, '$.recipient.email') as customer_email,
  JSON_VALUE(sb.data, '$.purchase_date') as purchase_date,
  JSON_VALUE(sb.data, '$.shipments[0].actual_fulfillment_date') as fulfillment_date,
  JSON_VALUE(sb.data, '$.shipments[0].delivery_date') as delivery_date,
  JSON_VALUE(sb.data, '$.shipments[0].location.name') as shipped_from,
  JSON_VALUE(sb.data, '$.shipments[0].tracking.tracking_number') as tracking_number,
  JSON_VALUE(inv_item, '$.lot') as lot_number,
  JSON_VALUE(inv_item, '$.expiration_date') as lot_expiration,
  CAST(JSON_VALUE(inv_item, '$.quantity') AS INT64) as units,
  JSON_VALUE(inv_item, '$.name') as item_name
FROM `eli-health-prod.eli_health_shipbob.orders` sb,
UNNEST(JSON_QUERY_ARRAY(sb.data, '$.shipments[0].products')) as prod,
UNNEST(JSON_QUERY_ARRAY(prod, '$.inventory_items')) as inv_item
WHERE JSON_VALUE(sb.data, '$.channel.name') = 'elihealth'
  AND LOWER(JSON_VALUE(sb.data, '$.recipient.email')) = LOWER(${customer_email})
ORDER BY JSON_VALUE(sb.data, '$.purchase_date') DESC

SQL Query 10: Batch Summary (All Lots Overview)

File: eli-kpi/src/sql/bigquery/shipbob/batch-summary.sql

-- Batch Summary: Overview of all lots shipped
-- Shows per-lot metrics: orders, units, date range, locations
-- Parameters: none

WITH
lot_shipments AS (
  SELECT
    JSON_VALUE(inv_item, '$.lot') as lot_number,
    JSON_VALUE(inv_item, '$.expiration_date') as expiration_date,
    JSON_VALUE(inv_item, '$.name') as item_name,
    sb.id as order_id,
    JSON_VALUE(sb.data, '$.shipments[0].location.name') as shipped_from,
    JSON_VALUE(sb.data, '$.recipient.address.country') as country,
    CAST(JSON_VALUE(inv_item, '$.quantity') AS INT64) as units,
    JSON_VALUE(sb.data, '$.purchase_date') as purchase_date
  FROM `eli-health-prod.eli_health_shipbob.orders` sb,
  UNNEST(JSON_QUERY_ARRAY(sb.data, '$.shipments[0].products')) as prod,
  UNNEST(JSON_QUERY_ARRAY(prod, '$.inventory_items')) as inv_item
  WHERE JSON_VALUE(sb.data, '$.channel.name') = 'elihealth'
    AND JSON_VALUE(sb.data, '$.status') IN ('Fulfilled', 'Completed')
    AND JSON_VALUE(inv_item, '$.lot') IS NOT NULL
),

-- Current inventory remaining per lot
lot_inventory AS (
  SELECT
    JSON_VALUE(lot, '$.lot_number') as lot_number,
    CAST(JSON_VALUE(lot, '$.fulfillable_quantity') AS INT64) as remaining_stock
  FROM `eli-health-prod.eli_health_shipbob.inventory` i,
  UNNEST(JSON_QUERY_ARRAY(i.data, '$.fulfillable_quantity_by_lot')) as lot
  WHERE CAST(JSON_VALUE(i.data, '$.is_lot') AS BOOL) = true
    AND CAST(JSON_VALUE(i.data, '$.is_active') AS BOOL) = true
)

-- Main summary per lot
SELECT
  ls.lot_number,
  ls.item_name,
  MIN(ls.expiration_date) as expiration_date,
  DATE_DIFF(
    DATE(SAFE.PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', MIN(ls.expiration_date))),
    CURRENT_DATE(),
    DAY
  ) as days_until_expiry,
  COUNT(DISTINCT ls.order_id) as total_orders,
  SUM(ls.units) as total_units_shipped,
  MIN(ls.purchase_date) as first_order_date,
  MAX(ls.purchase_date) as last_order_date,
  COALESCE(MAX(li.remaining_stock), 0) as remaining_stock
FROM lot_shipments ls
LEFT JOIN lot_inventory li ON ls.lot_number = li.lot_number
GROUP BY ls.lot_number, ls.item_name
ORDER BY MIN(ls.purchase_date) ASC;

-- Per-lot location breakdown (separate query)
-- SELECT
--   lot_number,
--   shipped_from as location,
--   COUNT(DISTINCT order_id) as orders,
--   SUM(units) as units
-- FROM lot_shipments
-- GROUP BY lot_number, shipped_from
-- ORDER BY lot_number, orders DESC;

-- Per-lot country breakdown (separate query)
-- SELECT
--   lot_number,
--   country,
--   COUNT(DISTINCT order_id) as orders
-- FROM lot_shipments
-- GROUP BY lot_number, country
-- ORDER BY lot_number, orders DESC

SQL Query 11: Lot Expiration Alert (Lots Expiring Within 90 Days)

File: eli-kpi/src/sql/bigquery/shipbob/lot-expiration-alert.sql

-- Lot Expiration Alert
-- Find lots expiring within 90 days that still have stock
-- Used for: daily Slack alerts, FIFO compliance
-- Parameters: none

SELECT
  i.id as inventory_id,
  JSON_VALUE(i.data, '$.name') as item_name,
  JSON_VALUE(lot, '$.lot_number') as lot_number,
  JSON_VALUE(lot, '$.expiration_date') as expiration_date,
  DATE_DIFF(
    DATE(SAFE.PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', JSON_VALUE(lot, '$.expiration_date'))),
    CURRENT_DATE(),
    DAY
  ) as days_until_expiry,
  CAST(JSON_VALUE(lot, '$.onhand_quantity') AS INT64) as onhand_quantity,
  CAST(JSON_VALUE(lot, '$.fulfillable_quantity') AS INT64) as fulfillable_quantity,
  CAST(JSON_VALUE(lot, '$.committed_quantity') AS INT64) as committed_quantity,
  -- Which fulfillment centers hold this lot
  ARRAY(
    SELECT AS STRUCT
      JSON_VALUE(fc, '$.name') as fc_name,
      CAST(JSON_VALUE(fc, '$.onhand_quantity') AS INT64) as onhand,
      CAST(JSON_VALUE(fc, '$.fulfillable_quantity') AS INT64) as fulfillable
    FROM UNNEST(JSON_QUERY_ARRAY(lot, '$.fulfillable_quantity_by_fulfillment_center')) as fc
    WHERE CAST(JSON_VALUE(fc, '$.onhand_quantity') AS INT64) > 0
  ) as locations_with_stock
FROM `eli-health-prod.eli_health_shipbob.inventory` i,
UNNEST(JSON_QUERY_ARRAY(i.data, '$.fulfillable_quantity_by_lot')) as lot
WHERE CAST(JSON_VALUE(i.data, '$.is_active') AS BOOL) = true
  AND CAST(JSON_VALUE(i.data, '$.is_lot') AS BOOL) = true
  AND CAST(JSON_VALUE(lot, '$.onhand_quantity') AS INT64) > 0
  AND DATE_DIFF(
    DATE(SAFE.PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', JSON_VALUE(lot, '$.expiration_date'))),
    CURRENT_DATE(),
    DAY
  ) <= 90
  AND DATE(SAFE.PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', JSON_VALUE(lot, '$.expiration_date'))) > CURRENT_DATE()
ORDER BY DATE(SAFE.PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', JSON_VALUE(lot, '$.expiration_date'))) ASC

SQL Query 12: Batch Funnel (Lot → Activation Rates)

File: eli-kpi/src/sql/bigquery/shipbob/batch-funnel.sql

-- Batch Funnel: Per-lot activation metrics
-- For each lot, how many customers received it, how many tested, activation rate
-- Joins: ShipBob orders → Shopify → Firebase → Biometrics readings
-- Parameters: none

WITH
internal_emails AS (
  SELECT DISTINCT LOWER(email) as email
  FROM `eli-health-prod.firebase_users.auth_users`
  WHERE LOWER(email) LIKE '%@eli.health'
),

valid_app_users AS (
  SELECT u.id as app_user_id, LOWER(fa.email) as email
  FROM `eli-health-prod.eli_health_biometricspublic.user` u
  JOIN `eli-health-prod.firebase_users.auth_users` fa ON u.accountId = fa.uid
  LEFT JOIN internal_emails ie ON LOWER(fa.email) = ie.email
  WHERE u.deleted IS NULL
    AND ie.email IS NULL
),

-- Orders with lot info, linked to app users
lot_orders AS (
  SELECT
    JSON_VALUE(inv_item, '$.lot') as lot_number,
    JSON_VALUE(inv_item, '$.expiration_date') as expiration_date,
    sb.id as shipbob_order_id,
    LOWER(o.email) as customer_email,
    vau.app_user_id,
    SAFE.PARSE_TIMESTAMP(
      '%Y-%m-%dT%H:%M:%E*SZ',
      JSON_VALUE(sb.data, '$.shipments[0].delivery_date')
    ) as delivery_date
  FROM `eli-health-prod.eli_health_shipbob.orders` sb,
  UNNEST(JSON_QUERY_ARRAY(sb.data, '$.shipments[0].products')) as prod,
  UNNEST(JSON_QUERY_ARRAY(prod, '$.inventory_items')) as inv_item
  JOIN `eli-health-prod.eli_health_shopify.orders` o
    ON CAST(JSON_VALUE(sb.data, '$.reference_id') AS INT64) = o.id
  LEFT JOIN valid_app_users vau ON LOWER(o.email) = vau.email
  LEFT JOIN internal_emails ie ON LOWER(o.email) = ie.email
  WHERE JSON_VALUE(sb.data, '$.channel.name') = 'elihealth'
    AND JSON_VALUE(inv_item, '$.lot') IS NOT NULL
    AND ie.email IS NULL
),

-- First test per user after delivery (if they have an app account)
first_test AS (
  SELECT
    lo.lot_number,
    lo.shipbob_order_id,
    lo.app_user_id,
    lo.delivery_date,
    MIN(r.created) as first_test_date
  FROM lot_orders lo
  JOIN `eli-health-prod.eli_health_biometricspublic.reading` r
    ON lo.app_user_id = r.userId
    AND r.status = 'completed'
    AND (lo.delivery_date IS NULL OR r.created >= lo.delivery_date)
  WHERE lo.app_user_id IS NOT NULL
  GROUP BY 1, 2, 3, 4
)

SELECT
  lo.lot_number,
  MIN(lo.expiration_date) as expiration_date,
  COUNT(DISTINCT lo.shipbob_order_id) as total_orders,
  COUNT(DISTINCT lo.customer_email) as total_customers,
  COUNT(DISTINCT lo.app_user_id) as customers_with_app,
  COUNT(DISTINCT ft.app_user_id) as customers_who_tested,
  ROUND(100.0 * COUNT(DISTINCT ft.app_user_id) /
    NULLIF(COUNT(DISTINCT lo.app_user_id), 0), 1) as activation_rate_pct,
  ROUND(AVG(
    CASE WHEN ft.first_test_date IS NOT NULL AND lo.delivery_date IS NOT NULL
    THEN TIMESTAMP_DIFF(ft.first_test_date, lo.delivery_date, HOUR) / 24.0
    END
  ), 1) as avg_days_to_first_test
FROM lot_orders lo
LEFT JOIN first_test ft
  ON lo.lot_number = ft.lot_number
  AND lo.shipbob_order_id = ft.shipbob_order_id
GROUP BY lo.lot_number
ORDER BY lo.lot_number

Lot Expiration Alert — Implementation

Full Technical Spec: Lot Expiration Alerts — contains complete module design, Block Kit formatting, edge cases, and testing plan.

Architecture

Idempotent Deduplication (Slack Channel History)

Uses the same pattern as the App Store Review Notifier (appstore-notifier.ts): Slack channel history is the deduplication database. No separate state table needed.

Dedup key embedded in context block: lot_alert:{lot_number}:{expiration_date}

Logic:

Query lots expiring within 90 days with stock > 0
Read #alerts-shipbob-inventory channel history, extract lot_alert: keys from context blocks
Only alert for lots whose key is NOT found in channel history
Post each new lot as a Block Kit message with the dedup key in a context block

API Endpoints

Endpoint	Method	Description
`/alerts/lot-expiration`	POST	Check for expiring lots and send Slack alerts
`/alerts/lot-expiration/status`	GET	Show current expiring lots and alert history

Cloud Scheduler

Setting	Value
Name	`lot-expiration-alert`
Schedule	`0 13 * * *` (8:00 AM EST / 13:00 UTC daily)
Timezone	`America/New_York`
Endpoint	`POST /alerts/lot-expiration`
Auth	OIDC token (KPI service account)

TypeScript Interfaces

Add to eli-kpi/src/types.ts:

// ShipBob Fulfillment KPIs

export interface DeliveryToFirstTest {
  total_delivered: number;
  users_who_tested: number;
  users_never_tested: number;
  tested_pct: number;
  avg_days_to_first_test: number | null;
  median_days_to_first_test: number | null;
  p90_days_to_first_test: number | null;
  tested_within_7d_pct: number;
  tested_within_14d_pct: number;
  tested_within_30d_pct: number;
}

export interface DeliveryToFirstTestByCarrier {
  carrier: string;
  deliveries: number;
  tested: number;
  avg_days: number;
}

export interface PostDeliveryActivation {
  month: string;
  kits_delivered: number;
  activated_7d_count: number;
  activation_rate_7d_pct: number;
  activated_14d_count: number;
  activation_rate_14d_pct: number;
  activated_30d_count: number;
  activation_rate_30d_pct: number;
}

export interface FulfillmentAdjustedCompletion {
  current_month: string;
  current_successful_tests: number;
  current_tests_delivered: number;
  current_completion_rate: number;
  previous_month: string;
  previous_successful_tests: number;
  previous_tests_delivered: number;
  previous_completion_rate: number;
  month_over_month_change: number;
  variant: string;
}

export interface OrderToDeliveryTime {
  total_orders: number;
  avg_days_total: number;
  median_days_total: number;
  p90_days_total: number;
  avg_days_to_fulfill: number;
  avg_days_in_transit: number;
}

export interface OrderToDeliveryByCarrier {
  carrier: string;
  orders: number;
  avg_days: number;
  median_days: number;
}

export interface OrderToDeliveryByCountry {
  recipient_country: string;
  orders: number;
  avg_days: number;
}

export interface OrderToDeliveryMonthly {
  month: string;
  orders: number;
  avg_days: number;
  median_days: number;
}

export interface ReturnRate {
  total_orders: number;
  total_returns: number;
  return_rate_pct: number;
  avg_return_cost: number;
  total_return_cost: number;
  returns_completed: number;
  returns_processing: number;
}

export interface ReturnByReason {
  reason: string;
  count: number;
}

export interface InventoryHealth {
  inventory_id: number;
  item_name: string;
  is_active: boolean;
  is_lot_tracked: boolean;
  fulfillable: number;
  onhand: number;
  committed: number;
  sellable: number;
  awaiting: number;
  backordered: number;
  exception_qty: number;
  avg_daily_orders: number;
  estimated_days_of_stock: number | null;
}

export interface LotInventory {
  inventory_id: number;
  item_name: string;
  lot_number: string;
  expiration_date: string;
  days_until_expiry: number;
  fulfillable: number;
  onhand: number;
  committed: number;
  awaiting: number;
  by_fulfillment_center: Array<{
    fc_name: string;
    fulfillable: number;
    onhand: number;
    committed: number;
  }>;
}

export interface BatchTraceForward {
  shipbob_order_number: string;
  shopify_order_id: number;
  customer_name: string;
  customer_email: string;
  customer_phone: string | null;
  address1: string;
  city: string;
  state: string;
  country: string;
  zip_code: string;
  shipped_from: string;
  tracking_number: string | null;
  carrier: string | null;
  order_status: string;
  purchase_date: string;
  fulfillment_date: string;
  delivery_date: string | null;
  lot_number: string;
  lot_expiration: string;
  units_from_lot: number;
  item_name: string;
}

export interface BatchTraceReverse {
  shipbob_order_number: string;
  customer_name: string;
  customer_email: string;
  purchase_date: string;
  fulfillment_date: string;
  delivery_date: string | null;
  shipped_from: string;
  tracking_number: string | null;
  lot_number: string;
  lot_expiration: string;
  units: number;
  item_name: string;
}

export interface BatchSummary {
  lot_number: string;
  item_name: string;
  expiration_date: string;
  days_until_expiry: number;
  total_orders: number;
  total_units_shipped: number;
  first_order_date: string;
  last_order_date: string;
  remaining_stock: number;
}

export interface BatchSummaryByLocation {
  lot_number: string;
  location: string;
  orders: number;
  units: number;
}

export interface BatchSummaryByCountry {
  lot_number: string;
  country: string;
  orders: number;
}

export interface BatchFunnel {
  lot_number: string;
  expiration_date: string;
  total_orders: number;
  total_customers: number;
  customers_with_app: number;
  customers_who_tested: number;
  activation_rate_pct: number;
  avg_days_to_first_test: number | null;
}

export interface ExpiringLot {
  inventory_id: number;
  item_name: string;
  lot_number: string;
  expiration_date: string;
  days_until_expiry: number;
  onhand_quantity: number;
  fulfillable_quantity: number;
  committed_quantity: number;
  locations_with_stock: Array<{
    fc_name: string;
    onhand: number;
    fulfillable: number;
  }>;
}

export interface LotExpirationAlertResult {
  total_expiring_lots: number;
  already_alerted: number;
  newly_posted: number;
  failed: number;
  lot_details: Array<{
    lot_number: string;
    expiration_date: string;
    days_until_expiry: number;
    onhand_quantity: number;
    status: 'posted' | 'already_alerted' | 'failed';
  }>;
}

API Endpoints

Endpoint	Method	Description	Response Type
`/api/fulfillment/delivery-to-first-test`	GET	Time from delivery to first test	`DeliveryToFirstTest`
`/api/fulfillment/activation-rate`	GET	Post-delivery activation rates	`PostDeliveryActivation[]`
`/api/fulfillment/completion-rate`	GET	Fulfillment-adjusted completion	`FulfillmentAdjustedCompletion`
`/api/fulfillment/delivery-time`	GET	Order-to-delivery time	`OrderToDeliveryTime`
`/api/fulfillment/return-rate`	GET	Return rate metrics	`ReturnRate`
`/api/fulfillment/inventory`	GET	Inventory health	`InventoryHealth[]`
`/api/fulfillment/lots`	GET	Lot inventory detail	`LotInventory[]`
`/api/fulfillment/batch/trace`	GET	Forward trace: lot → customers	`BatchTraceForward[]`
`/api/fulfillment/batch/reverse`	GET	Reverse trace: customer → lots	`BatchTraceReverse[]`
`/api/fulfillment/batch/summary`	GET	All lots overview	`BatchSummary[]`
`/api/fulfillment/batch/funnel`	GET	Per-lot activation rates	`BatchFunnel[]`

Dashboard Layout

URL: /fulfillment

Implementation Sequence

Phase 1: Core SQL + Service Functions

Create eli-kpi/src/sql/bigquery/shipbob/ directory
Add all 12 SQL files (6 core + 4 batch traceability + 1 batch funnel + 1 lot expiration)
Add TypeScript interfaces to types.ts
Add fetch functions to analytics-service.ts

Phase 2: API Routes

Extend shipbob-routes.ts with fulfillment API endpoints
Add batch traceability endpoints (trace, reverse, summary, lots, funnel)
Register routes in server.ts

Phase 3: Dashboard

Create shipbob-dashboard.ts with HTML generation
Add /fulfillment route to server.ts
Add batch traceability section with recall lookup UI
Style using existing Eli Design System CSS

Phase 4: Lot Expiration Alerts

See Lot Expiration Alerts for the full technical spec.

Create lot-expiration-notifier.ts module with query + Slack channel history dedup + Block Kit posting
Add /alerts/lot-expiration endpoint to routes
Create #alerts-shipbob-inventory Slack channel and invite eli_health_alerts bot
Register Cloud Scheduler job at 8:00 AM EST daily via Terraform
Test with window_days override (current lots are outside the 90-day window)

Phase 5: Validation

Run each SQL query independently via bq CLI
Verify join match rates (ShipBob → Shopify → Firebase)
Compare fulfillment-adjusted completion rate against original
Validate delivery dates are populated for expected % of orders
Validate lot data: confirm lot numbers present on fulfilled orders
Test recall trace: verify all customers for a known lot are returned
Test expiration alert: verify idempotent behavior (no duplicate Slack messages)

Data Validation Queries

Run these after first ShipBob sync to verify data quality:

-- 1. Check delivery date coverage
SELECT
  COUNT(*) as total_orders,
  COUNTIF(JSON_VALUE(data, '$.shipments[0].delivery_date') IS NOT NULL) as has_delivery_date,
  ROUND(100.0 * COUNTIF(JSON_VALUE(data, '$.shipments[0].delivery_date') IS NOT NULL) / COUNT(*), 1) as delivery_date_pct
FROM `eli-health-prod.eli_health_shipbob.orders`
WHERE JSON_VALUE(data, '$.channel.name') = 'elihealth';

-- 2. Check ShipBob → Shopify join match rate
SELECT
  COUNT(*) as shipbob_orders,
  COUNTIF(o.id IS NOT NULL) as matched_shopify,
  ROUND(100.0 * COUNTIF(o.id IS NOT NULL) / COUNT(*), 1) as match_rate_pct
FROM `eli-health-prod.eli_health_shipbob.orders` sb
LEFT JOIN `eli-health-prod.eli_health_shopify.orders` o
  ON CAST(JSON_VALUE(sb.data, '$.reference_id') AS INT64) = o.id
WHERE JSON_VALUE(sb.data, '$.channel.name') = 'elihealth';

-- 3. Check full chain match rate (ShipBob → Shopify → Firebase → Biometrics)
SELECT
  COUNT(*) as shipbob_orders,
  COUNTIF(o.id IS NOT NULL) as has_shopify,
  COUNTIF(fa.uid IS NOT NULL) as has_firebase,
  COUNTIF(u.id IS NOT NULL) as has_biometrics,
  COUNTIF(r_count > 0) as has_tests
FROM `eli-health-prod.eli_health_shipbob.orders` sb
LEFT JOIN `eli-health-prod.eli_health_shopify.orders` o
  ON CAST(JSON_VALUE(sb.data, '$.reference_id') AS INT64) = o.id
LEFT JOIN `eli-health-prod.firebase_users.auth_users` fa
  ON LOWER(o.email) = LOWER(fa.email)
LEFT JOIN `eli-health-prod.eli_health_biometricspublic.user` u
  ON fa.uid = u.accountId AND u.deleted IS NULL
LEFT JOIN (
  SELECT userId, COUNT(*) as r_count
  FROM `eli-health-prod.eli_health_biometricspublic.reading`
  WHERE status = 'completed'
  GROUP BY userId
) tests ON u.id = tests.userId
WHERE JSON_VALUE(sb.data, '$.channel.name') = 'elihealth';

-- 4. Check lot data coverage on fulfilled orders
SELECT
  COUNT(*) as fulfilled_orders,
  COUNTIF(JSON_VALUE(
    JSON_QUERY_ARRAY(
      JSON_QUERY_ARRAY(data, '$.shipments[0].products')[OFFSET(0)],
      '$.inventory_items'
    )[OFFSET(0)],
    '$.lot'
  ) IS NOT NULL) as has_lot_number,
  ROUND(100.0 * COUNTIF(JSON_VALUE(
    JSON_QUERY_ARRAY(
      JSON_QUERY_ARRAY(data, '$.shipments[0].products')[OFFSET(0)],
      '$.inventory_items'
    )[OFFSET(0)],
    '$.lot'
  ) IS NOT NULL) / COUNT(*), 1) as lot_coverage_pct
FROM `eli-health-prod.eli_health_shipbob.orders`
WHERE JSON_VALUE(data, '$.channel.name') = 'elihealth'
  AND JSON_VALUE(data, '$.status') IN ('Fulfilled', 'Completed');

-- 5. Lot summary: all lots with order counts and stock
SELECT
  JSON_VALUE(inv_item, '$.lot') as lot_number,
  JSON_VALUE(inv_item, '$.expiration_date') as expiration,
  COUNT(DISTINCT sb.id) as orders,
  SUM(CAST(JSON_VALUE(inv_item, '$.quantity') AS INT64)) as units_shipped
FROM `eli-health-prod.eli_health_shipbob.orders` sb,
UNNEST(JSON_QUERY_ARRAY(sb.data, '$.shipments[0].products')) as prod,
UNNEST(JSON_QUERY_ARRAY(prod, '$.inventory_items')) as inv_item
WHERE JSON_VALUE(sb.data, '$.channel.name') = 'elihealth'
  AND JSON_VALUE(inv_item, '$.lot') IS NOT NULL
GROUP BY lot_number, expiration
ORDER BY orders DESC;

-- 6. Spot-check: sample delivery-to-test journey
SELECT
  JSON_VALUE(sb.data, '$.order_number') as shipbob_order,
  JSON_VALUE(sb.data, '$.shipments[0].delivery_date') as delivery_date,
  o.email,
  MIN(r.created) as first_test_after_delivery,
  TIMESTAMP_DIFF(
    MIN(r.created),
    SAFE.PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', JSON_VALUE(sb.data, '$.shipments[0].delivery_date')),
    DAY
  ) as days_to_test
FROM `eli-health-prod.eli_health_shipbob.orders` sb
JOIN `eli-health-prod.eli_health_shopify.orders` o
  ON CAST(JSON_VALUE(sb.data, '$.reference_id') AS INT64) = o.id
JOIN `eli-health-prod.firebase_users.auth_users` fa
  ON LOWER(o.email) = LOWER(fa.email)
JOIN `eli-health-prod.eli_health_biometricspublic.user` u
  ON fa.uid = u.accountId
JOIN `eli-health-prod.eli_health_biometricspublic.reading` r
  ON u.id = r.userId AND r.status = 'completed'
  AND r.created >= SAFE.PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', JSON_VALUE(sb.data, '$.shipments[0].delivery_date'))
WHERE JSON_VALUE(sb.data, '$.channel.name') = 'elihealth'
  AND JSON_VALUE(sb.data, '$.shipments[0].delivery_date') IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY delivery_date DESC
LIMIT 20;

Revision History

Date	Author	Change
2026-01-29	Chip	Initial implementation plan
2026-01-30	Chip	Added SQL queries 7-11 for batch traceability and lot expiration alerts, new TypeScript interfaces, new API endpoints, lot expiration Slack alert architecture, updated dashboard layout and validation queries
2026-01-30	Chip	Audit fixes: fixed nested ARRAY_AGG SQL syntax in queries 1, 4, 5, 10 (moved breakdowns to separate queries); aligned lot expiration dedup to Slack channel history approach (removed BigQuery state table); added SQL query 12 (batch funnel — per-lot activation rates); added ExpiringLot and LotExpirationAlertResult interfaces; updated overview diagram to show all 12 queries

Implementation Overview​

File Locations​

Common CTEs (Shared Across Queries)​

Join Flow Diagram​

SQL Query 1: Delivery-to-First-Test Time​

SQL Query 2: Post-Delivery Activation Rate​

SQL Query 3: Fulfillment-Adjusted Test Completion Rate​

SQL Query 4: Order-to-Delivery Time​

SQL Query 5: Return Rate​

SQL Query 6: Inventory Health (with Lot Tracking)​

SQL Query 7: Lot Inventory Detail​

SQL Query 8: Batch Traceability — Forward Trace (Lot → Customers)​

SQL Query 9: Batch Traceability — Reverse Trace (Customer → Lot)​

SQL Query 10: Batch Summary (All Lots Overview)​

SQL Query 11: Lot Expiration Alert (Lots Expiring Within 90 Days)​

SQL Query 12: Batch Funnel (Lot → Activation Rates)​

Lot Expiration Alert — Implementation​

Architecture​

Idempotent Deduplication (Slack Channel History)​

API Endpoints​

Cloud Scheduler​

TypeScript Interfaces​

API Endpoints​

Dashboard Layout​

Implementation Sequence​

Phase 1: Core SQL + Service Functions​

Phase 2: API Routes​

Phase 3: Dashboard​

Phase 4: Lot Expiration Alerts​

Phase 5: Validation​

Data Validation Queries​

Revision History​

Implementation Overview

File Locations

Common CTEs (Shared Across Queries)

Join Flow Diagram

SQL Query 1: Delivery-to-First-Test Time

SQL Query 2: Post-Delivery Activation Rate

SQL Query 3: Fulfillment-Adjusted Test Completion Rate

SQL Query 4: Order-to-Delivery Time

SQL Query 5: Return Rate

SQL Query 6: Inventory Health (with Lot Tracking)

SQL Query 7: Lot Inventory Detail

SQL Query 8: Batch Traceability — Forward Trace (Lot → Customers)

SQL Query 9: Batch Traceability — Reverse Trace (Customer → Lot)

SQL Query 10: Batch Summary (All Lots Overview)

SQL Query 11: Lot Expiration Alert (Lots Expiring Within 90 Days)

SQL Query 12: Batch Funnel (Lot → Activation Rates)

Lot Expiration Alert — Implementation

Architecture

Idempotent Deduplication (Slack Channel History)

API Endpoints

Cloud Scheduler

TypeScript Interfaces

API Endpoints

Dashboard Layout

Implementation Sequence

Phase 1: Core SQL + Service Functions

Phase 2: API Routes

Phase 3: Dashboard

Phase 4: Lot Expiration Alerts

Phase 5: Validation

Data Validation Queries

Revision History