Skip to content
home

Data and analytics engineering · 2 min read

Building a weekly marketing dataset in dbt

A layered structure for spend, outcomes and controls that stays trustworthy.

By WebIQ Analytics · 25 December 2025

A mix model, a forecast and a performance dashboard all want the same thing: one row per week (and per region, if you model regionally) with spend, outcomes and controls. dbt is a good way to build it and keep it right.

The layers

  • Sources. Raw exports from ad platforms, GA4, CRM and finance, declared as dbt sources with freshness checks.
  • Staging. One model per source: rename columns, cast types, standardise currency and dates. No joins yet.
  • Intermediate. Map platform campaigns to channels, aggregate to weeks, and join spend, outcomes and controls.
  • Mart. The modelling table: one row per week and region, one column per channel and control.
Sources, staging, intermediate and mart layers, with tests at each.SourcesAd platforms, GA4, CRM,financeStagingRename, type, clean onesource eachIntermediateJoin spend, outcomes andcontrolsMartOne row per week (andregion)Tests at every layer: not null, unique keys, accepted values, freshness, and a check that weekly totals matchfinance.Each layer has one job, so problems are found where they start.
Sources, staging, intermediate and mart layers, with tests at each.

Decisions to make once

  • Week definition. Monday to Sunday is common; pick one and use it everywhere.
  • Channel mapping. A seed file mapping every platform campaign to a channel, owned by marketing and reviewed when campaigns change.
  • Spend basis. Delivered spend net of fees, in one currency.
  • Outcome definition. Agreed with finance, with returns handled consistently.

Tests worth having

  • not_null and unique on the week and region keys.
  • accepted_values on channel names.
  • Source freshness alerts when a platform export is late.
  • A reconciliation test: weekly spend totals within a tolerance of invoices or finance figures.
  • A check that no week is missing a channel that should be present.

Keeping it useful

Document every model and column in dbt so analysts and modellers can see where each number came from. Schedule runs to finish before the people who use the data start work. And treat the channel mapping as a living file: most silent errors come from new campaigns that were never mapped.

Built this way, the dataset makes every model refresh routine, and answers finance's favourite question: where did that number come from?

The data foundations your measurement depends on

No model is better than the data under it. We build the weekly, validated dataset your mix models, tests and dashboards need, and keep it trustworthy.