Lead Import & Marketing Attribution Columns

One shared module turns spreadsheet columns into lead fields for BOTH the manual CSV/XLSX importer and the Google Sheets connector, and the lead's marketing-attribution data is surfaced as read-only columns the user can show, pin, and map to on import.

Marketing attribution = where a lead came from. Two kinds: acquisition (which Meta/Google page, form, campaign, ad set, ad drove the lead) and touch (the UTM source/medium/campaign/term/content on the link they arrived through). It is captured automatically by the ingestion pipeline (Meta/Google webhooks, the web attribution script) — it is NOT a user-created custom field.

Why this exists

Before this change the two import flows had separate mapping logic — different target-field lists, different column-matching heuristics, and different row→lead code. A fix on one side silently skipped the other. And attribution lived only on the lead detail page: it could not be shown as a table column, and nothing stopped a user re-creating a "Source" custom field that duplicated the built-in attribution data (forking the value into two places).

This change introduces one shared import-mapping module as the single source of truth, makes both flows consume it, and adds attribution as first-class (read-only) columns + import targets with a duplicate-name guard.

Architecture Overview

How It Works

1. One target-field catalog

getImportTargetFields(customerUID) returns every field a column can map to, each tagged with a group:

• core — contactName, phone, email, status, leadTemperature, opportunityValue, createdAt.
• attribution — the 12 built-in marketing-attribution fields (keyed attr:*), split into Acquisition (attr:acq_page/form/campaign/adset/ad/platform) and Attribution/touch (attr:source/medium/campaign/term/content/channel_platform).
• custom — the org's own CrmCustomField rows (ones that duplicate a core/default key are skipped).

2. One matching heuristic

buildHeuristicMapping(columns, fields) is the only deterministic column→field matcher. It normalizes a header (lowercase, strip non-alphanumeric), looks it up against field labels/keys plus a core-alias table ("full name"→contactName, "mobile"→phone, "source"→attr:source, …), and de-dupes so each target is claimed once. Core fields always win over a same-named custom field. The LLM step (mapColumnsWithAI, unchanged) is seeded with this heuristic — it only ever refines within the allow-listed targets.

3. One value-application step

applyMappedRow(mapped, opts) routes each mapped value to its home on the lead:

• a core target → the top-level field,
• an attribution target → metadata (see the key-routing note below),
• a custom field → industryData (coerced to the field's declared type).

4. Attribution → metadata, then rebuilt

Attribution columns do not have a bespoke write path. Each attr:* target writes into the lead's metadata bag (acquisition → metaPageName/metaCampaignName/… ; touch → utmSource/utmMedium/…). The repository's existing normalizeAttribution then rebuilds the structured lead.attribution object from metadata — the same path Meta/Google ingestion uses.

Key-routing gotcha: touch targets write to the utm* metadata keys on purpose. The importer stamps metadata.source with the CHANNEL (csv_import / google_sheet); writing a mapped touch source to metadata.source would clobber that. touchFromBag reads utmSource into attribution.lastTouch.source, so the mapped value is preserved in metadata.utmSource without overwriting the channel.

5. Duplicate-name guard

When a user tries to create a custom field whose name (or derived key, or the bare concept — "Source" ≈ "Attribution Source") matches a built-in attribution field, findAttributionCollision catches it and both create-field endpoints return 409 ATTRIBUTION_FIELD_COLLISION. The user is told to map the column to the existing attribution field instead, or rename. The two import UIs also warn before the request is sent.

6. Attribution as read-only table columns

The frontend mirrors the catalog (app/src/lib/attributionColumns.ts). The leads table builds the 12 attribution columns as read-only virtual columns — resolved live from getLeadOrigin(lead) (acquisition) and lead.attribution (touch), never editable, never persisted as CrmCustomField. They appear in the Columns panel under a "Marketing Attribution" group (Acquisition + Attribution subsections) and are hidden by default; a user opts each one into the table, where it can be pinned and placed like any column.

File Reference

File	Role	Key Functions
utils/attributionColumns.ts	Canonical attribution catalog + collision detector	ATTRIBUTION_COLUMNS, findAttributionCollision(), isAttributionKey()
services/import/importMapping.service.ts	Shared target catalog + heuristic	getImportTargetFields(), buildHeuristicMapping()
services/import/applyMappedRow.ts	Shared row→lead value-application	applyMappedRow()
services/CrmLeadImportService.ts	CSV/XLSX import; now delegates to the shared module	getAvailableFields(), getMapping(), buildLeadData()
controllers/crmCustomField.controller.ts	Leads create-field endpoint; attribution 409 guard	createCustomField()
controllers/googleSheetsConfig.controller.ts	Sheets create-field + suggest; same guard + attribution targets	createCustomFieldHandler(), suggestMappingHandler()
services/connecter/googleSheetsPull.service.ts	Sheets import; routes attribution → metadata	prepareSheetRow(), importFromConfig()
services/connecter/googleSheetsSuggest.service.ts	Sheets mapping suggestion; delegates to shared heuristic	suggestMapping()
services/connecter/googleSheetsMapping.util.ts	Sheets mapping validation; accepts attr:* targets	parseSheetsMapping()
services/ai/columnMapper.service.ts	Shared LLM mapping layer (unchanged)	mapColumnsWithAI()

Frontend mirror: app/src/lib/attributionColumns.ts, components/leads/leadUnifiedColumns.tsx, components/leads/LeadColumnManager.tsx, components/leads/LeadImportDialog.tsx + CreateCustomFieldInline.tsx, components/connecter/GoogleSheetsPanel.tsx.

Data Model

Attribution is NOT a collection. It lives in two typed homes on CrmLead:

The 12 attr:* column ids are fixed (see ATTRIBUTION_COLUMNS); they are the SAME ids used as import mapping targets and as frontend column fieldKeys.

Note — normalized Channel/Source are derived from these fields. The lead's metadata.source, metadata.platform/metaPlatform, utm*, and sourceHistory[] (populated here) are the inputs the lead-classification feature folds into the normalized CrmLead.classification (channel/source/platform) shown in the lead's Details & attribution panel. Importing/mapping attribution is unchanged; the classifier (utils/leadClassification.utils.ts) just reads these same fields at the insert seam. See CRM-LEAD-DATA-MODEL.md (Note B).

API Endpoints

Both create-field endpoints below return 409 { success:false, error:{ code:"ATTRIBUTION_FIELD_COLLISION", message, attributionField:{ key, label } } } on a name collision.

Create a lead custom field

• Method / Path: POST /api/leads/custom-fields
• Auth: verifyJwt → manager (create)
• Body: { "fieldKey": "budget", "displayName": "Budget", "fieldType": "number", … }

Create a Google Sheets custom field

• Method / Path: POST on the Google Sheets config route (createCustomFieldHandler)
• Auth: verifyJwt → manager (create)

CSV import (unchanged routes; now attribution-aware targets)

• POST /api/leads/import/upload → returns availableFields[] (each with a group, incl. attribution).
• PATCH /api/leads/import/:id/mapping, POST /api/leads/import/:id/ai-mapping, POST /api/leads/import/:id/validate, POST /api/leads/import/:id/execute.

Google Sheets import mapping

• The suggest handler returns attribution targets for import; parseSheetsMapping accepts attr:* as valid oktivoField values.

How to Test (End-to-End)

CSV path

1. Leads → Import → upload a CSV with a UTM Source and an Ad Campaign column.
2. In mapping, open a column's target dropdown → the Marketing Attribution group lists the attr:* targets. Map UTM Source → Attribution Source, Ad Campaign → Attribution Campaign.
3. Try "New field" named Source → blocked with the collision warning (Create is disabled).
4. Run the import. Open a created lead: the Marketing Attribution section shows the values. In Mongo, metadata.utmSource holds the mapped source and metadata.source is still csv_import.

Google Sheets path

1. Connecter → Google Sheets → Add import sheet; a column maps to the same Marketing Attribution targets.
2. Trying to create a custom field named like an attribution field is blocked identically.

Table columns

1. Leads → Columns → Marketing Attribution group (Acquisition + Attribution). Show "Attribution Source"; it appears as a read-only, pinnable column populated from the lead's attribution data.

Troubleshooting

Symptom	Cause	Fix
409 ATTRIBUTION_FIELD_COLLISION on field create	Field name/key duplicates a built-in attribution field	Map the column to the existing attribution target, or rename the field
Mapped touch source shows the channel, not the UTM value, in attribution.lastTouch.source	metadata.source (channel) is read before utmSource	Expected — the mapped value is preserved in metadata.utmSource; the structured touch source reflects the acquisition channel
Attribution column always empty for a lead	That lead carries no Meta/Google/UTM attribution	Expected — virtual columns resolve to empty when there's no attribution data
CSV and Sheets map a header differently	A caller bypassed the shared module	Both flows MUST go through importMapping.service — don't re-add a local heuristic or catalog

Changelog

• Added the shared services/import/ module (catalog + heuristic + value-application) and utils/attributionColumns.ts; refactored CSV (CrmLeadImportService) and Google Sheets (googleSheetsPull/Suggest/Config/Mapping) onto it, removing the duplicated per-flow heuristics (buildAutoMatchLookup, FIELD_SYNONYMS).
• Added attribution import targets + the ATTRIBUTION_FIELD_COLLISION guard to both create-field endpoints.
• Added read-only virtual marketing-attribution columns to the leads table + a "Marketing Attribution" group in the Columns panel.