LTV Drivers

Purpose

Provide a driver-based view of customer lifetime value, attributing period-over-period changes in LTV to changes in monetization and retention dynamics.

This model is designed as an explanatory and analytics layer, enabling:

Clear attribution of LTV expansion or contraction

It turns LTV from a black-box KPI into explainable metric.

Business Logic

Start from LTV
Compute period-over-period deltas using LAG
Attribute LTV change into:
- ARPU effect: changes in ARPU
- Lifetime effect: changes in customer lifetime
Ensure driver components reconcile to total LTV 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`
arpu	LTV change driven by ARPU variation
customer_lifetime	LTV change driven by lifetime variation
ltv_delta	Total period-over-period change in LTV

Sample & Implementation

Drivers of changes in customer lifetime value (LTV) over time.
date	value_type	arpu	customer_lifetime	ltv_delta
2025-02-01	budget	720.974	544632.7821	546035.4462
2025-03-01	budget	1381.1054	17376.1222	18778.6154
2025-04-01	budget	1381.4993	17397.51	18800.0769
2025-05-01	budget	1381.8202	17418.5776	18821.1538
2025-06-01	budget	1382.0601	17439.3336	18841.8462
2025-07-01	budget	1382.4389	-69308.4515	-68006.0385
2025-08-01	budget	1284.2614	15886.0297	17188.3571
2025-09-01	budget	1284.5843	15904.0957	17206.5
2025-10-01	budget	1284.8616	15921.9157	17224.3571
2025-11-01	budget	1285.0874	15939.4956	17241.9286

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


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

calculated_values as (
    select
        date,
        value_type,
        (arpu - LAG(arpu) OVER (PARTITION BY value_type ORDER BY date)) * LAG(customer_lifetime) OVER (PARTITION BY value_type ORDER BY date) as arpu,
        (customer_lifetime - LAG(customer_lifetime) OVER (PARTITION BY value_type ORDER BY date)) * LAG(arpu) OVER (PARTITION BY value_type ORDER BY date) as customer_lifetime,
        (ltv - LAG(ltv) OVER (PARTITION BY value_type ORDER BY date)) as ltv_delta
    from ltv
)

select *
from calculated_values
where arpu IS NOT NULL

Dependencies

Transformations: LTV