Building A Strategic Maturity Model

Business struggle to understand LTV, it’s often looked in isolation in turn it becomes a vanity metric; it requires a robust set of metrics to enrich LTV.

The areas business stumble over to understand LTV:

1. Having a clean dataset
2. Mixing old and new customers
3. Mislabelling legacy loyalists as new acquisitions

The challenge with LTV is turning a goldmine of data into an actionable strategy.

I developed a process using Google Ecosystem to help solve for the LTV challenges for D2C/E-commerce business generating up to £10m in revenue. The LTV model prioritises total yield per original acquisition, providing a clean, audit-ready view of how historical spend drives cashflow.

The LTV model is built for D2C/E-com (non subscription) generating up-to £10m in annual revenue, it does not transfer over to other business models:

• The LTV model can predict when a customer will lapse then waiting for a cancellation in subscription models
• With D2C/E-com variance in pricing, the model tracks cumulative profit with also capturing the upsell

To ensure a high level of data and technical accuracy I have created a structured workflow that is based around 5-Phases split into Data Foundation and Business Intelligence. The process developed goes from collecting raw transactional data to modelling customer cohorts based on the birth year (also known as the first purchase date) to provide business level insights.

Data Foundation:

• Phase 0: Ingestion – Data Architecture and Mapping
• Phase 1: Integrity – Cleaning and Validation
• Phase 2: Discovery – Establishing the baseline cohorts and segments

Business Intelligence:

• Phase 3: LTV Modelling – The engine of profit and payback
• Phase 4: Strategic Outlook – Forecasting the following year and impact

The steps involved in data foundation determine the quality of data and insights when it comes to business intelligence.

The Business Maturity Roadmap

Business maturity comes from being able to diagnose where the business is leaking cash to building long term profitability.. It’s moving from playing the short-term acquisition game to playing the sustainable long-term game.

There are 5 steps in the roadmap to business maturity:

Step 1: The Foundation (Cohort Trajectory & Retention)

• Goal: Prove the business is building an accumulating, loyal customer base rather than churning through one-off buyers.
• Cohort Retention Matrix – GitHub 3.2
  • The Story: Tracks active customer retention percentages across monthly age buckets ()
  • The Insight: Exposes drop-off curves and measures the true shift from volatile new-customer acquisition to stable returning-customer velocity.
• LTV Benchmark – GitHub 3.3
  • The Story: A macro cumulative trajectory showing 24-month revenue and profit growth per customer.
  • The Insight: Identifies whether long-term revenue growth is driven by expanding customer transaction value or simply higher upfront acquisition volumes.

Step 2: Repurchase Cadence & Margin Contribution

• Goal: Understand customer re-order intervals and measure how legacy cohorts drive base profitability.
• Repurchase Cadence – GitHub 3.4
  • The Story: Calculates the average and median days elapsed between sequential customer purchases (e.g., Order 1 2 vs. Order 2  3).
  • The Insight: Establishes exact re-engagement windows for email/SMS marketing, preventing customer churn before it occurs.
• Contribution Share – GitHub 3.5
  • The Story: Breaks down annual revenue and gross profit contribution share generated by legacy cohorts vs. newly acquired customers.
  • The Insight: Proves business health by showing whether legacy cohorts form a self-sustaining profit baseline that funds ongoing operations.

Step 3: Harvesting Late Money & Behavior

• Goal: Identify long-term repeat buyers who generate high-margin yield over extended time horizons.
• Late-Money Calendar Yield – GitHub 4.1.1
  • The Story: Isolates profit and revenue streams generated by customers acquired 12+ months ago.
  • The Insight: Demonstrates brand equity and long-term customer retention without requiring continuous ad spend.
• Behavioral Time-Lag – GitHub 4.1.2
  • The Story: Measures the total elapsed days between a customer’s first purchase and their latest order.
  • The Insight: Sets realistic timeframes for evaluating marketing ROI—proving that if average customer lag is 9 months, ad performance cannot be judged on a 30-day window.

Step 4: Loyalty & Conversion Bridges

• Goal: Separate one-time buyers from high-value power customers to fix product-market leaky buckets.
• First-to-Second Bridge Financials – GitHub 4.1.3
  • The Story: Categorizes buyers into distinct behavior tiers: 1 Order (Single-Purchase), 2 Orders (Bridge Customers), and 3+ Orders (Power Buyers).
  • The Insight: Clarifies whether retention drop-offs stem from poor initial product experience or ineffective post-purchase marketing.
