Marketing Attribution Data Model: From Click to Revenue

Key Takeaways: A marketing attribution data model connects a campaign interaction to a durable lead, a CRM lifecycle event, a deal or order, and a revenue outcome. It keeps identifiers, timestamps, consent state, and ownership explicit. The model is not an attribution report by itself. It is the evidence structure that lets a team explain how a report was produced.

The key design choice is to preserve facts before applying a reporting rule. Store the click or campaign context, then calculate first-touch, last-touch, position-based, or other views from those facts. Do not overwrite the original record every time a new visit occurs.

Start with the business outcome

Define the outcome before defining the event. Lead can mean form submitted, lead accepted, qualified opportunity, signed contract, paid order, or retained customer. Those are different events with different owners and timestamps.

Write the outcome contract first:

Outcome	Business meaning	Required evidence
Lead created	A form or inquiry became a record.	Stable lead ID and creation time.
Qualified lead	A human or rule accepted the lead.	CRM stage, owner, and qualification time.
Opportunity	A commercial opportunity exists.	Deal ID, pipeline, and amount rule.
Closed revenue	The business recorded the outcome.	Closed time, value, currency, and transaction ID.

The offline conversion tracking service is relevant when the marketing event starts in a browser but the valuable outcome happens later in a CRM or sales process. The marketing data engineering guide provides the broader system path, while the GA4, BigQuery, and CRM architecture guide and consent-aware attribution guide cover adjacent implementation boundaries.

Core entities in the model

Keep the first version small. Most teams need these entities:

1. Touchpoint: a tagged landing visit, ad click, referral, or direct interaction.
2. Conversion event: a form submission, call, signup, order, or other defined event.
3. Lead: the durable person or inquiry record created by the conversion.
4. Lifecycle event: qualification, disqualification, opportunity creation, or closure.
5. Revenue fact: the recognized amount, currency, transaction, and date.
6. Delivery event: a record sent to another system, including status and retry data.

These entities may live in different products. The model is the contract between them, not a requirement to place everything in one table.

Preserve identifiers without confusing them

Different identifiers answer different questions:

Identifier	Question it answers	Typical owner
utm_source, utm_medium, utm_campaign	Which manually tagged campaign context was present?	URL and capture layer
utm_id	Which campaign ID was assigned?	Campaign taxonomy
gclid	Which Google Ads click identifier was available?	Google Ads and lead record
fbclid or other platform click ID	Which platform click identifier was available?	Platform and lead record
session_id	Which analytics session or visit was observed?	Analytics layer
lead_id	Which durable inquiry record was created?	CRM
deal_id	Which commercial opportunity or outcome was changed?	CRM
transaction_id	Which order or revenue event is being counted?	Commerce or finance

The UTM and click-ID capture product can be part of the site-side boundary, but a captured identifier does not prove that a later destination accepted or matched an outcome.

A minimal logical schema

One practical shape is:

touchpoint
  touchpoint_id
  observed_at
  landing_url
  referrer
  utm_source
  utm_medium
  utm_campaign
  utm_id
  utm_content
  gclid
  fbclid
  consent_state

conversion_event
  event_id
  occurred_at
  event_name
  lead_id
  transaction_id
  touchpoint_id
  source_system

lifecycle_event
  lifecycle_event_id
  lead_id
  deal_id
  stage
  occurred_at
  owner

revenue_fact
  transaction_id
  deal_id
  amount
  currency
  recognized_at

The exact table names are implementation choices. The important properties are stable IDs, event times, source systems, and relationships that can be checked.

Connect click to lead

At lead creation, capture enough context to answer:

• Which touchpoint or campaign values were present?
• Which landing page and form produced the record?
• Which consent state applied?
• Was the click identifier available?
• Which lead ID was created?
• Was this a new lead or a duplicate submission?

Keep the original values and normalized reporting values separately when normalization could hide a meaningful difference. For example, Meta and meta may represent a taxonomy error worth fixing rather than two values to silently merge.

Google Analytics documentation recommends consistent campaign naming and explains that parameter values are case-sensitive. The campaign parameter guide is therefore a useful reference for the taxonomy layer, not a substitute for CRM persistence.

Connect lead to lifecycle outcome

The lead record should not be the only business fact. Model lifecycle changes as events or a history table rather than repeatedly overwriting one status field. A history makes late qualification, re-opened deals, and ownership changes explainable.

Example:

lead-created       2026-09-04T09:10:00Z
lead-qualified     2026-09-05T14:30:00Z
opportunity-opened 2026-09-06T11:00:00Z
deal-closed        2026-09-18T16:45:00Z

The time of the event matters. A worker that imports the closed deal on September 19 should not replace the business event time with the processing time.

Keep attribution rules as views

First-touch and last-touch are useful reporting views, but they should not destroy the underlying touchpoint history.

