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/Ecom, it does not transfer over to other business models:

• The LTV model can predict when a customer will lapse then wating for a cancellation in subscription models
• With D2C/Ecom 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 roadmap

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

There are 5 steps in the roadmap to business maturity:

Step 1: The Foundation

Goal: Prove the business is building a base than just churning through one-time buyers.

• Matured LTV – GitHub 3.2:
  • The Story: Tracks how much a customer purchased in i.e., 2025 and how much they are worth across 30 days, 60 days and 90 days
  • The Insight: Understanding the shift between new customer revenue v returning customer revenue
• Yearly side-by-side performance – GitHub 3.3:
  • The Story: A macro comparison of total revenue and profit growth year-over-year
  • The Insight: Identifying if growth is coming from a higher volume of customers or higher value customers

Step 2: Harvesting Late Money

Goal: Identifying customers who consistently generate high-profit revenue over time, forming a stable baseline for the business.

• Late-money calendar yield – GitHub 4.1.1
  • The Story: Isolates revenue from customers who have not purchased in 12+ months
  • The Insight: Proves the brand strength and that customers are willing to buy repeatedly
• Behavioural Time-Lag – GitHub 4.1.2
  • The Story: Measures the days between a customer’s first and last purchase
  • The Insight: Sets the benchmark for how marketing should be judged i.e., if the lag is 9 months marketing success cannot be judged in 30 days

Step 3: Loyalty

Goal: Separating one-time buyers from power buyers

• First-to-second bridge financials – GitHub 4.1.3:
  • The Story: Categorising users into the following buckets 1 order (Ghosts), 2 orders (Bridge) and 3+ orders (Power buyers)
  • The Insight: Exposes if its product or marketing problem

Step 4: Operational Pulse & Unit Truth

Goal: Real time monitoring and transaction level auditing

• Daily/Weekly/Monthly Performance – GitHub 4.1.4
  • The Story: Breakdown of new v returning metrics
  • The Insight: Track the impact of campaigns, promotions etc
• Granular first-second order audit – GitHub 4.1.5
  • The Story: A breakdown of every customer’s journey from 1^st to 2^nd purchase
  • The Insight: Calculates the exact day_to_bridge from 1^st to 2^nd purchase and when the business hits profitability

Step 5: Scaling

Goal: Determining the breakeven point to help better control scaling

• CAC Payback & Profit Milestone – GitHub 4.3
  • The Story: Connects Ad-spend to cumulative profit of a customer over time
  • The Insight: Helps the business understand if spending X on a customer today will turn profitable on Day Y. It helps with understanding how aggressive to scale

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

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 (Payback Cycle)

Focus: Quantifying the distribution and payback

• The How (Distribution): Moving from revenue to net profit (Contribution Margin) and tracking how it’s distributed across cohorts (GitHub 3.1–3.2)
• The When (Velocity): Measure the speed of the payback at 1 day, 30 days, 60 days, 90 days, 180 days, 365 days, 1-year and 2-year milestones (GitHub 3.3) to see how fast “CAC” is paid back
• The Goal: Identify the “Matured LTV” (£115) and determine which cohorts are the most efficient “cash-flow drivers.”

Phase 4: The Strategic Outlook 

Focus: Predicting the future

• The How (Contribution): Analyse the baseline (GitHub 4.1) measuring how much repeat profit comes from previous years cohorts
• The When (Forecasting): Using previous years insights (GitHub 4.3) to forecast where the current cohort will likely to be by the end of the next year
• The Goal: Provide rich insights to the business where to invest and where to pull back. While understanding how much can be spent to acquire the next i.e., 10K customers

Deep Dive into Matured LTV (3.2), Payback Velocity (3.3) and CAC Payback (4.3)

The rich insights come from 3.2 Matured LTV, 3.3 Payback Velocity and 4.3 CAC Payback. The business question each will be able to answer:

• Github 3.2 – What is the value of the customer after 30 days, 60 days, 90 days etc
• Github 3.3 – How fast is the payback
• Github 4.3 – Using historic data to help forecast the next month, quarter and year

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

3.2 Matured LTV

Block 1 – The Birth Certificate (Customer Acquisition)

WITH customer_acquisition AS (
-- Step 1: Establish the baseline "Day 0" and Cohorts
SELECT 
customer_id, 
MIN(order_date) AS first_purchase_date,
MAX(order_date) AS last_purchase_date,
EXTRACT(YEAR FROM MIN(order_date)) AS cohort_year,
FORMAT_DATE('%Y-%m', MIN(order_date)) AS cohort_month
FROM `enter.tablename_123456.events_*` t
GROUP BY 1
),

The Logic: Identifying when a customer makes there first purchase and calculating there ‘current age’ in days

The Rationale: This allows to anchor every customer to a specific day starting off with Day 0. This allows to measure velocity

