Our engine never sees your warehouse password
datahashi

BlogModeling & metrics

Why marketing CAC and ROAS never matched finance

How governed derived metrics and transparent lineage unify customer acquisition cost and return on ad spend across marketing channels and finance.

The datahashi team9 min read

The problem

At the end of every month, growth marketing and finance present conflicting numbers. In this sample data, ad platforms report siloed conversions while unified warehouse records show true channel acquisition costs of 9000 for Paid Search, 8750 for Paid Social, and 2750 for Affiliate Network. Without a shared semantic model, teams argue over contradictory ratios.

The disagreement stems from fragmented definitions across advertising platforms. Ad networks claim credit for overlapping conversion windows. Marketing analysts calculate CAC by dividing blended channel spend by top-of-funnel signup leads. Finance calculates CAC by dividing raw ledger invoices by verified paying customer accounts. Neither team can trace how the other constructed their numbers.

You need a central semantic model that defines certified ad spend, acquired customers, and return on ad spend. You also need dependency lineage so marketing and finance inspect the exact upstream measures behind every ratio.

+--------------------+        +---------------------+
| Ad Network Reports |        | Finance Board Deck  |
| (Channel Silos)    |        | (General Ledger)    |
+--------------------+        +---------------------+
          |                              |
          +--------------+---------------+
                         |
           Conflicting calculations ✗
                         |
                         v
           Separate data silos & ad tools

The data

The warehouse model stores advertising channels, daily campaign spend records, and customer conversion transactions. Each spend record links to a marketing channel. Each conversion captures the attributed channel, the customer identifier, and transaction revenue.

+--------------------+              +----------------------+
|      channels      |              |   campaign_spends    |
+--------------------+              +----------------------+
| channel_id (PK)    |<---+         | spend_id (PK)        |
| channel_name       |    +---------| channel_id (FK)      |
+--------------------+    |         | spend_date           |
          ^               |         | cost                 |
          |               |         +----------------------+
          |               |
          |               |         +----------------------+
          |               |         |     conversions      |
          |               |         +----------------------+
          |               +---------| conversion_id (PK)   |
          +-------------------------| channel_id (FK)      |
                                    | customer_id          |
                                    | converted_at         |
                                    | revenue              |
                                    +----------------------+

Here is the SQL DDL and sample data runnable directly in DuckDB:

CREATE TABLE channels (
    channel_id VARCHAR PRIMARY KEY,
    channel_name VARCHAR NOT NULL
);

CREATE TABLE campaign_spends (
    spend_id VARCHAR PRIMARY KEY,
    channel_id VARCHAR NOT NULL REFERENCES channels(channel_id),
    spend_date DATE NOT NULL,
    cost DOUBLE NOT NULL
);

CREATE TABLE conversions (
    conversion_id VARCHAR PRIMARY KEY,
    channel_id VARCHAR NOT NULL REFERENCES channels(channel_id),
    customer_id VARCHAR NOT NULL,
    converted_at DATE NOT NULL,
    revenue DOUBLE NOT NULL
);

INSERT INTO channels VALUES
    ('search', 'Paid Search'),
    ('social', 'Paid Social'),
    ('affiliate', 'Affiliate Network');

INSERT INTO campaign_spends VALUES
    ('sp_01', 'search', '2026-10-01', 12000.0),
    ('sp_02', 'search', '2026-10-02', 15000.0),
    ('sp_03', 'social', '2026-10-01', 8000.0),
    ('sp_04', 'social', '2026-10-02', 9500.0),
    ('sp_05', 'affiliate', '2026-10-01', 3000.0),
    ('sp_06', 'affiliate', '2026-10-02', 2500.0);

INSERT INTO conversions VALUES
    ('cnv_01', 'search', 'cust_101', '2026-10-01', 32000.0),
    ('cnv_02', 'search', 'cust_102', '2026-10-01', 18000.0),
    ('cnv_03', 'search', 'cust_103', '2026-10-02', 45000.0),
    ('cnv_04', 'social', 'cust_201', '2026-10-01', 12000.0),
    ('cnv_05', 'social', 'cust_202', '2026-10-02', 14000.0),
    ('cnv_06', 'affiliate', 'cust_301', '2026-10-01', 6000.0),
    ('cnv_07', 'affiliate', 'cust_302', '2026-10-02', 7500.0);

