Inside the funnel: Acima customer economics

-- Cohort the customer base by first-lease month, then track
-- active share and cumulative contribution out to month 12.
with first_lease as (
  select customer_id,
         date_trunc('month', min(origination_ts)) as cohort_month
  from acima.leases
  group by 1
),
activity as (
  select f.cohort_month,
         datediff('month', f.cohort_month,
                  date_trunc('month', l.origination_ts)) as month_index,
         count(distinct l.customer_id)                   as active_customers,
         sum(l.contribution_after_losses)                as contribution
  from first_lease f
  join acima.leases l using (customer_id)
  group by 1, 2
)
select cohort_month,
       month_index,
       active_customers,
       sum(contribution) over (
         partition by cohort_month order by month_index
       ) as cumulative_ltv
from activity
order by cohort_month, month_index;

-- Funnel decomposition with a charge-off guardrail, the metric
-- the Android-onboarding experiment moves.
select a.channel,
       count(*)                                           as applications,
       sum(a.is_approved::int)                            as approved,
       sum(a.is_activated::int)                           as activated,
       round(sum(a.is_activated::int)
             / nullif(sum(a.is_approved::int), 0), 4)     as approve_to_activate,
       round(sum(l.charged_off::int)
             / nullif(count(l.lease_id), 0), 4)           as charge_off_rate
from acima.applications a
left join acima.leases l using (application_id)
where a.applied_ts >= dateadd('day', -90, current_date)
group by 1
order by applications desc;

-- models/marts/fct_cohort_ltv.sql
-- The cohort-LTV query above, productionized: sourced from staging
-- refs (not raw tables), so the metric is defined once and reused
-- everywhere downstream. config + tags drive how dbt builds it.
{{ config(materialized='incremental', unique_key=['cohort_month','month_index']) }}

with first_lease as (
  select customer_id,
         date_trunc('month', min(origination_ts)) as cohort_month
  from {{ ref('stg_acima__leases') }}
  group by 1
),
activity as (
  select f.cohort_month,
         datediff('month', f.cohort_month,
                  date_trunc('month', l.origination_ts)) as month_index,
         count(distinct l.customer_id)                   as active_customers,
         sum(l.contribution_after_losses)                as contribution
  from first_lease f
  join {{ ref('stg_acima__leases') }} l using (customer_id)
  group by 1, 2
)
select cohort_month,
       month_index,
       active_customers,
       sum(contribution) over (
         partition by cohort_month order by month_index
       ) as cumulative_ltv
from activity

-- models/marts/fct_cohort_ltv.yml  (the governance layer)
-- These tests run on every build. A nullable cohort_month or a
-- negative LTV fails CI before it ever reaches a dashboard.
models:
  - name: fct_cohort_ltv
    description: Cohort-level retention and cumulative contribution. One source of truth for LTV.
    columns:
      - name: cohort_month
        tests: [not_null]
      - name: month_index
        tests:
          - not_null
          - dbt_utils.accepted_range: { min_value: 0, max_value: 12 }
      - name: cumulative_ltv
        tests:
          - dbt_utils.accepted_range: { min_value: 0 }

-- models/marts/fct_funnel_by_channel.sql
-- The funnel query, productionized. The guardrail that lives in a
-- WHERE clause above becomes a test that runs in CI: if any channel's
-- charge-off rate breaches tolerance, the build fails and the bad
-- number never reaches a dashboard.
{{ config(materialized='table') }}

with applications as (
  select * from {{ ref('stg_acima__applications') }}
  where applied_ts >= dateadd('day', -90, current_date)
),
leases as (
  select * from {{ ref('stg_acima__leases') }}
)
select a.channel,
       count(*)                                           as applications,
       sum(a.is_approved::int)                            as approved,
       sum(a.is_activated::int)                           as activated,
       round(sum(a.is_activated::int)
             / nullif(sum(a.is_approved::int), 0), 4)     as approve_to_activate,
       round(sum(l.charged_off::int)
             / nullif(count(l.lease_id), 0), 4)           as charge_off_rate
from applications a
left join leases l using (application_id)
group by 1
order by applications desc

-- models/marts/fct_funnel_by_channel.yml  (the guardrail, as a test)
models:
  - name: fct_funnel_by_channel
    description: Approve-to-activate funnel by channel, with the charge-off guardrail enforced in CI.
    columns:
      - name: channel
        tests:
          - not_null
          - accepted_values: { values: ['dtc', 'merchant'] }
      - name: approve_to_activate
        tests:
          - dbt_utils.accepted_range: { min_value: 0, max_value: 1 }
      - name: charge_off_rate
        # The guardrail: growth that lifts activation but breaches this
        # band fails the build. Loss is managed by category, not ignored.
        tests:
          - dbt_utils.accepted_range: { min_value: 0, max_value: 0.15 }