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 often report performance at campaign, ad set, ad, creative, keyword, placement, date, or device level. Ecommerce and CRM systems usually 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.

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 often simple:

• A campaign-date table should not contain duplicate campaign-date keys.
• Spend totals in modeled tables should reconcile to the platform export.
• Revenue joins should not change order counts unless the model says they should.
• Null campaign identifiers should be measured, not ignored.
• Attribution windows should be visible in the model name or documentation.

These checks are not decoration. They are the difference between a dashboard that looks good and a dashboard that can support decisions.

Where this shows up in real work

This pattern comes from marketing warehouse work where GA4, ad platform, CRM, and revenue data needed to become BI-ready BigQuery tables. The architecture is reusable across teams with similar reporting problems:

Sources -> Staging -> Gold marts -> BI tables

The modeling pattern is reusable across marketing warehouses where platform, order, and CRM grains need to be reconciled before reporting.

A better dashboard brief

Before building a dashboard, I want to know:

• 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 metric definitions need to be shared across reports?

That brief saves a lot of dashboard rework.

Related project

See the project write-up for the Dataform + BigQuery Marketing Analytics Warehouse.

---

If your reporting numbers disagree across tools, the fix is often upstream of the dashboard. I can help map the grain, model the warehouse, and add the checks before the numbers reach stakeholders.