# Lead Source Attribution Cleanup + Overall Traffic Reporting

Document `REVOPS-LI-006` · Workflow `LI-006` · HubSpot + GA4 + n8n · Prepared 8 October 2026

This implements the attribution direction in your pasted document, with **GA4 for overall traffic** and **HubSpot for captured contact attribution**. It captures raw conversion payloads, normalizes UTMs, preserves original source, updates newer latest touches, reconciles shared submissions, creates optional review tasks, and exposes an aggregate report.

All six workflows import inactive. HubSpot writes default to **off**. No credentials, property IDs or real account data are embedded. This is a configured-build template, not a deployed automation.

## The reporting model

| Layer | What it answers | Source |
|---|---|---|
| Overall traffic | Sessions, distinct visitors, engaged sessions, page views, key events by source/medium/campaign/channel | GA4 Data API |
| Captured conversions | Which tracked submissions occurred, which contacts were matched, and which sources were captured | Website/form backend events + HubSpot lookup |
| Contact history | First known acquisition source, latest reliable conversion source, source lock and quality status | Custom HubSpot properties |
| Governance | Missing data, source disagreements, identity conflicts, inferred timestamps and proposed/applied changes | PostgreSQL audit vault |

GA4 aggregate reports cannot identify a CRM contact by email. This package does not pretend that a session count and a CRM conversion-date count are a person-level join. It shows them alongside each other without inventing a cohort conversion rate, revenue attribution or ROAS. Multiple submissions can belong to one contact; captured conversion counts are not automatically new-lead counts.

## Files to import

| n8n JSON | Role |
|---|---|
| `01-attribution-capture.n8n.json` | Authenticate and persist raw conversion events before normalization; acknowledge and start the worker. |
| `02-hubspot-attribution-worker.n8n.json` | Resolve identity, read existing CRM values, normalize/classify, protect first touch, update newer latest touch, audit, review and drain queued work. |
| `03-attribution-property-setup.n8n.json` | One-time custom contact property setup. |
| `04-ga4-traffic-sync.n8n.json` | Daily, paginated GA4 traffic sync with a seven-day refresh window and separate overall totals. |
| `05-attribution-report-api.n8n.json` | Authenticated GET endpoint returning traffic, conversions and data-quality information. |
| `06-attribution-operations.n8n.json` | Error handler plus scheduled queue/review/stalled-work inspection. |

Supporting files: required `database-setup.sql`, sandbox `database-smoke-test.sql`, `website-attribution.js`, `sample-conversion-payload.json`, `hubspot-attribution-properties.json`, and `validation-report.json`. Only files ending `.n8n.json` are workflow imports.

## Setup

1. Use a dedicated PostgreSQL 14+ application database reachable from n8n. Run `database-setup.sql`. If setup and n8n use different database roles, apply the commented grants at the bottom for your application login. Use TLS. The schema is `attribution`; it does not alter n8n's internal tables or the earlier `revops` schema.
2. Run `database-smoke-test.sql` in your sandbox. It rolls everything back. These tests were supplied but could not be executed here because a PostgreSQL server was not running.
3. Import all six `.n8n.json` files individually into a current n8n instance that supports their node versions. Save each workflow.
4. Bind all placeholder credential references to the credentials below.
5. Run workflow 03 manually with contact schema read/write permissions. It creates missing custom properties and refuses incompatible existing definitions. HubSpot native analytics source fields are untouched.
6. In workflow 02's **Attribution Configuration**, set your `own_domains` hostnames. Leave `write_to_hubspot: false` for sandbox verification. Optionally supply `review_owner_id`; otherwise tasks use the contact's owner if present.
7. In workflow 01 **Start Attribution Worker**, select your imported workflow 02. In workflow 02 **Drain Next Event**, select workflow 02 itself. Keep **Wait for Sub-Workflow Completion** off for both. Allow intake and worker calls in the worker's workflow settings.
8. In workflow 04 **GA4 Configuration**, set the numeric property ID and exact property timezone, for example `Asia/Tbilisi`. Use the property ID, not the `G-...` measurement ID. In workflow 05 **Report Configuration**, set the same property ID.
9. Select workflow 06 as the **Error Workflow** for 01, 02 and 04. IDs are instance-specific and must be selected after import. Save/publish/activate 01, 02, 04, 05 and 06 as your n8n version requires. Workflow 03 stays manual.
10. Install the website/form integration described below. Capture a test submission and run the worker manually if needed. Inspect its proposed changes and audit row. Run GA4 sync manually and inspect its final report node.
11. When the first-touch/latest-touch and review cases behave correctly, change `write_to_hubspot` to `true`. This permits contact patches and optional review-task creation. Earlier dry-run events are not automatically replayed into CRM.

