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

Marketing dashboards usually fail quietly. The chart loads, the table filters, and the totals look precise. The problem is that spend, clicks, conversions, orders, CRM events, and revenue often live at different grains.

If those grains are joined too early, the dashboard can duplicate spend, inflate revenue, or make attribution look cleaner than the source data really is.

The grain problem

Ad platforms report performance at campaign, ad set, ad, creative, keyword, placement, date, or device level. Ecommerce and CRM systems report at order, customer, lead, subscription, or invoice level.

Those are not interchangeable.

A common failure pattern looks like this:

1. Spend is pulled at campaign-date level.
2. Orders are pulled at order line level.
3. CRM events are pulled at lead or contact level.
4. Everything is joined on campaign name, date, or a UTM field.
5. The dashboard shows duplicated spend or revenue because the join expanded one side.

The system looks finished, but the business question is no longer trustworthy.

A concrete example

Say you have Meta Ads spend at campaign_id + date grain, and an orders table where each campaign can have many orders on the same date.

A naive join looks like this:

SELECT
  s.campaign_id,
  s.date,
  s.spend,
  SUM(o.revenue) AS revenue
FROM spend s
LEFT JOIN orders o
  ON s.campaign_id = o.attributed_campaign_id
  AND s.date = o.order_date
GROUP BY 1, 2, 3

If a campaign generated 8 orders on a given date, the spend value in s gets repeated 8 times before the GROUP BY collapses it. The grouped spend now equals the original value. Looks fine.

But add one more join — say, GA4 sessions at campaign_id + date + session_source — and you can end up with a cartesian expansion where spend or revenue gets multiplied by the session count.

I have seen dashboards where reported ROAS was 4x the actual value because of a three-way join at the wrong grain. The chart showed a number. The number was wrong. No error was thrown.

The three joins that cause most of the damage

1. Spend joined to order line items Orders tables often have one row per SKU, not per order. Joining spend to order lines duplicates spend once per line item. Fix: aggregate orders to order-date grain before joining.

2. Ad performance joined to GA4 sessions GA4 sessions can have multiple rows per campaign per day (by device, source/medium, landing page). Joining without aggregating first multiplies ad spend. Fix: pre-aggregate sessions to campaign + date before the join.

3. CRM events joined without a deduplication step Lead creation, qualification, and close events all live in the same CRM events table. A pipeline that pulls all events and joins on campaign can count the same lead multiple times. Fix: use ROW_NUMBER() or a QUALIFY step to select one event per lead per stage before joining.

How I model it

For marketing warehouse work, I use explicit layers:

• Raw source tables keep the platform shape intact.
• Staging models clean naming, dates, types, and identifiers.
• Intermediate models resolve source-specific grain.
• Marts expose business-level metrics at one declared grain.
• BI tables are built for the exact dashboard question.

In Dataform or dbt, every reporting model should answer: one row represents what?

-- mart__campaign_daily_performance.sqlx
-- GRAIN: one row per campaign_id + date
-- SPEND source: meta_ads_staging (already at campaign-date grain)
-- ORDERS source: orders_staging aggregated to campaign-date in intermediate layer
-- SESSIONS source: ga4_sessions_staging aggregated to campaign-date in intermediate layer
 
SELECT
  c.campaign_id,
  c.date,
  c.spend,
  c.impressions,
  c.clicks,
  COALESCE(o.orders, 0) AS orders,
  COALESCE(o.revenue, 0) AS revenue,
  COALESCE(s.sessions, 0) AS sessions
FROM campaign_daily c
LEFT JOIN orders_by_campaign_date o USING (campaign_id, date)
LEFT JOIN sessions_by_campaign_date s USING (campaign_id, date)

The intermediate aggregation happens before the mart join. The mart never sees order-line or session-level rows.

How I model it

For marketing warehouse work, I prefer explicit layers:

• Raw source tables keep the platform shape intact.
• Staging models clean naming, dates, types, and identifiers.
• Intermediate models resolve source-specific grain.
• Marts expose business-level metrics at one declared grain.
• BI tables are built for the exact dashboard question.

In Dataform or dbt-style projects, this means every reporting model should answer a simple question: one row represents what?

If that answer is unclear, the dashboard is not ready.

