Warehouse-Native Attribution: Building Models in SQL

Most SaaS teams reach for an attribution tool the moment they want to know which channels produce revenue. But if you already run a data warehouse — and by Series A most do — the raw material for attribution is already sitting there: ad clicks, web events, product usage, and CRM opportunities all landing in the same place. Warehouse-native attribution means building the model directly in SQL, usually orchestrated by dbt, instead of shipping that data to a black box and buying back a version of your own funnel. The result is attribution logic you can read, version, and reconcile against revenue.

This is a build-versus-buy decision with a clear dividing line. A turnkey tool is right when you need a dashboard tomorrow and have no data team. Warehouse-native is right when you have the warehouse, a dbt project, and a recurring argument about which channels actually work — because the answer to that argument should be inspectable SQL, not a vendor's undisclosed weighting. This post walks through the architecture: staging raw touches, resolving identity, ordering the path, applying crediting rules, and — critically — being honest about the data the model never sees. It is the construction companion to choosing which attribution model fits your data in the first place.

Why model attribution in the warehouse at all

The case for warehouse-native attribution is control over logic you would otherwise never see. When a third-party tool tells you paid search drove 40% of pipeline, you cannot inspect how it stitched identities, which touches it counted, or how it weighted them — you get a number and a brand color. A warehouse model is the opposite: every join, every crediting weight, every identity rule is SQL in your git history, which means when finance challenges the number you can show the exact path that produced it. That auditability is not a nicety. Attribution numbers drive budget, and a number nobody can explain is a number nobody should spend against.

The second advantage is scope. A marketing-attribution SaaS sees marketing touches; your warehouse sees everything. A warehouse-native model can factor in the effect and cost of coupons and discounts applied at checkout as alternative forms of paid spend, blend server-side events the browser never fired, and join product-usage signals that a pure-marketing tool has no access to. As RudderStack's reference pattern puts it, in the warehouse "each touchpoint has a timestamp" so you can build a single dbt model that outputs a table of user touches flagged with whether the user eventually converted (RudderStack). That unified view is what lets attribution connect to the same CRM-to-revenue join the rest of your funnel analytics already depends on.

The staging layer: one clean schema for every touch

The first job is to turn a mess of source-specific tables into one common touch schema. Ad platforms export clicks with their own column names; your web analytics fires events with a different shape; the CRM logs activities in a third format. A staging model per source normalizes each into the same handful of columns — identity, timestamp, channel, campaign, and cost where it exists — so that downstream models never have to know which system a touch came from. This is ordinary dbt discipline: thin staging models that clean and rename, one per raw source, with the business logic deferred to later layers.

Getting the channel taxonomy right at this layer is what makes or breaks the model, because attribution is only as trustworthy as the labels on the touches. If the same campaign shows up as "fb", "facebook", and "Meta" across three sources, your credit fragments across three phantom channels. Enforce a single canonical channel mapping in staging — a seed file or lookup that every source resolves against — and the problem disappears before it reaches the path builder. This is the warehouse expression of the same UTM taxonomy governance that keeps attribution honest at the point of tagging; here you enforce it at the point of loading.

Identity resolution is the hard part

Attribution lives or dies on identity, and identity is where warehouse-native models earn their keep or fall apart. A single buyer touches you as an anonymous cookie, a click ID, a form-fill email, and a CRM contact — four identifiers for one person, and until you collapse them onto a single key, their touch path is shattered into four fragments that each get partial, wrong credit. The intermediate layer of your dbt project has to do this stitching: map anonymous identifiers to known ones as they resolve, roll individual contacts up to the buying account for B2B, and produce a stable key every touch can hang off.

This is the same discipline covered in anonymous-to-known user stitching, and it is worth reading before you write a line of attribution SQL, because no crediting rule can rescue a broken identity graph. Practically, build identity resolution as its own set of dbt models with their own tests — a contact should map to exactly one account, an anonymous ID should resolve deterministically once known — and treat attribution as a consumer of that resolved graph rather than trying to stitch identity and credit touches in the same query. Separating the two keeps each testable, and identity bugs surface as failing tests instead of quietly skewed pipeline reports.

Ordering the path and applying crediting rules

Once every touch carries a channel tag and a resolved account key, building the path is deterministic. An intermediate model orders each account's touches by timestamp up to the conversion event, producing the ordered sequence attribution operates on. From there the crediting rule is just how you distribute one conversion's credit across that sequence: first-touch gives it all to the opener, last-touch to the closer, linear splits evenly, U-shaped weights the first and last heavily. Snowplow's SQL tutorial walks through first- and last-touch models step by step, and the open-source rittmananalytics ra_attribution dbt package is a multi-touch reference you can read rather than reinvent.

The design win here is making the crediting rule swappable. A dbt macro that takes the ordered path and a model name can toggle between linear, U-shaped, and first/last-touch without rewriting the SQL, which means you can run the same conversions through several models and compare the channel credit each produces side by side. That comparison is the point: seeing that first-touch credits your content channel and last-touch credits branded search tells you more about your funnel than any single model does. It also sets up the harder question of which model to trust when they disagree — a question attribution alone cannot settle, and one we take up in incrementality testing for top-of-funnel.

Be honest about the data the model never sees

A warehouse-native model changes where you attribute, not how much data reaches you. If a visitor declines tracking, that touch never lands in the warehouse — the coverage gap is identical to a SaaS tool's, because both draw from the same consented stream. The danger is that a model you built yourself feels more complete than it is, and quietly redistributes the credit for unattributable conversions across the channels it can see, overstating them. The correct behavior is the opposite: measure what fraction of conversions have no trackable first touch and carry that as an explicit "unattributed" bucket in the output.

Owning the SQL is precisely what lets you be honest about this. You can blend in server-side events the browser never fired, join self-reported attribution from a signup survey to triangulate the dark portion of the funnel, and report a coverage rate alongside every channel number so readers know how much of reality the model captured. A tool that hides its coverage gap produces a confident, wrong picture; a warehouse model that surfaces the gap produces a less flattering, more decision-useful one. That is the whole trade — you exchange turnkey polish for auditable honesty.

When to build it, and when not to

Build warehouse-native attribution when three things are true: you already run a warehouse with the touch and CRM data flowing into it, you have someone who can own a dbt project, and attribution numbers are driving real budget decisions that people argue about. Those conditions together mean the cost of modeling is low (the data is already there) and the value of transparency is high (money moves on the answer). Below that threshold, the weeks of identity and staging work do not pay back, and a turnkey tool that gets you a directional answer this week is the right call — the same way early-stage teams should reach for a simpler attribution approach at low volume.

The migration path is also gentler than it looks. You do not have to rip out your tool to start; run a warehouse-native model in parallel, reconcile its channel credit against the tool's, and investigate every material disagreement — usually the discrepancy is an identity or taxonomy bug in one of the two, and finding it improves both. Once the warehouse model matches or beats the tool on completeness and you trust the SQL, the tool becomes optional. Warehouse-native attribution is less a product you install than a capability you grow into, and it belongs in the same warehouse-centric stack as your semantic layer for product metrics — one governed source of truth that marketing, product, and finance all query from.