## Credentials

| Credential | n8n type | Configuration |
|---|---|---|
| ATTRIBUTION Postgres | Postgres | Dedicated application DB/login and encrypted connection. |
| ATTRIBUTION Form Secret | Header Auth | Header such as `X-Attribution-Secret` with a random secret; trusted backend only. |
| ATTRIBUTION Report Secret | Header Auth | Separate report-read header/secret, such as `X-Attribution-Report-Secret`. |
| ATTRIBUTION HubSpot | Header Auth | `Authorization: Bearer YOUR_PRIVATE_APP_TOKEN`. Contact read/write, schema read/write for setup, and permissions to create task activities when enabled. Verify the task operation's accepted scopes for your app platform. |
| ATTRIBUTION GA4 | OAuth2 API | Google OAuth authorization-code credential. Auth URL `https://accounts.google.com/o/oauth2/v2/auth`; access-token URL `https://oauth2.googleapis.com/token`; scope `https://www.googleapis.com/auth/analytics.readonly`; authentication in body. Register n8n's displayed redirect URI in your Google OAuth client. Enable Google Analytics Data API and authorize a Google account with access to the property. Use offline access (`access_type=offline`, and consent prompting when needed) for refresh tokens. |

No AI credential is required. UTM normalization and source governance use deterministic rules.

## Website, forms and GA4

GA4/GTM must already measure your actual site traffic. This workflow reads GA4; it does not install GA4 or reconstruct visitors that GA4 never observed. Configure the relevant lead conversion events as key events in GA4 if they should appear in key-event reporting. The report's `keyEvents` metric includes **all** key events configured in the property, not only lead forms.

`website-attribution.js` is an optional integration helper:

- `capture({analyticsConsent:true})` retains the first known browser touch and current session touch, raw UTMs, click IDs and first landing/referrer context.
- Storage occurs only after explicit analytics consent is passed by your own consent layer. Call `clear()` on revocation if your implementation requires clearing it.
- The helper uses a 30-minute inactivity window and a 90-day first-touch retention window. Its visitor/session IDs are local tracking identifiers, **not GA4's own user/session IDs**.
- `conversionPayload(...)` prepares form attribution data. It does not submit forms or call n8n.
- After the backend confirms a successful form submission, `emitSuccessfulConversion(payload, true)` pushes an allowlisted `revops_lead_conversion` event to `dataLayer`. Configure your GTM GA4 event tag. Email, phone, contact ID and raw form payload are excluded from that analytics event.

Example integration in your form's success flow:

```javascript
// On landing, after your consent layer permits analytics storage:
RevOpsAttribution.capture({ analyticsConsent: true });

// Keep this object stable if the same submission must be retried.
const attribution = RevOpsAttribution.conversionPayload(
  { email: submittedEmail },
  {
    analyticsConsent: analyticsAllowed,
    eventName: 'demo_request',
    formId: 'demo-form',
    formName: 'Book a demo',
    formType: 'native'
  }
);

// Send through your normal form backend. The backend verifies submission
// success, records the same submission_id, creates/updates the HubSpot contact
// through your capture flow, and forwards attribution to the n8n webhook.
// After backend confirmation, emit the analytics conversion once:
RevOpsAttribution.emitSuccessfulConversion(attribution, analyticsAllowed);
```