The model

The model defines three entities: Channel, CampaignSpend, and Conversion. Both fact entities declare many-to-one relationships to the shared Channel entity.

The metrics section introduces derived metrics and multi-stage metrics. The metric cac divides total_spend by new_customers. The metric roas calculates return on advertising spend by dividing total_revenue by total_spend. The multi-stage metric avg_revenue_per_customer uses avg_per(gross_revenue, customer_id) to execute a two-pass aggregation.

source: warehouse_main

entities:
  - name: Channel
    table: channels
    primary_key: channel_id
    dimensions:
      - name: channel_id
        column: channel_id
        type: string
      - name: channel_name
        column: channel_name
        type: string

  - name: CampaignSpend
    table: campaign_spends
    primary_key: spend_id
    dimensions:
      - name: spend_id
        column: spend_id
        type: string
      - name: channel_id
        column: channel_id
        type: string
      - name: spend_date
        column: spend_date
        type: date
        grains: [day, month, quarter, year]
    measures:
      - name: spend
        expr: cost
        agg: sum

  - name: Conversion
    table: conversions
    primary_key: conversion_id
    dimensions:
      - name: conversion_id
        column: conversion_id
        type: string
      - name: channel_id
        column: channel_id
        type: string
      - name: customer_id
        column: customer_id
        type: string
      - name: converted_at
        column: converted_at
        type: date
        grains: [day, month, quarter, year]
    measures:
      - name: gross_revenue
        expr: revenue
        agg: sum
      - name: customer_count
        expr: conversion_id
        agg: count

relationships:
  - name: spend_channel
    from: CampaignSpend
    to: Channel
    cardinality: many_to_one
    keys:
      - [channel_id, channel_id]

  - name: conversion_channel
    from: Conversion
    to: Channel
    cardinality: many_to_one
    keys:
      - [channel_id, channel_id]

metrics:
  - name: total_spend
    expr: spend
    description: "Total advertising cost"
    unit: USD
    format: currency
  - name: total_revenue
    expr: gross_revenue
    description: "Total attributed gross revenue"
    unit: USD
    format: currency
  - name: new_customers
    expr: customer_count
    description: "Count of conversion rows"
  - name: cac
    expr: total_spend / new_customers
    description: "Customer acquisition cost"
    unit: USD
    format: currency
  - name: roas
    expr: total_revenue / total_spend
    description: "Return on advertising spend ratio"
    format: decimal
  - name: avg_revenue_per_customer
    expr: avg_per(gross_revenue, customer_id)
    description: "Average gross revenue per customer across conversion records"
    unit: USD
    format: currency

Notice the semantic distinction between measures and metrics:

  • A measure is a raw aggregate computed in a single SQL pass over a physical entity table. Examples include spend and gross_revenue.
  • A derived metric is arithmetic evaluated over existing measures or metrics after aggregation passes complete. The cac and roas metrics combine spend and revenue across distinct fact tables.
  • A multi-stage metric aggregates an aggregate. The leaf avg_per(gross_revenue, customer_id) first sums revenue per customer, then computes the mean across all customers.

For full syntax reference, consult the model specification. For details on fan-out prevention across multi-fact schemas, read the breakdown of fan-out safe metrics.

The queries

Clients send requests adhering to the standard JSON API defined in the query specification. Where a request sets include_sql, the engine returns compiled SQL only if it was started with ENGINE_ALLOW_SQL=true. MCP never returns SQL.

Query 1: Channel-level spend, CAC, and ROAS

The hero query requests total spend, new customers, CAC, and ROAS broken down by marketing channel name:

