LTV And CAC Queries

Why this matters

As a Marketing Analyst, you will regularly answer questions like: Are we paying too much to acquire customers? When do we earn back our acquisition costs? Which channels bring higher lifetime value customers? SQL LTV and CAC queries turn raw events, orders, and spend tables into answers that guide budget allocation, campaign scaling, and payback decisions.

• [ ] Report monthly CAC by channel for performance reviews
• [ ] Track 3, 6, and 12-month LTV by cohort and channel
• [ ] Estimate payback period using gross margin assumptions
• [ ] Flag channels with rising CAC or slowing payback

Who this is for

• Marketing Analysts who need actionable unit economics
• Growth/Performance Marketers who work with SQL-based warehouses
• Data Analysts building KPI dashboards for acquisition

Prerequisites

• Comfort with SQL joins, aggregations, and date functions
• Basic understanding of cohorts and attribution
• A revenue table (orders or invoices) and a spend table

Concept explained simply

CAC (Customer Acquisition Cost) is how much you spent divided by how many new customers you got in the same period/channel. LTV (Lifetime Value) is the average revenue collected per customer over a time window (e.g., 6 months after signup). Payback is how long it takes the gross margin from a cohort to equal or exceed the CAC used to acquire them.

Mental model

• Pick a cohort definition: usually customer signup month
• Line up revenue to the right of that signup month (0, 1, 2... months since signup)
• Compute CAC for that cohort's channel and acquisition month
• Compare cumulative gross margin from that cohort with its CAC to find payback

Data model used in examples

Assume these warehouse tables (names and columns can be adapted to your stack):

customers(
  id            bigint primary key,
  signup_date   date not null,
  channel       text not null
)

orders(
  order_id      bigint primary key,
  customer_id   bigint not null,
  order_date    date not null,
  revenue       numeric(12,2) not null
)

marketing_spend(
  date          date not null,
  channel       text not null,
  spend         numeric(12,2) not null
)

Worked example 1: Monthly CAC by channel

Goal: compute CAC = spend / new_customers for each month and channel.

-- PostgreSQL
WITH new_customers AS (
  SELECT
    date_trunc('month', signup_date)::date AS month,
    channel,
    COUNT(*) AS new_customers
  FROM customers
  GROUP BY 1,2
), spend AS (
  SELECT
    date_trunc('month', date)::date AS month,
    channel,
    SUM(spend) AS spend
  FROM marketing_spend
  GROUP BY 1,2
)
SELECT
  COALESCE(s.month, n.month) AS month,
  COALESCE(s.channel, n.channel) AS channel,
  COALESCE(spend, 0) AS spend,
  COALESCE(new_customers, 0) AS new_customers,
  CASE WHEN COALESCE(new_customers, 0) = 0 THEN NULL
       ELSE ROUND(COALESCE(spend,0)::numeric / new_customers, 2) END AS cac
FROM spend s
FULL JOIN new_customers n
  ON s.month = n.month AND s.channel = n.channel
ORDER BY 1,2;

Worked example 2: 6-month LTV by cohort and channel

Goal: average revenue collected within 6 months of signup for each cohort (signup month) and channel.

WITH customer_cohorts AS (
  SELECT
    id AS customer_id,
    channel,
    date_trunc('month', signup_date)::date AS cohort_month,
    signup_date
  FROM customers
), cohort_orders AS (
  SELECT
    c.cohort_month,
    c.channel,
    o.customer_id,
    o.revenue
  FROM customer_cohorts c
  JOIN orders o
    ON o.customer_id = c.customer_id
  WHERE o.order_date >= c.signup_date
    AND o.order_date < c.signup_date + INTERVAL '6 months'
), revenue_by_cohort AS (
  SELECT
    cohort_month,
    channel,
    SUM(revenue) AS revenue_6m
  FROM cohort_orders
  GROUP BY 1,2
), cohort_sizes AS (
  SELECT cohort_month, channel, COUNT(*) AS customers
  FROM customer_cohorts
  GROUP BY 1,2
)
SELECT
  cs.cohort_month,
  cs.channel,
  cs.customers,
  ROUND(COALESCE(r.revenue_6m,0)::numeric / NULLIF(cs.customers,0), 2) AS ltv_6m
FROM cohort_sizes cs
LEFT JOIN revenue_by_cohort r USING (cohort_month, channel)
ORDER BY 1,2;

Worked example 3: Payback period (months) by cohort

