ShipBob Fulfillment KPIs

Status: Draft - Pending Review Data Source: ShipBob BigQuery Integration Implementation: eli-kpi Related: Engagement Metrics 2026

---

Why These Metrics Exist

The current user journey has a blind spot between purchase and first test. We know 34,579 users purchased, but only 2,238 downloaded the app (6.1%). We don't know whether the drop-off happens because:

• Kits haven't arrived yet (fulfillment lag)
• Kits arrived but users never opened them (activation failure)
• Kits arrived but users couldn't figure out the app (onboarding friction)

ShipBob data fills this gap by providing actual delivery dates, turning the unknown into a measurable funnel stage.

---

Where ShipBob Fits in the User Journey

The fulfillment stage is the physical bridge between a digital purchase and a physical product in the user's hands. Without measuring it, we're guessing about the entire middle of the funnel.

---

Data Linking: How ShipBob Connects to Everything

The Complete Chain

Join Keys

From	To	Join Field	Type	Match Rate
ShipBob reference_id	Shopify order.id	Direct ID	INT64	100%
Shopify email	Firebase email	Case-insensitive	STRING	~94%
Firebase uid	Biometrics accountId	Direct UID	STRING	100%
Biometrics user.id	Readings userId	Direct ID	STRING	100%

JSON Field Access (ShipBob Orders)

ShipBob data is stored as raw JSON blobs. Key fields extracted via JSON_VALUE():

Field	JSON Path	Example
Shopify Order ID	$.reference_id	6095829762093
Order Status	$.status	Fulfilled, Delivered
Purchase Date	$.purchase_date	2026-01-28T19:52:59Z
Recipient Email	$.recipient.email	user@example.com
Delivery Date	$.shipments[0].delivery_date	2026-02-05T14:30:00Z
Tracking Number	$.shipments[0].tracking.tracking_number	1Z999AA10...
Carrier	$.shipments[0].tracking.carrier	UPS
Fulfillment Date	$.shipments[0].actual_fulfillment_date	2026-01-29T08:00:00Z
Product SKU	$.products[0].sku	VIP-KIT-CL-2
Quantity	$.products[0].quantity	1
Lot Number	$.shipments[0].products[0].inventory_items[0].lot	CB0016
Lot Expiration	$.shipments[0].products[0].inventory_items[0].expiration_date	2026-11-17T00:00:00
Shipped From	$.shipments[0].location.name	Elwood (IL)
Shipping Method	$.shipping_method	USA Standard

---

Proposed KPIs

Overview

---

KPI 1: Delivery-to-First-Test Time

The Question: After a kit lands on a user's doorstep, how long before they take their first test?

Why It Matters: This is the single most important metric ShipBob unlocks. It separates fulfillment problems (kit hasn't arrived) from activation problems (kit arrived, user hasn't tested). A long delivery-to-test time suggests onboarding friction. A short time suggests product-market fit.

Formula:

Delivery-to-First-Test = First completed reading date - ShipBob delivery date

Output:

Metric	Description
avg_days_to_first_test	Average days from delivery to first test
median_days_to_first_test	Median days (less affected by outliers)
tested_within_7d_pct	% of users who tested within 7 days
tested_within_14d_pct	% who tested within 14 days
tested_within_30d_pct	% who tested within 30 days
never_tested_pct	% of delivered kits with no test (30+ days)

Segmentation:

• By carrier (UPS / FedEx / USPS)
• By geography (state/province/country)
• By product SKU
• By subscription type (monthly / prepaid / one-time)
• Monthly trend

---

KPI 2: Post-Delivery Activation Rate

The Question: Of all kits confirmed delivered, what percentage resulted in at least one completed test?

Why It Matters: This is the purest measure of product-market fit. The user has the kit in hand. They either use it or they don't. Unlike the existing engagement metrics that use "purchase date" as the starting point, this uses confirmed delivery.

Formula:

Activation Rate = Users with >= 1 completed test after delivery / Users with confirmed delivery

Time Windows:

Window	Description
7-day	Early activation (strong intent)
14-day	Standard activation
30-day	Extended activation

Segmentation: Same as KPI 1.

---

KPI 3: Fulfillment-Adjusted Test Completion Rate

The Question: What is the real test completion rate when we use actual delivery dates instead of order dates?