• Granular First-Second Order Audit – GitHub 4.1.5
  • The Story: Performs an order-level audit of the customer transition path from purchase #1 to purchase #2.
  • The Insight: Pinpoints the exact median days required to convert a first-time buyer into a repeat buyer.

Step 5: Scaling & Payback Efficiency

• Goal: Determine CAC recovery speed to control unit economics and safely scale paid acquisition.
• CAC Payback & Profit Milestones – GitHub 4.3
  • The Story: Maps marketing acquisition costs (CAC) against cumulative profit trajectory over fixed time horizons (30, 60, 90, 180, 365 days).
  • The Insight: Identifies the precise day a cohort breakevens on ad spend, providing the exact financial threshold required to scale acquisition aggressively.

Data Foundation

The data foundation phase can be best described as: The How

Focus: Architecture, Accuracy and Definitions.

Phase 0

The first step is all about data collection. The data requirements for LTV modelling are:

• customer_id
• order_date
• revenue
• profit

The one caveat is that it assumes profit represents true order contribution margin ( Net Sales Revenue − COGS − Variable Operational Costs).

To get the required data into Google Big Query which is being used as the centralised data warehouse for LTV modelling there are 2 routes:

1. Using Google sheets as the data pipeline into Big Query. In most cases the data will be exported from another data platform i.e., Shopify, Stripe etc
2. Big Query may be used as a data warehouse with Shopify data being piped into Big Query it would then involve building a view of the required data

Whichever option is preferred the most critical step is getting the right data filtered and inputted into Google sheets or a Big Query view.

Focusing on option 1 and Google sheets the link below will provide a template with the required (dummy) data.

LTV Google sheet template with dummy data

The main watchouts with using Google sheets as the data pipeline:

• 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

Once the data has been validated in Google sheets then the process starts getting the data into Big Query: (working on the assumption a project is already set-up with billing)

• Create a dataset under the selected project
• Go to the selected dataset and create table
  • Select Drive (Google drive)
  • Copy the URL of the Google sheet in the browser address bar. Big Query cares about /d/ in the URL – https://docs.google.com/spreadsheets/d/abcdef_1234567890
  • Select file format as Google Sheet
  • Sheet range would be ltvsalesdatabyday!A:D (if using the Google sheet template > Google sheet template)
  • With it being a small dataset manually setting the schema should be straight forward
    • customer_id = STRING
    • order_date = DATE
    • revenue = NUMERIC
    • profit = NUMERIC
  • Under Advanced Options > Header Rows to Skip, Input 1. This will ensure it will ignore the header columns in the Google sheet

With phase 0 completed and data now uploaded into Big Query, phase 1 and phase 2 is all about validating and understanding the data that will drive the insights in phase 4 and phase 5 which is the business intelligence.

Phase 1 and Phase 2 are about validating and understand the data.

Phase 1: The Data Audit

Focus: Proving the data accuracy

• The How (Validation): Deploying automated data integrity checks (GitHub 1.1, 1.2,1.3) to identify null values and neutralize duplicate transactions that would inflate profit
• The When (Timelines): Validate “Day 0” dates (GitHub 1.4) to solve for “Left Censoring” ensuring customers are attributed to the correct historical starting point
• The Goal: Establish a “Single Source of Truth” where every stakeholder can trust that the LTV numbers are based on clean, deduplicated financial reality

Phase 2: The Cohort Blueprint

Focus: Establishing the baseline of customer segments.

• The How (Segmentation): Group unique customer database into “Birth Year” (first purchase date) cohorts (GitHub 2.1, 2.2) to track behaviour over time rather than aggregate
• The When (Frequency): Analyse “Purchase Interval” (GitHub 2.3) to identify exactly how many days it takes for a customer to move from a “Trialist” to a “Loyalist” bucket
• The Goal: Define the “Denominator”—the specific groups of people we are measuring—so that the LTV math in the next phase is mathematically sound.

Business Intelligence

The business intelligence phase can be best described as: The So What

Focus: Growth, Profitability and Future Strategy.

Phase 3: The Profit Engine & Unit Economics 

Focus: Quantifying the distribution and payback

• The How (Distribution & Cadence): Tracking how cumulative profit scales across monthly cohorts (GitHub 3.1–3.2) while calculating order-to-order repurchase intervals (GitHub 3.4)
• The When (Contribution & Benchmark): Establishing 24-month cumulative LTV baselines (GitHub 3.3) and evaluating legacy vs. new cohort contribution share (GitHub 3.5)
• The Goal: Identify matured LTV thresholds, optimize repurchase timing windows, and pinpoint which cohorts serve as primary cash-flow drivers.

Phase 4: Strategic Forecasting & Unit Economics

Focus: Predicting the future

• The How (Baseline & Migration): Analyzing baseline migration metrics (GitHub 4.1.0–4.1.5) to evaluate how legacy cohorts retain and transition profitability year-over-year.
• The When (Forecasting): Using historical milestone performance (GitHub 4.3) to project payback timelines and predict end-of-year cohort value
• The Goal: Provide actionable guidance on acquisition spending limits (CAC thresholds), optimal capital allocation, and payback recovery speed

Deep Dive into Retention (3.2), Repurchase Cadence (3.4), and Payback (4.2)

The core strategic insights stem from 3.2 Cohort Retention, 3.4 Repurchase Cadence, and 4.2 Payback Velocity. Together, they answer three critical business questions:

• Github 3.2 – What is the value of the customer after 30 days, 60 days, 90 days etc
• Github 3.4 – How many days elapse before a customer transitions from Order 1 to Order 2?
• Github 4.2 – How rapidly does net profit recover acquisition spending (CAC)? 

All 3 work in combo proving the health of the business.

3.2 LTV Retention (Matrix)

Block 1 – The Baseline (Cohort Anchor Sizes)

WITH cohort_sizes AS (
-- Step 1: Establish initial starting size for each monthly cohort
SELECT 
DATE_TRUNC(first_order_date, MONTH) AS cohort_month,
COUNT(DISTINCT customer_id) AS cohort_start_size
FROM `mrdipeshashah.customersalesdata.view_order_grain_master`
GROUP BY 1
),

The Logic: Identifies each customer’s acquisition month (Day 0) and calculates the total initial customer volume (cohort_start_size) brought in during that period.

The Rationale: Establishes a fixed baseline denominator for every cohort. This prevents data dilution when evaluating returning customer activity over time.

Technical Details: DATE_TRUNC(first_order_date, MONTH) normalizes all acquisition dates to the 1st of the month, while COUNT(DISTINCT customer_id) ensures duplicate order IDs do not inflate initial cohort volume.

Block 2 – The Activity Engine (Monthly Trajectory)

active_customers_by_month AS (
-- Step 2: Track active purchasing customers by cohort age in months
SELECT 
DATE_TRUNC(first_order_date, MONTH) AS cohort_month,
DATE_DIFF(DATE_TRUNC(order_date, MONTH), DATE_TRUNC(first_order_date, MONTH), MONTH) AS cohort_age_months,
COUNT(DISTINCT customer_id) AS active_customers
FROM `mrdipeshashah.customersalesdata.view_order_grain_master`
GROUP BY 1, 2
)

The Logic: Categorizes repeat purchases into standardized monthly maturity buckets (cohort_age_months) relative to the customer’s initial purchase date.

The Rationale: Normalizes customer lifecycles into standardized age windows ($0, 1, 2 \dots 11$ months), enabling fair side-by-side comparisons between historic and recent cohorts at identical tenures.

Technical Details: DATE_DIFF(…, MONTH) measures elapsed age in months rather than days. GROUP BY 1, 2 aggregates transaction counts into a clean 2D grid matrix structure.

Block 3 – The Retention Matrix (Maturity Calculation)

-- Step 3: Combine active counts against initial cohort size to derive retention %
SELECT 
a.cohort_month,
a.cohort_age_months,
s.cohort_start_size,
a.active_customers,
ROUND(a.active_customers / s.cohort_start_size, 4) AS retention_rate
FROM active_customers_by_month a
JOIN cohort_sizes s 
ON a.cohort_month = s.cohort_month;

The Logic: Compares active repeat customers in any given month against the original cohort starting size to calculate retention percentages.

The Rationale: Translates raw transaction volume into standard retention rates that power the heatmap matrix, making drop-off curves immediately readable.

Technical Details: The inner JOIN aligns monthly active user counts back to their fixed baseline (s.cohort_start_size), while ROUND(…, 4) formats the resulting decimal value for Looker Studio percentage fields.

