Financial consolidation

Compute leaf revenue with different rules per legal entity, roll it up to Europe, and eliminate intercompany sales in the month they apply — one model, not a spreadsheet of one-off queries.

This advanced tutorial assumes the mappings, FOR bindings, and RELATED() model from E-commerce , plus persisted features from SaaS metrics . Run Data, Model, and the revenue library before the consolidation queries.

Data

Europe Ops has two leaves: France and Germany. Each leaf has its own revenue rules (include shipping? include returns?). Transactions are tagged by account; February also has two intercompany sales between the leaves.

FeatureQL

/* SQL */
CREATE SCHEMA IF NOT EXISTS tutorial_finance;
--
DROP TABLE IF EXISTS tutorial_finance.intercompany;
--
DROP TABLE IF EXISTS tutorial_finance.transactions;
--
DROP TABLE IF EXISTS tutorial_finance.revenue_rules;
--
DROP TABLE IF EXISTS tutorial_finance.entities;
--
CREATE TABLE tutorial_finance.entities (
  entity_id BIGINT,
  name VARCHAR,
  parent_id BIGINT,
  currency VARCHAR
);
--
INSERT INTO tutorial_finance.entities VALUES
  (1, 'Europe Ops', NULL, 'EUR'),
  (2, 'France Unit', 1, 'EUR'),
  (3, 'Germany Unit', 1, 'EUR');
--
CREATE TABLE tutorial_finance.revenue_rules (
  entity_id BIGINT,
  includes_shipping BOOLEAN,
  includes_returns BOOLEAN
);
--
INSERT INTO tutorial_finance.revenue_rules VALUES
  (2, false, true),
  (3, true, false);
--
CREATE TABLE tutorial_finance.transactions (
  tx_id BIGINT,
  tx_entity_id BIGINT,
  account VARCHAR,
  amount DOUBLE,
  tx_date DATE
);
--
INSERT INTO tutorial_finance.transactions VALUES
  (1, 2, 'gross_sales', 30000e0, DATE '2024-01-10'),
  (2, 2, 'shipping_revenue', 1500e0, DATE '2024-01-10'),
  (3, 2, 'returns', -1000e0, DATE '2024-01-25'),
  (4, 3, 'gross_sales', 40000e0, DATE '2024-01-12'),
  (5, 3, 'shipping_revenue', 2500e0, DATE '2024-01-12'),
  (6, 3, 'returns', -3000e0, DATE '2024-01-28'),
  (7, 2, 'gross_sales', 28000e0, DATE '2024-02-10'),
  (8, 2, 'returns', -800e0, DATE '2024-02-25'),
  (9, 3, 'gross_sales', 45000e0, DATE '2024-02-12'),
  (10, 3, 'shipping_revenue', 3000e0, DATE '2024-02-12'),
  (11, 3, 'returns', -2000e0, DATE '2024-02-28');
--
CREATE TABLE tutorial_finance.intercompany (
  interco_id BIGINT,
  from_entity_id BIGINT,
  to_entity_id BIGINT,
  account VARCHAR,
  amount DOUBLE,
  tx_date DATE
);
--
INSERT INTO tutorial_finance.intercompany VALUES
  (1, 2, 3, 'interco_sale', 8000e0, DATE '2024-02-15'),
  (2, 3, 2, 'interco_sale', 6000e0, DATE '2024-02-20');
--
SELECT CAST(COUNT(*) AS INTEGER) AS cnt FROM tutorial_finance.transactions;

Result

Count BIGINT
11

France excludes shipping and includes returns. Germany includes shipping and excludes returns. That difference is data — not three separate formulas.

Model

FeatureQL

