Net LTV from Order Data Skill

---
name: net-ltv-from-order-data
description: Calculate a clean, net customer lifetime value from raw order data (Shopify, WooCommerce, Stripe, or similar export). Use whenever a founder or marketer asks for LTV, cohort value, payback period, or "how much is a customer worth" and hands over an order export. The trap is that raw order totals include taxes, shipping, tips, discounts, cancelled orders, refunded orders, and failed payments — and an LLM asked for LTV will happily add all of it up and hand back a number that looks great and is wrong by 30 to 50 percent. Trigger this skill even when the user has not asked to "clean" the data; if the request is about LTV, CAC payback, or scaling spend against customer value, route through here first. A gross LTV that the user mistakes for net will push them to overspend on acquisition. Weight is not purity.
---

# Net LTV from order data

## The problem this skill solves

A founder exports two years of Shopify orders, feeds it to an LLM, and asks for 90-day LTV. The LLM sums the `total` column, divides by unique customers, and reports a number that looks like treasure. The founder starts planning to double CAC against it.

The number is junk. The `total` column includes GST the founder owes the government, a flat ₹150 shipping fee per order, orders that were cancelled five minutes after checkout, orders that were refunded in full, and a handful of failed payments that never settled. None of that money is actually retained by the business. Scale spend against it and you are lighting cash on fire.

LLMs happily confuse gross for net because the column is called `total` and totals are what you add up. Your job is to strip the data down to real, retained revenue before you calculate anything.

## When to use this skill

Use it whenever all of these are true:

- The user has given you an order export, or can give you one, from Shopify, WooCommerce, Stripe, Razorpay, a custom backend, or a spreadsheet.
- The user is asking an LTV, cohort-value, payback, or scaling question. Examples: "what is our 90-day LTV", "can we afford CAC of X", "how much is a repeat customer worth", "should we spend more on acquisition".
- The answer will be used to make a spend decision.

If the user only has a summary figure and no underlying data, say so and ask for the export. Do not calculate LTV from a single aggregated number the user already computed — you cannot audit what you cannot see.

Do not use this skill for engagement metrics, cohort retention curves that do not involve money, or product-level revenue analysis. Those are different jobs.

## The core method

Think of the order export as raw ore. You cannot weigh it and call it gold. You have to refine it first. Six steps.

### Step 1. List every column and ask what each one contains

Before you sum anything, look at the column headers. A Shopify export has `total`, `subtotal`, `taxes`, `shipping`, `discount`, `refunded_amount`, `financial_status`, `cancelled_at`, and more. Write out what each one means. If you are not sure, ask the user. The user knows their own data better than any default schema guess.

### Step 2. Drop orders that are not real revenue

Remove, before you touch arithmetic:

- Orders where `financial_status` is `pending`, `voided`, `expired`, or `failed`.
- Orders with a non-null `cancelled_at`.
- Orders where the payment gateway flagged a failure.
- Test orders (the user will often have a handful — search for their own email, or orders with ₹0 or ₹1 totals).

Count how many you dropped and at what value. Tell the user. A healthy business loses a small percentage here; a broken one loses 20%+ and the founder usually does not know.

### Step 3. Strip non-retained money from the orders that remain

From each surviving order, subtract:

- Taxes (GST, VAT, sales tax — whatever the user's jurisdiction calls it). This money is collected on behalf of the government. It is not yours.
- Shipping fees charged to the customer, unless the user explicitly wants shipping-inclusive LTV. Most of the time shipping revenue is a pass-through to the carrier.
- Tips, if the platform separates them and they go to staff.
- Gift card redemptions that were paid out in a prior period (tricky — flag this and ask).

What is left is gross merchandise value retained by the business. Not net profit, but a cleaner top-line than `total`.

### Step 4. Apply refunds and returns against the right cohort

A customer who bought in March and returned the item in May should have the refund subtracted from their March value, not May's. If the export has a `refunded_amount` field per order, use it directly. If refunds are in a separate table, join them by order ID and date.

Do not accept a model-estimated "average return rate". If the data does not have refund information, say so plainly — "I cannot calculate net LTV without refund data, here is gross LTV with a note" — rather than hallucinating a number.

### Step 5. Now calculate LTV

Only now do you group by customer and compute. For a 90-day LTV:

- Identify each customer's first order date (the cohort anchor).
- Sum their net retained revenue from day 0 to day 90 after that first order.
- Average across customers in the cohort.

Report cohorts separately by acquisition month. A single blended LTV across two years hides the fact that last quarter's customers are worth half what 2023's were, which is exactly the thing the founder needs to know before doubling CAC.

### Step 6. Show the stack

End with a line-by-line reconciliation. Something like:

- Raw total across all orders: ₹1.2 cr
- Less cancelled and failed orders: –₹14 L
- Less taxes: –₹18 L
- Less shipping pass-through: –₹9 L
- Less refunds: –₹11 L
- Net retained revenue: ₹68 L
- Unique first-time customers in period: 4,200
- 90-day net LTV: ₹1,619

The founder can argue with any line. That is the point. A single LTV number with no stack is unfalsifiable and therefore useless.

## What a good answer looks like

**User prompt:** "Here is our Shopify export for the last two years. What is our 90-day LTV?"

**Bad answer:** Sum `total`, divide by unique customers, report ₹2,400. Done in one line.

**Good answer:**