3.3 Yearly Benchmark 

Block 1 – Customer Lifespan & Age Engine

WITH customer_lifespan AS (
-- Step 1: Extract transactions and calculate customer age in relative months
SELECT 
customer_id,
order_date,
first_order_date,
revenue,
profit,
DATE_DIFF(DATE_TRUNC(order_date, MONTH), DATE_TRUNC(first_order_date, MONTH), MONTH) AS age_month
FROM `mrdipeshashah.customersalesdata.view_order_grain_master`
),

The Logic: Extracts order-level financials and calculates the exact tenure (age_month) of each transaction relative to the customer’s acquisition date.

The Rationale: Normalizes customer spending timelines so all revenue and profit streams can be aggregated by month of maturity ($0, 1, 2 \dots 24$).

Technical Details: DATE_DIFF(DATE_TRUNC(order_date, MONTH), DATE_TRUNC(first_order_date, MONTH), MONTH) calculates month-based tenure regardless of calendar year.

Block 2 –Total Baseline & Monthly Aggregations

),
total_unique_customers AS (
SELECT COUNT(DISTINCT customer_id) AS grand_total_customers
FROM `mrdipeshashah.customersalesdata.view_order_grain_master`
),
milestone_totals AS (
SELECT
c.age_month,
SUM(c.revenue) AS monthly_revenue,
SUM(c.profit) AS monthly_profit
FROM customer_lifespan c
GROUP BY c.age_month
)

The Logic: Creates two parallel CTEs: one capturing the total historical customer population (grand_total_customers) and another summing total sales/profit generated in each specific age month.

The Rationale: Separates numerator (accumulated revenue/profit) from denominator (total customers) to compute true overall lifetime averages.

Technical Details: COUNT(DISTINCT customer_id) provides a single static denominator, while GROUP BY c.age_month collects raw incremental dollars.

Block 3 – Cumulative LTV Benchmark Calculation

-- Step 3: Compute running cumulative totals and average LTV metrics
SELECT 
m.age_month AS cohort_age_months,
SUM(m.monthly_revenue) OVER(ORDER BY m.age_month ASC) AS total_cumulative_revenue,
SUM(m.monthly_profit) OVER(ORDER BY m.age_month ASC) AS total_cumulative_profit,
ROUND(SUM(m.monthly_revenue) OVER(ORDER BY m.age_month ASC) / t.grand_total_customers, 2) AS avg_cumulative_ltv_revenue,
ROUND(SUM(m.monthly_profit) OVER(ORDER BY m.age_month ASC) / t.grand_total_customers, 2) AS avg_cumulative_ltv_profit
FROM milestone_totals m
CROSS JOIN total_unique_customers t
WHERE m.age_month <= 24;

The Logic: Uses window functions to accumulate incremental monthly numbers into continuous cumulative running totals over a 24-month lifecycle.

The Rationale: Generates the blended 24-month LTV benchmark curve, allowing you to see how average customer revenue and profit scale side-by-side over time.

Technical Details:

• SUM(…) OVER(ORDER BY m.age_month ASC) builds the cumulative trajectory
• CROSS JOIN attaches the grand total customer count to calculate per-customer averages
• WHERE m.age_month <= 24 caps the output to a clean 2-year analysis window

Additional Info:

To avoid bad data impacting LTV and diluting the averages with customers who have not fully matured.

Example 1.0: In January 2026 looking at the 2025 cohort not all customers would have matured.

Example 2.0: Looking at one individual customer who makes a purchase on 31^st December 2025. On 1^st July 2026 the customer who made a purchase on 31/12/2025 will be 182 days old.

In July 2026, the 180-day performance can be compared to previous years with having total trust in the data.

The 2025 customers who joined between Jan 2025 and June 2025 would have hit their 1-year birthday so will be added to the avg_profit_365d.

4.2 CAC Payback & Profit Milestone

Block 1 – Base Cohorts & Milestone Alignment

WITH first_orders AS (
-- Step 1: Identify customer acquisition date and initial cohort year
SELECT
customer_id,
MIN(order_date) AS first_order_date,
EXTRACT(YEAR FROM MIN(order_date)) AS cohort_year
FROM `mrdipeshashah.customersalesdata.view_order_grain_master`
GROUP BY 1

),
cohort_totals AS (
-- Step 2: Compute baseline customer count per cohort
SELECT 
cohort_year, 
COUNT(DISTINCT customer_id) AS total_customers 
FROM first_orders 
GROUP BY 1
),

