Commerce and order-lifecycle analytics

Commerce analytics spans several systems:

storefront → cart and checkout behavior
order service → commercial agreement and order state
payment provider → authorization, capture, refund, dispute
fulfillment system → shipment and delivery
finance system → accounting treatment

GraphJSON can connect minimized analytical facts from these systems. It should not become the authority for inventory, order state, payment settlement, tax, or recognized revenue.

Define the grains#

Do not place order-, item-, and payment-level amounts into one event and sum them together without a grain filter.

Recommended collections#

commerce_behavior
order_events
payment_events
fulfillment_events
commerce_rollups

Separate collections when owners, sensitivity, volume, or retention differ. High-volume product views should not determine the lifecycle of payment and refund facts.

Instrument the conversion journey#

Suggested behavioral events:

product_viewed
product_added_to_cart
checkout_started
checkout_step_completed
order_submitted

Example:

{
  "event": "checkout_started",
  "event_id": "evt_01J...",
  "session_id": "ses_01J...",
  "user_id": "usr_42",
  "account_id": "acct_7",
  "cart_id": "cart_91",
  "currency": "usd",
  "cart_subtotal_minor": 12900,
  "item_count": 3,
  "checkout_version": "v4",
  "schema_version": 2
}

Do not treat checkout_started as an order. A user can abandon, retry, open several tabs, or change the cart.

Use A/B tests and funnels for a defined conversion funnel and Sessionization for visit semantics.

Model immutable order transitions#

{
  "event": "order_placed",
  "event_id": "order:ord_91:placed:v1",
  "order_id": "ord_91",
  "user_id": "usr_42",
  "account_id": "acct_7",
  "currency": "usd",
  "subtotal_minor": 12900,
  "discount_minor": 1000,
  "tax_minor": 952,
  "shipping_minor": 500,
  "order_total_minor": 13352,
  "item_count": 3,
  "sales_channel": "web",
  "order_version": 1,
  "schema_version": 2
}

Use one immutable transition per meaningful state:

order_placed
order_confirmed
order_cancelled
order_fulfilled
order_delivered

Carry a monotonic order_version when changes can share one second or arrive out of order. Do not overwrite the placed event with the current order row.

If current state is needed, select the latest version or emit an explicit snapshot according to Event-time state and reference data.

Keep money typed and balanced#

Use integer minor units plus lowercase currency:

amount_minor = 13352
currency = usd

Do not assume every currency has two decimal places. Never sum different currencies into one unlabeled number.

Define:

order_total
  = subtotal
  - discount
  + tax
  + shipping
  + approved fees

Test the equation for every order contract version. Decide whether gratuity, gift cards, store credit, marketplace fees, and tax are included.

Gross merchandise value, collected cash, net sales, and recognized revenue are not synonyms. Name each metric and its authority.

Choose an item representation#

For product and category analysis, prefer one analytical event per order line:

{
  "event": "order_item_placed",
  "event_id": "order:ord_91:item:line_2:v1",
  "order_id": "ord_91",
  "line_id": "line_2",
  "product_id": "prod_44",
  "variant_id": "var_12",
  "category_at_order": "accessories",
  "quantity": 2,
  "unit_price_minor": 4200,
  "line_discount_minor": 500,
  "line_total_minor": 7900,
  "currency": "usd",
  "order_version": 1
}

This keeps the grain explicit and avoids repeatedly unnesting a large item array. Preserve the order-level event for order count and total.

Do not sum both order totals and line totals in the same metric. They are two representations of the same sale.

Separate payments from orders#

Payment lifecycle:

payment_authorized
payment_capture_succeeded
payment_capture_failed
refund_succeeded
dispute_opened
dispute_won
dispute_lost

One order can have several payment attempts, split tenders, partial captures, partial refunds, and disputes. Use separate payment_id, attempt_id, and provider event ID fields.

{
  "event": "payment_capture_succeeded",
  "event_id": "stripe:evt_123",
  "order_id": "ord_91",
  "payment_id": "pay_61",
  "amount_minor": 13352,
  "currency": "usd",
  "provider": "stripe",
  "provider_event_id": "evt_123"
}

Use the Stripe integration for provider events or emit a normalized application-owned contract. Avoid counting both as one payment unless the deduplication rule is explicit.

Model refunds and returns separately#

