LTV and Cohort Read - E-commerce
Use this skill when
The user needs to know what a customer is worth over time, not just what the first order was worth.
Common requests:
- "How much can we afford to pay for a customer?"
- "Which channel brings customers who come back?"
- "What is our repeat rate really?"
- "Which product should be our entry offer?"
Required input
Minimum useful input:
- Order export with customer identifier, order date, order value, and first-order flag or enough history to derive it.
- At least two full purchase cycles of history, or twelve months if the cycle is unknown.
Recommended additional input:
- Acquisition source per customer or per first order.
- First product or collection purchased.
- Discount used on first order.
- COGS or margin basis so value can be stated in contribution, not revenue.
- Ad spend by channel and month for CAC.
Before analysis
- Confirm how customers are identified, and whether guest checkout fragments the same person across email addresses.
- Confirm the observation window. A twelve-month LTV claim needs cohorts that have had twelve months.
- Do not extrapolate an LTV curve from cohorts that are three weeks old. Report what is measured and mark the rest as projection.
- Decide whether value is stated in revenue or contribution margin, and stay consistent.
Analysis workflow
- The cohort table comes from
../scripts/cohort_read.py, not from reading the export. If the input is an order CSV, run ../scripts/cohort_read.py <file> (add --by source or --by first_product to segment). It groups customers by first order, computes cumulative value at each horizon, prints the cohort size next to every number, and refuses to report a horizon a cohort has not lived through, printing a dash instead of a zero. That last guard is the one manual analysis gets wrong most often.
- Build monthly acquisition cohorts and track cumulative value per customer at 30, 60, 90, 180, and 365 days.
- Compute repeat rate at each horizon, and the median time between first and second order.
- Identify the second-order window: the period after which a customer who has not reordered rarely does. This is the window every retention flow should target.
- Segment cohorts by acquisition source, first product, and whether the first order was discounted.
- Compare cohort value against CAC for the same period to produce a payback window per channel.
- Flag cohorts that look large on revenue but weak on repeat, and the reverse.
- State clearly which differences are large enough to act on and which are noise given cohort size.
Decision and evidence standard
Tag every finding with evidence: export, cohort_export, margin_csv, cogs_export, hypothesis, or needs_data. Full vocabulary: ../references/output-standard.md.
Rank findings by how much of the customer base they describe. A pattern in a segment of eleven people is a note, not a finding.
Small cohorts get low confidence. Say the cohort size next to any comparison between segments.
Output format
Cohort verdict
What the store can actually afford to pay for a customer, and how firm that number is.
Cohort table
| Cohort |
Customers |
Day 30 |
Day 90 |
Day 180 |
Day 365 |
Repeat rate |
Second-order window
Median days to second order, and the point after which reorder probability collapses.
Segment comparison
| Segment |
Customers |
Repeat rate |
Value at 180d |
CAC |
Payback |
Read |
What this changes
Two or three decisions this supports: bid targets, entry product, flow timing, discount policy on first orders.
Missing data
Say how many months of history the file actually holds, how many cohorts have reached each horizon, and whether guest checkout is fragmenting the same person across several customer records. All three cap what this analysis can honestly claim.
Example input and output
Input:
- 24 months of orders with customer ids
- acquisition source per first order
- ad spend by channel by month
- COGS per SKU
Good output excerpt:
| Finding |
Evidence |
Severity |
Confidence |
Business impact |
Effort |
Owner decision |
| Half of all second orders land within 41 days, and reorder probability collapses after 90 |
export, cohort_export |
high |
high |
retention |
S |
do_now |
| Customers acquired on a first-order discount repeat at roughly half the rate of full-price first orders, on a small cohort |
cohort_export, hypothesis |
medium |
low |
retention/margin |
M |
investigate |
What not to do yet: raise CAC targets on a 365-day LTV number when only two cohorts have reached 365 days.
Guardrails
- Do not project an LTV curve beyond the data and present it as measured.
- Do not compare segments without stating cohort sizes.
- Do not state LTV in revenue and compare it to CAC as if it were profit.
- Do not ignore guest checkout and identity fragmentation.
- Do not recommend spend increases on payback windows the store's cash position cannot fund.
1---2name: ltv-cohort-read-ecommerce3description: Reads customer cohorts from order exports to show repeat rate, time to second order, payback window, and which acquisition sources or first products produce customers who come back. Use when deciding how much a first order can cost, whether a channel is worth its CAC, or which product should be the entry point.4---56# LTV and Cohort Read - E-commerce78## Use this skill when910The user needs to know what a customer is worth over time, not just what the first order was worth.1112Common requests:1314- "How much can we afford to pay for a customer?"15- "Which channel brings customers who come back?"16- "What is our repeat rate really?"17- "Which product should be our entry offer?"1819## Required input2021Minimum useful input:2223- Order export with customer identifier, order date, order value, and first-order flag or enough history to derive it.24- At least two full purchase cycles of history, or twelve months if the cycle is unknown.2526Recommended additional input:2728- Acquisition source per customer or per first order.29- First product or collection purchased.30- Discount used on first order.31- COGS or margin basis so value can be stated in contribution, not revenue.32- Ad spend by channel and month for CAC.3334## Before analysis35361. Confirm how customers are identified, and whether guest checkout fragments the same person across email addresses.372. Confirm the observation window. A twelve-month LTV claim needs cohorts that have had twelve months.383. Do not extrapolate an LTV curve from cohorts that are three weeks old. Report what is measured and mark the rest as projection.394. Decide whether value is stated in revenue or contribution margin, and stay consistent.4041## Analysis workflow42430. **The cohort table comes from `../scripts/cohort_read.py`, not from reading the export.** If the input is an order CSV, run `../scripts/cohort_read.py <file>` (add `--by source` or `--by first_product` to segment). It groups customers by first order, computes cumulative value at each horizon, prints the cohort size next to every number, and refuses to report a horizon a cohort has not lived through, printing a dash instead of a zero. That last guard is the one manual analysis gets wrong most often.441. Build monthly acquisition cohorts and track cumulative value per customer at 30, 60, 90, 180, and 365 days.452. Compute repeat rate at each horizon, and the median time between first and second order.463. Identify the second-order window: the period after which a customer who has not reordered rarely does. This is the window every retention flow should target.474. Segment cohorts by acquisition source, first product, and whether the first order was discounted.485. Compare cohort value against CAC for the same period to produce a payback window per channel.496. Flag cohorts that look large on revenue but weak on repeat, and the reverse.507. State clearly which differences are large enough to act on and which are noise given cohort size.5152## Decision and evidence standard5354Tag every finding with evidence: `export`, `cohort_export`, `margin_csv`, `cogs_export`, `hypothesis`, or `needs_data`. Full vocabulary: `../references/output-standard.md`.5556Rank findings by how much of the customer base they describe. A pattern in a segment of eleven people is a note, not a finding.5758Small cohorts get low confidence. Say the cohort size next to any comparison between segments.5960## Output format6162### Cohort verdict6364What the store can actually afford to pay for a customer, and how firm that number is.6566### Cohort table6768| Cohort | Customers | Day 30 | Day 90 | Day 180 | Day 365 | Repeat rate |69|---|---|---|---|---|---|---|7071### Second-order window7273Median days to second order, and the point after which reorder probability collapses.7475### Segment comparison7677| Segment | Customers | Repeat rate | Value at 180d | CAC | Payback | Read |78|---|---|---|---|---|---|---|7980### What this changes8182Two or three decisions this supports: bid targets, entry product, flow timing, discount policy on first orders.8384### Missing data8586Say how many months of history the file actually holds, how many cohorts have reached each horizon, and whether guest checkout is fragmenting the same person across several customer records. All three cap what this analysis can honestly claim.8788## Example input and output8990Input:9192- 24 months of orders with customer ids93- acquisition source per first order94- ad spend by channel by month95- COGS per SKU9697Good output excerpt:9899| Finding | Evidence | Severity | Confidence | Business impact | Effort | Owner decision |100|---|---|---|---|---|---|---|101| Half of all second orders land within 41 days, and reorder probability collapses after 90 | `export`, `cohort_export` | high | high | retention | S | do_now |102| Customers acquired on a first-order discount repeat at roughly half the rate of full-price first orders, on a small cohort | `cohort_export`, `hypothesis` | medium | low | retention/margin | M | investigate |103104What not to do yet: raise CAC targets on a 365-day LTV number when only two cohorts have reached 365 days.105106## Guardrails107108- Do not project an LTV curve beyond the data and present it as measured.109- Do not compare segments without stating cohort sizes.110- Do not state LTV in revenue and compare it to CAC as if it were profit.111- Do not ignore guest checkout and identity fragmentation.112- Do not recommend spend increases on payback windows the store's cash position cannot fund.
mardab96