Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Marketing Analytics › Business Intelligence for Marketing

Designing a Marketing Data Model

How to design the tables behind marketing reporting: facts for spend, sessions, leads and orders, shared dimensions for date, channel and campaign, and keys.
Guideadvanced10 min readUpdated

Why reporting needs a model

The reporting versus BI comparison describes the order in which tables usually arrive: the analytics export first, then spend from each ad platform, then the first join of spend to outcomes. Teams that stop there end up with a folder of queries, each joining raw tables directly, each making its own choices about which date to use, how to name a channel and which orders count. The choices are small and the differences between queries are small, until two dashboards report different revenue by channel for the same month and a meeting is spent finding out why.

A data model removes those choices from the queries by making them once, in tables. A fact table records events at one grain with the keys that connect them to the dimensions. A dimension table describes the things facts refer to, such as a date, a channel or a campaign, once, with every attribute reports need. Reports join facts to dimensions on keys and aggregate; the definitions that used to live in each query now live in the dimension tables and the metrics layer. The SQL for marketers guide covers how to write the joins; the design decides what there is to join.

The design is not tied to a tool. It works in any warehouse and any BI layer, and the names below are a convention rather than a requirement.

Facts at their natural grain

Each business process produces its own fact table, and each table's grain is the level at which that process naturally records events. Write the grain as a sentence at the top of every table's documentation: "one row per day, platform, account and campaign". A fact table that mixes grains, such as daily spend rows and monthly adjustment rows in one table, will be summed wrongly by someone eventually.

  • Ad spend. One row per day, platform, account and campaign, with spend, impressions, clicks and the platform's own conversions and conversion value, and the platform's campaign ID as well as its name. Ad-level detail can sit in a second table at a finer grain if creative analysis needs it.
  • Sessions. One row per session, built from the analytics export, with the session's date and start time, the user identifier, the landing page, the source, medium and campaign, and keys to the channel and campaign dimensions. In the GA4 export a session is the user identifier with the session identifier parameter, as the BigQuery export basics guide explains, so this table is built rather than copied.
  • Key events. One row per key event, with its name, time, session key and value. Keeping them separate from sessions avoids summing a session's conversions twice when a session holds several.
  • Leads and opportunities. One row per lead from the CRM, with creation date, the source captured on the form, status and owner; and one row per opportunity, or per stage change if the pipeline's history matters, with the amounts and dates finance recognises.
  • Orders and order lines. One row per order, with date, customer key, revenue, discounts, tax, shipping and refund status; and one row per order line, with product key, quantity and line revenue. Revenue questions read orders; product questions read lines.

The facts carry measures and keys, not descriptions. A campaign's market, objective and product line belong on the campaign dimension, not repeated on every spend row, so that a correction to a campaign's market is made once. The academy exercise on designing a marketing data model for a home cleaning franchise practises setting these grains for spend, sessions, leads and bookings when every branch reports in its own spreadsheet.

Shared dimensions: date, channel, campaign, market

Shared dimensions are what make facts from different systems comparable. Every fact that has a date joins to the same date table; every fact that has a channel joins to the same channel table.

DimensionOne row perAttributesWhy it is shared
DateCalendar dayWeek, month, quarter, fiscal period, trading week, holiday and event flagsEvery report cuts by the same weeks and periods, including the trading calendar finance uses
ChannelChannelName, group, paid or organic, ownerSpend and outcomes meet on one channel list
CampaignCampaign keyParsed naming fields such as market, objective, product, audience; launch and end dates; ownerOne campaign, whatever each platform calls it
MarketCountry or regionCurrency, reporting time zone, region groupCurrency and time zone rules live in one place
ProductProduct or product groupCategory, range, margin bandOrders, lines and campaigns share one product list

The channel dimension is fed by a mapping from raw values to channel names: for analytics data, from source and medium, following or adapting the default Channel Grouping the analytics tool applies; for ad platforms, from platform and campaign type. Keep the mapping in a table, not in a CASE statement copied between queries, so that a new source value is mapped once.