Technical Details:

On Line 5 MIN(order_date) – This finds the first purchase, it also helps with the age filter and ensure there’s no data dilution on line 42 – DATE_DIFF(CURRENT_DATE(), MIN(order_date), DAY)

Block 2 – The Profit Ladder (Cumulative Performance)

running_profit AS (
-- Step 2: Calculate cumulative profit to find the "Payback Day"
SELECT 
t.customer_id,
t.order_date,
t.profit,
SUM(t.profit) OVER (PARTITION BY t.customer_id ORDER BY t.order_date) as cumulative_profit,
a.first_purchase_date
FROM `enter.tablename_123456.events_*` t
JOIN customer_acquis

The Logic: Transforming one-off orders into a running total of profit for every transaction

The Rationale: This shows the increase customer value and find at what point does a customer become profitable

Technical Details:

On Line 18 SUM(profit) OVER(PARTITION BY customer_id ORDER BY order_date) – The Window function orders profit to run chronologically. It tracks ‘loss-making’ customer to a ‘profit-driving’ customer.

Block 3 – The Payback Trigger (Threshold Identification)

payback_calc AS (
-- Step 3: Identify the first date they hit $150
SELECT 
customer_id,
MIN(order_date) as date_hit_150
FROM running_profit
WHERE cumulative_profit >= 150
GROUP BY 1
)

The Logic: Pinpointing when a customer cumulative profit reached the required target

The Rationale: It calculates time to payback

Technical Details:

On Line 29 WHERE cum_profit >= 150 provides the target filter to calculate the payback

Block 4 – The Milestone Windows (Standardization)

SUM(IF(DATE_DIFF(t.order_date, a.first_purchase_date, DAY) <= 0, t.profit, 0)) AS ltv_1_day,
SUM(IF(DATE_DIFF(t.order_date, a.first_purchase_date, DAY) <= 30, t.profit, 0)) AS ltv_30_days,
SUM(IF(DATE_DIFF(t.order_date, a.first_purchase_date, DAY) <= 60, t.profit, 0)) AS ltv_60_days,
SUM(IF(DATE_DIFF(t.order_date, a.first_purchase_date, DAY) <= 90, t.profit, 0)) AS ltv_90_days,
SUM(IF(DATE_DIFF(t.order_date, a.first_purchase_date, DAY) <= 180, t.profit, 0)) AS ltv_180_days,
SUM(IF(DATE_DIFF(t.order_date, a.first_purchase_date, DAY) <= 365, t.profit, 0)) AS ltv_1_yr,
SUM(IF(DATE_DIFF(t.order_date, a.first_purchase_date, DAY) <= 730, t.profit, 0)) AS ltv_2_yrs

The Logic: Creating ‘time-boxes’ windows (1,30,60,90,180, 365 days and 2 years) that force all data into those windows regardless of when the customer made their first purchase

The Rationale: It ensures that 2023 can be compared to 2025 at the same life stage

Technical Details:

On Line 49 SUM(IF(DATE_DIFF(t.order_date, b.first_purchase_date, DAY) <= 90, t.profit, 0)) it looks at every order and asks did this order happen in the first 90 days if yes then its added to the milestone total

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.

3.3 Payback Velocity

Block 1 – The Base

WITH customer_base AS (
SELECT 
customer_id, 
EXTRACT(YEAR FROM MIN(order_date)) AS cohort_year,
MIN(order_date) AS first_purchase_date,
DATE_DIFF(CURRENT_DATE(), MIN(order_date), DAY) AS individual_age_days
FROM `enter.tablename_123456.events_*`
GROUP BY 1
),

The Logic: Anchors every user to their first_purchase_date and calculates their current age

The Rationale: It can exclude ‘younger users’ from long from long term averages so that the data is not skewed

Technical Details:

DATE_DIFF(CURRENT_DATE(), …, DAY) AS individual_age_days is the gatekeeper to manage each customers birthday

Block 2 – The Milestone Windows

cohort_milestones AS (
SELECT 
b.cohort_year,
b.customer_id,
b.individual_age_days,
-- Velocity Milestones (The "Speed" Metrics)
SUM(CASE WHEN DATE_DIFF(t.order_date, b.first_purchase_date, DAY) <= 30 THEN t.profit ELSE 0 END) AS prof_30d,
SUM(CASE WHEN DATE_DIFF(t.order_date, b.first_purchase_date, DAY) <= 60 THEN t.profit ELSE 0 END) AS prof_60d, -- ADDED
SUM(CASE WHEN DATE_DIFF(t.order_date, b.first_purchase_date, DAY) <= 90 THEN t.profit ELSE 0 END) AS prof_90d,
SUM(CASE WHEN DATE_DIFF(t.order_date, b.first_purchase_date, DAY) <= 180 THEN t.profit ELSE 0 END) AS prof_180d,
-- Full Year Target
SUM(CASE WHEN DATE_DIFF(t.order_date, b.first_purchase_date, DAY) <= 365 THEN t.profit ELSE 0 END) AS prof_365d
FROM customer_base b
LEFT JOIN `enter.tablename_123456.events_*` t 
ON t.customer_id = b.customer_id
GROUP BY 1, 2, 3
)

The Logic: For every customer a snapshot of their total profit in the following windows –  1, 30, 60, 80, 180 days, 1 + 2 years which is calculated from their first_purchase_date

The Rationale: It provides insights on the profit curve. Being able to answer the question out of the year 1 profit how much is captured in 90 days?

Technical Details:

SUM(CASE WHEN DATE_DIFF(…) <= 90 THEN t.profit ELSE 0 END) AS prof_90d ensures customers are allocated to the right windows

Block 3 – The Aggregated View

 -- 1. Velocity Columns (Early Efficiency)
ROUND(AVG(CASE WHEN individual_age_days >= 30 THEN prof_30d END), 2) AS avg_profit_30d,
ROUND(AVG(CASE WHEN individual_age_days >= 60 THEN prof_60d END), 2) AS avg_profit_60d, -- ADDED
ROUND(AVG(CASE WHEN individual_age_days >= 90 THEN prof_90d END), 2) AS avg_profit_90d,
ROUND(AVG(CASE WHEN individual_age_days >= 180 THEN prof_180d END), 2) AS avg_profit_180d,

-- 2. Matured LTV (The "Fair Fight" Benchmark)
ROUND(AVG(CASE WHEN individual_age_days >= 365 THEN prof_365d END), 2) AS avg_ltv_matured_365d,

-- 3. Blended LTV (The "Real-Time" Total)
ROUND(AVG(prof_365d), 2) AS avg_ltv_blended_to_date, 

The Logic: Calculated the average profit per cohort for users who reached the age milestone i.e, 60 days

The Rationale: Provides insights on how the cohort is performing v past years i.e., avg_profit_90d how does 2025 compare to 2024

Technical Details:

ROUND(AVG(CASE WHEN individual_age_days >= 90 THEN prof_90d END), 2) customers are allocated to the right windows

4.3 CAC Payback & Profit Milestone

Block 1 – The Cumulative Engine

daily_profit_accumulation AS (
SELECT 
t.customer_id,
b.cohort_year,
DATE_DIFF(t.order_date, b.first_purchase_date, DAY) AS days_since_join,
SUM(t.profit) OVER(PARTITION BY t.customer_id ORDER BY t.order_date) AS cumulative_profit
FROM `enter.tablename_123456.events_*` t
JOIN customer_base b ON t.customer_id = b.customer_id
),

The Logic: Creating a daily running total of profit for every customer which starts from their first_purchase_date

The Rationale: To see the full customers journey and their purchases it can spot the exact day certain milestones were hit

Technical Details:

SUM() OVER(PARTITION BY…) ensures every customer profit is calculated independently

Block 2 – The Multi-Threshold Trigger

payback_milestones AS (
SELECT 
customer_id,
cohort_year,
-- Break-even Threshold (CAC)
MIN(IF(cumulative_profit >= 80, days_since_join, NULL)) AS days_to_payback_80,
-- Strategic Profit Target (Aligns with 3.2)
MIN(IF(cumulative_profit >= 150, days_since_join, NULL)) AS days_to_profit_150
FROM daily_profit_accumulation
GROUP BY 1, 2
)

The Logic: Identifying two targets, i.e., $80 break-even and $150 profit target

The Rationale: Bridges the gap between marketing and finance. Marketing cares about the payback where Finance cares about profit

Technical Details:

Using MIN(IF(…)) captures the earliest date for the two milestones, creating a timestamp to understand when the customer goes from break-even to profit

Block 3 – The Velocity Comparison

SELECT 
cohort_year,
COUNT(customer_id) AS total_customers,
-- Break-even Metrics
COUNT(days_to_payback_80) AS customers_at_breakeven,
ROUND(AVG(days_to_payback_80), 0) AS avg_days_to_breakeven,
-- Profit Target Metrics
COUNT(days_to_profit_150) AS customers_at_profit_target,
ROUND(AVG(days_to_profit_150), 0) AS avg_days_to_profit_target
FROM payback_milestones
GROUP BY 1
ORDER BY 1;

The Logic: Calculating the average time to reach break-even and profit targets

The Rationale: It allows to measure year-over-year efficiency

Technical Details:

The COUNT columns act as a conversion rate. For example, if 1000 customers hit the $80 break-even but only 400 hit the $150 profit target it can recognise where the churn is occurring

Data Refresh & Integrity

The data integrity is built on real time maturity. The SQL architecture 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.3) the business should be able to scale by prioritising profit over revenue.