Customer Value Drivers

Purpose

Provide a driver-based view of customer value evolution, attributing period-over-period changes in average customer value to underlying customer dynamics.

This model is designed as an explanatory reporting layer, enabling:

Attribution of ARPA changes to customer mix effects
Separation of acquisition, retention, and churn impacts
Clear reconciliation between customer value levels and movements

It shifts the analysis from what changed to what caused the change.

Business Logic

Start from Customer Value
Join with Customer Count View
Compute period-over-period deltas using LAG
Attribute customer value changes proportionally based on customer mix:
- Existing customer base
- New customers
- Churned customers
Ensure driver components reconcile to total customer value change
Exclude the first period where lagged values are unavailable

Output Contract

Definitions

Column	Description
date	Date of the snapshot (`YYYY-MM-DD`)
value_type	Indicator such as `actual`, `budget`
current_base	Customer value change driven by existing customers
new_customers	Customer value change driven by newly acquired customers
churn	Customer value change driven by churn dynamics (negative impact)
customer_value_delta	Total change in average customer value vs prior period

Sample & Implementation

Drivers of period-over-period changes in average customer value.
date	value_type	current_base	new_customers	churn	customer_value_delta
2025-02-01	budget	73.9779	-6.9276	1.5009	73.9770
2025-03-01	budget	72.8667	0.0011	-0.0002	72.8668
2025-04-01	budget	71.7884	0.0006	-0.0001	71.7907
2025-05-01	budget	70.7415	3.3762	-7.3151	70.7394
2025-06-01	budget	69.7247	-0.0010	0.0002	69.7261
2025-07-01	budget	68.7575	0.0010	-0.0002	68.7592
2025-08-01	budget	67.8167	0.0010	-0.0002	67.8164
2025-09-01	budget	66.9014	0.0007	-0.0001	66.9011
2025-10-01	budget	66.0104	-0.0003	8.3919	66.0120
2025-11-01	budget	65.1428	-0.0005	0.0001	65.1409

{{config(materialized='view')}}


with customer_value as (
    select * from {{ ref('customer_value') }}
),

customer_count_view as (
    select * from {{ref('customer_count_view')}}
),

calculated_values as (
    select
        cv.date,
        cv.value_type,
        (cv.current_base - LAG(cv.total_customer_value) OVER (PARTITION BY cv.value_type ORDER BY cv.date)) * (ccv.current_base / ccv.ending_balance) as current_base,
        (cv.new_customers - LAG(cv.total_customer_value) OVER (PARTITION BY cv.value_type ORDER BY cv.date)) * (ccv.new_customers / ccv.ending_balance) as new_customers,
        -(cv.churn - LAG(cv.total_customer_value) OVER (PARTITION BY cv.value_type ORDER BY cv.date)) * (ccv.churn / ccv.ending_balance) as churn,
        (cv.total_customer_value - LAG(cv.total_customer_value) OVER (PARTITION BY cv.value_type ORDER BY cv.date) ) as customer_value_delta
    from customer_value cv
    left join customer_count_view ccv on cv.date = ccv.date and cv.value_type = ccv.value_type
)

select *
from calculated_values
where current_base IS NOT NULL

Dependencies

Transformations: Customer Value and Customer Count View