Set up marketing attribution across the B2B funnel

Before writing any code, pick the model you will defend in the quarterly business review. B2B teams converge on multi-touch rather than single-touch because the sales cycle crosses too many channels for one touch to carry the credit honestly. Linear attribution splits credit evenly across every touch and is the easiest to explain, W-shape gives 30 percent each to first touch, lead-conversion touch, and opportunity-creation touch with the remaining 10 percent split across middle touches, and U-shape gives 40 percent each to first touch and lead-conversion touch with 20 percent split across middle touches. Pick one as the primary and keep first-touch and last-touch available as supporting views. Document the choice in the same wiki page your UTM convention lives in, and get a signed note from the CFO before you build.

• List the models under consideration: first-touch, last-touch, linear, W-shape, U-shape, time-decay.
• Decide which one answers the primary question (where to invest next quarter) and which supporting views you will keep.
• Get written sign-off from marketing, sales, and finance leadership on the primary model.
• Publish the model definition and its credit-split math in the wiki so anyone can reproduce a report by hand.

The lookback window is the time horizon the attribution model considers when assigning credit for a closed-won deal. Too short and you lose the first-touch channel that discovered the account six months ago, too long and you credit touches that had no plausible influence on the buying decision. B2B teams typically land on 90 days for self-serve and mid-market segments and 180 days for enterprise cycles, with any touch outside the window excluded from the model entirely. Set the window as a config value rather than a hardcoded number, document it alongside the model, and keep a changelog of every adjustment with the reason. If the sales team pushes to extend the window to capture a specific hero deal, resist the one-off and let the data lead the next cycle-length review.

• Measure the median and 90th percentile days-to-close across the last twelve months of closed-won deals.
• Pick a lookback window that covers at least the 90th percentile, rounded to 30, 90, or 180 days.
• Store the window as a config value so a model-wide change is a one-line edit.
• Document every change in a dated changelog so finance can reconcile reports across quarters.

The attribution model operates on a touch table, not on the lead and opportunity records directly. Every form fill, every tracked click, every webinar registration, and every meeting booked creates a touch row with the lead id, timestamp, utm_source, utm_medium, utm_campaign, utm_content, utm_term, page url, referrer, and a touch-type enum (anonymous_page_view, form_fill, meeting_booked, webinar_registration, content_download, inbound_call). Keep anonymous and known touches in the same table and join them by the anonymous-visitor id that your web tracking tool produces, so a touch that happened before the lead identified themselves still shows up in the model once they fill a form. Index the table by lead id and by opportunity id so the model query runs in sub-second time even against six-figure touch volumes.

• Create the touches table with columns for lead_id, opportunity_id, anonymous_id, timestamp, five UTM fields, page_url, referrer, and touch_type.
• Backfill the table from the last 90 days of GA4 and form-submission data so the model has history to validate against.
• Index on lead_id, opportunity_id, and timestamp so the model query stays fast as volume grows.
• Write a retention rule: keep raw touches for 25 months to cover year-over-year reports plus a buffer.

Every lead carries two reserved sets of fields the model treats as immutable context: first_touch_source, first_touch_medium, first_touch_campaign, first_touch_content, first_touch_term, and first_touch_date, plus the matching last_touch six. Write the first-touch set from the earliest touch row associated with the anonymous visitor id, and the last-touch set from the most recent touch before the lead was created. These are the fields sales reps read on the lead card, the fields a routing rule evaluates to assign owners, and the fields a quick ad-hoc report groups by when leadership asks which channel is working this week. The multi-touch model consumes the touches table directly, so the lead-level stamp fields exist for human readability and for the single-touch supporting views, not for the primary model.

• Create first_touch and last_touch field sets on the lead object (six fields each).
• Write first-touch values from the earliest associated touch row at the moment of lead creation.
• Write last-touch values from the most recent touch at every subsequent form fill or conversion event.
• Expose both sets on the lead detail layout so sales reps can see the context without opening a report.

When a lead converts to an opportunity, the attribution context has to travel with it. Create the same first_touch and last_touch field sets on the opportunity object, and populate them at lead-to-opportunity conversion by copying the lead-level values. Also stamp an opportunity_created_touch set from the touch that most immediately preceded the opportunity creation event, since the W-shape and U-shape models weight that touch heavily. Freeze all three sets as read-only the moment the opportunity reaches closed-won or closed-lost, so a finance audit six months later can trust the fields have not drifted. The opportunity record is where revenue lives in the CRM, and revenue attribution is impossible if the source context does not travel from the lead onto the opportunity at conversion time.