The campaign dimension is where the Naming Convention pays off. A convention that encodes market, objective, product and audience in fixed positions lets the cleaned layer parse those fields into columns; the data operations and taxonomies guide covers how the convention is governed so that the parsing keeps working. Where a name does not parse, the row should land in a visible "unparsed" state rather than with blank attributes.

The date dimension carries the trading calendar: fiscal weeks and periods, sale events, public holidays, the weeks that finance closes on. Reports that cut by fiscal period read it from here rather than computing it, which is the only way weekly marketing reports and monthly finance reports end up on the same boundaries.

The campaign mapping table

The campaign mapping table is the most important table in a marketing model and the one most often missing. It has one row per way a campaign is identified in a source, and maps each one to a single campaign key.

SourceSource identifierCampaign key
A search platformThe platform's campaign IDThe business's own key
A social platformThe platform's campaign IDThe same key, where the campaign is the same
AnalyticsThe cleaned campaign value from the link's campaign parameterThe same key
CRMThe campaign field captured on the leadThe same key

With it, spend from several platforms, sessions from analytics and leads from the CRM all meet on one campaign key, and a question such as cost per qualified lead by campaign becomes a join rather than a weekly matching exercise.

Three rules keep it working. Map platform IDs rather than names wherever an ID exists, so that renames do not break the join. Add new rows as part of the campaign launch, not when the report breaks; the launch checklist should include the mapping. And report unmapped rows: a scheduled query that lists every spend row, session campaign value and CRM campaign that has no key, with the amount of spend or the number of sessions attached, so the gap is visible and has an owner.

Keys, identity and history

Keys. Give each dimension row a stable key of your own, and keep the source system's identifier beside it. Natural keys from sources change: platforms reuse names, CRMs merge records, product codes are reissued. A key you control survives those changes, and the source identifier is still there for tracing a row back.

Identity. Customer and user identifiers are what join sessions to leads to orders, and they are also personal data. Decide where each is allowed. A common pattern keeps identifiers in the raw and cleaned layers, where access is restricted, and builds reporting tables at aggregated grains, such as day by channel by campaign, that carry no identifier at all. Where identity resolution is done by a customer data platform (CDP), the model consumes its resolved customer key rather than repeating the matching; the data flow and integrations guide covers where that resolution sits in the stack. Never use an email address or phone number as a join key in reporting tables, hashed or not; the guide to keeping personal data out of analytics explains why a hash still identifies the person.

History. Dimension attributes change: a campaign moves to another market, a channel is regrouped, a product changes category. Decide for each attribute whether reports should show the current value for all history or the value at the time. Most marketing reporting wants current values, which is a simple overwrite. Where history matters, such as a channel regrouping that should not restate last year, keep validity dates on the mapping rows so that facts join to the version that was valid on their date. Record every regrouping in the change log the metrics layer keeps. The academy checkpoint on data models, platform loads and data tests tests keys, identity and history beside the grain of each fact and the tests a marketing table needs.

Layers from raw to reporting

Data moves through three layers, each with one job.

  • Raw. Data exactly as it landed: the analytics export's daily tables, the ad platforms' reports, the CRM extract. Never edited, so any later step can be rerun from it. The GA4 export creates its daily tables in your own project, as Google's setup documentation describes, and they belong in this layer untouched.
  • Cleaned. One table per source entity, with types fixed, columns renamed to the house convention, duplicates removed, currencies and time zones converted to the reporting standard with the originals kept, campaign names parsed and keys attached. This is where most of the modelling work lives.
  • Reporting. Facts and dimensions joined and aggregated to the grains dashboards need, with measures that the metrics layer defines: spend, sessions, leads, orders, and the ratios built from them such as cost per acquisition, customer acquisition cost (CAC) and return on ad spend (ROAS). Dashboards read only from here.

Currency and time zone conversion belongs in the cleaned layer, once. Each source arrives in its own currency and time zone; converting each to the reporting standard on the way in, with the rate convention recorded, means every reporting table and every dashboard agrees on what a day and a pound or dollar are.

A starter model

Most teams do not need every table on day one. The model below answers the first round of joined questions, spend against outcomes by day, channel and campaign, and grows by adding facts rather than rebuilding.

