CAC payback,
measured on a real cohort.

Move 02

Stitch the patient journey end-to-end into one event spine.

Ad-click → landing → signup → booking → appointment completed → repeat booking, all keyed to a single patient pseudo-ID resolved across the app database and the marketing-platform identifiers. Fivetran into BigQuery for the ad platforms, a CDC stream off Postgres for the app events, dbt for the joins. Bronze was load-as-is. Silver was where identity got resolved.

Move 03

Build the cohort-LTV mart that finance and growth could read together.

One row per acquisition cohort × channel × month-since-acquisition. Completed revenue net of refunds, accumulating month over month. Payback is then a straightforward read against fully-loaded acquisition spend in the cohort's acquisition month. This is the model that ended the argument:

-- models/marts/mart_cohort_payback.sql
-- One row per (acquisition_cohort_month, channel, months_since_ack).
-- Tracks cumulative completed-net revenue per acquired patient
-- against fully-loaded acquisition spend in the cohort month.

with cohorts as (
    select
        patient_pseudo_id,
        acquisition_channel,
        date_trunc('month', activated_at) as cohort_month
    from {{ ref('silver_patient_activations') }}
    where activated = true             -- per metric contract:
                                       --   completed + kept + not refunded
),
revenue as (
    select
        c.cohort_month,
        c.acquisition_channel,
        date_diff('month',
                  c.cohort_month,
                  date_trunc('month', a.completed_at)
        )                                          as months_since_ack,
        sum(a.net_revenue_cad)                     as revenue_in_month,
        count(distinct c.patient_pseudo_id)        as cohort_size
    from cohorts c
    join {{ ref('silver_appointments_completed') }} a
      on a.patient_pseudo_id = c.patient_pseudo_id
     and a.refunded = false
    group by 1, 2, 3
),
spend as (
    select
        date_trunc('month', spend_date) as cohort_month,
        channel                         as acquisition_channel,
        sum(spend_cad)
          + sum(platform_fee_cad)
          + sum(creative_amort_cad)     as fully_loaded_spend
    from {{ ref('silver_acquisition_spend') }}
    group by 1, 2
)
select
    r.cohort_month,
    r.acquisition_channel,
    r.months_since_ack,
    r.cohort_size,
    r.revenue_in_month,
    sum(r.revenue_in_month) over (
        partition by r.cohort_month, r.acquisition_channel
        order by r.months_since_ack
        rows between unbounded preceding and current row
    )                                              as cum_revenue,
    s.fully_loaded_spend,
    safe_divide(
        sum(r.revenue_in_month) over (
            partition by r.cohort_month, r.acquisition_channel
            order by r.months_since_ack
            rows between unbounded preceding and current row
        ),
        s.fully_loaded_spend
    )                                              as payback_ratio
from revenue r
left join spend s
       on s.cohort_month         = r.cohort_month
      and s.acquisition_channel  = r.acquisition_channel

Move 04

Read the curve. Kill what doesn't pay back.

Two channels — one mid-funnel display network and one affiliate vertical — had payback ratios under 0.7 at month twelve. They weren't paying back on any reasonable horizon. The growth lead walked the curves into a finance review in week six and the spend was cut the following Monday. The reallocated budget went to a Google brand-search line that had a six-month payback on the same cohort math.

Move 05

Hand it to growth. Document the contract.

The mart, the metric contract, and a Metabase dashboard with cohort curves overlaid on payback thresholds — shipped with a fifteen-page Notion that the next analyst hire would read on day one. We were out by week eight. The growth team owns the model. The contract is still the document on the wall.