LTV Command Center Logic

Purpose

The LTV Command Center contains two related but distinct views:

Live LTV is a forward-looking unit-economics model. It combines recent operating benchmarks with finance assumptions to estimate gross-profit LTV per hardware customer.
Cohort LTV is a backward-looking observed-actuals model. It measures cumulative realized hardware and subscription gross profit for customers grouped by first hardware-order month.

Do not compare the two as if they were identical measures. Live LTV estimates a full expected lifetime using churn; Cohort LTV reports only revenue observed through each cohort age.

Shared conventions

Monetary outputs represent gross profit, not gross revenue, unless a field is explicitly labeled revenue.
Stripe timestamps are converted from UTC to America/Los_Angeles before date bucketing.
Shopify timestamps are interpreted in America/Los_Angeles.
Stripe data is restricted to LIVEMODE = TRUE and excludes STATUS = 'incomplete_expired' for lifecycle calculations.
A qualifying paid invoice has a subscription, is paid (STATUS = 'paid' or PAID = TRUE), and has AMOUNT_PAID > 0.
Hardware products are Shopify line items titled exactly Tin Can or beginning with Tin Can Flashback.
Shopify test, deleted, voided, pending, free, and non-hardware lines are excluded where applicable.
ANALYTICS_DB.ANALYTICS.DIM_DEVICE is the canonical device source. RAW_DB.TINCAN.LEGACY_DEVICES is used only as a bridge from a canonical device to its legacy Stripe subscription ID.

Live LTV model

Output definition

Total LTV per Customer is expected gross profit from hardware, shipping, and subscription revenue associated with an average first hardware order:

$$\text{Total LTV}=\text{Hardware LTV}+\text{Shipping LTV}+\text{Subscription LTV}$$

The customer unit is a first-order hardware customer. Units per order scales per-unit economics to that customer/order grain.

Input hierarchy

The model uses three input classes:

Type 1 — live/override: warehouse-derived benchmarks. A blank override uses the live value; a supplied override replaces it.
Type 2 — finance input: manually maintained finance assumptions.
Type 3 — modeled estimate: a manual operating assumption used because no reliable live benchmark is presented.

The app reads the warehouse baseline and notebook finance inputs once, then performs scenario recalculation locally in the browser. Scenario edits do not write back to notebook inputs or warehouse data. Reset to live baseline restores the current finance-input values and clears all Type 1 overrides.

Finance and modeled assumptions

Current notebook defaults:

Hardware cost, landed duties paid: $43 per unit
Shipping ASP: $7 per unit
Shipping cost: $7 per unit
Subscription gross margin: 80%
Activation rate of fulfilled units: 94%

Activation is intentionally modeled. The live query calculates a trailing activation ratio for diagnostics, but the LTV formula uses the manual activation assumption.

Live benchmark definitions

Hardware ASP

Trailing 12 weeks through yesterday:

$$\text{Hardware ASP}=\frac{\sum(\text{hardware line gross}-\text{allocated order discount})}{\sum \text{hardware units}}$$

Order discounts are allocated to each hardware line in proportion to its share of total line-item value.

Units per order

Trailing 12 weeks through yesterday:

$$\text{Units per order}=\frac{\text{hardware units}}{\text{distinct hardware orders}}$$

Sold-to-fulfilled rate

A unit-weighted rate using mature orders placed 120–210 days before the calculation date:

$$\text{Sold-to-fulfilled}=\frac{\text{units on orders currently marked fulfilled}}{\text{all hardware units sold}}$$

This maturity window reduces the bias from recently sold orders that have not yet had time to fulfill.

Subscription prices

Average positive Stripe plan amount among subscriptions created in the trailing 12 weeks, separately for monthly and annual billing intervals.

Canonical device-to-subscription bridge

For each row in DIM_DEVICE, the model resolves a Stripe subscription ID in this order:

RAW_DB.TINCAN.SUBSCRIPTION.STRIPE_SUBSCRIPTION_ID via DIM_DEVICE.SUBSCRIPTION_ID
RAW_DB.TINCAN.LEGACY_DEVICES.STRIPE_SUBSCRIPTION_ID via DIM_DEVICE.LEGACY_DEVICE_ID

The device activation date is DIM_DEVICE.CREATED_AT converted to Pacific time.

Trial attach rate

For canonical device activations in the trailing 12 weeks:

$$\text{Trial attach}=\frac{\text{activations linked to a positive-price monthly/annual subscription with a trial end}}{\text{all canonical device activations}}$$

Trial-to-paid conversion

Among positive-price subscriptions linked to activated devices whose trial ended in the trailing 12 weeks:

$$\text{Trial-to-paid}=\frac{\text{subscriptions with at least one qualifying paid invoice}}{\text{eligible ended trials}}$$

The paid event is the first qualifying Stripe invoice; it does not need to fall inside the 12-week trial-end window.

Paid subscriber mix

The annual/monthly mix is calculated from activated-device-linked, positive-price subscriptions that:

have monthly or annual billing cadence;
are active or past_due;
are past trial as of the calculation date; and
have not ended as of the calculation date.

Monthly mix is derived as:

$$\text{Monthly mix}=1-\text{Annual mix}$$

Only annual mix is independently overrideable.

Monthly subscriber churn

Monthly churn uses the full positive-price monthly Stripe subscription population, not only device-linked subscriptions. For each of the six most recently completed calendar months:

Paying at start: created before month start, past trial before month start, and not ended before month start.
Churned paying: ended during the month and had reached the end of trial before ending.

The displayed rate is exposure-weighted across all six months:

$$\text{Monthly churn}=\frac{\sum \text{churned paying}}{\sum \text{paying at start}}$$

Live LTV formulas

Unit economics

$$\text{Hardware GP per unit}=\text{Hardware ASP}-\text{Hardware cost}$$

$$\text{Shipping GP per unit}=\text{Shipping ASP}-\text{Shipping cost}$$

Sold-to-paid conversion

$$\text{Sold-to-paid rate}=\text{Sold-to-fulfilled}\times\text{Activation}\times\text{Trial attach}\times\text{Trial-to-paid}$$

Expected lifetime

The model assumes a constant monthly churn hazard and uses the reciprocal approximation:

$$\text{Expected lifetime months}=\frac{1}{\text{monthly churn}}$$

Annual-plan churn is an optional monthly-rate override. If blank, it falls back to the resolved monthly-plan churn. Therefore, annual and monthly expected lifetimes are identical unless annual churn is overridden.

A zero churn value produces a null lifetime and null downstream subscription LTV rather than an infinite value.

Subscription LTV per paid subscriber

$$\text{Annual-plan LTV}=\text{Annual price}\times\text{Subscription margin}\times\frac{\text{Annual lifetime months}}{12}$$

$$\text{Monthly-plan LTV}=\text{Monthly price}\times\text{Subscription margin}\times\text{Monthly lifetime months}$$

$$\text{Blended subscription LTV}=\text{Annual mix}\times\text{Annual-plan LTV}+\text{Monthly mix}\times\text{Monthly-plan LTV}$$

LTV per hardware customer

$$\text{Hardware LTV}=\text{Hardware GP per unit}\times\text{Units per order}$$

$$\text{Shipping LTV}=\text{Shipping GP per unit}\times\text{Units per order}$$

$$\text{Subscription LTV}=\text{Sold-to-paid rate}\times\text{Blended subscription LTV}\times\text{Units per order}$$

The final multiplication by units per order assumes each hardware unit independently contributes the modeled sold-to-paid opportunity.

Input history and sensitivity

The Input Trends tab reconstructs Type 1 metrics monthly from January 2022 onward using the same point-in-time lookbacks:

trailing 12 weeks for hardware ASP, units/order, prices, trial attach, and trial conversion;
120–210-day mature windows for fulfillment;
active paid mix as of each period end; and
six completed months of churn preceding each period.