Checks I add before BI

The most useful checks are simple to write and catch the majority of grain problems before they reach stakeholders.

In Dataform, these are ASSERT blocks that run at build time:

-- assert: no duplicate campaign-date keys in spend mart
SELECT campaign_id, date, COUNT(*) AS n
FROM ${ref('mart__campaign_daily_performance')}
GROUP BY 1, 2
HAVING n > 1

-- assert: modeled spend reconciles to platform export within 1%
SELECT
  ABS(SUM(mart_spend) - SUM(raw_spend)) / NULLIF(SUM(raw_spend), 0) AS pct_diff
FROM (
  SELECT SUM(spend) AS mart_spend FROM ${ref('mart__campaign_daily_performance')}
    WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
),
(
  SELECT SUM(spend) AS raw_spend FROM ${ref('stg__meta_ads_spend')}
    WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
)
HAVING pct_diff > 0.01

-- assert: joining to orders does not change campaign count
-- (a cartesian join would produce more campaign rows)
SELECT COUNT(DISTINCT campaign_id) AS n FROM ${ref('mart__campaign_daily_performance')}
HAVING n != (SELECT COUNT(DISTINCT campaign_id) FROM ${ref('stg__meta_ads_spend')})

In dbt, the same patterns appear as dbt test singular tests or store_failures: true generic tests.

These are not decoration. They are the difference between a dashboard that looks right and one that is right.

Attribution windows add another layer

One more grain issue that catches people: attribution windows.

Platforms apply different lookback windows (1-day click, 7-day click, 28-day click, view-through). If your spend table and your orders table use different attribution windows, comparing ROAS across them is comparing different definitions of the same conversion.

The fix is to make the attribution window a visible part of the model:

-- mart__campaign_performance__7d_click.sqlx
-- Attribution: 7-day click window
-- Do NOT join with last-touch CRM conversions without aligning windows

When the window is in the model name or a column, analysts cannot accidentally mix attribution definitions.

Where this shows up in real work

This pattern comes up in almost every marketing warehouse engagement. The symptom is usually a BI tool showing numbers that do not match what the ad platform shows, or a spend reconciliation check that keeps failing.

The architecture that solves it:

Sources → Staging (clean, typed, grain-preserving)
       → Intermediate (grain-resolved per domain)
       → Marts (one declared grain per reporting question)
       → BI tables (built for the exact dashboard)

Staging preserves source shape. Intermediates resolve grain for each domain (ads, orders, sessions, CRM). Marts join pre-resolved grains. BI tables serve specific dashboard queries.

Each layer has a documented grain. Any model without a documented grain is a model waiting to cause a problem.

How to audit an existing dashboard for grain problems

If you already have a dashboard and numbers do not look right, here are the checks worth running before assuming the data is just wrong:

Check 1: Count rows at each join point Run each source query independently and count rows. Then run the join and count again. If the row count after the join is higher than the highest input count, a join is expanding rows.

Check 2: Sum a known value before and after joining Take total spend from the raw platform export. Sum it in your mart. If the numbers differ by more than 1%, the model has a grain problem.

Check 3: Check for duplicate grain keys Run a GROUP BY on the declared grain columns and look for COUNT(*) > 1. One duplicate in a campaign-date mart means every metric for that campaign-date is wrong.

Check 4: Look at the join conditions If a join uses a text field (campaign name, UTM string, product category) rather than a stable ID, it is vulnerable to naming inconsistencies and case mismatches. These fail silently and produce gaps, not errors.

A better dashboard brief

Before building a dashboard, I want answers to these:

• What decision should this dashboard support?
• What is the reporting grain?
• Which source owns each metric?
• Which numbers must reconcile back to source exports?
• Which joins can multiply rows?
• Which attribution window applies to each conversion metric?
• Which metric definitions need to be shared across reports?

That brief saves most of the rework.

Related project

The Dataform + BigQuery Marketing Analytics Warehouse case study covers how these patterns were applied in a production warehouse with GA4, Google Ads, Salesforce CRM, and Looker Studio-facing outputs.

If your dashboard numbers disagree across tools, the fix is usually upstream of the dashboard itself. I can map the grain, model the warehouse correctly, and add the reconciliation checks before numbers reach stakeholders.