{
  "metrics": ["total_spend", "new_customers", "cac", "roas"],
  "dimensions": [
    {
      "name": "channel_name",
      "entity": "Channel"
    }
  ],
  "order": [
    { "ref": "total_spend", "desc": true }
  ],
  "include_sql": true
}

The gateway evaluates both fact sources and combines them across channels:

channel_name total_spend new_customers cac roas
Paid Search 27000 3 9000 3.5185
Paid Social 17500 2 8750 1.4857
Affiliate Network 5500 2 2750 2.4545

When compiled, the engine avoids joining campaign_spends directly to conversions. Direct joins between independent fact tables cause cartesian row multiplication. Instead, the engine groups each fact table independently in subqueries q0 and q1, then merges them using a FULL OUTER JOIN:

SELECT
  COALESCE(q0."channel_name", q1."channel_name") AS "channel_name",
  CAST(q0."spend" AS DOUBLE) AS "total_spend",
  CAST(q1."customer_count" AS DOUBLE) AS "new_customers",
  (CAST(q0."spend" AS DOUBLE) / NULLIF(CAST(q1."customer_count" AS DOUBLE), 0)) AS "cac",
  (CAST(q1."gross_revenue" AS DOUBLE) / NULLIF(CAST(q0."spend" AS DOUBLE), 0)) AS "roas"
FROM (
  SELECT
    "Channel"."channel_name" AS "channel_name",
    CAST(SUM("CampaignSpend"."cost") AS DOUBLE) AS "spend"
  FROM "campaign_spends" AS "CampaignSpend"
  JOIN "channels" AS "Channel"
    ON "CampaignSpend"."channel_id" = "Channel"."channel_id"
  GROUP BY "Channel"."channel_name"
) AS q0
FULL OUTER JOIN (
  SELECT
    "Channel"."channel_name" AS "channel_name",
    COUNT("Conversion"."conversion_id") AS "customer_count",
    CAST(SUM("Conversion"."revenue") AS DOUBLE) AS "gross_revenue"
  FROM "conversions" AS "Conversion"
  JOIN "channels" AS "Channel"
    ON "Conversion"."channel_id" = "Channel"."channel_id"
  GROUP BY "Channel"."channel_name"
) AS q1
  ON q0."channel_name" = q1."channel_name"
ORDER BY "total_spend" DESC

Both teams now inspect identical numbers. Division by zero is protected with NULLIF, avoiding runtime warehouse query crashes when a channel has zero conversions.

Query 2: Multi-stage average revenue per customer

Marketing teams also want to know customer quality by channel. Simply dividing total revenue by total conversions computes the average order value, not the average revenue per customer. The multi-stage metric avg_revenue_per_customer executes a two-pass calculation:

{
  "metrics": ["avg_revenue_per_customer"],
  "dimensions": [
    {
      "name": "channel_name",
      "entity": "Channel"
    }
  ],
  "order": [
    { "ref": "avg_revenue_per_customer", "desc": true }
  ],
  "include_sql": true
}

The engine returns the true customer average across each acquisition channel:

channel_name avg_revenue_per_customer
Paid Search 31666.67
Paid Social 13000.00
Affiliate Network 6750.00

Here is the compiled two-pass SQL:

SELECT
  q0."channel_name" AS "channel_name",
  CAST(q0."__nested_avg_per_customer_id__.gross_revenue" AS DOUBLE) AS "avg_revenue_per_customer"
FROM (
  SELECT
    "channel_name",
    CAST(AVG("__nested_avg_per_customer_id__.gross_revenue") AS DOUBLE) AS "__nested_avg_per_customer_id__.gross_revenue"
  FROM (
    SELECT
      "Channel"."channel_name" AS "channel_name",
      "Conversion"."customer_id" AS "__hashi_nest_key",
      CAST(SUM("Conversion"."revenue") AS DOUBLE) AS "__nested_avg_per_customer_id__.gross_revenue"
    FROM "conversions" AS "Conversion"
    JOIN "channels" AS "Channel"
      ON "Conversion"."channel_id" = "Channel"."channel_id"
    WHERE "Conversion"."customer_id" IS NOT NULL
    GROUP BY "Channel"."channel_name", "Conversion"."customer_id"
  ) AS "Conversion"
  GROUP BY "channel_name"
) AS q0
ORDER BY "avg_revenue_per_customer" DESC