A return request, accepted return, received item, and completed refund are different facts.

{
  "event": "refund_succeeded",
  "event_id": "stripe:evt_124",
  "order_id": "ord_91",
  "refund_id": "re_12",
  "amount_minor": 4200,
  "currency": "usd",
  "reason_code": "customer_return",
  "refund_scope": "partial"
}

Use the completed provider or ledger fact for refunded-money metrics. Product return rates may use item quantity instead. Keep both units visible.

Do not subtract disputes, refunds, discounts, and cancellations from “GMV” without publishing the exact definition.

Track fulfillment#

Useful facts:

fulfillment_requested
shipment_created
shipment_handed_to_carrier
shipment_delivered
fulfillment_failed

Store bounded service-level attributes:

{
  "event": "shipment_delivered",
  "shipment_id": "shp_42",
  "order_id": "ord_91",
  "fulfillment_method": "parcel",
  "carrier": "ups",
  "warehouse_region": "us-west",
  "promised_by": 1785513600,
  "delivered_at": 1785427200,
  "delivery_duration_hours": 48
}

Avoid full addresses, tracking URLs, delivery instructions, or customer names.

Query order and refund value#

WITH facts AS (
  SELECT
    JSONExtractString(json, 'currency') AS currency,
    'order_placed' AS event,
    JSONExtractInt(json, 'order_total_minor') AS amount_minor
  FROM order_events
  WHERE
    JSONExtractString(json, 'event') = 'order_placed'
    AND timestamp >= toUnixTimestamp(now() - INTERVAL 30 DAY)

  UNION ALL

  SELECT
    JSONExtractString(json, 'currency') AS currency,
    'refund_succeeded' AS event,
    JSONExtractInt(json, 'amount_minor') AS amount_minor
  FROM payment_events
  WHERE
    JSONExtractString(json, 'event') = 'refund_succeeded'
    AND timestamp >= toUnixTimestamp(now() - INTERVAL 30 DAY)
)
SELECT
  currency,
  countIf(event = 'order_placed') AS orders,
  sumIf(amount_minor, event = 'order_placed') AS placed_value_minor,
  sumIf(amount_minor, event = 'refund_succeeded') AS refunds_minor,
  placed_value_minor - refunds_minor AS placed_less_refunds_minor
FROM facts
GROUP BY currency

Label this result precisely. It is not automatically collected cash or accounting revenue.

Calculate average order value#

AOV = sum(order total at the selected order state) / distinct orders

Use one selected version per order. Exclude test and duplicate orders. State whether cancelled orders, tax, shipping, and discounts are included.

Always show order count beside AOV. A large order can move the average substantially.

Measure repeat purchase#

Choose:

• stable customer identity
• eligible first order
• repeat window
• cancellation and refund treatment
• anonymous-to-known behavior

For example:

customers placing a second non-cancelled order
within 90 days of their first non-cancelled order

Do not use current email as the historical customer key. Use a stable permitted ID and follow Identity stitching.

Reconcile each authority#

For a settled interval:

Compare stable IDs, counts, currency totals, min/max times, state distributions, and sampled records.

Use Continuous reconciliation for certification and response.

Build the dashboard#

Recommended sections:

1. browse-to-order funnel
2. placed orders and value by currency
3. payment success and recovery
4. AOV and item quantity with denominators
5. cancellation, return, refund, and dispute rates
6. time to fulfill and deliver
7. first and repeat purchase cohorts
8. source completeness and reconciliation state

Segment only by approved bounded fields. Product IDs, order IDs, customer IDs, and tracking IDs are for authorized drill-down, not chart legends.

Launch checklist#

• Each event has one order, line, payment, refund, or fulfillment grain.
• Order, payment, inventory, and finance authorities are named.
• Money uses minor units and explicit currency.
• Order and item totals cannot be double-counted.
• Attempts, outcomes, state changes, and snapshots remain distinct.
• Provider retries preserve stable event IDs.
• Addresses, payment instruments, and raw provider payloads are excluded.
• AOV, GMV, net sales, cash, and revenue have separate definitions.
• Repeat purchase uses stable permitted identity.
• Critical totals reconcile across source systems.

Continue with Multi-currency and FX analytics for conversion and restatement policy, or Marketplace and two-sided platform analytics when one transaction connects demand and supply.