TableGrainKey columnsSourceLayer
fct_ad_spend_dailyDay, platform, account, campaigndate_key, campaign_key, channel_keyAd platform reportsReporting
fct_sessionsSessiondate_key, campaign_key, channel_keyAnalytics exportReporting
fct_key_eventsKey eventdate_key, session_keyAnalytics exportReporting
fct_ordersOrderdate_key, customer_key, channel_keyOrder systemReporting
fct_leadsLeaddate_key, campaign_key, channel_keyCRMReporting
dim_dateDaydate_keyBuilt once, with the trading calendarReporting
dim_channelChannelchannel_keyChannel mapping tableReporting
dim_campaignCampaigncampaign_keyCampaign mapping and parsed namesReporting
map_campaignSource identifierSource, source ID, campaign_keyMaintained by marketing operationsCleaned

Build the date, channel and campaign dimensions first, then spend, then whichever outcome fact the first KPI depends on. Each new fact then joins to dimensions that already exist and are already trusted.

Illustrative example — not a real result

Kettlebrim Homeware replaces its spreadsheet joins

Kettlebrim Homeware, an invented homeware brand, has run its weekly report from twelve spreadsheet joins, each matching platform exports to analytics campaigns by hand. It rebuilds the report on five fact tables and four shared dimensions. Over eighteen months of history, fct_ad_spend_daily holds 41,800 rows across Google Ads, Meta and a marketplace ads account; fct_sessions 3.9 million rows; fct_key_events 212,000; fct_orders 96,400; and fct_order_lines 241,000. The dimensions are small: 548 dates, 11 channels, 386 campaigns and 1,920 products. The campaign mapping table holds 1,140 rows, because one autumn throws campaign, key KB-AW-THROWS, appears as "UK_Shopping_Throws_AW" in Google Ads, as "Throws Autumn - Prospecting" and "Throws Autumn - Retargeting" in Meta, and as "throws-autumn" in the campaign parameter on its links. Once all four map to the one key, the campaign's cost per order is a single join. The first unmapped report lists £3,400 of marketplace spend with no key, from campaigns launched without a mapping row; the launch checklist gains a mapping step.

The BI hub places the model between the raw data and the metrics layer, dashboards and governance that sit on top of it.

Common mistakes

  • Mixing grains in one table. Daily rows and monthly adjustments in one fact will be summed together.
  • Joining on campaign names. The first rename breaks the report; map IDs to a key.
  • Channel logic copied into every query. Keep one mapping table and join to it.
  • Converting currency and time zone in dashboards. Convert once in the cleaned layer.
  • Descriptions repeated on fact rows. Corrections then have to be made in thousands of places.
  • Identifiers in reporting tables. Keep customer identifiers in restricted layers and aggregate above them.
  • No unmapped report. Spend without a campaign key disappears from campaign reporting without anyone noticing.
  • Editing raw data. Nothing can be rebuilt once the original is gone.

Frequently asked questions

Do I need a data model if I only have a few reports?

You need the parts of one that stop the reports disagreeing. A team with a handful of dashboards still benefits from a single spend table at a stated grain, a channel mapping table that every report uses and a date table with the trading calendar. The full set of facts and dimensions can wait until the questions need it; the shared dimensions are what keep two reports from giving two answers, and they are cheap to build early.

Should campaign names or campaign IDs be the join key?

IDs, wherever the platform provides them. Names change when someone fixes a typo, adds a market or relaunches a campaign, and every change breaks a join that depended on the old text. Load the platform's campaign ID beside the name, map the ID to your own campaign key in the mapping table, and parse the name only for the descriptive fields the naming convention promises. Where only names exist, as with campaign parameters on links, map the cleaned name to the same key and watch for unmapped values.

Where should currency and time zone conversion happen?

Once, in the cleaned layer, on the way in. Each source arrives in its own currency and time zone; convert to the reporting currency and zone there, keep the original values beside the converted ones, and record the rate convention. Converting at query time, in each dashboard, is how two reports built on the same table end up a day or a few per cent apart, and nobody can tell which conversion each used.

Terms used on this page

Where this page is used

Part of these learning paths

Also related

Sources

  1. [GA4] Set up BigQuery Export — Google Analytics Help

Related