The Sensitivity Table changes two selected assumptions across a grid and recomputes Total LTV with the same client-side formulas. Its default ranges are centered on the current effective scenario, so overrides change the sensitivity baseline.

Cohort LTV model

Output definition

Cohort LTV is cumulative observed gross-profit LTV per original first-order hardware customer, indexed by months since first order.

Customers are assigned to the calendar month of their earliest qualifying Shopify hardware order. Cohorts begin in January 2024. The denominator remains the original number of customers in the cohort at every age; it is not an active-customer denominator.

For cohort $$c$$ at age $$t$$:

$$\text{Observed LTV}{c,t}=\frac{\text{Hardware GP}{c,t}+\text{Shipping GP}{c,t}+\text{Subscription GP}{c,t}}{\text{Original cohort customers}_c}$$

where:

$$\text{Hardware GP}=\text{Cumulative hardware net revenue}-(\text{Hardware cost}\times\text{Cumulative units})$$

$$\text{Shipping GP}=(\text{Shipping ASP}-\text{Shipping cost})\times\text{Cumulative units}$$

$$\text{Subscription GP}=\text{Subscription gross margin}\times\text{Cumulative subscription revenue}$$

The cohort chart responds only to the current hardware cost, shipping ASP, shipping cost, and subscription gross-margin assumptions. Conversion, mix, price, activation, and churn inputs do not affect observed Cohort LTV because the cohort model uses realized revenue rather than forecasting it.

Hardware customer identity and cohorts

A Shopify customer key is resolved as:

shopify:<customer id> when Shopify customer ID exists;
otherwise email:<normalized email>.

A customer's first qualifying hardware order determines first_order_date and cohort_month. All qualifying hardware purchases on or after that first order contribute to cumulative cohort hardware units and net revenue.

Hardware net revenue is line gross less the line's proportional allocation of the order-level discount.

Paid subscription revenue

Paid subscription revenue is taken from qualifying Stripe invoices and excludes tax:

$$\text{Subscription revenue}=\frac{\text{Amount paid}-\text{Tax}}{100}$$

The model supports two subscription bases:

Attributed only: revenue directly linked to a Shopify hardware customer and cohort.
Attributed + allocated: attributed revenue plus a weighted allocation of the unlinked remainder.

Hardware revenue, gross-profit transformations, and the original cohort denominator are identical between the two bases.

Subscription attribution hierarchy

Each paid invoice is assigned at most one match method, in priority order:

Customer ID bridge: Stripe customer → Tin Can ACCOUNT → Shopify customer ID.
Email: unique normalized Shopify email matched to Tin Can user/account email or Stripe invoice email.
Transactional address: unique normalized Shopify address matched to Stripe invoice street + first five ZIP characters.
911/emergency address: subscription emergency-service address matched to a unique normalized Shopify address.
Unlinked: no deterministic match.

Email and address maps are accepted only when the normalized value maps to exactly one Shopify customer key. This avoids many-to-many identity collisions.

A subscription inherits the customer key from its earliest linked invoice. Linked subscription revenue is then assigned to that customer's first-order cohort. Negative cohort ages are floored at zero, so revenue dated before the first-order cohort month is reported at age zero.

Activation month for allocation

Every Stripe subscription receives an activation month using this priority:

earliest DIM_DEVICE.CREATED_AT for the Tin Can customer;
Stripe trial start;
Stripe subscription creation.

The paying-customer key is Tin Can CUSTOMER_ID where available, otherwise Stripe customer ID.

Allocation of unlinked revenue

Unlinked paid invoice revenue is allocated across first-order cohorts using the observed relationship between subscription activation month and hardware cohort among linked subscriptions.

For each activation month:

Use its direct linked-subscription cohort distribution when there are at least 300 linked subscriptions.
Otherwise pool linked subscriptions whose activation months fall within ±1 month, if the pool has at least 300 subscriptions.
Otherwise assign 100% to the same calendar month as activation.

