Customer Count View

Purpose

Produce a customer count base view evolution over time.

This model consolidates starting balances, customer movements (new, upsell, downsell, churn), and cumulative effects into a single, standardized dataset that can be reused consistently across reports, dashboards, and analyses.

Business Logic

Start from cumulative customer counts provided by customer_count
Derive the customer base per period:
- Current base = previous period ending balance
- Ending balance = starting balance + cumulative net new
Decompose customer movements using revenue_changes:
- New customers
- Upsell
- Downsell
- Churn
All calculations are partitioned by value_type_id and ordered by date
Output provides both:
- Point-in-time customer base
- Period movements explaining the change

Output Contract

Definitions

Column	Description
date	Date represented as `YYYY-MM-DD`.
value_type_id	Value Type identifier.
current_base	Customer base at the start of the period.
new_customers	Number of new customers acquired in the period.
upsell	Number of customers contributing to upsell movements in the period.
downsell	Number of customers contributing to downsell movements in the period.
churn	Number of customers lost in the period.
net_new	Net customer change in the period (new − churn).
ending_balance	Customer base at the end of the period.

Sample & Implementation

Customer quantity metrics derived from customer counts and revenue changes.
date	value_type_id	current_base	new_customers	upsell	downsell	churn	net_new	ending_balance
2025-01-01	2	3000	60	4	1	25	35	3035
2025-02-01	2	3035	60	4	1	13	47	3082
2025-03-01	2	3082	60	4	1	13	47	3129
2025-04-01	2	3129	60	4	1	13	47	3176
2025-05-01	2	3176	60	4	1	13	47	3223
2025-06-01	2	3223	60	4	1	13	47	3270
2025-07-01	2	3270	60	4	1	14	46	3316
2025-08-01	2	3316	60	4	1	14	46	3362
2025-09-01	2	3362	60	4	1	14	46	3408
2025-10-01	2	3408	60	4	1	14	46	3454

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


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

changes as (
    select * from {{ref('revenue_changes')}}
),

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

new_customers as (
    select
        date,
        value_type_id,
        nr_of_customers
    from changes
    where revenue_type_id = 1
),

upsell_customers as (
    select
        date,
        value_type_id,
        nr_of_customers
    from changes
    where revenue_type_id = 2
),

downsell_customers as (
    select
        date,
        value_type_id,
        nr_of_customers
    from changes
    where revenue_type_id = 3
),

churn_customers as (
    select
        date,
        value_type_id,
        nr_of_customers
    from changes
    where revenue_type_id = 4
)

select
    cc.date,
    vt.name as value_type,
    cc.current_base,
    nc.nr_of_customers as new_customers,
    uc.nr_of_customers as upsell,
    dc.nr_of_customers as downsell,
    chc.nr_of_customers as churn,
    cc.net_new,
    cc.ending_balance
from customer_count cc
left join new_customers nc on nc.date = cc.date and nc.value_type_id = cc.value_type_id
left join upsell_customers uc on uc.date = cc.date and uc.value_type_id = cc.value_type_id
left join downsell_customers dc on dc.date = cc.date and dc.value_type_id = cc.value_type_id
left join churn_customers chc on chc.date = cc.date and chc.value_type_id = cc.value_type_id
left join value_types vt on vt.id = cc.value_type_id

Dependencies

Facts: Customer Count and Revenue Changes
Semantic Layers: Value Types