View	Rule	Risk if stored as the only source
First touch	Earliest eligible touchpoint before the lead.	Later evidence becomes invisible.
Last touch	Latest eligible touchpoint before the outcome.	A support or return visit may receive all credit.
Position-based	Allocate a defined share across selected touches.	Shares can look precise without a clear eligibility window.
Platform-reported	Use a platform’s own attribution model.	Scope and windows may differ from CRM reporting.

Store the touchpoint facts once, then calculate the view with a dated rule. Record the rule version in the report or model output.

Model consent and data minimization

Consent is part of the data contract. Store the state and the decision time alongside the event that depends on it. Do not put email addresses, phone numbers, names, or other personal data into Analytics campaign parameters. Google Analytics guidance explicitly warns against sending personally identifiable information to Analytics.

For a consent-aware model, distinguish:

• what was observed in the URL;
• what was stored in a first-party lead record;
• what was allowed into an analytics system;
• what was allowed to leave the site;
• what was hashed or normalized by a downstream integration;
• what was withheld or marked unknown.

The server-side tracking and consent service can help define those boundaries. Server-side delivery still needs an approved purpose and field contract.

Connect CRM outcomes to revenue

Revenue attribution fails when the model has a lead but no durable connection to the deal or order. Require:

• a stable lead-to-deal relationship;
• one defined owner for revenue value and currency;
• a clear closed or paid timestamp;
• a transaction or order ID when deduplication needs it;
• a rule for refunds, cancellations, split deals, and renewals;
• a documented treatment for outcomes without a captured click ID.

Do not discard outcomes that lack a GCLID or UTM. Keep them in the business model and mark the available attribution evidence. Google Ads documentation recommends sending available conversion data and describes separate matching paths; the absence of one key does not make the revenue record invalid.

Warehouse layers that remain explainable

The BigQuery marketing data warehouse is useful when source events, CRM changes, and revenue facts need common queries. Google documents a raw GA4 export to BigQuery, while also noting that exported data and Analytics interface reports can differ. Treat the warehouse as an evidence layer, not as a promise that every dashboard number will match automatically.

A simple layered model is:

1. raw: source payloads and ingestion metadata.
2. normalized: stable field names, types, and approved taxonomy values.
3. identity: relationships between touchpoints, leads, deals, and transactions.
4. outcomes: lifecycle and revenue facts.
5. attribution_views: dated model outputs with rule versions.
6. delivery_status: accepted, processed, matched, reported, failed, and retried states.

Never hide a failed delivery by joining only successful rows into a marketing report.

Feed an ad platform without losing state

For an offline conversion workflow, a useful delivery record includes:

event_id
conversion_action
conversion_time
lead_id
deal_id
transaction_id
gclid, when available
approved user-provided data, when applicable
consent state
attempted_at
transport_status
processing_status
match_status

Google Ads documentation distinguishes input, processing, matching, and reporting concerns. Keep those statuses separate in your own logs. A file upload or API response can prove transport acceptance while the ad account still has no matched or visible conversion.

Validate the model with six synthetic cases

Before trusting the model, run:

1. tagged visit, form, qualification, and closed deal;
2. direct visit with no campaign parameters;
3. second campaign after an earlier first touch;
4. consent denied before form submission;
5. validation failure followed by one successful retry;
6. closed deal with no click ID but a valid CRM relationship.

For each case, inspect raw source, normalized fields, lead, lifecycle history, revenue fact, and delivery status. The Tracking and Attribution Audit is the right handoff when those records are spread across systems and no owner can explain the full path.

Frequently asked questions

Is an attribution model the same as a data model?

No. The data model stores entities, relationships, timestamps, and source facts. An attribution model applies rules to those facts. Keep the rules versioned and reversible.

Should first-touch and last-touch use the same field?

No. Preserve the touchpoint history and expose separate derived fields or views. A single mutable source field makes later audits difficult.

Does a GCLID guarantee a Google Ads conversion match?

No. It is one input to a matching workflow. Check the conversion action, time, consent, destination response, processing state, and reporting state separately.

Where should UTM fields live?

Store them at the lead or order boundary when the business needs campaign context after the browser session. Keep them separate from click IDs and from a computed attribution-model label.

Conclusion

Build the model around durable business records, not a dashboard screenshot. Preserve touchpoints, connect them to leads and lifecycle events, link outcomes to revenue, and expose delivery states. Then the team can change attribution rules without losing the evidence that made the result explainable.

Sources

• Google Analytics URL builders and campaign parameters, retrieved 2026-09-04.
• Google Analytics BigQuery export, retrieved 2026-09-04.
• Google Analytics and BigQuery data differences, retrieved 2026-09-04.
• Google Ads offline conversion imports, retrieved 2026-09-04.
• Google Ads enhanced conversions for leads checklist, retrieved 2026-09-04.

Evidence note: The schema and tests are implementation patterns. They do not describe a customer’s configuration or measured result. Adapt them to your source schemas and verify current platform behavior before use.