The Logic: Isolates initial acquisition dates per customer to establish baseline cohort sizes.

The Rationale: Ensures LTV and payback curves are normalized per total acquired customer rather than skewed by repeat-buyer survivorship bias.

Technical Details: COUNT(DISTINCT customer_id) establishes the precise denominator for per-customer cumulative averages.

Block 2 – The Cumulative Engine & Milestone Bucketing

order_milestones AS (
SELECT
f.cohort_year,
t.customer_id,
DATE_DIFF(t.order_date, f.first_order_date, DAY) AS days_since_acquisition,
t.profit
FROM `mrdipeshashah.customersalesdata.view_order_grain_master` t
JOIN first_orders f ON t.customer_id = f.customer_id
)

The Logic: Measures exact time elapsed (days_since_acquisition) from day 0 acquisition for every subsequent transaction.

The Rationale: Replaces calendar dates with customer relative age, enabling apples-to-apples comparison between older and newer cohorts.

Technical Details: DATE_DIFF(…, DAY) standardizes customer lifespan timelines across the full dataset.

Block 3 – Milestone Aggregation & Velocity Output

SELECT
m.cohort_year,
milestone.day_bucket AS payback_milestone_day,
ROUND(SUM(m.profit) / c.total_customers, 2) AS cumulative_profit_per_customer
FROM order_milestones m
CROSS JOIN UNNEST([
STRUCT(30 AS day_bucket),
STRUCT(60 AS day_bucket),
STRUCT(90 AS day_bucket),
STRUCT(180 AS day_bucket),
STRUCT(365 AS day_bucket)
]) AS milestone
JOIN cohort_totals c ON m.cohort_year = c.cohort_year
WHERE m.days_since_acquisition <= milestone.day_bucket
GROUP BY 1, 2, c.total_customers
ORDER BY m.cohort_year ASC, milestone.day_bucket ASC;

The Logic: Generates cumulative net profit per customer across key time thresholds (30, 60, 90, 180, and 365 days).

The Rationale: Bridges marketing and finance by pinpointing the exact milestone day where cumulative profit crosses acquisition spending (CAC breakeven).

Technical Details:

• UNNEST([STRUCT(…)]) creates standard time buckets dynamically without manual repeating CTEs.
• WHERE days_since_acquisition <= milestone.day_bucket ensures strict cumulative accumulation up to each milestone boundary.

Data Refresh & Integrity

The data integrity is built on real time maturity. The SQL automatically process customers time-based windows as their age incrementally gets older from their first_purchase_date. This provides the business with a living forecast that becomes increasingly more accurate with every passing day.

The logic is built to handle the transaction volume as a business moves towards £10m of revenue, with the infrastructure remaining stable and accurate.

Logic

The Problem: Most LTV models freeze cohorts value too early or dilute by including customers who haven’t had time to buy

The Integrity Solution: The use of DATE_DIFF(CURRENT_DATE(), first_purchase_date, DAY) acts as a real-time filter

The Result: A customer who made their first purchase on 31^st December 2025 will be in the 180-day window on 1^st July 2026. This ensure the data is always complete.

Rationale

The Technical Detail: The SQL uses AVG(CASE WHEN individual_age_days >= Milestone THEN profit END)

The Business Rationale: It protects the LTV Benchmark by forcing Big Query to ignore customers who have not reached the window requirements i.e., 180 days. This prevents unpredictable data like low revenue generated or high % of new customers which drags metrics in the wrong direction.

Technical Details

To maintain a single version of the truth across 3.2, 3.3 and 4.3 the following data standards are adhered to:

Refresh Strategy

Execution: Use CURRENT_DATE()

Audit Trial: Every time the script is run it recalculates every customer. In March 2026, 2025 data will be far more complete than it was in January 2026, come June 2026 the 2025, data will be close to fully matured.

Summary

Data is only valuable if it drives a decision. This whole process moves the business from a reactive state checking platform led dashboards to a predictive state and scaling the business on proven unit economics. By understanding LTV (GitHub 3.2) to Payback (GitHub 4.2) the business should be able to scale by prioritising profit over revenue.