Analytics & ROI

Merchant analytics must prove Tryvio impact without broad attribution. The merchant-facing revenue metric is Tryvio attributed revenue: revenue from purchased line items whose product has a Tryvio proof row for the same shopper inside the attribution window.

Current production state

Shipped web/API release: 29cad2a fix(analytics): ship ROI dashboard metrics.

The merchant Overview now includes a Tryvio ROI panel and proof drawer. The API response from /api/shopify/metrics includes:

• totals: funnel, attribution, Shopify order/revenue context, and comparison values.
• valueMetrics: attributed revenue, Tryvio cost, ROAS, ROI, and estimated lift states.
• proofRows: merchant-safe attributed line proof rows.
• billing: active plan state and usage counters.

The dashboard is client-rendered inside Shopify Admin, and /api/shopify/metrics is authenticated through the embedded app session. Public curl checks will return 401; verify live data from an authenticated Shopify Admin iframe or through controlled server-side diagnostics.

Source of truth

1. The widget records a tried-product proof after a successful result view.
2. /api/events persists that proof into tried_products.
3. Shopify pixel checkout events are normalized per line item.
4. /api/events writes one row per attributed purchased line into attributed_order_lines.
5. attributed_orders remains a backward-compatible summary table for existing dashboard readers.

analytics_events is the funnel source. As of 2026-07-10 all event-derived dashboard metrics are computed in SQL over the full date range; since 2026-07-27 the app calls the single-pass rewrite merchant_metrics_event_totals_v2(p_store_id, p_from, p_to) — see SQL aggregation (2026-07-10, v2 2026-07-27) below. listRecentAnalyticsEvents() (the old 50,000-row JS-side fetch) has been removed from all metrics computation; a targeted per-session fetch is still used for proof-drawer event chains.

Sessions and baseline source

Store-level sessions, store conversion rate, and the baseline used for estimated lift are derived from Tryvio’s own Shopify web pixel, not from ShopifyQL. Each distinct product_viewed event fired by the pixel counts as one shopper session. The previous ShopifyQL FROM sessions approach required the read_reports scope and Shopify Protected Customer Data (Level 2) approval; these are unavailable on most plans and consistently returned unavailable / 0.

Total orders and total revenue continue to come from the Shopify Orders API (unchanged).

Baseline conversion = the non-Tryvio cohort: shoppers who fired product_viewed but did not trigger any Tryvio interaction. Their purchase conversion rate is compared with the Tryvio cohort’s rate to produce the estimated lift figure. Sample-size guards still apply: the card shows Collecting data when either cohort is too small, and Not enough baseline traffic yet when the non-Tryvio baseline cohort is empty.

Attribution rules

The attribution window is 30 days. V1 Shopify historical reconciliation stays within Shopify’s default 60-day order access unless the app later requests broader scopes.

Proof payload

tried_products stores:

• store_id
• tryon_session_id
• shopper_session_id, shopper_pseudo_id
• product_id, product_handle, external_product_id, external_variant_id
• role: primary, bundle_complement, related_product, gallery
• source: main_product, bundle_outfit, related_product, gallery, server
• tried_at, expires_at
• attribution_key for idempotent writes
• metadata

attributed_order_lines stores the line-level purchase proof:

• order_id, order_line_id, optional order_name
• product identifiers for the purchased line
• quantity, line_value, currency
• tryon_session_id, optional tried_product_id
• attribution_mode: same_session_same_product or 30d_product_lookback
• proof JSON showing the matched tried-product timestamp, role, and source
• idempotency_key so repeated pixel events do not duplicate revenue

Dashboard semantics

The dashboard should show:

• Store-wide orders and revenue from the Shopify Orders API; store sessions and baseline conversion from Tryvio’s own web pixel (product_viewed events), which requires no additional Shopify scope or approval.
• Tryvio attributed revenue from attributed_order_lines when line-level rows exist.
• Tryvio purchases as unique attributed order_id count, not raw line count.
• Product table revenue as the sum of attributed line values for that product.
• Conversion lift only when sample-size guards pass; otherwise show a collecting-data state.
• ROI and ROAS only when cost inputs exist. Missing billing cost must be unavailable, not zero.

Overview card definitions:

Do not count passive visitors as Tryvio shoppers. A visitor who only loaded a product page or saw a widget impression is not an engaged Tryvio shopper until they trigger a Tryvio event such as click, upload, generation, result view, add-to-cart, checkout, or attributed purchase.

SQL aggregation (2026-07-10, v2 2026-07-27)

All event-derived Overview/funnel/proof metrics (button taps, try-ons, shoppers, funnel steps, step rates, top-product event columns, baseline cohorts) are computed by the Postgres RPC merchant_metrics_event_totals_v2(p_store_id, p_from, p_to) (apps/web/supabase/migrations/20260727d_metrics_v2.sql), called once for the current range and once for the comparison range. The RPC aggregates over the full date range in SQL — counts, distinct sessions, and distinct shoppers per event type, plus baseline cohorts — and returns totals, not raw rows.

