Integrating Marketing and Attribution Data

In plain terms

A customer clicks a search ad, later follows an email link, and buys a subscription. The order database records one purchase, while several marketing reports may assign credit to it. Integrating marketing attribution data means connecting spend, observed interactions, identities, and conversions, then making the credit rule explicit. Assigned credit alone does not establish that an advertisement caused a purchase.

This article builds on the event identity, incremental loading, and managed ingestion articles. The following examples examine campaign mapping, paths, attribution rules, revisions, calendars, and reconciliation. It does not connect to advertising services or estimate incremental advertising effects.

The five tables and their grains

The grain states what one row represents. Spend is an aggregate: its key includes vendor, account, campaign, reporting date, currency, and any requested breakdown such as device or country. Store the reporting time zone, extraction time, and report configuration too. Campaign IDs are stable within their vendor/account scope; names are descriptive attributes. Mixing different breakdowns can count the same spend twice.

A touchpoint is one observed click or visit, keyed by an event ID, with event time, campaign identifiers, and an anonymous or account ID. Deduplicate retries using that event ID; a vendor click ID is supporting evidence, not a universal event key. Identity links connect identifiers with their provenance, validity interval, and applicable measurement permission. A conversion is one defined business event, keyed by order or conversion ID, with account, time, status, and amount. An account can have many conversions. Refunds, cancellations, and duplicate events require an explicit conversion definition.

Vendor attribution reports are a fifth dataset. Their grain depends on the API and selected dimensions: they may be aggregates with fractional or modeled conversions, rather than individually identifiable purchases. Keep their model, window, metric definition, and report date basis. Business records establish the chosen conversion population; observed touches only describe what the platform captured. Vendor figures remain useful for their reporting and optimization purposes, but cannot be assumed to be a deduplicated cross-channel total.

Campaign taxonomy and safe joins

Maintain a campaign dimension mapping scoped vendor IDs to channel, objective, market, launch, and owner. Preserve names as reported and version the mapping when historical classification matters. A rename must not create a new campaign. A new, unmapped campaign must remain in an unknown bucket and in total spend; report it to the owner for classification. An inner join that drops it makes the dashboard silently incomplete.

Aggregate spend and attributed conversion credit separately to the same reporting grain before joining them. Joining one campaign-day spend row directly to ten touches repeats that spend ten times. For example, 100 dollars of spend and 200 dollars of credited revenue produce a reported ROAS of 2, not 0.2 after a fan-out join. Align currency and its conversion rule, taxes, refunds, date basis, and channel scope first. ROAS uses revenue credit; cost per acquisition uses a defined acquisition count. Neither ratio proves incremental return.

From a click to a conversion path

A landing event carries campaign parameters or a click identifier. Login can link its anonymous ID to an account, but a shared device or later account switch makes a permanent anonymous-to-person mapping unsafe. Resolve links under a documented validity rule and retain the identity snapshot used for a report. Respect applicable collection and linkage permissions; an identifier match alone is not permission to join.

For each conversion, collect eligible touches on its resolved identities and filter to the attribution lookback window. Define whether each time boundary is included and how ties are ordered, and apply the versioned identity-link validity rules. A conversion without an eligible touch is unattributed under this policy. Missing tracking, missing links, or an expired window can produce such a path in production, so unattributed does not establish a direct visit.

A path is evaluated separately for each conversion. If one customer makes two eligible purchases after one click, that click can appear in both paths under an every-purchase policy; each purchase still contributes only one total credit. Counting only the first acquisition, or resetting the path after a purchase, produces a different metric and needs an explicit rule. Repeated delivery of the same conversion ID is a duplicate, not a second purchase.

Attribution rules and what their totals mean

Last touch assigns all credit to the final eligible touch; first touch to the first; linear divides it equally. Time decay weights recent touches more heavily. For example, a time-decay rule can use a seven-day half-life and normalize the weights to sum to one. Position-based attribution gives 40% each to the first and last touches and shares 20% among the middle touches; one-touch and two-touch paths receive 100% and 50/50 respectively. For search, email, social, a linear path gives each one third; position-based gives 0.4, 0.2, 0.4.

For every conversion, credits must be finite, nonnegative, and sum to one, including an unattributed bucket for an empty path. Summing these shares yields conversion credit, not additional purchases. Multiplying each share by that conversion’s eligible revenue gives revenue credit, with a separate refund policy and rounding rule.