The inner subquery groups by channel and customer identifier, summing each customer’s spend. The outer query averages those customer totals per channel.

Query 3: Inspecting metric lineage

When a finance analyst asks how cac was computed, they query the lineage endpoint at POST /v1/lineage:

{
  "node": "metric:cac",
  "direction": "upstream"
}

The engine inspects the validated model graph and returns all upstream dependencies:

{
  "version": "m1:bda62c35eaabc2327e382f610a96b792211c076dbe538ad1ccdd43e9bb55e40b",
  "node": {
    "id": "metric:cac",
    "kind": "metric",
    "name": "cac"
  },
  "upstream": [
    {
      "id": "entity:CampaignSpend",
      "kind": "entity",
      "name": "CampaignSpend"
    },
    {
      "id": "entity:Conversion",
      "kind": "entity",
      "name": "Conversion"
    },
    {
      "id": "measure:CampaignSpend.spend",
      "kind": "measure",
      "name": "spend",
      "entity": "CampaignSpend"
    },
    {
      "id": "measure:Conversion.customer_count",
      "kind": "measure",
      "name": "customer_count",
      "entity": "Conversion"
    }
  ]
}

The lineage trace proves to finance and marketing that cac depends on CampaignSpend.spend and Conversion.customer_count.

Query 4: Refusal for unmodeled marketing metric

When an ad-hoc report requests an arbitrary metric that does not exist in the governed model, the engine refuses the query:

{
  "metrics": ["blended_marketing_roi"]
}

The server returns HTTP 404:

{
  "error": "model: resolve: metric \"blended_marketing_roi\" is not exposed by the named view",
  "code": "not_found",
  "reason": "not_in_view",
  "ref": "blended_marketing_roi"
}

The query fails before generating any database queries. Unverified metrics cannot pollute official reporting.

What to notice

  • Isolated multi-fact aggregation. Queries referencing measures across distinct fact tables never produce runaway join explosions. The engine groups each fact table independently before combining them at the shared dimension grain.
  • Auditable lineage paths. The POST /v1/lineage endpoint traces dependencies programmatically. Analysts inspect exactly which raw measures and entities produce each top-line ratio.
  • Two-pass metric expressiveness. The avg_per multi-stage metric syntax handles complex calculations like average revenue per customer. Authors avoid writing intricate nested subqueries by hand.
  • Division safety. Derived metric expressions automatically guard division using NULLIF. When conversion counts drop to zero in a specific channel, the metric evaluates to null instead of halting execution.
  • Attribute sources for external claims. When regional marketing teams access spend data, their corporate tokens rarely contain specific campaign channel entitlements. The engine resolves user permissions via HTTP attribute sources configured on the auth binding, securing row access policies.

For more modeling examples, see the guide on advanced modeling and the tutorial on building your first metric in DuckDB.

Limits

  • Additive measure requirements across facts. When derived metrics compose measures from multiple fact tables, those measures must be additive. Combining a non-additive measure like distinct customer count across a fan-out boundary returns a compilation error.
  • Two-pass multi-stage ceiling. The multi-stage metric feature supports two passes over a single inner dimension key. Complex multi-step marketing funnels, cohort retention curves, and rolling sequence windows require transformed tables in the warehouse.
  • Attribute sources supply claims, not policy logic. The attribute_sources HTTP adapter fetches claim values (such as allowed channel lists) for an authenticated principal. It never evaluates custom SQL predicates or decision rules; the engine’s compiled policy bundle evaluates all access logic.
  • Warehouse attribution logic. The semantic layer evaluates metrics based on existing attributed rows. Multi-touch attribution modeling, sessionization, and clickstream attribution windows must be processed upstream in warehouse transformation pipelines.

Product notes

An occasional email when we ship something or write something worth reading.