The backend, not browser code, supplies the webhook secret and any authoritative contact ID. Add your normal form bot/rate/size controls there. Never include the secret in frontend JavaScript. Keep personal data out of campaign names, page URLs and GA4 parameters.

For embedded forms or external calendars, implement a provider-specific success callback/webhook adapter. Pass the same `submission_id` to the website conversion event and form backend event wherever the provider permits it. Browser cross-domain storage, iframe messaging and arbitrary third-party widgets are not automatically integrated by this helper.

The webhook's production path is:

```text
POST https://YOUR_N8N_HOST/webhook/revops-li-006-conversion
```

Use `sample-conversion-payload.json` as the contract. Replace its example address and historical timestamps for sandbox testing.

## Event contract and reconciliation

Required fields: stable `event_id` UUID and `producer` (`website`, `form`, `crm`, or `analytics`). `event_id` is unique for one producer's event. Replays use the same ID and identical payload.

`submission_id` identifies one conversion across systems. A form event and website event have **different event IDs but the same submission ID**. Contact identity can be an authoritative `contact_id` or email. The worker reads an existing HubSpot contact; it does not create contacts or merge duplicates. Your normal form-to-CRM flow creates the contact. The earlier lead-routing template can serve that role after adapting its payload.

Optional tracking data: top-level UTMs, click IDs, landing/conversion/referrer URLs, form metadata, conversion timestamp, visitor/session IDs, and a `first_touch` object. A missing conversion timestamp uses receipt time and records the inference. Invalid or future timestamps trigger review. There is a 64 KiB envelope guard; raw JSON is persisted before cleanup. The vault preserves parsed JSON values, not the original HTTP byte formatting.

Automatic matching uses direct email/contact ID or an unambiguous shared submission ID. Different emails/IDs for the same submission block updates. Visitor/session/form matches in the 15-minute-before / 60-minute-after **receipt-time** window are retained as suggestions only. They do not automatically authorize a CRM match; the template does not falsely treat receipt time as contact creation time. Phone and company domain alone are not safe enough for this template to attach a conversion to a person.

Missing identity or a not-yet-created contact gets a five-minute recheck, up to 12 attempts or a two-hour horizon. Late matching events are available from raw history. After that the event enters review. Related lookup is capped at 100 events for one reconciliation; pathological volumes or recycled submission IDs require manual investigation.

Webhook responses: 202 for stored events and identical replays; 409 when the event UUID is reused with different data; 400 for invalid envelopes. A failed database write produces an error, not a false queued acknowledgment.

## Classification and preservation rules

| Evidence / condition | Result |
|---|---|
| Complete known UTMs | Normalize aliases and classify using source-aware medium rules. |
| `linkedin` + `cpc` | Paid Social; generic CPC is interpreted in its source context. |
| `google` + `cpc` in captured form data | Paid Search heuristic; GA4 traffic uses GA4's actual session channel grouping instead. |
| `fbclid` alone | Facebook click context, partial attribution, channel unresolved; paid status is not inferred. |
| `gclid` alone | Google paid-click context, network unresolved; it is not forced into Paid Search. |
| Missing UTMs and missing referrer | Unknown unless the capture layer explicitly provides complete observed Direct evidence. |
| External referrer only | Conservative Referral classification with a heuristic warning. |
| Form type/newsletter/demo/webinar event | Keep the conversion type separate; it is not sufficient evidence of acquisition channel. |
| Unknown or ambiguous social medium | Review rather than assuming paid or organic. |

Source aliases include `li` / `linkedin_ads` → `linkedin`, `fb` / `meta` → `facebook`, and `google_ads` → `google`. Instagram stays distinct from Facebook. Campaign labels use lowercase/underscores. Raw variants are retained; GA4 reporting flags normalized campaign groups with multiple raw name variants.

