Audit UTMs and attribution hygiene across campaigns

Define a fixed 60-90 day audit window and record the exact start date, end date, time zone, included business units, and excluded test traffic before exporting anything. From Google Analytics 4, export landing page, session source, session medium, campaign, content, term, key events, conversions, and any available first-user acquisition dimensions for the same window. From HubSpot, export campaigns, contacts or leads, original and latest source fields, campaign membership, lifecycle stage, opportunity or deal association, and owner fields used in reporting. Collect paid media, email, event, partner, and landing-page URL exports that contain the UTMs marketers actually launched, not only the values that survived into GA4 or the CRM. Load each source into a separate raw tab in Google Sheets and create a Control Totals tab with source name, export time, filters, row count, and key aggregate totals. Preserve all raw values exactly as exported, including blanks, casing differences, and obvious mistakes, because those defects are evidence for the governance audit. Reconcile the workbook totals to each source system and resolve material row-count or conversion-count differences before normalization begins.

Create a normalized audit tab with one row per observed campaign URL or campaign record and separate columns for every raw and standardized value. Include final URL, raw source, raw medium, raw campaign, raw content, raw term, landing page, channel, offer, region, audience, CRM campaign, lifecycle stage, conversion event, owner, and source-system record ID. Add corrected-value columns beside the raw fields, but leave them blank until the issue patterns and naming rules are approved. Create controlled status values for Clean, Missing, Inconsistent, Duplicate, Ambiguous, Legacy, Internal Test, and Needs Owner Review. Add issue category, issue detail, confidence, proposed correction, mapping rule, reviewer, and resolution status so each decision has an audit trail. Use formulas or validation rules to flag whitespace, capitalization drift, illegal characters, unexpected source-medium combinations, blank required fields, and duplicate campaign identifiers. QA the normalized table by tracing a sample of rows back to every raw tab and confirming that no source record, blank value, or malformed URL was silently dropped.