Why It Matters: The existing monthly-test-completion-rate-delivered.sql uses order date to attribute "tests delivered" to a month. A January 28 order might not arrive until February 5 — those tests get counted in January's denominator but the user can't possibly take them until February. This inflates January's denominator and deflates its completion rate.

Enhancement:

• Replace o.created_at (Shopify order date) with sb.shipments[0].delivery_date (ShipBob actual delivery)
• Falls back to order date when no ShipBob data exists

Formula:

Adjusted Completion Rate = Successful tests this month / Tests actually delivered this month

Where "actually delivered" means ShipBob confirmed the kit was delivered in that month, not just ordered.

---

KPI 4: Order-to-Delivery Time

The Question: How long does it take from purchase to kit arriving at the customer's door?

Why It Matters: Fulfillment speed directly correlates with activation. Users who wait too long lose motivation. This metric tracks operational efficiency and identifies geographic or carrier issues.

Formula:

Order-to-Delivery = ShipBob delivery_date - Shopify order created_at

Output:

Metric	Description
avg_days	Average order-to-delivery
median_days	Median (less skewed by outliers)
p90_days	90th percentile (worst-case for most users)
by_carrier	Breakdown by shipping carrier
by_region	Breakdown by recipient state/country
monthly_trend	Trend over time

---

KPI 5: Return Rate

The Question: What percentage of fulfilled orders are returned?

Why It Matters: Returns signal product dissatisfaction, shipping damage, or wrong-item issues. Tracking reason codes reveals systemic problems.

Formula:

Return Rate = Returns count / Fulfilled orders count

Output:

Metric	Description
return_rate_pct	Overall return rate
by_reason	Breakdown by return reason code
by_product	Return rate per product SKU
monthly_trend	Trend over time
avg_return_cost	Average return processing cost

---

KPI 6: Inventory Health (with Lot/Batch Tracking)

The Question: Do we have enough stock to fulfill incoming orders, and where is each batch?

Why It Matters: Stockouts break the funnel entirely — orders can't be fulfilled. Beyond aggregate stock levels, lot-level tracking is critical for regulatory compliance, recall readiness, and expiration management. Eli Health's Cortisol Pack has is_lot: true in ShipBob, meaning every unit is tracked by manufacturing batch.

Output:

Metric	Description
sellable_quantity	Current sellable inventory per SKU
committed_quantity	Inventory reserved for pending orders
days_of_stock	Estimated days until stockout (sellable / avg daily orders)
backordered	Items on backorder
by_fulfillment_center	Stock levels by warehouse (Elwood IL + Brampton ON)
by_lot	Stock breakdown per lot number
lot_expiration	Expiration date per lot
lot_location	Which fulfillment center holds each lot

Lot-Level Detail:

Each lot record includes:

• lot_number — Manufacturing batch ID (e.g., CB0014, CB0015, CB0016)
• expiration_date — Shelf life end date
• fulfillable_quantity — Available units for that lot
• onhand_quantity, committed_quantity, awaiting_quantity
• by_fulfillment_center — Per-lot, per-location stock split

---

KPI 7: Batch Traceability (Recall Readiness)

The Question: Given a manufacturing lot number, which customers received it? And given a customer, which lot did they receive?

Why It Matters: In a product recall scenario, Eli Health must identify every customer who received a specific manufacturing batch within hours, not days. This is a regulatory requirement for health products. Without this, a recall would require contacting ShipBob manually, cross-referencing spreadsheets, and potentially missing affected customers.

Data Source: ShipBob stores lot information at the shipment level — each fulfilled order records exactly which lot was pulled from the warehouse in shipments[0].products[].inventory_items[].lot.

Capabilities:

Forward Trace (Lot → Customers)

For a recall: given lot number, find all customers who received it.

Input: Lot CB0012
Output: 223 orders, 633 units shipped
  → Customer name, email, address
  → Tracking number
  → Shipped from (Elwood IL or Brampton ON)
  → Order date

Reverse Trace (Customer → Lot)

For a customer complaint: given a customer, find which lot they received.

Input: Customer email
Output: Order #9964, Lot CB0012, 2 units
  → Expiration: 2027-08-12
  → Shipped from: Brampton (Ontario) 2

Batch Funnel (Lot → Usage)

Per-batch metrics: how many units were shipped, how many customers tested, activation rate by batch.

