Attribution is the marketing analyst’s most frequently-asked and most-poorly-defined task. "Which channel drove the purchase?" — five stakeholders, five different mental models, one dashboard that has to pick one. This post is the SQL side of that problem: how each of the five common attribution models actually gets computed, where each one is honest, where each one lies, and the three biases that warp every model regardless of which one you pick.

Shared data model for every example: `fact_sessions(session_id, customer_id, channel, session_at)` tracks every touchpoint; `fact_purchases(purchase_id, customer_id, amount, purchased_at)` tracks conversions. A "customer journey" is the ordered set of sessions for a given customer up to a given purchase.

Attribution is a definitional question, not a SQL question

Before you write any SQL, write down the definition you’re being asked to operationalize. "Which channel drove the purchase?" is not a definition — it’s a debate. The five models below are each a concrete answer to that debate, and the model you pick will change your dashboard numbers by 30–60% on the same underlying data.

If the stakeholder can’t name a model ("last-touch? linear? time-decay?"), that’s the first conversation — not the SQL. Running the wrong model and shipping the number is how junior analysts accidentally reshape a budget decision.

Ask which model before writing any SQL

The two simplest models, and by far the most common in ad-hoc reporting. Last-touch assigns the full conversion credit to the most recent channel before the purchase; first-touch assigns it to the channel that opened the journey.

-- Last-touch attribution by channel
WITH journey AS (
  SELECT
    p.purchase_id,
    p.amount,
    s.channel,
    ROW_NUMBER() OVER (
      PARTITION BY p.purchase_id
      ORDER BY s.session_at DESC
    ) AS rn
  FROM fact_purchases p
  JOIN fact_sessions s
    ON s.customer_id = p.customer_id
   AND s.session_at <= p.purchased_at
)
SELECT channel, SUM(amount) AS attributed_revenue
FROM journey
WHERE rn = 1  -- most recent session only
GROUP BY channel
ORDER BY attributed_revenue DESC;

First-touch is structurally identical — swap `ORDER BY s.session_at DESC` to `ASC`. Both are trivially fast, deeply biased, and the right answer for specific questions. Last-touch over-credits bottom-of-funnel channels (direct, brand search). First-touch over-credits awareness channels (display, content). Neither tells you the truth about a multi-touch journey.

Linear gives every touchpoint in the journey equal credit. If a purchase had 4 sessions before it — Google, email, direct, paid social — each channel gets 25% of the revenue. Harder to manipulate than first/last-touch, much harder to defend ("why is email getting credit for a direct-type-in purchase?") but structurally more honest about multi-touch reality.

-- Linear attribution: divide the purchase amount across every touch
WITH journey AS (
  SELECT
    p.purchase_id,
    p.amount,
    s.channel,
    COUNT(*) OVER (PARTITION BY p.purchase_id) AS touches
  FROM fact_purchases p
  JOIN fact_sessions s
    ON s.customer_id = p.customer_id
   AND s.session_at <= p.purchased_at
)
SELECT
  channel,
  SUM(amount * 1.0 / touches) AS attributed_revenue
FROM journey
GROUP BY channel
ORDER BY attributed_revenue DESC;

Position-based (aka "U-shaped") gives 40% to first-touch, 40% to last-touch, and splits the remaining 20% across middle touches. Time-decay weights recent touches more heavily using a decay function (typically half-life of 7 days). These are the "compromise" models; they don’t make anyone fully happy but they don’t enrage anyone either.

-- Time-decay attribution: weight by exp(-ln(2) * days_before_purchase / 7)
-- Half-life of 7 days means a touch 7 days before the purchase gets 50%
-- of the credit of a touch on the purchase day.
WITH journey AS (
  SELECT
    p.purchase_id,
    p.amount,
    s.channel,
    EXTRACT(EPOCH FROM (p.purchased_at - s.session_at)) / 86400.0 AS days_before
  FROM fact_purchases p
  JOIN fact_sessions s
    ON s.customer_id = p.customer_id
   AND s.session_at <= p.purchased_at
),
weighted AS (
  SELECT
    purchase_id,
    channel,
    amount,
    EXP(-0.693 * days_before / 7.0) AS raw_weight
  FROM journey
),
normalized AS (
  SELECT
    purchase_id,
    channel,
    amount,
    raw_weight / SUM(raw_weight) OVER (PARTITION BY purchase_id) AS weight
  FROM weighted
)
SELECT channel, SUM(amount * weight) AS attributed_revenue
FROM normalized
GROUP BY channel
ORDER BY attributed_revenue DESC;

The normalization step is the one that’s easy to forget. If you don’t divide by the sum of weights per purchase, your total attributed revenue won’t equal total revenue — and a CFO who spot-checks that exact sum will catch you immediately.

Regardless of which model you pick, the underlying data has three biases that your SQL can’t fix. Knowing about them is the difference between a trusted attribution number and a questioned one.

A disagreement rate above 40% means the model choice is reshaping a material fraction of your reported numbers. At that point the right analyst move is not to pick a model — it’s to report two numbers side-by-side and force the conversation about which question you’re actually answering.

Last-touch, first-touch, linear, position-based, and time-decay attribution — written in SQL against a realistic event log. Includes the three gotchas that bias every model.

Attribution Models in SQL: Last-Touch, First-Touch, Linear | caseSQL

Attribution Models in SQL: Last-Touch, First-Touch, Linear, and the Rest

Marketing analytics lives in a narrow band: big enough that spreadsheets buckle, small enough that the data team doesn’t prioritize it. Which means the person closest to the numbers — you — needs to pull them yourself. SQL is how. This guide is the shape of the role distilled to its query-writing parts: the five reports you’ll run every week, the way attribution actually gets computed, and the data-quality gotchas that make senior analysts cautious and junior analysts confident.