CREATE OR REPLACE FEATURES IN FM.FINANCE AS
SELECT
    fc_entities := ENTITY(),
    entity_id := INPUT(BIGINT#fc_entities),
    transactions := ENTITY(),
    tx_id := INPUT(BIGINT#transactions),
    interco := ENTITY(),
    interco_id := INPUT(BIGINT#interco),
    month_input := INPUT(VARCHAR)
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.FINANCE.FC_ENTITIES	CREATED	Feature created as not exists
FM.FINANCE.ENTITY_ID	CREATED	Feature created as not exists
FM.FINANCE.TRANSACTIONS	CREATED	Feature created as not exists
FM.FINANCE.TX_ID	CREATED	Feature created as not exists
FM.FINANCE.INTERCO	CREATED	Feature created as not exists
FM.FINANCE.INTERCO_ID	CREATED	Feature created as not exists
FM.FINANCE.MONTH_INPUT	CREATED	Feature created as not exists

Map entities, rules, transactions, and intercompany rows:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.FINANCE AS
SELECT
    tables.dim_entities := EXTERNAL_COLUMNS(
        entity_id BIGINT#fc_entities BIND TO entity_id,
        name VARCHAR,
        parent_id BIGINT,
        currency VARCHAR
        FROM TABLE(tutorial_finance.entities)
    ),
    tables.rules := EXTERNAL_COLUMNS(
        entity_id BIGINT#fc_entities BIND TO entity_id,
        includes_shipping BOOLEAN,
        includes_returns BOOLEAN
        FROM TABLE(tutorial_finance.revenue_rules)
    ),
    tables.fct_tx := EXTERNAL_COLUMNS(
        tx_id BIGINT#transactions BIND TO tx_id,
        tx_entity_id BIGINT#fc_entities,
        account VARCHAR,
        amount DOUBLE,
        tx_date DATE
        FROM TABLE(tutorial_finance.transactions)
    ),
    tables.interco := EXTERNAL_COLUMNS(
        interco_id BIGINT#interco BIND TO interco_id,
        from_entity_id BIGINT#fc_entities,
        to_entity_id BIGINT#fc_entities,
        account VARCHAR,
        amount DOUBLE,
        tx_date DATE
        FROM TABLE(tutorial_finance.intercompany)
    )
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.FINANCE.TABLES.DIM_ENTITIES	CREATED	Feature created as not exists
FM.FINANCE.TABLES.RULES	CREATED	Feature created as not exists
FM.FINANCE.TABLES.FCT_TX	CREATED	Feature created as not exists
FM.FINANCE.TABLES.INTERCO	CREATED	Feature created as not exists

Persist the fields the metrics reuse — including a MONTH_INPUT parameter so one revenue definition works for any month:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.FINANCE AS
SELECT
    entity_name := tables.dim_entities[name],
    includes_shipping := tables.rules[includes_shipping],
    includes_returns := tables.rules[includes_returns],
    tx_account := tables.fct_tx[account],
    tx_amount := tables.fct_tx[amount],
    tx_month := DATE_FORMAT(CAST(tables.fct_tx[tx_date] AS TIMESTAMP), '%Y-%m'),
    tx_entity_id := tables.fct_tx[tx_entity_id],
    ic_month := DATE_FORMAT(CAST(tables.interco[tx_date] AS TIMESTAMP), '%Y-%m')
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.FINANCE.ENTITY_NAME	CREATED	Feature created as not exists
FM.FINANCE.INCLUDES_SHIPPING	CREATED	Feature created as not exists
FM.FINANCE.INCLUDES_RETURNS	CREATED	Feature created as not exists
FM.FINANCE.TX_ACCOUNT	CREATED	Feature created as not exists
FM.FINANCE.TX_AMOUNT	CREATED	Feature created as not exists
FM.FINANCE.TX_MONTH	CREATED	Feature created as not exists
FM.FINANCE.TX_ENTITY_ID	CREATED	Feature created as not exists
FM.FINANCE.IC_MONTH	CREATED	Feature created as not exists

Leaf revenue for January

Gross sales, plus shipping/returns only when that entity’s rules say so.

FeatureQL

WITH
    GROSS := COALESCE(ENTITY_ID.RELATED(
        SUM(TX_AMOUNT) FILTER (WHERE TX_ACCOUNT = 'gross_sales' AND TX_MONTH = MONTH_INPUT)
        GROUP BY TX_ENTITY_ID
    ), 0e0),
    SHIPPING := COALESCE(ENTITY_ID.RELATED(
        SUM(TX_AMOUNT) FILTER (WHERE TX_ACCOUNT = 'shipping_revenue' AND TX_MONTH = MONTH_INPUT)
        GROUP BY TX_ENTITY_ID
    ), 0e0),
    RETURNS_AMT := COALESCE(ENTITY_ID.RELATED(
        SUM(TX_AMOUNT) FILTER (WHERE TX_ACCOUNT = 'returns' AND TX_MONTH = MONTH_INPUT)
        GROUP BY TX_ENTITY_ID
    ), 0e0),
    REVENUE := GROSS
        + IF(INCLUDES_SHIPPING, SHIPPING, 0e0)
        + IF(INCLUDES_RETURNS, RETURNS_AMT, 0e0),
SELECT
    ENTITY_NAME,
    REVENUE
FROM FM.FINANCE
FOR
    MONTH_INPUT := BIND_VALUE('2024-01'),
    ENTITY_ID := BIND_VALUES(ARRAY[2, 3]),
    NESTED TX_ID := BIND_COLUMNS(tx_id FROM SQL(SELECT tx_id FROM tutorial_finance.transactions ORDER BY tx_id))
ORDER BY ENTITY_NAME;

Result

ENTITY_NAME VARCHAR	REVENUE DECIMAL
France Unit	29000.0
Germany Unit	42500.0

France 29000 EUR (30000 − 1000 returns; shipping ignored). Germany 42500 EUR (40000 + 2500 shipping; returns ignored).

Same definition, February

Only the bound month changes.

FeatureQL

WITH
    GROSS := COALESCE(ENTITY_ID.RELATED(
        SUM(TX_AMOUNT) FILTER (WHERE TX_ACCOUNT = 'gross_sales' AND TX_MONTH = MONTH_INPUT)
        GROUP BY TX_ENTITY_ID
    ), 0e0),
    SHIPPING := COALESCE(ENTITY_ID.RELATED(
        SUM(TX_AMOUNT) FILTER (WHERE TX_ACCOUNT = 'shipping_revenue' AND TX_MONTH = MONTH_INPUT)
        GROUP BY TX_ENTITY_ID
    ), 0e0),
    RETURNS_AMT := COALESCE(ENTITY_ID.RELATED(
        SUM(TX_AMOUNT) FILTER (WHERE TX_ACCOUNT = 'returns' AND TX_MONTH = MONTH_INPUT)
        GROUP BY TX_ENTITY_ID
    ), 0e0),
    REVENUE := GROSS
        + IF(INCLUDES_SHIPPING, SHIPPING, 0e0)
        + IF(INCLUDES_RETURNS, RETURNS_AMT, 0e0),
SELECT
    ENTITY_NAME,
    REVENUE
FROM FM.FINANCE
FOR
    MONTH_INPUT := BIND_VALUE('2024-02'),
    ENTITY_ID := BIND_VALUES(ARRAY[2, 3]),
    NESTED TX_ID := BIND_COLUMNS(tx_id FROM SQL(SELECT tx_id FROM tutorial_finance.transactions ORDER BY tx_id))
ORDER BY ENTITY_NAME;

Result

ENTITY_NAME VARCHAR	REVENUE DECIMAL
France Unit	27200.0
Germany Unit	48000.0

France 27200, Germany 48000.

Europe consolidated — January

Sum the two leaves. No intercompany sales in January, so nothing to eliminate.

FeatureQL

WITH
    gross := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'gross_sales' AND tx_month = month_input)
            GROUP BY tx_entity_id
        ),
        0e0
    ),
    shipping := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'shipping_revenue'
            AND tx_month = month_input)
            GROUP BY tx_entity_id
        ),
        0e0
    ),
    returns_amt := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'returns' AND tx_month = month_input)
            GROUP BY tx_entity_id
        ),
        0e0
    ),
    revenue := gross + IF(includes_shipping, shipping, 0e0)
    + IF(includes_returns, returns_amt, 0e0),
    consolidated_revenue_eur := ARRAY_SUM(ARRAY_AGG(revenue))