Input: All lots
Output per lot:
  → Total units shipped
  → Total customers
  → Customers who activated (took a test)
  → Activation rate
  → Average days to first test

Output:

Metric	Description
lot_number	Manufacturing batch identifier
expiration_date	Shelf life for the lot
total_orders	Number of orders that received this lot
total_units_shipped	Total units shipped from this lot
shipped_from	Fulfillment center(s) that shipped this lot
date_range	Earliest to latest order date for this lot
affected_customers	Full customer list (for recall)
activation_rate	% of customers from this lot who took a test

Segmentation:

• By fulfillment center (US vs Canada)
• By lot number
• By date range
• By country/region

---

Fulfillment Center Reference

Eli Health uses two ShipBob fulfillment centers:

Center	ID	Country	Region	Active Lots
Elwood (IL)	178	US	Midwest	CB0014, CB0015, CB0016
Brampton (Ontario) 2	146	Canada	Ontario	CB0012

Key fact: Batches can be split across both locations. For example, lot CB0007 was shipped from both Elwood (1,954 units) and Brampton (425 units). A recall for a single lot may require contacting customers in both countries.

---

Current Lot Inventory (as of Jan 2026)

Lot	Expiry	Fulfillable	Location	Status
CB0016	2026-11-17	28	Elwood (IL)	Low stock, expiring soon
CB0012	2027-08-12	128	Brampton (ON)	Canada-only stock
CB0014	2027-10-07	4,051	Elwood (IL)	Active
CB0015	2027-10-31	4,450	Elwood (IL)	Active
CB0016	2027-11-17	2,296	Elwood (IL)	Active

Historical lots (fully shipped): CB0007, CB0013, CB0017, CB0018

---

Lot Data JSON Paths

In Inventory table (eli_health_shipbob.inventory):

Field	JSON Path	Example
Lot tracking enabled	$.is_lot	true
Lot array	$.fulfillable_quantity_by_lot	Array of lot objects
Lot number	$.fulfillable_quantity_by_lot[N].lot_number	CB0016
Expiration	$.fulfillable_quantity_by_lot[N].expiration_date	2027-11-17T00:00:00
Fulfillable qty	$.fulfillable_quantity_by_lot[N].fulfillable_quantity	2296
Per-FC breakdown	$.fulfillable_quantity_by_lot[N].fulfillable_quantity_by_fulfillment_center	Array

In Orders table (eli_health_shipbob.orders):

Field	JSON Path	Example
Lot (per shipment item)	$.shipments[0].products[N].inventory_items[M].lot	CB0016
Expiration (per item)	$.shipments[0].products[N].inventory_items[M].expiration_date	2026-11-17T00:00:00
Quantity from lot	$.shipments[0].products[N].inventory_items[M].quantity	6
Shipped from	$.shipments[0].location.name	Elwood (IL)

---

KPI 8: Lot Expiration Alerts (Slack)

The Question: Are there manufacturing lots sitting in a warehouse that will expire before they can be shipped to customers?

Why It Matters: An expired lot cannot be shipped. If lot CB0016 has 28 units expiring in November 2026 and nobody notices until October, those units are wasted — lost revenue and lost tests that could have helped users. For a health product, this is both a financial and regulatory issue. FIFO (First In, First Out) is the manufacturing standard: ship the oldest lots first.

How It Works:

Alert Rules:

• Trigger: Any lot with expiration_date within 90 days of today AND onhand_quantity > 0
• Schedule: Daily at 8:00 AM EST (13:00 UTC)
• Idempotent: Each lot is only notified once. Uses Slack channel history for deduplication (same pattern as App Store Review Notifier). Each message embeds a lot_alert:{lot_number}:{expiration_date} key in a context block. If a lot has already been posted to Slack, do not post it again.
• Channel: #alerts-shipbob-inventory (private, finance + operations team)

Slack Message Format:

*Lot Expiration Warning*

Lot *CB0016* (Cortisol Pack) expires on *2026-11-17* (87 days remaining)
  Location: Elwood (IL)
  Stock remaining: 28 units
  Action needed: Ship or transfer before expiration

Lot *CB0012* (Cortisol Pack) expires on *2027-08-12* (if within 90 days)
  Location: Brampton (Ontario) 2
  Stock remaining: 128 units

Implementation:

• Run as part of the daily ShipBob sync (after inventory sync completes at 3:00 AM UTC)
• Or as a separate Cloud Scheduler job at 8:00 AM EST
• Query eli_health_shipbob.inventory for lots expiring within 90 days
• Read Slack channel history to check for previously posted lot alerts (deduplication via context blocks)
• Post via Slack bot (eli_health_alerts bot token)
• See Lot Expiration Alerts for the full technical spec

---

Future: Klaviyo Integration for Expiring User Tests

The Idea: If we know which lot a customer received, and that lot is expiring soon, we can proactively email the customer via Klaviyo: "Your cortisol tests expire in 3 months — use them while they're still valid."

Data Chain:

Lot CB0012 (expiring 2027-08-12)
  → Orders table: find all customers who received CB0012
  → Shopify email → Klaviyo profile
  → Trigger Klaviyo flow: "Your tests are expiring"

Prerequisites:

• Batch traceability queries (already designed above)
• Klaviyo API integration for triggering flows
• Marketing team coordination (Morgan) for email copy and timing

Status: Concept — requires further discussion with marketing team.

---

How These Enhance Existing 2026 KPIs

Existing KPI	Current Approach	ShipBob Enhancement
Test Completion Rate (22.2%)	Uses order date as "delivery" proxy	Use actual ShipBob delivery date for accurate denominator
WAU (16.6%)	Eligible = purchased in last 90 days	Could use "kit delivered in last 90 days" for tighter eligibility
MAU Standard (41.1%)	Same purchase-based eligibility	Same delivery-based eligibility option
Power Users (13%)	No fulfillment context	Correlate fulfillment speed with power user conversion

---

Data Sources

Sync Schedule

Dataset	Sync Time (UTC)	Method	Freshness
ShipBob	3:00 AM	eli-kpi (daily)	Orders: 7-day rolling window
Shopify	Daily	Airbyte	Incremental
LoopWork	4:00 AM	eli-kpi (daily)	Full sync
Firebase	Real-time	Native replication	Continuous
Biometrics	Real-time	Datastream CDC	Continuous

---

Priority & Sequencing

Priority	KPI	Rationale
1	Delivery-to-First-Test Time	Highest strategic value; answers the biggest unknown in the funnel
2	Post-Delivery Activation Rate	New funnel metric for the gap between delivery and engagement
3	Batch Traceability (Recall Readiness)	Regulatory requirement; recall readiness for health products
4	Fulfillment-Adjusted Test Completion Rate	Enhances existing metric with better data
5	Inventory Health (with Lot Tracking)	Operational dashboard with batch-level visibility
6	Order-to-Delivery Time	Operational health metric
7	Return Rate	Quality signal

---

Open Questions

1. Delivery date availability: What percentage of ShipBob orders have a populated delivery_date? Some carriers may not report delivery confirmation. Need to validate data completeness after first sync.

2. Multiple shipments per order: Some orders may have multiple shipments (partial fulfillment). Should we use the first shipment's delivery date, the last, or track per-shipment?

3. Eligibility window adjustment: Should the 90-day eligibility window for WAU/MAU be measured from delivery date instead of purchase date? This would be a more accurate "time since kit in hand" window.

4. Return handling in activation: If a kit was delivered, returned, and reshipped — does the activation clock restart? How do we handle this edge case?

5. International vs domestic: ShipBob shows shipping_method (e.g., "USA Standard"). Should we segment KPIs by domestic vs international shipping?

6. Batch/lot availability: Does ShipBob track lot numbers? RESOLVED: Yes. The Cortisol Pack has is_lot: true. Lot number, expiration date, and per-fulfillment-center breakdown are all available in the fulfillable_quantity_by_lot array (inventory) and shipments[].products[].inventory_items[].lot (orders). Every fulfilled order records which lot was used.

7. Multiple fulfillment centers: Do batches get split across locations? RESOLVED: Yes. Eli Health uses Elwood (IL) for US and Brampton (Ontario) for Canada. Batches can be split — e.g., CB0007 shipped from both locations. A recall query must cover both centers.

---

Revision History

Date	Author	Change
2026-01-29	Chip	Initial specification
2026-01-30	Chip	Added KPI 7 (Batch Traceability), KPI 8 (Lot Expiration Alerts), expanded KPI 6 with lot tracking, documented lot data JSON paths, fulfillment center reference, current lot inventory, and Klaviyo integration concept