Focusing on DTC/E-commerce business generating up to £10m in revenue I have developed a process using the Google Ecoysystem to better understand LTV which is supported by a robust set of metrics. It takes DTC/E-commerce brands from raw data to Strategic Maturity.
- Moving away from metrics such as ROAS + Traffic to LTV and Bridge (2nd order) efficiency.
- Identifying when the exact no of days when a customer becomes profitable
- Understanding the conversion rate from 1st purchase to 2nd purchase
- Identifying one time customers v loyal customers
- Using historic cohort data to predict next years revenue
The repository is organized into four distinct modules:
| Module | Scope | Description |
|---|---|---|
| 1.0 | Validation | Data integrity guardrails, ensuring financial and data accuracy. |
| 2.0 | Segmentation | Analyzing the customer base by purchase frequency and profit buckets. |
| 3.0 | Migration | Tracking matured LTV and side-by-side performance of yearly cohorts. |
| 4.0 | Strategic Bridge | Deep dives into CAC payback, bridge financials, and unit economic milestones. |
The most critical step is collecting the right data and getting the right data structure.
This Google Doc provides an example of the data required, how it should be structured.
https://docs.google.com/spreadsheets/d/1BIXYFb17sxFHbq_a42uQxtVTX84_Hj-Ee0zOm_t2ArA/edit?usp=sharing
The data requirements are:
- customer_id
- order_date
- revenue
- profit
Key watchouts:
- The order_date is always YYYY-MM-DD i.e., 2026-06-13
- Revenue and Profit values are not mixed up i.e., profit is not greater than revenue
- Ensure both are numeric not text
- Consistency of customer_id its always lowercase or uppercase and not mixed
- Ensure there’s no trailing space
- No duplicate records
- Customer Maturity: What is the LTV value of a customer acquired today after 30, 60, and 90 days? (Module 3.2)
- Payback Velocity: How many days does it take for a customer’s cumulative profit to exceed their acquisition cost (CAC)? (Module 3.3)
- Predictive Forecasting: Based on historic cohort decay and retention yields, what is the revenue forecast for the next quarter? (Module 4.4)
- The "Bridge" Efficiency: What percentage of customers move from first to second purchase, and how does it impact profit margin? (Module 4.1.3)
- Profit Concentration: What percentage of total annual profit is generated by the top 10% of customers? (Module 2.2)
- Retention Economics: Is it more cost-effective to acquire a new customer or reactivate a churned one? (Module 4.2)
- Marketing Timing: What is the average days between orders where a customer is most likely to repurchase? (Module 3.4)
- 1.1 Nulls and Blanks: Detects orphaned orders missing customer identifiers.
- 1.2 Duplicate Transaction: Identifies "Duplicates" where a transaction occurs twice.
- 1.3 Date Range Validation: Ensures the dataset fits within expected logical time bounds.
- 1.4 Validating Dates Day0: Flags "Future Orders" and identifies date format flips (DD/MM vs MM/DD).
- 1.5 Financial Validation: Uses
SAFE_DIVIDEto prevent system crashes on zero-revenue orders.
- 2.1 Cohort Size: Tracks the volume of customers acquired in specific time periods.
- 2.2 Annual Active Customer: Measures the health of the brand by identifying active vs. lapsed users.
- 2.3 Frequency Bucket Analysis: Groups customers by "number of times purchased" (1x, 2x, 3x+).
- 2.4 Monthly Bucket Analysis: Tracks how purchase frequency shifts month-over-month.
- 2.5 Profit Bucket Analysis: Segments the database by actual margin contributed per customer.
- 3.1 LTV Bucket Summary: High-level overview of LTV distribution across the base.
- 3.2 Mater Matured LTV: Calculates the value of a cohort after it has "aged" (e.g., 12 months).
- 3.3 Yearly Side-by-Side Performance: Compares the quality/LTV i.e., 2023 cohorts vs 2024 cohorts.
- 3.4 Days Between Orders: Calculates "Retention Velocity" which could help inform email/SMS marketing campaign timings.
- 4.1.0 Migration Retention Profitability: Tracks the total profit gain as customers migrate from New to Returning.
- 4.1.1 Late Money Calendar Yield: Measures revenue generated by old cohorts in the current calendar month.
- 4.1.2 Time Lag First-Last Purchase: Analyzes the total "Customer Lifespan" in days from first to most recent order.
- 4.1.3 First-to-Second Bridge Financials: The "Holy Grail" view of profit achieved specifically at purchase #2.
- 4.1.3b Monthly Bridge Financials: A month-by-month breakdown of bridge crossing efficiency.
- 4.1.4 Daily Weekly Monthly Performance: Granular performance tracking for short-term trend identification.
- 4.1.5 Granular First-Second Order Audit: Identifies the specific products that best convert 1x buyers into 2x buyers.
- 4.2 Revenue Profit Contribution: Analyzes the "Efficiency Ratio" of every pound spent vs. profit returned.
- 4.3 CAC Payback Profit Milestone: Calculates the exact day a customer covers their acquisition cost.
- 4.4 Monthly Yearly Performance: The "Executive View" for long-term strategic forecasting.
To get started I have shared two testing datasets.
Red Dataset - dataset with many errors
https://docs.google.com/spreadsheets/d/1og04NJxjfeBYWcuTw5WFuoWs5siGREiIdg6Oqa5wMgw/edit?usp=sharing
Run module 1.0 - Data Integrity & Validation it should throw out multiple errors meaning till the data gets fixed it cannot proceed any further
Green Dataset - the pefect dataset, with clean, structured data
https://docs.google.com/spreadsheets/d/1aY6ut88A6mO_ZCtAPMZAAxOZxihWw9qk2FjWtaS6SUM/edit?usp=sharing
Run all modules. 4.1.3 should be an interesting one to understand
To better understand product LTV the data requirements are:
- customer_id
- order_date
- product_id (sku_id)
- product_name
- revenue
- profit
To better understand geo LTV the data requirements are:
- customer_id
- order_date
- city_id/postcode
- city_name
- revenue
- profit
The master view would comibne customer + product + geo
- customer_id
- order_date
- product_id(sku_id)
- product_name
- city_id/postcode
- city_name
- revenue
- profit
A calculator designed to translate raw customer data into a high-level growth insights. It isolates the "First Order" cost of acquisition from the "Bridge" (2nd order) to lifetime profitability.
https://docs.google.com/spreadsheets/d/1q2Ah-IfcfJ73Ik6Fc-FeK-5Kp9aRrTUNTmpf2V__aiw/edit?usp=sharing
The calculator is dynamic with the goal to better undersrand Profit Per User. It automatically scales based on AOV and Bridge targets, showing exactly how much additional CAC can be afforded as back-end efficiency improves.
These metrics represent the Net Profitability of your acquisition engine.
| Metric | Unit | Definition |
|---|---|---|
| Day 0 Net Cash Flow | £ Total | The total profit or loss for the cohort after subtracting Marketing Costs (CAC) from the initial Gross Profit. |
| Month 12 Cohort Profit | £ Total | The estimated total profit from this group after 1 year, including repeat "Bridge" revenue. |
| Max Allowable CAC | £ / User | The break-even ceiling; the maximum you can spend to acquire a user while breaking even over 12 months. |
| Current Profit Gap | £ / User | The "Marketing Treadmill"—the net profit or loss realized on the very first transaction per user. |
This section quantifies the financial impact of specific strategic interventions per user.
| Metric | Unit | Definition |
|---|---|---|
| Baseline LTV | £ / User | The current total projected profit value of a single customer over 12 months. |
| Improve Bridge (+10%) | £ / User | The incremental profit gained per retained customer by improving the retention bridge efficiency by 10%. |
| Increase Order 2 AOV | £ / User | The profit added to every user in the cohort by increasing repeat spend by £10. |
| Profit Per User | £ / User | Profit Lift (Orders 1-2). The additional profit realized per user by optimizing the path from the first to the second purchase. |
| Profit Swing | % / User | Turnaround Magnitude. Measures the total value created by the strategy (Moat + Gap recovery) relative to the initial loss. |
When improving both Retention and AOV, it gains a "compounding bonus", newly retained customers are also spending at the higher AOV.
The Formula:
Total Lift = (New AOV × Margin × New Bridge Rate) — (Old AOV × Margin × Old Bridge Rate)
- Input Data: Input into the blue cells: Monthly Users, CAC, AOV, and Margins (Section 1 and 3).
- Review the Gap: Check the Current Profit Gap. If it is negative, you are paying a "fee" to acquire customers and relying entirely on the "2nd purchase" to reach profitability.
- Simulate Growth: Profit Per User and Max Allowable CAC provide the Strategic Insights to outscale competitors.