v2 (issue #128) is a single-pass rewrite of the original merchant_metrics_event_totals (20260710_merchant_metrics_sql_aggregation.sql): the v1 shape ran ~15 sort-based count(DISTINCT) aggregates plus EXCEPT cohort chains over the raw event range, and on a 346k-events/month store every sort spilled to disk at the cluster’s small work_mem — cold-cache runs exceeded the 2-minute statement timeout daily (“Failed to load merchant metrics”). v2 replaces every DISTINCT sort with hash GROUP BYs over narrow keys (per-session bool_or flags, per-cohort flags, per-(product, session) inner grouping) fed by one materialized narrow scan, sets work_mem = '96MB' inline, and revokes EXECUTE from PUBLIC/anon/authenticated (service_role only — v1 had been PUBLIC-executable). The output contract is unchanged: same signature, same 22-key jsonb, parity-gated byte-identical against v1 across every active store and range before the swap. v1 remains in the database until v2 is live everywhere; rollback = point the code back at v1.

The merchant Overview includes a Tryvio ROI panel with:

• Tryvio attributed revenue: strict product-level attributed line revenue.
• Tryvio cost: prorated subscription cost for the selected date range plus known usage charges.
• ROAS: Tryvio attributed revenue divided by Tryvio cost.
• ROI: (Tryvio attributed revenue - Tryvio cost) / Tryvio cost.
• Estimated lift: Tryvio shopper purchase conversion vs. the non-Tryvio baseline cohort (product_viewed but no Tryvio interaction); shown only after the sample-size guard passes; Collecting data when either cohort is too small, Not enough baseline traffic yet when the baseline cohort is empty.

Each value card exposes tooltip copy explaining the formula and limitation. If billing inputs are unavailable, the state must render as unavailable or collecting data, never as a misleading 0. Pixel-based session data (store conversion, baseline) does not depend on Shopify report scopes and is available on any plan as long as the web pixel is installed.

View proof opens attributed line rows without shopper PII. Rows include order id/name, line value, product identifiers, try-on session id, role/source, attribution mode/source, tried timestamp, order timestamp, and the available event chain timestamps from widget click through checkout/order.

Billing cost semantics

ROI cost is calculated for the selected dashboard range:

tryvioCost = proratedPlanFee + usageChargesForRange
roas = tryvioAttributedRevenue / tryvioCost
roi = (tryvioAttributedRevenue - tryvioCost) / tryvioCost

For standard plans, the monthly fee comes from the plan catalog. For custom plans, it comes from the store’s custom billing cache, which is reconciled from Shopify. If a restored custom plan has current_period_starts_at but no current_period_ends_at, the metrics route uses a 30-day fallback period so cost is still available.

Usage charges for range (fixed 2026-07-10)

getUsageChargesForRange (api/shopify/metrics/route.ts) now sources the usage component of tryvioCost from tryon_billing_ledger — overage rows (was_overage = true, status = 'billable') created within the selected range, × getOverageRate(store), for all plans (standard and custom). If the requested range starts before the store’s earliest ledger row (coverage rule), it falls back to summing settled overage_charged billing_events for that range instead.

Previously, for custom-0 plans, cost was analytics successfulGenerations × rate — successfulGenerations is a session count, not a billable-try-on count, so this overstated or understated cost depending on retries/refunds. This produced Icedout’s $139.86 vs. a real $381 overage charge. Analytics (analytics_events) are no longer used for cost anywhere; ROAS and ROI inherit the corrected cost automatically since they’re derived from tryvioCost.

Custom plans with custom_try_on_limit = 0 mean zero included try-ons, not a hard usage limit. The dashboard must describe this as billable usage, for example 32 billable try-ons used this period, not 32 / 0.

Widget and pixel contract

The storefront widget writes a 30-day local proof history under tryvio_attribution_history_v1 and adds hidden _tryvio_* line properties when Tryvio adds an item to cart. These are additive signals. The durable source of truth is still server-side proof in Supabase.

The web pixel must only attach a Tryvio session to add-to-cart or checkout events when the purchased line’s product matches a proof by handle or external product id. Missing product identity must not credit Tryvio.

/api/events normalizes both Tryvio’s flat per-line checkout payload and nested Shopify checkout payloads before attribution. For multi-line payloads, only lines matching tried-product proof are credited; the legacy same-session fallback is limited to a single-line payload to avoid overclaiming.

Orders reconciliation

POST /api/shopify/attribution/reconcile runs an on-demand Shopify Orders reconciliation for the selected range, clamped to the v1 60-day window. It fetches recent Shopify orders, inspects Tryvio line/order attributes, matches line items against tried-product proof where available, and upserts attributed rows with source orders_reconciliation. This is the recovery path when the pixel missed checkout_completed.

2026-07-01 icedout incident

Symptoms on zmwqzs-ir.myshopify.com:

• Custom range 2026-06-30 to 2026-07-01 showed 1 button tap, 0 try-ons, and about 124 shoppers.
• Changing to 2026-07-01 or last 7 days kept nearly the same shopper count.
• Billing showed 32 / 0 try-ons used this period.
• ROI was not visible.

Root causes:

1. ROI UI/API work had not been shipped to production before the owner checked the Shopify Admin dashboard.
2. The dashboard read only the first Supabase REST page of analytics_events, truncating high-volume date ranges.
3. shoppers counted passive visitors/impressions instead of engaged Tryvio shoppers.
4. Custom plan copy rendered used / limit even when 0 meant zero included billable try-ons.
5. Restored custom plans with missing current_period_ends_at made cost/ROI unavailable.

Expected after the fix logic for that store:

• 2026-06-30 to 2026-07-01: about 77 button taps, 43 try-ons, 50 engaged Tryvio shoppers, and 40 completed/result-viewed try-ons.
• 2026-07-01 only: about 1 button tap, 0 try-ons, and 1 engaged Tryvio shopper.
• Custom plan usage copy: 32 billable try-ons used this period.

Verification commands for this release were full vitest, tsc, and production pnpm build.