How to calculate LTV:CAC by cohort in Snowflake

Why cohorts, not a blended number

LTV:CAC asks whether a customer eventually pays back what it cost to acquire them, and by what multiple. Computed across your whole customer base it is almost always misleading: the numerator is dominated by customers acquired years ago at yesterday's prices, while the denominator reflects this quarter's spend. The fix is to hold acquisition period constant – group customers by the month of their first order, then track each group's cumulative value and compare it to what that specific month's acquisition cost.

Cohorting turns one vanity number into an answer to the operative questions: are recent cohorts paying back faster or slower than older ones, and how many months does a cohort take to cover its own CAC?

Defining the inputs

Three definitions, fixed before any SQL, because every downstream number inherits them:

• Cohort: the calendar month of a customer's first ever order, in your reporting timezone.
• LTV: cumulative net revenue (after discounts and refunds) per cohort, tracked by cohort age. Use contribution margin instead if landed costs are modelled – see the FAQ.
• Cohort CAC: total acquisition spend in the cohort's month ÷ customers acquired that month. This is blended CAC, pinned to the cohort's acquisition window – the build is in the companion guide Blended CAC vs channel CAC.

The prep prerequisites are the usual ones: a deduplicated orders table (PREP_ORDERS, one row per order) and a conformed spend table (PREP_CHANNEL_SPEND, one row per day per channel). If those don't exist yet, start with What is data prep for Snowflake.

Step 1 – assign cohorts

One row per customer, stamped with their cohort month. This dataflow is worth materialising on its own – RFM, retention and repurchase models all reuse it.

-- Cohort = month of first ever order
create or replace table MODEL_COHORTS as
select customer_id,
       min(order_date)                     as first_order_date,
       date_trunc('month', min(order_date)) as cohort_month
from PREP_ORDERS
group by customer_id;

Step 2 – cumulative revenue by cohort age

Join orders back to cohorts, bucket every order into a "month number" (how many months after acquisition it happened), and accumulate. The window function does the LTV curve for you.

-- Cumulative net revenue per cohort, by cohort age
with cohort_orders as (
  select c.cohort_month,
         datediff('month', c.cohort_month,
                  date_trunc('month', o.order_date)) as month_number,
         o.net_revenue
  from PREP_ORDERS o
  join MODEL_COHORTS c using (customer_id)
),
by_age as (
  select cohort_month,
         month_number,
         sum(net_revenue) as revenue
  from cohort_orders
  group by 1, 2
)
select cohort_month,
       month_number,
       revenue,
       sum(revenue) over (
         partition by cohort_month
         order by month_number
       ) as cumulative_revenue
from by_age
order by cohort_month, month_number;

Sanity-check the grain before moving on: exactly one row per cohort month per month number, and month_number = 0 revenue should reconcile with first-order revenue for that month. A fan-out here – usually a duplicate customer in the cohort table – inflates every LTV downstream.

Step 3 – cohort CAC and the ratio

Divide cumulative revenue per customer by the cohort's CAC. Cohort size and spend come from the acquisition month alone.

-- LTV:CAC for every cohort at every age
with cohort_size as (
  select cohort_month, count(*) as customers
  from MODEL_COHORTS
  group by 1
),
cohort_spend as (
  select date_trunc('month', date_day) as cohort_month,
         sum(spend)                    as acquisition_spend
  from PREP_CHANNEL_SPEND
  group by 1
)
select l.cohort_month,
       l.month_number,
       s.customers,
       round(sp.acquisition_spend / nullif(s.customers, 0), 2)  as cohort_cac,
       round(l.cumulative_revenue / nullif(s.customers, 0), 2)  as ltv_per_customer,
       round((l.cumulative_revenue / nullif(s.customers, 0)) /
             nullif(sp.acquisition_spend / nullif(s.customers, 0), 0), 2) as ltv_to_cac
from COHORT_LTV l
join cohort_size  s  using (cohort_month)
join cohort_spend sp using (cohort_month)
order by l.cohort_month, l.month_number;

Reading the output

Three reads matter more than the rest:

• Payback month: the first month_number where ltv_to_cac crosses 1.0. If it is drifting later across recent cohorts, acquisition is getting more expensive relative to the customers it buys – whatever the topline CAC chart says.
• Same-age comparison: always compare cohorts at the same age (every cohort at month 3, say). Comparing a 12-month-old cohort's ratio to a 2-month-old one just measures time.
• The heuristic, handled with care: the folk rule that a "good" LTV:CAC is around 3:1 is only a heuristic, and it moves with margin structure and payback tolerance. The durable signal is your own trend – cohorts paying back faster or slower than the ones before them.

Remember that young cohorts are incomplete by construction: month 6 hasn't happened yet for a customer acquired in May. Cut any cohort-vs-cohort chart at ages every plotted cohort has reached, or recent cohorts will look artificially weak – the same right-censoring trap covered in the retention guide.

Making it a model, not a query

Run once, this analysis answers one meeting's question. The value is in making it a model: the three dataflows above (cohorts → cohort LTV → LTV:CAC), scheduled monthly after the orders and spend dataflows refresh, with the grain checks run before anything ships. That takes real orchestration – dependencies, not timers. Teams typically reach for dbt plus Airflow here, which works, but puts an engineer back in the loop of what is fundamentally analyst logic; the trade-off is laid out in Refyner vs dbt / Airflow.

In Refyner, this is the built-in Customer LTV model – lifetime value by acquisition cohort, channel and product, prepped and scheduled on your own Snowflake, with the dataflows open for your analysts to adapt. Building and sampling dataflows is free; scheduled runs are pay-as-you-go – details on the pricing page.

Keep reading: