PnL Metrics

Purpose

Produce a canonical set of PnL metrics (gross margin, contribution margin, total cost, EBITDA, and related percentages) derived from the rolled-up PnL data.

This model encodes financial definitions once so that margins and ratios are consistent across all reports, dashboards, and analyses.

Business Logic

Start from the fully aggregated GL hierarchy provided by pnl_rollup
Compute absolute PnL metrics using reserved GL account IDs:
- Gross Margin 3000 = Revenue 1000 − COGS 2000
- Contribution Margin 5000 = Gross Margin 3000 − Commercial Costs 4000
- Total Cost 7000 = COGS 2000 + Commercial Costs 4000 + Fixed Costs 6000
- EBITDA 8000 = Contribution Margin 5000 − Fixed Costs 6000
Append derived metric rows to the base PnL data
Compute percentage KPIs relative to Revenue 1000:
- COGS %
- Gross margin %
- Commercial costs %
- Contribution margin %
- Fixed costs %
- Total cost %
- EBITDA %
Percentage calculations:
- Use revenue as denominator
- Protect against division by zero
Output includes both:
- Absolute values (amounts)
- Relative values (percentages)

Final dataset is ordered by value type, date, and GL account.

Output Contract

Definitions

The output contract has the same setup as PnL Rollup but only includes the absolute and relative margins.

Column	Description
date	Date represented as `YYYY-MM-DD`. Links to Dates semantic contract
gl_id	GL Account identifier. Links to GL Accounts semantic contract
gl_account	GL Account name at any hierarchy level. Links to GL Accounts semantic contract
value_type_id	Value Type identifier. Links to Value Type semantic contract
type	Value Type name. Links to Value Type semantic contract
amount	Aggregated amount including all child accounts

Sample & Implementation

PnL metrics derived from rolled-up GL accounts.
date	gl_id	gl_account	value_type_id	value_type	amount
2025-01-01	2001	cogs_pct	1	actuals	0.0425419403037417
2025-01-01	3000	gross_margin	1	actuals	13159474.0
2025-01-01	3001	gross_margin_pct	1	actuals	0.9574580596962583
2025-01-01	4001	commercial_costs_pct	1	actuals	0.016153021301092
2025-01-01	5000	contribution_margin	1	actuals	13159474.0
2025-01-01	5001	contribution_margin_pct	1	actuals	0.9574580596962583
2025-01-01	6001	fixed_costs_pct	1	actuals	0.022506038556834754
2025-01-01	7000	total_cost	1	actuals	1116041.0
2025-01-01	7001	total_cost_pct	1	actuals	0.08120100016166845
2025-01-01	8000	ebitda	1	actuals	13159474.0
2025-01-01	8001	ebitda_pct	1	actuals	0.9574580596962583

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

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

base_and_gross as (
    select
        date,
        3000 as gl_id,
        'gross_margin' as gl_account,
        value_type_id,
        value_type,
        sum(case when gl_id = 1000 then amount else 0 end) - sum(case when gl_id = 2000 then amount else 0 end) as amount
    from pnl_rollup
    group by date, value_type_id, value_type

    UNION ALL

    select
        date,
        7000 as gl_id,
        'total_cost' as gl_account,
        value_type_id,
        value_type,
        sum(case when gl_id = 2000 then amount else 0 end) + sum(case when gl_id = 4000 then amount else 0 end) + sum(case when gl_id = 6000 then amount else 0 end)
    from pnl_rollup
    group by date, value_type_id, value_type
),

base_and_gross_and_contribution as (
    select * from base_and_gross

    UNION ALL

    select
        date,
        5000 as gl_id,
        'contribution_margin' as gl_account,
        value_type_id,
        value_type,
        sum(case when gl_id = 3000 then amount else 0 end) - sum(case when gl_id = 4000 then amount else 0 end) as amount
    from base_and_gross
    group by date, value_type_id, value_type
),

base_ebitda as (
    select * from base_and_gross_and_contribution

    UNION ALL

    select
        date,
        8000 as gl_id,
        'ebitda' as gl_account,
        value_type_id,
        value_type,
        sum(case when gl_id = 5000 then amount else 0 end) - sum(case when gl_id = 6000 then amount else 0 end) as amount
    from base_and_gross_and_contribution
    group by date, value_type_id, value_type
),

-- Combine with pnl_rollup for percentage calculations
combined as (
    select * from pnl_rollup
    UNION ALL
    select * from base_ebitda
)

select * from base_ebitda

UNION ALL

-- Calculate all percentages at the end
select
    date,
    3001 as gl_id,
    'gross_margin_pct' as gl_account,
    value_type_id,
    value_type,
    COALESCE(max(case when gl_id = 3000 then amount end) / nullif(max(case when gl_id = 1000 then amount end), 0), 0) as amount
from combined
group by date, value_type_id, value_type

UNION ALL

select
    date,
    2001 as gl_id,
    'cogs_pct' as gl_account,
    value_type_id,
    value_type,
    COALESCE(max(case when gl_id = 2000 then amount end) / nullif(max(case when gl_id = 1000 then amount end), 0), 0) as amount
from combined
group by date, value_type_id, value_type

UNION ALL

select
    date,
    4001 as gl_id,
    'commercial_costs_pct' as gl_account,
    value_type_id,
    value_type,
    COALESCE(max(case when gl_id = 4000 then amount end) / nullif(max(case when gl_id = 1000 then amount end), 0), 0) as amount
from combined
group by date, value_type_id, value_type

UNION ALL

select
    date,
    6001 as gl_id,
    'fixed_costs_pct' as gl_account,
    value_type_id,
    value_type,
    COALESCE(max(case when gl_id = 6000 then amount end) / nullif(max(case when gl_id = 1000 then amount end), 0), 0) as amount
from combined
group by date, value_type_id, value_type

UNION ALL

select
    date,
    7001 as gl_id,
    'total_cost_pct' as gl_account,
    value_type_id,
    value_type,
    COALESCE(max(case when gl_id = 7000 then amount end) / nullif(max(case when gl_id = 1000 then amount end), 0), 0) as amount
from combined
group by date, value_type_id, value_type

UNION ALL

select
    date,
    5001 as gl_id,
    'contribution_margin_pct' as gl_account,
    value_type_id,
    value_type,
    COALESCE(max(case when gl_id = 5000 then amount end) / nullif(max(case when gl_id = 1000 then amount end), 0), 0) as amount
from combined
group by date, value_type_id, value_type

UNION ALL

select
    date,
    8001 as gl_id,
    'ebitda_pct' as gl_account,
    value_type_id,
    value_type,
    COALESCE(max(case when gl_id = 8000 then amount end) / nullif(max(case when gl_id = 1000 then amount end), 0), 0) as amount
from combined
group by date, value_type_id, value_type

order by value_type_id, date, gl_id

Dependencies

Transformations: PnL Rollup