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.
| Dimension | One row per | Attributes | Why it is shared |
|---|---|---|---|
| Date | Calendar day | Week, month, quarter, fiscal period, trading week, holiday and event flags | Every report cuts by the same weeks and periods, including the trading calendar finance uses |
| Channel | Channel | Name, group, paid or organic, owner | Spend and outcomes meet on one channel list |
| Campaign | Campaign key | Parsed naming fields such as market, objective, product, audience; launch and end dates; owner | One campaign, whatever each platform calls it |
| Market | Country or region | Currency, reporting time zone, region group | Currency and time zone rules live in one place |
| Product | Product or product group | Category, range, margin band | Orders, 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 GroupingRules that assign each visit or conversion to a channel, such as organic search or email, from its source, medium and campaign values. 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 ConventionAn agreed pattern for naming campaigns, ads, events and tracking parameters so that reports can group them reliably. 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.
| Source | Source identifier | Campaign key |
|---|---|---|
| A search platform | The platform's campaign ID | The business's own key |
| A social platform | The platform's campaign ID | The same key, where the campaign is the same |
| Analytics | The cleaned campaign value from the link's campaign parameter | The same key |
| CRM | The campaign field captured on the lead | The 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 (CDPUnified first-party customer profiles.), 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 (CACCost to acquire one new customer.) and return on ad spend (ROASRevenue generated per unit of ad spend.). 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.
| Table | Grain | Key columns | Source | Layer |
|---|---|---|---|---|
fct_ad_spend_daily | Day, platform, account, campaign | date_key, campaign_key, channel_key | Ad platform reports | Reporting |
fct_sessions | Session | date_key, campaign_key, channel_key | Analytics export | Reporting |
fct_key_events | Key event | date_key, session_key | Analytics export | Reporting |
fct_orders | Order | date_key, customer_key, channel_key | Order system | Reporting |
fct_leads | Lead | date_key, campaign_key, channel_key | CRM | Reporting |
dim_date | Day | date_key | Built once, with the trading calendar | Reporting |
dim_channel | Channel | channel_key | Channel mapping table | Reporting |
dim_campaign | Campaign | campaign_key | Campaign mapping and parsed names | Reporting |
map_campaign | Source identifier | Source, source ID, campaign_key | Maintained by marketing operations | Cleaned |
Build the date, channel and campaign dimensions first, then spend, then whichever outcome fact the first KPIA metric tied directly to an objective. depends on. Each new fact then joins to dimensions that already exist and are already trusted.
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.