• Create first_touch, last_touch, and opportunity_created_touch field sets on the opportunity object.
• Copy first_touch and last_touch from the lead at conversion; stamp opportunity_created_touch from the latest touch row.
• Freeze all three sets as read-only on close so the historical record stays stable.
• Expose the three sets on the opportunity detail layout so sales and finance can see the full source story.

Most CRMs ship a native lead_source picklist that predates UTM tagging and that sales reps have been editing by hand for years. Rather than deleting it, map the UTM-driven touch data to the lead_source picklist values with a deterministic rule: utm_medium of cpc maps to Paid Search, utm_medium of social maps to Paid Social, utm_medium of email where utm_source is in your lifecycle allowlist maps to Nurture Email, and so on. Write the mapping as a case statement in a single automation that runs at lead creation, document the mapping in the wiki, and audit it quarterly for drift. The payoff is that the lead_source field finance has been reporting on for years now reflects the governed UTM reality, and nobody has to maintain two parallel source taxonomies.

• List every current lead_source picklist value and the UTM pattern that should map to it.
• Build a single automation that evaluates UTMs at lead creation and writes the correct lead_source value.
• Lock the lead_source field as read-only to sales reps so the automation remains the single source of truth.
• Audit the mapping quarterly against actual campaign activity and update the case statement as channels evolve.

The payoff of all the preceding work is a report that answers one question in a way the CFO will sign off on: how much closed-won revenue did each channel, each campaign, and each content asset produce in the last quarter under the agreed model. Build the report as a view that joins the opportunities table (filtered to closed-won in the reporting period) against the touches table (filtered to the lookback window before each opportunity close date), applies the model credit-split math per opportunity, and sums the attributed revenue by utm_source, utm_medium, and utm_campaign. Expose three cuts of the same data: channel (utm_medium), source (utm_source), and campaign (utm_campaign). Add a toggle to switch between first-touch, last-touch, and multi-touch views so stakeholders can see the sensitivity of the number to the model choice.

• Build a view that joins closed-won opportunities to touches within the lookback window per opportunity.
• Apply the credit-split math (linear, W-shape, or U-shape) and sum attributed revenue by utm_source, utm_medium, and utm_campaign.
• Add a model toggle so the same data can be shown first-touch, last-touch, and multi-touch.
• Schedule the report to refresh daily and send a weekly digest to marketing leadership.

The last build step is the reconciliation that turns a prototype into a trusted system. Pull GA4 session counts by utm_source and utm_medium for the last 90 days, pull the touch-table counts for the same window, and diff the two by channel. A gap under 5 percent is normal (bots, consent, iOS mail), a gap over 15 percent on any single channel is a bug worth hunting. Common culprits are a form that fails to pass UTMs on submission, a redirect that strips query parameters, an email client that rewrites tracking URLs, and a mobile deep-link flow that drops the parameters at install. Fix every gap over the threshold before you present the first attribution deck to leadership, so the model goes live with a clean reconciliation on the record.

• Pull GA4 sessions and touch-table rows by utm_source and utm_medium for the same 90-day window.
• Flag any channel where the gap exceeds 15 percent as a bug and open a ticket per channel.
• Fix the top three culprits (form passthrough, redirect strip, email rewriting) before model launch.
• Rerun the reconciliation monthly and report the gap as a data-quality metric to marketing leadership.

Attribution is not a launch, it is a cadence. Every quarter, pull the last four quarters of closed-won deals, rerun the model with each supporting view (first-touch, last-touch, linear, W-shape, U-shape), and compare the ranked channel list across models. If the top three channels are stable across all five models, the primary model is doing its job. If the top channels swap positions depending on the model, that is a signal that the sales cycle has shifted and the model weights may need to move with it. Treat weight changes as a config edit with a dated changelog entry, communicate every change to sales and finance before the next revenue report ships, and never retroactively rewrite historical reports with new weights.

• Rerun the last four quarters of closed-won revenue under every available model view.
• Compare the top ten channels and campaigns across models and flag any that change rank significantly.
• Propose weight changes as config edits with a written rationale and a changelog entry.
• Communicate any change to marketing, sales, and finance before the next revenue report publishes.