Raw events to revenue
This is the raw-data-to-BQML build: the architecture that carries raw source data all the way to ad platforms bidding on modeled value. It shows up here as one process run at two very different scales, a local SMB service company and a global enterprise, to make the point that the same discipline serves both.
The tower
Each floor depends on the one below it. Raw lands untouched, staging cleans and conforms, Kimball marts make it queryable, then identity, machine learning, and attribution make it mean something, and activation sends modeled value back out to bid, so the business gets insights and decisions out the top.
some things typically in raw
- [ crm ]
- [ ad platforms ]
- [ web analytics ]
- [ account enrichment ]
- [ email marketing ]
- [ market intelligence ]
- [ call tracking ]
- [ form & lead capture ]
- [ search console ]
- [ ecommerce & pos ]
- [ cdp ]
- [ sales engagement ]
- [ support tickets ]
- [ billing & erp ]
- [ consent & cmp ]
- [ social listening ]
One process, two scales
This is one architecture run at two very different sizes. The floors are identical; only the volume changes. Each chart below is labeled with the build it comes from, an SMB gain or an enterprise gain, so the same pattern is legible whether it runs on 20 million rows or 200 million.
The SMB is a regional home-services company whose systems shared no common key. The enterprise is a global company running an order of magnitude more rows and dimensions. Both climb the same floors in the same order.
Land everything, lose nothing
The bedrock rule is that raw tables are exact, append-only replicas of each source. Nothing is edited or “fixed” on the way in. The reason is durability: a source can change its schema, restate its history, or break, and the warehouse still holds the original record to rebuild from. Cleaning happens one floor up, never here.
Depending on the business, this means joining anywhere from 10 to 30 sources into one place, none of which share a key with any other.
This floor also decides ETL versus ELT, and the two have a real history. On-prem warehouses transformed data before loading it because storage and compute were both expensive and fixed in size: you shaped the data to fit the box before it went in, and that discipline still earns its place today in systems built around strict transactional consistency. Cloud OLAP warehouses flip the tradeoff. BigQuery and its peers store and query nested and repeated fields natively, so there is no penalty for landing a record exactly as the source sent it, array fields, nested objects, and all, then flattening it one floor up instead of on the way in. That is ELT: extract, load, then transform, and for analytics work at this scale it is the better default.
How a nested row goes across differently
Shape only. One raw row can carry a repeated group in a single cell; flattening it fans that group out into one row per member, each repeating the parent columns. Landing partitioned by date keeps this cheap to re-run at any size.
- crm
- ad platforms
- ga4 export
- web events
- call tracking
- form software
- search console
- youtube studio
- account enrichment
- market intelligence
The discipline floor
Bulk APIs re-send everything on every run, so raw layers fill with duplicates by design. Staging is where that noise gets removed, and where the newly flattened data gets put back into shape: deduplicate on business keys, normalize phone numbers and emails, cast types, and quarantine anything malformed.
Schema drift is the other half of this floor's job. Source systems add fields, rename them, or change a type without warning, and a brittle pipeline breaks the moment they do. Staging models expect drift: new columns default to null instead of failing the run, renamed fields get mapped explicitly rather than positionally, and type casts are defensive so a source that starts sending a string where it used to send an integer degrades to a flagged row instead of a crashed pipeline. The example beside this is the SMB contractor's CRM, where 9.6 million raw contact rows resolve to 68K clean ones. Every join above this floor is only as reliable as this step.
Raw vs. staged row counts
Log scale. The gap between the raw and staged bars is the duplication the rest of the warehouse never has to see.
What Kimball modeling is, and why it matters
The production layer follows Kimball dimensional modeling, the standard method for shaping a warehouse around analysis (OLAP) rather than around transactions (OLTP). Instead of the highly normalized tables an application uses, the data is reorganized into a small set of shapes that answer business questions directly.
A fact table records events at a declared grain, one row per order line or per touchpoint, and holds the measures you add up. Dimensions hold the context you filter and group by, and a dimension is conformed when every fact shares it, so customermeans the same thing in a revenue query and a support query. Bridge tables absorb genuinely many-to-many relationships without corrupting the fact's grain, and surrogate keys replace unstable source IDs so a record survives a migration.
The diagram below puts each of those four pieces in one place: a fact table for touchpoints in the middle, two conformed dimensions on either side each led by the surrogate key that replaces its unstable source ID, and a bridge table underneath resolving the many-to-many case where one web session maps to several candidate identities before resolution narrows it down.
The anatomy, with the four terms labeled
The fact declares a grain and holds only additive measures and foreign keys. Each dimension leads with a surrogate key the warehouse owns, sitting next to the unstable source ID it replaces. The bridge absorbs the many-to-many so the fact never has to double-count a session.
The benefits compound. Analysts join a fact to a few dimensions instead of tracing a web of application tables, BigQuery prunes and scans predictable denormalized stars, and a shifting source system is absorbed in staging and the dimension rather than in every downstream report. Because the tables are named around business concepts, both analysts and language models reading the warehouse interpret them correctly, which matters more as querying moves through AI.
“The dimensional model is the only viable technique for achieving both user understandability and high query performance in the face of ever-changing user questions”
The star at the heart of every mart
One fact at one grain, dimensions conformed so 'customer' and 'campaign' mean the same thing in every query, including the identity bridge that ties anonymous behavior to known people.
Identity resolution and bridging is the first floor that makes the data mean something. For the SMB it collapsed phone-first systems that shared no key into a single table of people. For the enterprise it layered deterministic and probabilistic matching across all staged data, filling in web and sales journeys that deterministic matching alone had left looking flat.
BQML models run where the data lives: SQL in, predictions out, no pipelines to maintain and no MLOps tax. Every lead and sales stage carries a probability of conversion and a predicted value, for both business decisions and ad platforms, before sales ever sees the record.
Data-driven attribution fit a model per funnel stage, up to opportunity creation, and re-priced every channel's credit against the linear baseline. For the SMB it took paid attribution from 10.3% to 55.8%of won revenue.
The crown sends modeled value back to the ad platforms: offline conversions with click IDs, enhanced conversions, and value rules. For the SMB, cleaner conversion imports were the whole difference. For the enterprise, Smart Bidding gets a price tag per lead instead of a lead count.
Why value-based bidding is the point of the build
Value-based bidding only works if the conversions sent back carry their full context. An ad platform optimizes toward whatever it's told, so a conversion imported as a flat, unweighted event teaches it that every lead is equal. Importing conversions with modeled value, recovered click IDs, and the downstream outcome attached lets the platform bid toward revenue instead.
This matters most for long sales cycles, and it's a real limit, not a nice-to-have. Ad platforms only retain a click ID for around 90 days. A business where 30% or more of closed deals land past that window can't attribute those deals back to the click that drove them, no matter how clean the warehouse is, and platforms optimizing on a truncated set of outcomes see rising false-negative rates over time as the deals they can never confirm as won pile up.
What the export carries matters as much as when it arrives. Sent as flat conversions, a platform learns lead volume. Sent with a modeled likelihood-to-close and a predicted lifetime value attached, the same export tells the platform which leads were actually worth winning, so it bids toward revenue instead of toward volume.
The effect is measurable. When the SMB's warehouse fixed its conversion imports in March 2026, its monthly booked revenue rate rose 58.4% against the five months before, with no increase in ad budget. The chart below is that at daily grain, so the month the campaigns spent re-training reads as the separate thing it was rather than as part of the climb.
What a 2026 window cannot show, and which belongs here anyway: this business peaked in 2024 and fell hard through 2025. The build changed the direction, not the ceiling. Read the ramp as a recovery off a trough rather than as a new high.
Daily booked revenue after the conversion fix
What every floor below adds up to, at daily grain. Revenue sits on the install-date anchor, indexed so the pre-signal baseline daily rate equals 100 — the dashed rule — from 2026-01-01 through the end of August 2026. Gray bars are single days and most are zero, because a contractor books whole jobs one at a time, so the 14-day trailing average (gold) is the series to read. It ends the window near 11x the baseline daily rate.
“On average, advertisers that switch from a Target CPA to a Target ROAS bid strategy can see 14% more conversion value at a similar return on ad spend.”
What the building is for
A warehouse is not the deliverable. The point is the decision at the top. For the SMB service company, the build restored attribution its CRM had stopped recording and lifted its monthly booked revenue rate 58.4% by feeding cleaner conversions to Google. For the global enterprise, it connected pipeline that anonymous research had left invisible and re-ranked the target market by modeled value. Each outcome is a query against a floor below, and each has its own page.