Original source, original detail and first-touch timestamp are set only if the source is empty and valid evidence exists. They are preserved once present, including UNKNOWN until a human authorizes a correction. An older original touch discovered later triggers review. Latest source and its UTMs change only when reliable incoming evidence is newer. Older events remain history. Empty fields from a valid newer Direct/Referral touch can clear obsolete latest campaign values so an old campaign is not carried into a new touch.

`revops_attribution_locked=true` prevents all automatic attribution property changes and new review tasks for that contact. Incoming data still enters history. `revops_source_conflict_flag=true` also holds later changes until a human resolves the conflict and clears the flag. Same-submission source disagreement blocks source-field writes and preserves both versions in the audit vault.

Review tasks are optional, created only when CRM writes are enabled and a contact is known. Existing task IDs in shared-submission history suppress another task. A lost response to task creation can still require timeline reconciliation; no exactly-once task guarantee is claimed.

Only custom `revops_` contact properties are written. HubSpot native Original Traffic Source properties are not rewritten. Associated company IDs are captured in the audit record; **company source fields are not overwritten from an individual contact's event**. Different people at one company can arrive through different campaigns, so company acquisition rollups need a separate agreed rule. Deal/customer history, revenue attribution, duplicate merging and sales routing remain outside this flow.

## GA4 sync and report

Workflow 04 runs daily at 08:00 Asia/Tbilisi by default. It calculates the previous seven complete days in the **GA4 property's configured timezone**, requests date/session source/session medium/session campaign/session default channel group, and pages all rows. Change the n8n schedule timezone if desired; report date boundaries continue to use the property timezone.

The worker stages a new database snapshot. It publishes only after every page and the unsegmented totals request succeed. A row-count change, premature empty page, timezone mismatch, invalid metrics or row cap failure prevents publication. Reports keep the last complete snapshot, which means you must check `published_at` for freshness. Refreshing the full seven-day window replaces disappearing/corrected campaign rows rather than leaving stale upserts visible. Historical windows outside that refresh are not automatically rebuilt; increase `lookback_days` (up to 90) or implement an explicit historical backfill.

Distinct users come from the **separate unsegmented request**. They are never summed across source rows or daily results. GA4's native channel label is retained, including Cross-network and Display, rather than inferred solely from `cpc`. The default page size is 10,000 and maximum accepted result size is 250,000 rows; exceeding it fails clearly instead of returning a silently truncated report.

The seven-day refresh accommodates ordinary late changes but is not a guarantee that GA4 data is finalized. Sampling metadata, thresholding metadata, `(other)` rows, schema restrictions and quota metadata are retained for inspection. Consent differences, blockers, timezone boundaries, event definitions and delayed reporting can explain differences between traffic and backend conversion counts.

Read the aggregate report with the separate report credential:

```text
GET https://YOUR_N8N_HOST/webhook/revops-li-006-report
X-Attribution-Report-Secret: YOUR_REPORT_SECRET
```

Response sections:

- `period`: explicit dates and property timezone.
- `traffic.overall_totals`: GA4 sessions, distinct total users, engaged sessions, page views and all key events.
- `traffic.by_campaign`: normalized source/medium/campaign plus native GA4 channel and traffic metrics.
- `captured_conversions`: deduplicated conversions, distinct matched contacts, unmatched/review counts and campaign breakdown.
- `quality`: event queue states, stalled worker indication and retained GA4 quality/quota metadata.
- `definitions`: metric and comparison limitations.

No lead email, raw form contents or visitor identifiers are returned by the report endpoint. Distinct matched contacts per campaign are also not additive across campaigns. The report does not calculate an apparent conversion rate from mismatched attribution models/time cohorts.

## Operations and audit

