Marketing data audit

Audits the marketing data behind a few named reports, dashboards, audiences or conversion uploads, in BigQuery or Snowflake and in the tools that feed them. Every check reads data or settings and changes nothing. The output is a trust verdict per output, a scored findings register with evidence, and a fix list for people to apply. A finding is a lead until its evidence is attached.

When to use

Use this skill when:

• Someone asks for a marketing data audit, data quality audit, data health check, or whether a dashboard can be trusted.
• CAC, ROAS, pipeline or revenue by channel disagree between dashboards, finance and the platforms.
• A warehouse, dbt project, Fivetran or Airbyte setup, or an agency's reporting is handed over.
• Warehouse audiences or offline conversion uploads are about to go live.
• A quarterly re-score after fixes.

Use something else when:

• One metric broke on a known date and the failing collection layer is unknown: tracking-break-map.
• GA4 purchases against orders, order by order: purchase-reconciliation (REC-04 reuses its thresholds). Warehouse or dashboard numbers against finance or platforms stay here.
• The cause of Unassigned or (not set): ga4-unassigned-not-set-diagnosis. Designing UTMs: utm-convention-and-registry.
• One GA4 property's settings: ga4-audit. Tags, pixels or consent on the site: tracking-audit or gtm-container-audit.
• CRM configuration and field writers: crm-audit. CRM records: crm-data-hygiene-audit.
• Repairing one sync or upload: martech-integration-engineering. Tools, spend and owners across the stack: martech-stack-audit.
• Installing monitors and SLAs after the audit (data-contracts-freshness-and-quality, planned) or repairing incremental models and backfills (dbt-marketing-models-qa, planned).

Inputs

Read these first if they exist (assets/example-acme-stack.md shows the fields):

• martech-context/stack.md: warehouse, datasets, connectors and reload windows, platform accounts, native time zones and currencies, owners.
• martech-context/metrics.md: contracts and owners for the metrics on the outputs. data-dictionary.md: grain and keys.
• martech-context/naming-rules.md and tracking-plan/utm-allowed-pairs.csv: UTM registry and channel rules. decisions.md: FX policy, accepted exceptions, attribution and consent decisions.

Then gather:

• One to five critical outputs with consumer, decision, deadline, owner and type, recorded in a copy of assets/audit-config-template.json.
• A read-only route per system (references/read-only-access.md) and each platform's own account list.
• Connector settings (conversion window size, refresh window, last successful sync, schema change policy, history mode), dbt artifacts (manifest.json, run_results.json, sources.json or freshness.json), BI tile definitions, business time zone and reporting currency. Ask owners with assets/evidence-request.md.

Never ask for passwords, keys, tokens or service account files in chat. Personal data leaves the warehouse only as counts and flags.

Procedure

1. Fix the outputs and the window

• Name one to five outputs and give each a type: paid_media_performance, pipeline_and_lead_source, revenue_attribution, activation_audience_or_upload or executive_kpi. The type sets its blocking checks (references/scoring-and-verdicts.md).
• If no output can be named, stop and agree one. An audit of every table produces findings nobody owns.
• Window: the last 28 days ending at run date minus 4 days, the newest GA4 daily table past its update window (a run on 2026-11-01 covers 2026-10-01 to 2026-10-28). Ad conversions restate for longer; REC-03 and FRS-05 handle that with their own periods, so do not move the whole window. Monthly pairs use the last closed month. Record run date, business time zone and reporting currency.
• Keep every file of the run in one folder in the user's project, for example audits/marketing-data-audit-<run date>/: the config copied from the template, query CSVs, script output, findings.csv, the report and the fix list. Never write inside the skill folder.
• Re-score: copy the previous config, set a new as_of and window, rerun every check, and list each check whose status changed since the last register.

2. Build the source manifest from the platforms

• List every required slice: system, account or property, environment, native time zone, currency, connector, reload window, owner.
• Take account lists from the platforms: Google Ads list_accessible_customers, GA4 get_account_summaries, Meta, LinkedIn, HubSpot and Stripe settings or exports.
• list_accessible_customers returns only accounts the identity opens directly. For a manager account, list its clients with the customer_client query (read-only-access.md), repeat for sub-managers, then keep the accounts with cost in the window.
• A manifest built from warehouse distinct values cannot show an account that never loaded. Warehouse values only find extras (Q03).

