Modeling Marketing Data

Raw connector tables are messy: inconsistent column names, mixed currencies, different grains, and platform-specific quirks. Querying them directly produces wrong, irreproducible numbers.

Data modeling is the discipline of turning raw rows into clean, consistent, business-ready tables. It is where ROAS, conversion, and revenue get defined once, correctly, so every report agrees.

The dominant analytics model is the star schema: a central fact table of measurable events surrounded by dimension tables that describe them. Facts hold numbers (spend, clicks, revenue); dimensions hold context (campaign, date, channel, customer).

This shape is intuitive for marketers and efficient for BI tools, which join one fact to several dimensions to slice metrics by any attribute.

A fact table is long and additive: one row per event or per day-campaign, with numeric measures you sum. A dimension table is wide and descriptive: one row per campaign or customer, with attributes you filter and group by.

The test: if you would SUM it, it is a fact; if you would GROUP BY it, it is a dimension. Spend is a fact; campaign name is a dimension.

The grain is what one row of a fact table represents. Declaring it first is the single most important modeling decision. Mixing grains, daily rows with lifetime totals, double-counts and corrupts every metric downstream.

State the grain in plain language: one row per campaign per day. Every column must then be true at that grain, and every load must respect it.

Before facts and dimensions, build staging models: one per source table, renaming columns to a standard, casting types, and standardizing units (cents to dollars, UTC dates). One staging model maps one raw table, nothing more.

Staging is the cleaning layer. It isolates source quirks so your downstream marts never need to know that Meta calls it spend and Google calls it cost.

Each ad platform reports differently, but after staging they share a common shape. The next model unions them into one cross-channel spend fact, the foundation of blended reporting.

This single table is what makes total ROAS possible. With every channel conformed to the same columns, one query sums spend across Google, Meta, and TikTok at once.

For cross-channel analysis, dimensions must be conformed: a shared dim_date and dim_channel that every fact joins to identically. Then "revenue by month by channel" means the same thing whether the source is ads, email, or web.

Conformed dimensions are what let you place spend and revenue side by side in one chart. Without them, joins misalign and totals quietly disagree.

Attribution assigns credit for a conversion to touchpoints. Last-click is simplest: the final marketing source before conversion gets full credit. First-click, linear, and position-based spread credit differently.

In a warehouse you implement attribution as a model, not a platform black box. With GA4 event-level data you can window touchpoints per user and apply any rule, then compare results across models honestly.

Dimension attributes change over time: a campaign's budget owner moves, a customer's tier upgrades. A Type 2 slowly changing dimension keeps history by adding a new row with validity dates instead of overwriting.

This matters for accurate point-in-time reporting. To know which segment a customer was in when they converted, you need the version of the dimension that was valid then, not today's.

Models are code, so test them. Tools like dbt let you assert that keys are unique and not null, that channel values are within an accepted set, and that relationships between tables hold.

Tests catch schema drift and bad joins before they reach a dashboard. Paired with auto-generated documentation and lineage, they make the model trustworthy and onboardable instead of a fragile black box.

The top layer is marts: business-ready tables shaped for specific audiences, like a marketing_performance mart that already joins spend to revenue and computes ROAS per channel per day.

BI tools read only from marts. By pre-joining and pre-aggregating here, dashboards stay fast and cheap, and every analyst inherits the same correct definitions.

You are building a fact table for ad performance and must avoid double-counting. What is the single most important thing to declare before writing any columns?

Modeling turns messy raw tables into trustworthy, business-ready data through layers: staging cleans and conforms each source, facts and conformed dimensions form a star schema, and marts pre-join everything for BI.

Declare the grain first, union channels for blended metrics, implement attribution and SCD Type 2 history in SQL, and test every model so wrong numbers fail loudly instead of reaching a dashboard.