The raw events table is immutable by workflow convention. Each attempt stores its previous CRM properties, normalized selection, match method, candidate event IDs, conflicts, proposed changes and preserved fields in `attribution.audit` **before** a CRM write. The event row records whether a patch succeeded and any review task ID. Captured conversion facts deduplicate on shared submission ID; without it, different event IDs remain separate, so duplicate counting is possible across producers that omit the shared key.

One persisted lease serializes attribution writes per tenant. It also protects original-source decisions from competing executions within this integration. The worker dispatches the next ready event immediately after finishing; a five-minute schedule handles retries and queued-work recovery. External HubSpot edits can still race the read/patch boundary; this is not a transactional lock on HubSpot itself.

Workflow 06 records automatic failures and returns operational counts every 15 minutes. It does not send Slack/email notifications. Use the report quality section, n8n execution monitoring or your own operations channel. Error Trigger does not run the same way for manual test failures. A stalled lease is not automatically stolen while an old execution might still write.

Inspect failures with:

```sql
SELECT event_id,status,attempts,execution_id,crm_applied,review_task_id,error,decision
FROM attribution.events WHERE event_id='YOUR_EVENT_UUID'::uuid;

SELECT * FROM attribution.audit WHERE event_id='YOUR_EVENT_UUID'::uuid ORDER BY attempt;
```

For a stuck manual execution, cancel/confirm termination first. Lock and inspect its event, reconcile any contact/task changes already made, then release its `worker_lease`. Requeue only after resolving the cause and confirming the intended preservation rules. Never delete raw history to force a retry. A failed GA4 run remains unpublished; start a new execution rather than attaching additional pages to that run.

Native HubSpot contact-update webhooks and provider form webhooks need adapters to the event contract. If you add a HubSpot update trigger, ignore writes made by this integration or limit the triggering fields to prevent loops. This template's scheduled worker retries its own queue; it does not scan every existing HubSpot contact for historical cleanup.

## Verification

61 local checks pass: workflow graph/JSON/embedded-code syntax, UTM aliases and URL cleanup, source-aware classification, missing/click-only evidence, timestamps, strong/weak identity matching, first/latest preservation, locks/conflicts, dry-run controls, GA4 pagination/schema/timezone/caps/totals, and browser helper consent behavior. See `validation-report.json`.

Not verified here: live n8n import/execution, PostgreSQL function execution/concurrency, Google OAuth, GA4 property-specific metric compatibility, HubSpot tenant permissions, website deployment or actual traffic. No external account was modified.

Before enabling writes, verify: identical webhook replay, same-submission form/website matching, conflicting email/UTMs, newer and older conversions, a locked source, missing CRM contact followed by creation, a late website event, task reconciliation, paginated GA4 results, timezone mismatch failure and a complete report with the expected counts. The supplied database smoke test is sequential; run concurrent requests in your own sandbox to validate the lease under load.

## Official references

- [GA4 runReport, paging and authorization](https://developers.google.com/analytics/devguides/reporting/data/v1/rest/v1beta/properties/runReport).
- [GA4 dimensions and metrics](https://developers.google.com/analytics/devguides/reporting/data/v1/api-schema), [response quality metadata](https://developers.google.com/analytics/devguides/reporting/data/v1/rest/v1beta/ResponseMetaData).
- [Google ad click IDs span ad products](https://www.google.com/ads/adtrafficquality/advertisers/).
- [HubSpot contact operations](https://developers.hubspot.com/docs/api-reference/legacy/crm/objects/contacts/guide), [task activities](https://developers.hubspot.com/docs/api-reference/legacy/crm/activities/tasks/guide).
- [n8n HTTP Request](https://docs.n8n.io/integrations/builtin/core-nodes/n8n-nodes-base.httprequest/), [Postgres](https://docs.n8n.io/integrations/builtin/app-nodes/n8n-nodes-base.postgres/), [sub-workflows](https://docs.n8n.io/integrations/builtin/core-nodes/n8n-nodes-base.executeworkflow/).