Everything below assumes a star schema that looks like most modern marketing warehouses: `dim_customers`, `dim_campaigns`, `fact_sends`, `fact_sessions`, `fact_purchases`. If you’ve worked with a dbt project, a Snowflake data model, or the caseSQL mission database, the shape will be familiar.

Excel is an excellent tool up to about 100,000 rows and one dimension of analysis. Past that, three things break at once: pivot performance, referential integrity (lookups silently fail when the sheet exceeds a cache boundary), and shareability — the file becomes the single source of truth for something it was never designed to own.

SQL solves each of those, but the real reason to learn it is different: **SQL lets you ask the next question.** In Excel, the fastest path is to pick a pivot, format it, and move on. In SQL, you can always go one layer deeper — group by a new dimension, join a new table, filter by a cohort — in the same window. That’s what senior analysts do all day, and it’s the reason recruiters screen for SQL before Tableau: the query language is the skill; the dashboard tool is the output.

Most marketing analytics teams run on PostgreSQL, Snowflake, BigQuery, or Redshift. For 90% of analyst work, the syntax is identical. The differences matter when you hit window functions (supported everywhere), date arithmetic (each vendor has its own functions), and arrays (a BigQuery special). This guide uses ANSI SQL; the exercises run against SQLite, which is the simplest dialect and forces good habits.

The SQL dialect question

The five queries every marketing analyst writes weekly

Every marketing analytics job has five query templates that come up over and over. Mastering the templates (not memorizing exact SQL) is 80% of what makes the role feel mechanical after the first three months.

The first one is the foundation. If you can write it cleanly, you can adapt it to the other four by swapping the `GROUP BY` column, adding a time dimension, or filtering on a segment.

Two subtle details to notice. First, `COUNT(DISTINCT customer_id)` not `COUNT(*)` — sends and purchases both have duplicate-row risk if a customer was hit twice. Second, `NULLIF(..., 0)` on the denominator so a zero-send campaign doesn’t crash the query; it just returns null. Those two habits alone mark the gap between a mid-level and a senior analyst.

Attribution in SQL: last-touch, first-touch, and reality

Attribution is where marketing analytics stops being arithmetic and starts being policy. The question "which campaign got credit for this purchase" has no mathematically correct answer — only a convention your org agrees on. The three conventions you’ll implement: **last-touch** (credit the last campaign before purchase), **first-touch** (credit the first campaign the customer ever saw), and **linear** (split credit evenly across all touches).

In SQL, all three are window-function problems. For last-touch, you want the most recent `fact_sends` row before each `fact_purchases` row. `ROW_NUMBER()` partitioned by customer, ordered by send date descending, filtered to rows before the purchase, take the first one. Once you’ve done that pattern three times, it becomes muscle memory.

First-touch is the same query with `ORDER BY sent_at ASC`. Linear attribution is trickier: you count how many touches a purchase had, then divide the purchase amount by that count. If your org ever asks for time-decay or U-shaped attribution, you’re implementing a weighted version of linear — same SQL skeleton, different weights.

Here’s what tutorials don’t tell you: most of the time a marketing query looks wrong, the query is right and the data is wrong. Refunded purchases left in the fact table. Campaign IDs that don’t match `dim_campaigns` because the campaign was renamed. Customers who show up twice because the CRM merged an old record. Timezone drift so a "last 30 days" window is actually 29.5.

Senior analysts assume the data lies until proven honest. Before they report a number upward, they run three checks:

If your `fact_purchases` table has a `refunded_at` column, every revenue query needs to decide: include refunded purchases at face value (overstates revenue), exclude them entirely (understates top-of-funnel signal), or subtract refund amount from purchase amount (cleanest). Pick one convention, put it in a comment at the top of the query, and apply it consistently.

Refunds are a fact table, not a filter

From queries to insights: the analyst-sense layer

The skill that separates a junior marketing analyst from a senior one isn’t writing more complex SQL. It’s noticing which number in a report matters and which is noise. That judgment comes from pattern recognition, which comes from seeing the same query under lots of different conditions — normal weeks, holiday spikes, bug weeks, refund waves, data-outage Wednesdays.

The fastest way to build that pattern-recognition is to write one query, then run 10 variations of it. What if I segment by new vs returning? What if I split paid vs organic? What if I look at the last 7 days instead of 30? Each variation teaches you what the numbers normally look like, so when they don’t, you notice.

When you run this against a real dataset, two things happen. First, you start to develop a feel for what a "normal" new-customer share looks like for your org (usually 10–30%). Second, when a campaign comes in at 70%, you know immediately whether it’s a genuinely great acquisition campaign or whether the campaign ID got re-used and the "customers" are really old ones mislabeled. That reflex — _does this number behave?_ — is what your manager is paying for.

If you want to build that reflex against a realistic marketing star schema with planted data-quality issues, the Marketing Analytics path on caseSQL is the fastest way. 25 missions, each one a real analyst ask, validated against multiple accepted approaches so you see the trade-offs rather than a single "right answer."

Before any marketing query leaves your laptop, run this three-line mental pass. It takes 20 seconds and catches 80% of the bugs that make junior analysts look unreliable:

None of these three are advanced SQL. All three are the difference between a query that looks right and a query that _is_ right.

A practical guide to the SQL skills marketing analysts actually use on the job — attribution, campaign ROI, segment analysis, and handling messy data. Free exercises included.

Marketing

Attribution Models in SQL: Last-Touch, First-Touch, Linear, and the Rest

SQL for Marketing Analysts: The Complete Guide