3. Open read-only, cost-guarded access

• Before step 4, agree a query budget with the user (total and per query) and record it in the config. Stop and ask before any query estimated above the per-query cap.
• BigQuery: execute_sql_readonly on the remote MCP server (deny execute_sql and cancel_job in the client; ask an administrator for an IAM deny on the read-write tools), or bq with Data Viewer and Job User. INFORMATION_SCHEMA queries bill at least 10 MB and are never cached.
  • bq: --dry_run first, then --maximum_bytes_billed and --label audit:marketing-data-audit.
  • MCP: the tool reference lists dryRun but no maximum bytes billed (checked 2026-11-06; confirm in the tool's schema). Dry-run each new query, or estimate from table sizes if your client lacks it. Then set jobTimeoutMs, pass the label audit=marketing-data-audit, and poll a long query with get_query_results; never rerun it.
• Snowflake: a role with USAGE and SELECT only, on an extra-small warehouse with a statement timeout and query tag, or the managed MCP server's SYSTEM_EXECUTE_SQL with read_only: true on its own server. EXPLAIN shows bytesAssigned before a large scan.
• Give every date expression an explicit time zone: BigQuery defaults to UTC, Snowflake sessions to America/Los_Angeles. Run only SELECT statements and read tools.

4. Run the checks domain by domain

Queries Q01 to Q23: assets/bigquery-audit-queries.sql and assets/snowflake-audit-queries.sql. Thresholds and evidence: references/checks-freshness-and-keys.md, references/checks-identity-metrics-reconciliation.md, references/checks-channels-and-controls.md. Source behaviour: references/source-behaviour.md. The rules that decide most audits:

• FRS: Freshness is measured per slice from data, never from one maximum date or from metadata. Snowflake LAST_ALTERED, dbt freshness without loaded_at_field and BigQuery wildcard metadata fail for a critical source. The reload window must cover the restatement window: GA4 3 days; Google Ads conversion windows up to 90 days against a Data Transfer refresh window of 7 days by default and a Fivetran rollback of 30.
• KEY: test the declared grain, then compare measure totals at fact grain with the published model; any inflation of money fails. Check deleted and history rows, micros and minor units, and currencies summed without conversion.
• IDN: click IDs on at least 95% of the control segment pass, under 80% fail, lowercase-only gclids over 1% fail. Billing to CRM must map 98% of billed value through explicit IDs, not email. An identifier linking more than 20 people fails.
• MET: every metric on an output needs a contract and a business owner. Run every implementation on the same closed period; a gap over 0.5% under one name fails. Averaged ratios, summed snapshots and cross-platform sums of claimed conversions fail.
• REC: write the comparison contract first (assets/comparison-contract-template.yaml). Copies must agree; different systems get a bridge, never a forced match. Spend within 1% per account-day and 0.5% per account-month (references/reconciliation-bridges.md).
• CHN: Unassigned over 5% of sessions or key events fails; spend joined to traffic under 85% fails; unresolved macros on paid campaigns fail.
• OPS: an activation sync or conversion upload that can run on a failed or stale model fails.
• One root cause scores in one check: missing slices in FRS-03, stale restatements in FRS-05, currency and units in KEY-08. The full precedence list is in the scoring reference.

5. Use the scripts for the mechanical parts

Python 3.9 or later, standard library only. Run from the run folder, with SKILL set to the skill folder's path:

python3 "$SKILL/scripts/reconcile_exports.py" platform.csv warehouse.csv --key platform,account_id,date \
  --measures spend --currency-col currency --rollup platform,account_id,month \
  --month-from date --mature-through 2026-10-28
python3 "$SKILL/scripts/classify_channels.py" sessions.csv --registry registry.csv --warehouse-map channel_map.csv
python3 "$SKILL/scripts/check_dbt_artifacts.py" target/manifest.json --run-results target/run_results.json \
  --sources target/sources.json --config audit-config.json --adapter bigquery

• reconcile_exports.py keeps missing, unexpected, duplicated and mismatched keys apart, with signed and absolute differences per currency and matched-only rollups.
• classify_channels.py applies GA4's default channel rules to source and medium rows and checks the registry (allowed-pairs.csv from utm-convention-and-registry works), variants, macros, paid click IDs and the warehouse's own channel logic. Load Google's source category list with --source-categories for full coverage.
• check_dbt_artifacts.py reports grain tests, failing tests and whether each critical source's freshness reads data or metadata. Critical nodes are the config's outputs[].models and every source they read; pass --critical only when a tag covers exactly that set. A config model missing from the manifest is NOT CHECKED for OPS-01, with the reason.
• If Python is unavailable, apply the reference thresholds by hand and label the report "scripts not run".

6. Record findings and score

• Copy the header of assets/findings-register.csv into findings.csv in the run folder; never edit the skill's own asset. Write one row per sub-finding, tagged with the outputs it affects or all, citing the query or script output, the value and the threshold source.
• Run python3 "$SKILL/scripts/score_audit.py" findings.csv --config audit-config.json.
• Verdict per output from its blocking checks: any FAIL gives "Do not use until fixed"; else any NOT CHECKED gives "Not assessable"; else any WARN gives "Use with caveats"; else "Decision-grade".
• The overall score only ranks where to look; the verdict per output decides what can be used. A FAIL is downgraded only with a dated decision from decisions.md.

7. Report and propose

• Report with assets/audit-report-template.md. Fix list with assets/fix-list-template.md and references/fix-list.md, ordered P1 to P4, each fix with the test that proves it and the expected change in reported numbers.
• Propose updates to stack.md (the manifest), metrics.md (missing contracts from assets/metric-contract-template.yaml) and decisions.md. Write nothing until the user approves.

Common failure modes

All 28, with the wrong fix: references/failure-modes.md.

• Global freshness hides a missing account. MAX(date) is current while one account stopped. Detect: Q03 lag per manifest slice.
• Metadata freshness passes on empty writes. LAST_ALTERED moves on zero-row DML; dbt falls back to metadata. Detect: check_dbt_artifacts.py, Q03b.
• Zero-filled spine. A failed load reads as a day without spend. Detect: Q04 on staging, reconcile_exports.py missing_in_b.
• Reload window shorter than the conversion window. Old days freeze below the platform. Detect: the REC-03 method on the month before the reload window.
• GA4 export gaps or intraday rows unioned with daily rows. Detect: Q01, Q02.
• Overlapping platform action types summed, or all_conversions used for conversions. Detect: MET-07 against the platform column.
• Shared identifiers over-link people. A role address merges many contacts. Detect: Q13b fan-in by salted reference.
• Stale channel CASE logic. AI Assistant (a GA4 channel since 13 May 2026) or paid-social mapped wrong. Detect: classify_channels.py --warehouse-map.

Worked example

Acme Cloud, a fictional B2B SaaS on BigQuery, audits five outputs for 2026-10-01 to 2026-10-28, run on 2026-11-01. Filled report: assets/example-acme-report.md.

The score run ends with 11 PASS, 25 WARN, 14 FAIL, 1 N/A, 1 NOT CHECKED; overall 45 (Failing), coverage 97.5%. Revenue by channel is usable with caveats; Paid media daily, Pipeline by source, the qualified-demo upload and Exec KPIs are not usable until fixed.

• FRS-03: three Google Ads days missing and zero-filled, so the dashboard showed no spend on days that cost $6,900.
• KEY-04 and KEY-06: pipeline joined deals to contacts and kept 12 deleted deals, overstating pipeline by $280,000.
• MET-04: Exec KPIs summed 1,214 platform-claimed conversions against 803 backend trials.
• OPS-04: the Google Ads upload runs on its own schedule even when the dbt job fails.

Full walk-through and expected script output: references/worked-example.md. Regression values for all four scripts: assets/regression-expected.json.

Acceptance checks

• Scope: the config lists one to five outputs with consumer, decision, deadline, type and owner, plus run date, window, time zone and currency. Pass: no placeholder text.
• Manifest: every expected slice traces to a platform account list (tool call, API response or export, dated). Pass: no slice sourced only from warehouse distinct values.
• Read-only and cost: the tool log shows only read tools and SELECT statements; every BigQuery query had a dry run and byte cap (bq), or a dry run or size estimate, a job timeout and the audit label (MCP), within the agreed budget; Snowflake ran with a timeout. Pass: no write, sync, re-sync or build call, except an OPS-04 drill the user approved on a development copy, with its approval, environment and run IDs recorded; no credential in chat.
• Coverage: all 52 checks have a status; every NOT CHECKED and N/A has a reason and the evidence that would close it. Pass: score_audit.py reports no missing checks.
• Evidence: every FAIL and WARN cites a query ID or script output, the value and the threshold source. Pass: no finding without evidence.
• One finding, one check: each root cause scores once, with cross-references. Pass: no double scoring under the precedence list.
• Reconciliation: each compared pair has a contract; results show missing, unexpected, duplicate and mismatched keys separately, with signed and absolute differences by currency. Pass: no comparison of net totals only.
• Verdicts: each output has a verdict with its blocking checks; downgraded FAILs cite a decision. Pass: score_audit.py output attached to the report.
• Fix list: each item has evidence, owner, change, test, expected effect on reported numbers, backfill or restatement need and approver. Pass: a colleague could apply it without asking a question.
• Approval: no martech-context file, model, connector, dashboard, CRM property or sync changed. Pass: proposals listed for approval; nothing written outside the run folder.

Boundaries

• Read-only. No DML, DDL, re-syncs, backfills, dbt runs on production, sync triggers, or dashboard, CRM or connector edits, even when the identity could. Fixes leave as a fix list for approval.
• A re-sync or backfill rewrites history and can cost money (connector usage, bytes processed). It is drafted with scope, cost driver and restatement notice; a named owner runs it.
• Publication-gate drills run only in development or on a safe copy, with the user's approval. The agent recommends pausing an unsafe upload; the owner decides.
• Decisions belong to owners: metric meaning (business owner), FX policy (finance), consent and privacy (privacy owner), attribution model (attribution-model-selector and the marketing lead).
• Personal data: counts and flags only; salted references stay in the session; nothing row-level in chat, the report or the repository.
• Hand-offs, read-only use:
  • BigQuery remote MCP server: execute_sql_readonly (deny execute_sql and cancel_job in the client). Snowflake managed MCP server: SYSTEM_EXECUTE_SQL with read_only: true and a SELECT-only default role.
  • Google Ads MCP server with labels: false and assets: false. Google Analytics MCP server without --allow-write. Meta Ads MCP server: reporting tools only, because it can change campaigns.
  • HubSpot remote MCP server: read tools only. Salesforce hosted MCP server: SObject Reads. Stripe MCP server: stripe_api_read through OAuth or a read-only agent API key. Fivetran MCP server: read scope. dbt: artifacts, or the dbt MCP server's read tools.
  • Order-level purchases: purchase-reconciliation. Capture fixes: tracking-audit. UTM fixes: utm-convention-and-registry. CRM hygiene: crm-data-hygiene-audit. Sync and upload repairs: martech-integration-engineering.

Live docs

Platform settings, limits and menu paths change. Before stating any parameter name, limit, quota or UI path:

• Fetch the current page from the vendor docs MCP server or llms.txt listed in sources.yaml, or open the canonical URL.
• If the live page disagrees with this skill, follow the live page and tell the user what changed.
• Cite the URL you used.

The entries that matter most here: the GA4 export schema and export limits pages, the GA4 default channel group page, the BigQuery MCP and INFORMATION_SCHEMA pricing pages, the Data Transfer Service refresh window, Google Ads conversion reporting, Snowflake parameters (TIMEZONE), the TABLES view (LAST_ALTERED), Account Usage latency and the managed MCP server, dbt freshness and artifacts, Fivetran Google Ads and Facebook Ads rollback windows and system columns, and Meta's Insights attribution window changes. Each reference gives its check dates. Re-check HubSpot property internal names, Stripe MCP tool names and Meta action type hierarchies at run time.