The Revenue Attribution Playbook

Attribution is where most GTM teams either overspend on a platform nobody trusts or fall back to last-touch and fly half-blind. There is a third path: a transparent, owned multi-touch model you can build in a sheet and actually defend to your CRO. Apollo, Google Sheets, and Claude. The prompts and the steps are below.

There are two ways attribution usually goes wrong. The first is a six-figure platform that still cannot see half the touches and that the sales team does not believe, so nobody acts on it. The second is last-touch attribution sitting in the CRM, which hands every deal to the final demo and quietly tells you to go run more demos. Both fail the only test that matters: does it change what you fund next quarter.

The goal here is not perfect attribution. Perfect attribution does not exist — every model is a deliberate simplification. The goal is a consistent, transparent model you own, that you can explain in one sentence and defend with the rows behind it. That is buildable in a sheet. And it only works on clean data, which is why this is playbook two: if you have not run the Data Quality Playbook, start there.

Pick your model

Before any tooling, decide how credit gets shared across a deal's touches. The common models, simplest to most balanced: first-touch gives all credit to the first interaction — good for demand gen, blind to everything after. Last-touch gives it all to the final touch — the CRM default, blind to everything before. Linear splits evenly — fair, but flattens the moments that actually move a deal. Time-decay weights later touches more — useful on short cycles. Position-based weights the first and last touch heavily and lets the middle share the rest.

What to buy, what to build

Stage 1 — Capture the touches

Attribution lives or dies on touch capture, and touch capture lives or dies on UTM discipline. Every link you control — ads, emails, social, content — gets tagged with the same convention, every time. An untagged link is an unattributable touch.

utm_source   = the platform      (google, linkedin, newsletter, partner-name)
utm_medium   = the channel type  (cpc, paid-social, email, organic-social, referral)
utm_campaign = the campaign      (q3-launch, gtm-eng-guide, webinar-attribution)

Example:
https://cascadegtm.co/guide?utm_source=linkedin&utm_medium=paid-social&utm_campaign=gtm-eng-guide

1. Stand up two tabs: touches (touch_id, date, account, contact, utm_source, utm_medium, utm_campaign, referrer) and deals (account, opp_id, amount, create_date, close_date, stage).
2. Feed touches from your sources: ad-platform exports, email-tool clicks, GA4 events, and CRM or Apollo activity. Outbound SDR touches count too — log them with utm_medium = outbound.
3. Pull deals from your CRM, or the sheet you use as one. Use won deals to attribute revenue, or all open deals to attribute pipeline.

Stage 2 — Normalize channels

Raw sources are messy: “li,” “linkedin,” and “LinkedIn-Ads” are one channel; a blank medium is Direct. Collapse everything to one canonical set before you assign a single dollar, or your report fragments into noise.

Normalize raw marketing touch sources into a single canonical channel set. I will paste touch rows.

Canonical channels (choose exactly one per row):
Paid Search | Paid Social | Organic Search | Organic Social | Email | Webinar | Event | Content | Referral | Direct | Outbound | Partner

Mapping guidance:
- utm_medium of cpc/ppc/paid -> Paid Search if source is google/bing, Paid Social if source is linkedin/meta/x.
- organic/seo -> Organic Search. social with no paid medium -> Organic Social.
- email/newsletter -> Email. webinar -> Webinar. event/field/conference -> Event.
- blog/content/guide/gated-asset -> Content. referral -> Referral. direct or blank -> Direct.
- sdr/outbound/sales-sourced -> Outbound. partner/reseller -> Partner.

Input, tab-separated:
touch_id  utm_source  utm_medium  utm_campaign  referrer

Return, tab-separated:
touch_id  channel  campaign_clean

campaign_clean: a readable name from utm_campaign (replace dashes/underscores with spaces, Title Case), or "-" if none.
Return only data rows. No header, no commentary.

Rows:
[PASTE RAW TOUCHES]

Stage 3 — Assemble paths and assign credit

A deal's touch path is simply that account's touches, in date order, up to the close. Build the path in Sheets, then let Claude apply the model — so the math stays transparent and re-runnable instead of buried in a tool.

// Build each deal's ordered touch path. deals tab: account in A2, close_date in E2.
// touches tab columns here: A=account, B=date, C=channel (adjust to your layout).
=TEXTJOIN(" > ", TRUE,
  QUERY(touches!$A$2:$C,
    "select C where A = '" & A2 & "' and B <= date '" & TEXT(E2,"yyyy-mm-dd") & "' order by B asc", 0))

// Result, e.g.:  Paid Social > Email > Event

Now hand the deals and their ordered paths to the model.

You are a revenue attribution engine. Apply a U-shaped (position-based) model to each deal's ordered touch path and return the credit each touch earns.

Model:
- First touch and last touch each get 40% of the deal amount.
- The remaining 20% is split evenly across the middle touches.
- One touch -> 100%. Two touches -> 50/50. Round credit to whole units.

Input, tab-separated, one deal per line. The path lists touches in order, separated by " > ":
opp_id  amount  path

Return, tab-separated, one line per touch position:
opp_id  position  channel  credit
(position = first | middle | last for multi-touch deals; = only for a single-touch deal)

Return only data rows. No commentary.

Rows:
[PASTE opp_id, amount, AND THE ORDERED PATH STRING PER DEAL]

Stage 4 — Report and narrate

Credit by touch rolls up to credit by channel and campaign. Sum it, then have Claude write the part your CRO acts on — grounded only in the numbers, with no invented precision.

// Credited revenue by channel. credit tab: channel in C, credit in E.
=SUMIF(credit!$C:$C, A2, credit!$E:$E)

// That channel's share of the total
=SUMIF(credit!$C:$C, A2, credit!$E:$E) / SUM(credit!$E:$E)

You are a GTM analyst writing for a CRO and CMO. Below is attributed revenue credit by channel and campaign for the period, with spend where available. Write a tight performance read.

For each channel: total credited revenue, its share of the total, and cost per credited dollar (spend / credited revenue) where spend is given. Then:
- Name the 2-3 channels to double down on, with the reason (efficiency and volume).
- Name the 1-2 to cut or fix, with the reason.
- Flag any channel where the sample is too thin to trust yet.

Ground every claim in the numbers provided. Invent nothing. Under 250 words, plain language, no hedging.

Data, tab-separated: channel  campaign  credited_revenue  touches  spend

Rows:
[PASTE CREDITED RESULTS BY CHANNEL / CAMPAIGN]

Make it run every week

1. Manual, free. Each week, refresh touches and deals, run the three prompts in order, update the rollup. Your channel read is never more than seven days stale.
2. Scheduled, with the API. An Apps Script trigger pulls deals and touches, calls Claude with the three prompts, and writes the credited tab and the written summary to a dashboard tab. The read lands in your inbox Monday morning.

What good looks like

• One documented model, applied consistently, and stated on every report.
• Every controllable link carries a UTM in the standard convention.
• Every touch maps to exactly one canonical channel.
• Every deal has a touch path; deals with no captured touches are counted and flagged, not silently dropped.
• Credited revenue reconciles to total closed revenue — credit sums to 100% per deal.
• You can defend any channel's number by pointing at the deals behind it.

Attribution you own and can explain will beat a black box you rent — not because it is more precise, but because people trust it enough to act on it. Build the model once, keep it consistent, and every budget conversation gets a little more honest.