SELECT
    consolidated_revenue_eur
FROM FM.FINANCE
FOR
    month_input := BIND_VALUE('2024-01'),
    entity_id := BIND_VALUES(ARRAY(2, 3)),
    NESTED tx_id := BIND_COLUMNS(
        tx_id
        FROM SQL(SELECT tx_id FROM tutorial_finance.transactions ORDER BY tx_id)
    )
;

Result

CONSOLIDATED_REVENUE_EUR DECIMAL
71500.0

71500 EUR.

Europe consolidated — February with eliminations

Same leaf revenue, minus intercompany sales booked between France and Germany (8000 + 6000).

FeatureQL

WITH
    gross := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'gross_sales' AND tx_month = month_input)
            GROUP BY tx_entity_id
        ),
        0e0
    ),
    shipping := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'shipping_revenue'
            AND tx_month = month_input)
            GROUP BY tx_entity_id
        ),
        0e0
    ),
    returns_amt := COALESCE(
        entity_id.RELATED(
            SUM(tx_amount)
            FILTER (WHERE tx_account = 'returns' AND tx_month = month_input)
            GROUP BY tx_entity_id
        ),
        0e0
    ),
    revenue := gross + IF(includes_shipping, shipping, 0e0)
    + IF(includes_returns, returns_amt, 0e0),
    interco_elim := COALESCE(
        entity_id.RELATED(
            SUM(tables.interco[amount])
            FILTER (WHERE tables.interco[account] = 'interco_sale'
            AND ic_month = month_input)
            GROUP BY tables.interco[to_entity_id]
        ),
        0e0
    ),
    consolidated_revenue_eur := SUM(revenue - interco_elim)
SELECT
    consolidated_revenue_eur
FROM FM.FINANCE
FOR CROSS
    month_input := BIND_VALUE('2024-02'),
    entity_id := BIND_VALUES(ARRAY(2, 3)),
    NESTED tx_id := BIND_COLUMNS(
        tx_id
        FROM SQL(SELECT tx_id FROM tutorial_finance.transactions ORDER BY tx_id)
    ),
    NESTED interco_id := BIND_COLUMNS(
        interco_id
        FROM SQL(SELECT interco_id FROM tutorial_finance.intercompany ORDER BY interco_id)
    )
;

Result

CONSOLIDATED_REVENUE_EUR DECIMAL
61200.0

75200 − 14000 = 61200 EUR.

What's next

Marketing attribution — first / last / linear credit models
Supply inventory — as-of stock from signed events
Analytics overview — concept map for this series

Last update at: 2026/08/04 11:25:38

On this page

Data Model Leaf revenue for January Same definition, February Europe consolidated — January Europe consolidated — February with eliminations What's next