Goal: find the first month offset where cumulative gross margin per acquired customer equals or exceeds CAC.

WITH params AS (
  SELECT 0.70::numeric AS gross_margin_rate  -- edit for your business
), customer_cohorts AS (
  SELECT id AS customer_id,
         channel,
         date_trunc('month', signup_date)::date AS cohort_month,
         date_trunc('month', signup_date)::date AS cohort_month_floor
  FROM customers
), orders_rel AS (
  SELECT
    c.cohort_month,
    c.channel,
    (EXTRACT(YEAR FROM age(date_trunc('month', o.order_date), c.cohort_month_floor)) * 12
     + EXTRACT(MONTH FROM age(date_trunc('month', o.order_date), c.cohort_month_floor)))::int AS month_offset,
    o.revenue
  FROM customer_cohorts c
  JOIN orders o ON o.customer_id = c.customer_id
  WHERE o.order_date >= c.cohort_month_floor
), gross_by_offset AS (
  SELECT
    cohort_month,
    channel,
    month_offset,
    SUM(revenue) * (SELECT gross_margin_rate FROM params) AS gross_margin
  FROM orders_rel
  GROUP BY 1,2,3
), cum AS (
  SELECT
    cohort_month,
    channel,
    month_offset,
    SUM(gross_margin) OVER (PARTITION BY cohort_month, channel ORDER BY month_offset) AS cum_gm
  FROM gross_by_offset
), new_customers AS (
  SELECT date_trunc('month', signup_date)::date AS month,
         channel,
         COUNT(*) AS new_customers
  FROM customers
  GROUP BY 1,2
), spend AS (
  SELECT date_trunc('month', date)::date AS month,
         channel,
         SUM(spend) AS spend
  FROM marketing_spend
  GROUP BY 1,2
), cac AS (
  SELECT s.month AS cohort_month,
         s.channel,
         CASE WHEN n.new_customers = 0 THEN NULL
              ELSE s.spend::numeric / n.new_customers END AS cac
  FROM spend s
  JOIN new_customers n ON s.month = n.month AND s.channel = n.channel
)
SELECT
  c.cohort_month,
  c.channel,
  ROUND(ca.cac::numeric, 2) AS cac,
  MIN(c.month_offset) FILTER (WHERE c.cum_gm >= ca.cac) AS payback_months
FROM cum c
JOIN cac ca USING (cohort_month, channel)
GROUP BY 1,2, ca.cac
ORDER BY 1,2;

Exercises — Practice

Try these on the same schema. A sample solution is provided in each exercise. Your answers can vary slightly and still be correct if logic matches.

Exercise 1: 3-month LTV by cohort and channel

Compute ltv_3m: average revenue within 3 months of signup per cohort_month and channel.

Exercise 2: Monthly blended CAC and payback flag

For each month and channel, compute CAC and mark payback_hit as yes/no depending on whether cumulative gross margin from cohorts up to 6 months reached CAC by month 6. Assume gross_margin_rate = 0.65.

Common mistakes and how to self-check

• Mistake: Using order month instead of signup-aligned months for LTV. Self-check: Does month 0 include same-month orders only? If not, fix your filter.
• Mistake: Dividing spend by all customers instead of new customers. Self-check: Ensure the denominator is new signups in that month/channel.
• Mistake: Ignoring zero months. Self-check: Do you lose months with no spend or no signups? Use FULL or careful LEFT joins.
• Mistake: Comparing revenue to CAC for payback. Self-check: Use gross margin (revenue * margin rate), not raw revenue.
• Mistake: Overlapping attribution windows. Self-check: Confirm each customer is counted once per chosen attribution dimension.

Practical projects

• Build a monthly CAC vs LTV dashboard by channel with 3/6/12-month views
• Create a cohort report that shows cumulative LTV curve and payback month for each cohort
• Add logic for refunds/discounts to compute net LTV

Learning path

1. Review your data model: customers, orders/subscriptions, spend
2. Write monthly CAC and validate against finance numbers
3. Add 3/6/12-month LTV by cohort and channel
4. Compute payback using a gross margin parameter
5. Productionize with scheduled queries or a BI tool

Next steps

• Add campaign-level granularity if available
• Split LTV by customer segments (e.g., first product, region)
• Incorporate refunds and cost of goods for net LTV and accurate payback

Mini challenge

Extend the LTV query to 9 months, include only orders with revenue >= 0, and output both per-customer LTV and cumulative LTV curve (month 0..9) for each cohort and channel.