Why Marketing Dashboards Break When Spend Is Modeled at the Wrong Grain

A marketing dashboard can load correctly while its totals are wrong. Spend, sessions, orders, order lines, and CRM events often describe different grains. Joining them without controlling that grain can multiply values.

A useful first step is to state what one row represents in every input and output.

A synthetic example of duplicated spend

Suppose a campaign has one spend row for a day and three orders. A join produces three rows containing the same daily spend.

This query is intentionally incorrect:

-- Synthetic table names and example only.
SELECT
  s.campaign_id,
  s.report_date,
  SUM(s.spend) AS spend,
  SUM(o.revenue) AS revenue
FROM campaign_daily_spend s
LEFT JOIN orders o
  ON s.campaign_id = o.attributed_campaign_id
 AND s.report_date = o.order_date
GROUP BY 1, 2;

If daily spend is 100 and there are three matching orders, the query reports spend of 300. A different join can duplicate revenue instead. The direction of the error depends on the grains and aggregations; it is not a universal ROAS multiplier.

These figures are examples, not client results.

Aggregate to the reporting question first

A campaign/day view needs one compatible row per campaign/day on each joined side:

WITH orders_by_campaign_date AS (
  SELECT attributed_campaign_id AS campaign_id,
         order_date AS report_date,
         COUNT(DISTINCT order_id) AS orders,
         SUM(revenue) AS revenue
  FROM order_level_reporting
  GROUP BY 1, 2
)
SELECT s.campaign_id, s.report_date, s.spend, o.orders, o.revenue
FROM campaign_daily_spend s
LEFT JOIN orders_by_campaign_date o
  USING (campaign_id, report_date);

This assumes the order input already has one row per order and a defined attribution mapping. If it contains order lines or repeated order events, resolve those first.

Counting distinct orders does not repair duplicated spend or revenue elsewhere in the join.

Keep different measures distinct

Platform conversions, attributed conversion value, transaction revenue, and CRM pipeline answer different questions. They should not acquire a shared label just because the dashboard can display them together.

Date/timezone, currency, refunds, attribution settings, and filters also need comparable definitions. A matching grand total can hide errors within accounts or dates.

What I check before trusting the output

• Uniqueness at the declared input and output grain.
• Source totals before and after the joins.
• Row counts and unmatched identifiers.
• Account/date scope and timestamp interpretation.
• The definitions behind every revenue and conversion measure.
• Missing source data, separately from genuine zero activity.

Raw → staging → marts makes these checks easier to locate. A dashboard should read the prepared reporting layer rather than reimplementing every source join.

The marketing dashboard case describes ingestion and reporting work behind an interface. The Dataform warehouse case shows a separate modeling engagement.

Repair the reporting layer before expanding it

A focused engagement can trace one disputed metric from the screen through its query and inputs, explain the discrepancy, and validate a bounded repair.

Discuss your reporting needs.