These are educational warehouse rules, not a list of every vendor’s current options. Google Ads documents data-driven attribution and last click, and has retired first click, linear, time decay, and position-based models. See Google Ads attribution models. Vendor models and windows vary, and reports can include view-through or modeled conversions.

Version the window, model, eligible touch types, identity snapshot, conversion definition, and effective date. Preserve permitted source snapshots and calculation versions so past reports remain explainable within retention limits. Comparing rules shows sensitivity to a reporting choice. To estimate additional purchases caused by advertising, use an appropriate incrementality design, such as randomized holdouts, with its own assumptions and analysis.

Reporting dates, conversion lag, and restatements

An event has an instant; a daily report has a calendar and a date basis. Store event timestamps in UTC and convert them with a named time zone when constructing reporting days. For example, America/Los_Angeles changes offset during March 2026. A fixed minus-eight-hour offset would misclassify some dates after daylight saving starts. A vendor account need not use Pacific time. Attribution reports may group by interaction date or conversion date; choosing the same time zone does not resolve that difference. See Analytics attribution settings and Python zoneinfo.

A daily spend aggregate cannot generally be converted into one UTC date: a vendor day spans parts of two UTC days. Preserve its original reporting date and time zone. To compare, aggregate your events on that same calendar; to split spend across UTC days, obtain finer-grained data or explicitly label an allocation estimate.

Keep three horizons separate. The attribution window defines which past touches may receive credit. Cohort maturation determines how long to observe a set of campaign interactions before assessing later conversions. The ingestion reread horizon captures late source corrections. Conversion lag informs maturation and window selection, but does not by itself set every reprocessing horizon. Late identities, refunds, source delays, and policy changes can require wider or targeted backfills.

For revised spend, fetch complete snapshots for selected reporting partitions, stage and validate them, then atomically replace those partitions, including rows that vanished or an empty snapshot. Retain extraction versions for as-of reporting. Choose routine reread periods from source behavior and monitoring, with deeper reconciliation and explicit backfills for older corrections.

Reconciliation and privacy in the pipeline

Compare like-for-like metrics before explaining a discrepancy: account, campaign, date basis, time zone, currency, extraction version, conversion definition, and attribution settings. Observed clicks are not necessarily billable clicks in a vendor API. A changing ratio suggests investigation of tags, redirects, consent, deduplication, reporting revisions, or scope; it does not identify the cause. Handle zero denominators and low-volume groups separately.

Reconcile connector spend against invoices with an explicit bridge for taxes, credits, fees, exchange rates, and reporting cutoffs. Compare platform and vendor conversions with their definitions visible; neither is required to be larger. Report unmapped spend, source revisions, missing partitions, and volume anomalies. Alert tolerances should reflect volume and expected behavior, rather than a universal percentage.

For this example’s proposed policy, collect measurement touches only where measurement consent permits it. This is a design choice under a reviewed policy, not a universal legal rule. Minimize identifiers and restrict access. Hashed emails and pseudonymous IDs can remain linkable and must not be described as anonymous merely because their representation changed. Record applicable consent state and policy version; govern retention, downstream deletion, and any affected derived reports under the relevant requirements.

Keep observed and modeled conversion metrics distinguishable where the API exposes that distinction, and otherwise record that they are mixed or unknown. A modeled conversion aggregate may be compared at an aggregate level with aligned scope and uncertainty, but cannot necessarily be matched to an individual order. This metadata belongs to conversion reporting, not to an invented observed-versus-modeled spend flag. Google modeled online conversions explains how observed and modeled conversions can appear together in reporting.

Assembling the subscription pipeline

Use managed connectors or source-specific adapters for ad reports and email click events. Land raw report versions with extraction manifests. Validate keys, requested dimensions, currencies, date ranges, and completeness before replacing reporting partitions. Merge email clicks by event ID while keeping message ID as a separate attribute; one message can have several clicks. Ingest business conversions and versioned identity links from the database and event pipelines.

Build eligible paths from those inputs, persist conversion-by-touch credit with a rule version, and represent empty paths explicitly as unattributed. Aggregate credit and spend separately before the reporting join. Rebuild affected conversions when an input or policy changes; a nightly rolling window is an optimization supplemented by targeted historical backfills. Keep rule configuration, input versions, run ID, and reconciliation results together. A dashboard user should be able to tell whether a number changed because of new business activity, a source revision, or a different attribution rule.