Cohort weights below 1% are removed. Remaining weights are normalized to sum to 100% within each activation month:

$$w_{a,c}=\frac{\text{retained raw weight}{a,c}}{\sum_c\text{retained raw weight}{a,c}}$$

Unlinked invoice revenue is multiplied by these weights and assigned to cohort/age cells. The same weights allocate unlinked paying customers as fractional paying-customer equivalents.

Active paying customers

At each cohort age, an attributed customer is active paying when the linked positive-price subscription:

was created before the end of the cohort-age month;
had ended trial by that date, if it had a trial; and
had not ended before that date.

The comparison date is capped at the current date for the current month.

$$\text{Active paying share}=\frac{\text{Active paying customers}}{\text{Original cohort customers}}$$

This is contextual retention information and is not the denominator used for LTV.

Allocation and identity validation

Revenue conservation

The allocation is required to conserve paid subscription revenue:

$$\text{Attributed revenue}+\text{Allocated revenue}=\text{Total qualifying paid subscription revenue}$$

CONSERVATION_DIFFERENCE should be zero apart from negligible floating-point rounding.

Paying-customer ceiling check

For each cohort:

$$\text{Implied paying customers per first-order customer}=\frac{\text{Linked paying customers}+\text{Allocated paying-customer equivalents}}{\text{Cohort customers}}$$

A cohort is eligible for validation only when it has at least 1,000 customers and is at least 120 days old.

0.55–0.90: healthy
above 0.90: flag as likely over-allocation
below 0.55: flag as likely under-allocation or a broken weight matrix
smaller or less mature cohorts: exempt

These thresholds are quality-control bounds, not business targets or adjustments to reported LTV.

Important limitations

Live LTV uses a reciprocal-churn lifetime approximation and assumes constant churn.
Annual churn defaults to monthly churn unless explicitly overridden; this is a modeling assumption, not an observed annual-plan churn estimate.
The activation rate is manually modeled even though a diagnostic calculated rate exists.
Live hardware and conversion inputs use different windows by design; the model is not a single acquisition cohort.
Cohort LTV is right-censored: newer cohorts have fewer observable months and should only be compared at the same age.
Cohort subscription allocation is modeled for unlinked revenue. Use the attributed-only basis when deterministic attribution is required.
Address matching is normalized street + first five ZIP characters and accepted only for unique Shopify mappings; it can still reflect shared households or stale addresses.
Tax is excluded from subscription revenue, while refunds, disputes, credits, and later invoice adjustments are not separately modeled beyond the paid invoice fields used by the query.
Cohort hardware cost, shipping economics, and subscription margin use the app's current assumptions for every historical period; they are not historical cost vintages.
The current cohort query generates a maximum of 37 age rows (0–36 months), subject to the current date.

Primary sources

RAW_DB.SHOPIFY.ORDERS
RAW_DB.SHOPIFY.FULFILLMENTS
RAW_DB.STRIPE.SUBSCRIPTIONS
RAW_DB.STRIPE.INVOICES
ANALYTICS_DB.ANALYTICS.DIM_DEVICE
RAW_DB.TINCAN.SUBSCRIPTION
RAW_DB.TINCAN.LEGACY_DEVICES
RAW_DB.TINCAN.ACCOUNT
RAW_DB.TINCAN.TINCAN_USER
RAW_DB.TINCAN.ADDRESS

Maintenance checklist

When changing the app or source SQL:

Keep app/model.js mathematically aligned with the notebook LTV model SQL.
Preserve the live/override/manual/modeled value-source labels.
Recheck Stripe lifecycle filters and Pacific-time conversion.
Confirm hardware product-title filters still capture the intended catalog.
Confirm allocation weights sum to one for every activation month.
Confirm attributed plus allocated subscription revenue equals all qualifying paid subscription revenue.
Review every eligible cohort for paying-customer validation flags.
Document any change to lookback windows, cohort start date, allocation thresholds, or identity-match priority.