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.

Since #149 the computation lives in lib/server/merchant-metrics-bundle.ts (computeMerchantMetrics): /api/shopify/metrics calls it and then runs the merchant-only follow-ups (web pixel, billing reconcile, notifications); the operator admin calls the same function through /api/admin/store-merchant-metrics (“view as merchant”), so the admin’s ROI can never differ from the merchant’s.

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-28 the app calls merchant_metrics_event_totals_v3(p_store_id, p_from, p_to) (the single-pass v2 rewrite with impressions pulled off the heavy scan) — 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

tryon_widget_error (#410)

A client-side widget failure used to be invisible in the DB — the Kylyan 21.08 incident was diagnosed by guessing. Since #410 the widget posts tryon_widget_error (source storefront, via navigator.sendBeacon with a fetch keepalive fallback) with eventPayload = { stage, message, version, ua }:

version is the widget build (tryvio-ai-NNN), ua the raw user agent (≤300 chars — diagnostic, not a segmentation field; it does not ride ctx). The ingest route stores it as any other event (no allow-list; shopDomain + productHandle are carried so the row is store- and product-scoped). No metric counts it; query it directly, e.g. select occurred_at, event_payload->>'stage', event_payload->>'message' from analytics_events where event_type = 'tryon_widget_error' and store_id = … order by occurred_at desc.

Widget event context (ctx, #304)

Every widget UX event — tryon_widget_impression, tryon_widget_click, tryon_modal_opened — carries an additive eventPayload.ctx object, so CTR and other funnel metrics can be segmented (e.g. mobile vs. desktop, or by button variant) without a new table. Born from the #302 post-mortem: a mobile-only default change halved click-through and the data at the time could not even confirm it was mobile. Fields:

Every field can also be the literal string "unknown".

Senders. tryvio-boot.js’s widgetCtx(root) computes the context once per root, inside kit.run, and it rides three events end-to-end: (1) the tryon_widget_impression payload, (2) modalConfig.buttonContext → the modal’s session POST body as context → the server stamps a normalized copy onto the server-emitted tryon_widget_click (/api/storefront/session), (3) the tryon_modal_opened payload (fired by the modal itself).

Normalization. The pure module apps/web/src/lib/widget/widget-event-context.ts exports computeWidgetEventContext (the tested widget-side spec — mirrored by widgetCtx in tryvio-boot.js; keep the two in parity) and normalizeWidgetEventContext (the server-side ingest clamp). Every field is forced onto its whitelisted enum: absent (an old cached widget that predates #304 sends no ctx at all) or foreign (a forged client) becomes the explicit string "unknown", never a default that would silently skew a segment; btnScale is clamped to 70–150 and snapped to steps of 10 so a forged client cannot explode cardinality. /api/events normalizes ctx for tryon_widget_impression and tryon_modal_opened only; /api/storefront/session normalizes it for the server-emitted tryon_widget_click. Old, cached widgets are never dropped — their rows are stored with every ctx field "unknown" (additive, backward-compatible contract).

Root attributes. Card roots carry data-tryvio-page-type (liquid request.page_type) and data-tryvio-attention, both stamped by tryvio-collection.js’s buildRoot; PDP launcher roots get data-tryvio-attention from the liquid snippet.

Impression de-dup (#354)

One tryon_widget_impression per (product handle × launcher scope) per page load. tryvio-boot.js’s firstImpressionFor(root, productHandle) holds that memory for the lifetime of the page; kit.run posts the impression only on the first call for a key.

The rule exists because a theme that re-renders the product section hands the widget a fresh copy of the launcher, which #354 then arms — without the rule, every variant change would buy another impression for the same button and inflate the denominator of the CTR that #302/#304/#325 are judged on. Measured on the #354 fixture: 4 impressions for 1 shopper-visible button across 3 variant changes before, 1 after.

What the rule deliberately still counts: a different product (SPA route change, a collection card — each card has its own tryvio-card-<handle> scope), an app embed and a hand-placed block on the same page (different scopes), and any genuine second page view (the memory is page-scoped). What it merges: the same product rendered as two cards on one page (both are tryvio-card-<handle>) — one impression, not two.

⚠️ Reading impression history: rows written by widget versions older than the #354 release can contain those duplicates for stores on re-rendering themes, so a CTR trend that crosses that release compares two different denominators.

Segmented CTR query. 05_tasks/proof/304/segmented-ctr.sql — per-device / per-button-variant CTR; measured on prod Icedout over 14 days: cold 5.5s / warm 209ms via analytics_events_store_type_occurred_idx (no new index needed).

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:

Widget coverage (#315, 2026-08-13)

Coverage = of the (product × day) pairs shoppers actually opened, the share that also had a try-on impression that day. Shown on the merchant’s Overview (the default landing) under “Reach”, above Conversion impact.

Do not compute it as impressions ÷ product_views. That ratio is what the issue was written from, and measured on prod it returns 125% for Icedout, 573% for rqgjg0-cr and 1617% for hxdgbj-2i — because the two sides count different surfaces. product_viewed comes from the web pixel and fires once per product page; tryon_widget_impression fires for every collection card too, so one collection page with 20 cards mints 20 impressions against zero product views. Counting over viewed (product × day) pairs makes the numerator a subset of the denominator, so the result is a percentage by construction.

Causes are only ever named when the data proves them. The issue guessed “page types disabled” and “products with try-on off”; on prod every store with a store_widget_config row has product enabled, and disabled-render impressions are 48 of 101,118 at the biggest store. The real explanation is products that are in the catalogue, active, opened by shoppers, and never showed a button (gyj58h-dv: 197 of 322). Order: widget_off → never_rendered (zero impressions anywhere, which is the only way we can observe a Theme-Editor embed toggle we cannot read) → placement_gap → inactive_products.

Collection reach is never folded into the ratio. It is reported on its own line from ctx.page = 'collection' (#304). Because old cached widgets send no ctx at all for weeks, a store with cards on and zero ctx reports “not measured yet”, never “no reach”.

Fail-soft: a coverage failure returns coverage: null and the card degrades alone. Migrations do not deploy with code, so before widget_coverage_stats is applied the RPC does not exist — and #316 is the story of exactly that throwing inside this same Promise.all and 500ing every merchant screen. Regression test: metrics/route.test.ts — “a broken coverage read degrades the CARD”.

~1.9% of product_viewed rows carry no resolved product_id and are excluded from the denominator.

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_v3(p_store_id, p_from, p_to) (apps/web/supabase/migrations/20260728b_metrics_v3_exclude_impressions.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 retirement (#137). Since 2026-07-28 the app calls v3 only; v1 has no caller left in the repo. 20260730c_metrics_v1_retire.sql revokes the remaining grants on merchant_metrics_event_totals and drops it — v1 was the last metrics RPC still executable by PUBLIC/anon. The migration is staged, not applied: the bar is ≥1 week of stability on the successor (v3 live 2026-07-28 ⇒ earliest 2026-08-04), applied under the db-migrate lock with the owner’s go. The v1 migration file stays for history. v2 stays in the database on purpose — it is v3’s rollback path (point getMerchantEventTotals back at v2); drop it in a later migration once v3 has had its own stability window.

Ingest clamp (#137). /api/events is public and unauthenticated, so a client-supplied occurredAt is clamped into [now−48h, now+5min] at the ingest boundary (lib/analytics/occurred-at.ts) before anything is written — a garbage or spoofed timestamp can no longer land outside (or drag) a merchant range window. The clamp runs once per request and the clamped value feeds the stored event, the derived events, tried_products and the 30-day attribution window. Events with no client timestamp are untouched (server clock), and server-emitted events bypass the clamp entirely because they call recordAnalyticsEvent directly.

Health alert (#137). computeHealthReport carries a merchant_metrics component: over the last 30 minutes of app_logs for route shopify/metrics it counts request.error rows and computes the p95 of request.success durationMs. ≥1 error, or p95 > 8 s (the statement-timeout budget) over ≥5 served requests, marks the component degraded — which degrades the whole report, so /api/health/deep returns 503 and the existing 10-minute watchdog emails the owner. No new alert channel; thresholds are constants in lib/server/health.ts.

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.

Activation ladder (operator, #306)

A store is ACTIVATED when it has ≥1 tryon_sessions row with status='completed' and source_channel='shopify_storefront'. Latched and permanent — a store that later goes quiet stays activated (quiet stores are the widget-health watchdog’s question, not this one).

Surfaced on /admin → Activation (/api/admin/activation) and in the Monday ops-digest. Both read one bounded RPC, activation_ladder_stats(), and all judgement lives in lib/activation/activation-ladder.ts — one definition, two surfaces.

Why session-truth and not the success event. tryon_generation_succeeded is a shopper-side beacon: it does not survive an uninstall and it over-counts live stores (one session can deliver several looks). Measured on prod 2026-08-12, the event definition reported 8 activated stores and session-truth reported 9 — the missing one was our only churned paying merchant, which kept 6 analytics events but 1 650 sessions and 1 367 billed try-ons. Full rationale and the other rejected candidates: 05_tasks/specs/activation-measurement.md.

Unit warning. The ladder counts completed sessions; the merchant-facing Try-ons card counts looks (the billing unit, see the table above). One session can deliver several looks, so the same store reads lower here. This is deliberate: the ladder is an instrument about stores.

Rungs are counted per predicate with those implications — a delivered try-on proves the store had products and a rendered widget even after an uninstall wipes its catalogue. Paying is not gated on the 10th try-on (a merchant may subscribe after one), so a rung can exceed the one above it; the UI draws that inversion in the warning colour instead of hiding it.

Stores that never activated carry one stall reason, earliest failure first: never_synced, no_traffic, widget_dark (shoppers on the PDP, zero impressions), rendered_not_clicked, upload_abandoned, generation_failing. The last two split the same step deliberately: on prod every stalled store has zero failed sessions and only created ones, so “clicked but no image” is abandonment, not a defect — a real failure outranks the abandonment reading whenever it exists.

⚠️ activation_ladder_stats() is a migration, and migrations do not deploy. Apply 20260812_activation_ladder_stats.sql, then notify pgrst, 'reload schema';, then run schema-drift.mjs, before this code reaches an environment. Until then the tab 500s and the digest falls back to one honest line.

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.

Plan fee currency (fixed 2026-07-31, #66)

proratedPlanFee is resolved by lib/billing/plan-fee.ts → live Shopify recurring price (source of truth, from getShopifySubscriptionPricing, fetched inside the metrics route’s Promise.all) → the fixed per-currency table (getPlanPricing, the numbers the subscription was created with) → the USD list price. Before this, the fee was always the USD list price while the card was labelled with the subscription’s currency, so a EUR-billed Starter read €39.99 against a €36.99 invoice and that inflated cost fed ROAS/ROI.

Known and deliberately unchanged: the settlement path still charges getOverageRate(store) (the USD number) into a possibly-EUR subscription. The cost card matches what is actually charged; correcting the charged rate is a live-money decision with its own issue.

Currency of the ratios (#66 AC3)

Revenue is in the store’s sell currency; cost is in the subscription currency. computeRoas / computeRoi take a RatioCurrencyContext:

• same currency (the normal case once the fee is localized) → plain division;
• different currency with fxRate → revenue converted into the cost currency, fxRate echoed on the metric;
• different currency without a rate → the value is still returned but carries currencyMismatch, which the dashboard renders as Approx. — revenue in X, cost in Y, not FX-converted.

There is no FX rate source wired yet (owner decision pending); passing currencies.fxRate in api/shopify/metrics/route.ts is the only wiring needed once one exists.

Try-ons headline vs the Top-products table (fixed 2026-07-31, #39)

The Try-ons card and the per-product table are different scopes and used to disagree with no explanation (Icedout, 30d: card 8,647 vs a table summing 4,396). Three fixes:

1. getMerchantMetrics returns an additive tryOnLooksBreakdown — looksTotal, looksInTopProducts, looksInOtherProducts, looksNotMatchedToProduct, productsWithLooks, topProductCount — built from the SAME ranking that produces topProducts (TOP_PRODUCTS_LIMIT = 10). Invariant: the three look buckets sum to looksTotal (the unmatched bucket floors at 0 for bundles, where one provider job counts once per product it dressed).
2. The card prints which source it used (billing ledger vs analytics), because billableTryOns is null for ranges predating ledger coverage and the source used to flip silently as the date range moved.
3. The per-product Try-ons column is looks only — the old tryOnLooks ?? tryOnSessions fallback could render a session count inside a looks column.

Copy lives in lib/client/merchant-metrics-labels.ts; the pure buckets in lib/analytics/looks-breakdown.ts.

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.