# Marketing data audit: Acme Cloud, 2026-10-01 to 2026-10-28 (fictional)

## Baseline

- Run date: 2026-11-01. Window: 2026-10-01 to 2026-10-28, ending at run date minus 4 days, the newest GA4 daily table past its update window. Monthly checks on 2026-10; FRS-05 also on 2026-09.
- Business time zone: America/New_York. Reporting currency: USD. FX policy: undocumented (monthly average rates in fx_rates_monthly).
- Warehouse: BigQuery, location US; datasets analytics_123456789, fivetran_google_ads, fivetran_facebook_ads, fivetran_linkedin_ads, fivetran_hubspot, fivetran_stripe, dbt_prod, dbt_prod_dbt_test__audit.
- Access routes: BigQuery remote MCP server, execute_sql_readonly only, with dryRun first on every new query; Google Ads MCP list_accessible_customers and search; Meta and LinkedIn account and account-day exports dated 2026-11-01; dbt artifacts from the production job; Looker Studio tile definitions as screenshots.
- Read time: 2026-11-01 13:05 to 15:40 UTC. Query budget: 10 GB billed in total, 1 GB per query, agreed with the Marketing ops lead. Audit query cost (Q23): 37 jobs, 2.31 GB billed.
- Scripts: reconcile_exports.py, classify_channels.py, check_dbt_artifacts.py and score_audit.py, all run (Python 3.9).
- Inputs used: stack.md, metrics.md, naming-rules.md (UTM registry). No data-dictionary.md or decisions.md exists.
- Threshold overrides: none.

## Verdicts per output

- O1 Paid media daily (paid_media_performance), used by the Paid media lead for daily budget shifts: Do not use until fixed.
  - Blocking FAIL: FRS-03 (three Google Ads days missing), FRS-05 (reload window shorter than the conversion window), KEY-08 (LinkedIn EUR summed as USD), MET-07 (overlapping Meta action types), CHN-07 (warehouse channel rules).
  - Caveats: MET-01, REC-02, REC-04, CHN-06.
- O2 Pipeline by source (pipeline_and_lead_source), used by the RevOps lead for the weekly review: Do not use until fixed.
  - Blocking FAIL: KEY-04 (fan-out), KEY-06 (deleted deals), IDN-05 (role address over-linking), MET-02 (two pipeline definitions).
  - Caveats: KEY-03, MET-01, CHN-08.
- O3 Revenue by channel (revenue_attribution), used by the Finance lead at month close: Use with caveats.
  - Caveats: IDN-03 (96.5% of billed value mapped), MET-01, MET-05 (FX policy undocumented), REC-06 (residual 1.8%), CHN-08.
- O4 Qualified-demo upload to Google Ads (activation_audience_or_upload), the bidding signal for demo campaigns: Do not use until fixed.
  - Blocking FAIL: IDN-05, OPS-04 (no publication gate). Also not assessed: OPS-08.
  - Caveats: IDN-01 (88.5% click ID coverage), REC-08 (6 of 214 rows failed; no consent field mapped).
  - Recommendation: pause the upload until FX-3 ships. The Paid media lead decides.
- O5 Exec KPIs (executive_kpi), used by the CEO in the weekly growth review: Do not use until fixed.
  - Blocking FAIL: KEY-08, MET-02, MET-04 (platform claims summed as total conversions).
  - Caveats: MET-01, REC-07.

## Score

- Overall 45 (44.52), Failing, coverage 97.5%, graded.
- The score ranks where to look between audits; the verdicts above decide what can be used.
- FRS Freshness and completeness: 34.4, coverage 100.0%
- KEY Grain, keys and values: 40.5, coverage 100.0%
- IDN Identity: 60.0 (capped from 63.3), coverage 100.0%
- MET Metric definitions and owners: 32.4, coverage 100.0%
- REC Reconciliation: 62.5, coverage 100.0%
- CHN UTM and channel classification: 43.8, coverage 100.0%
- OPS Controls and ownership: 38.5, coverage 81.3%

## FAIL findings, by priority

- P1 F03 FRS-03 (O1, O5): 3 of 84 account-days missing (Google Ads 1234567890, 2026-10-10 to 2026-10-12), platform spend 6,900.00 USD, 6.5% of USD value. Threshold: 0.5% of value or 1% of slices (default). Evidence: Q04; reconcile_exports.py missing_in_b. Owner: Analytics engineer.
- P1 F09 FRS-05 (O1, O5): Fivetran Conversion Window Size 30 against 90-day click windows; September conversions 387 against 412 on the platform (-6.1%); the GA4 session model re-reads 1 daily table. Threshold: older month off by more than 5%, or fewer than 3 GA4 tables re-read (default). Evidence: connector settings; REC-03 method on 2026-09; model config.
- P1 F16 KEY-04 (O2): pipeline 1,496,000.00 USD in mart_pipeline_by_source against 1,310,000.00 at deal grain with the same filters, +186,000.00 from joining deals to contacts. Evidence: Q08.
- P1 F19 KEY-06 (O2): 12 deleted deals worth 94,000.00 USD counted in pipeline. Evidence: Q09.
- P1 F21 KEY-08 (O1, O5): LinkedIn spend of 11,200.00 EUR summed and labelled as USD. Evidence: Q11; reconcile_exports.py currency mismatches (28 keys).
- P1 F27 IDN-05 (O2, O4): one role address links 27 contacts across 9 companies; role-address flag true, identifier left in the warehouse (the RevOps lead reruns Q13b there to locate it). Threshold: more than 20 persons (default). Evidence: Q13b.
- P1 F30 MET-02 (O2, O5): the Looker Studio pipeline field and the dbt metric differ by 7.4% on October (different stages and currency). Evidence: Q20; reconcile_exports.py --key date.
- P1 F32 MET-04 (O5): Total conversions shows 1,214 platform-claimed conversions against 803 backend trials. Evidence: tile definition; backend count.
- P1 F35 MET-07 (O1, O5): Meta trials sum omni_complete_registration and offsite_conversion.fb_pixel_complete_registration. Evidence: distinct action types against the Ads Manager column. Verify the hierarchy for the account.
- P1 F51 CHN-07 (O1): warehouse channel agreement 95.95%; paid-social mapped to Organic Social, (not set) to Direct, ai-assistant to Referral. Evidence: classify_channels.py --warehouse-map.
- P1 F57 OPS-04 (O4): the Hightouch upload runs at 07:00 regardless of the 06:00 dbt job result; freshness runs only through the job checkbox. Evidence: job steps and sync schedule.
- P2 F05 FRS-04 (O1, O5): ga4.events (events_*) freshness is metadata-based, unreliable on BigQuery. Evidence: check_dbt_artifacts.py.
- P2 F06 FRS-04 (O3): stripe.invoice freshness has no loaded_at_field. Evidence: check_dbt_artifacts.py.
- P2 F49 CHN-05 (O1): literal {{campaign.name}} on a paid LinkedIn campaign, 0.70% of sessions. Evidence: classify_channels.py.
- P2 F54 OPS-01 (O2): mart_pipeline_by_source has no tests. Evidence: check_dbt_artifacts.py.

## WARN findings

- P3 F15 KEY-03 (O2): 1.2% of deals (0.8% of value) without a company. RevOps lead, due 2026-11-21.
- P3 F23 IDN-01 (O4): 452 of 511 control-segment contacts hold a click ID (88.5%). Capture fix handed to tracking-audit. Paid media lead, due 2026-11-21.
- P3 F25 IDN-03 (O3): 96.5% of October billed value mapped by acme_account_id. Finance lead, due 2026-11-28.
- P3 F29 MET-01 (all): field completeness across the 13 metrics is 165 of 195 required fields (84.6%); revision policy and version are missing on 9 metrics, technical owner on 8, window and maturity on Pipeline and Qualified demo. RevOps lead, due 2026-11-14.
- P3 F33 MET-05 (O3, O5): FX policy and rate direction undocumented. Finance lead, due 2026-11-28.
- P3 F37 REC-02 (O1): one Meta account-day off by -22.50 USD (-1.50%); every month within 0.5% on matched keys. Paid media lead, due 2026-11-14.
- P3 F39 REC-04 (O1, O5): GA4 sign_up against backend trials, unexplained gap 3.1%. Analytics engineer, due 2026-11-21.
- P3 F41 REC-06 (O3): October bridge residual 1.8% of billed value. Finance lead, due 2026-11-28.
- P3 F42 REC-07 (O5): Meta claims 2.3 times backend trials; residual 74 against 226 trials (32.7%) after named components, with modelled conversions left in the residual. Paid media lead, due 2026-11-28.
- P3 F50 CHN-06 (O1): 91.2% of paid spend joins to traffic. Paid media lead, due 2026-11-14.
- P3 F53 CHN-08 (O2, O3): 9.8% of web-form contacts with Offline or unknown Original Traffic Source. RevOps lead, due 2026-11-28.
- P3 F62 REC-08 (O4): last 7 days, the model selected 214 rows, Hightouch sent 214 and Google Ads accepted 208 (6 failed, 2.8%, status NEEDS_ATTENTION); all 214 went without consent.ad_user_data. Analytics engineer, due 2026-11-14; the privacy owner decides on consent.
- P4: F01 FRS-01 manifest from warehouse values; F07 FRS-04 _fivetran_synced; F10 FRS-06 peak 930,412 events (93% of the limit); F11 FRS-07 zero-fill; F13 KEY-02 0.3% of events without a session ID; F20 KEY-07 time zones undocumented (1.3% of a day moves); F34 MET-06 no change log; F36 REC-01 contracts awaiting approval; F43 REC-08 no data-through date; F45 CHN-01 unapproved pairs 3.20%; F46 CHN-02 Unassigned 2.12%; F48 CHN-04 variants 1.58%; F55 OPS-02 test warning without owner since 2026-10-13; F58 OPS-05 no deal stage monitor; F59 OPS-06 no backups.

## PASS

- FRS-02: worst lag 1 day at 09:00 America/New_York (Q03).
- KEY-01: 0 excess duplicates in the four money and conversion facts (Q05).
- KEY-05: livemode filter documented, one HubSpot portal, namespace columns present.
- IDN-02: 93.1% of web-form contacts join to a session. IDN-04: no conflicting mappings. IDN-06: IDs stored as strings, no collisions.
- MET-03: ratios computed from sums. REC-03: conversions for 2026-10-03 to 2026-10-28, the days inside the 30-day rollback at the 2026-11-01 sync, within 1.5% per account. REC-05: 99.2% of submissions reach HubSpot.
- CHN-03: 99.13% of click-ID sessions in paid channels. OPS-03: freshness and tests every 12 hours against a 1-day SLA.

## Not checked and not applicable

- NOT CHECKED OPS-08: dataset access lists are not visible to the audit identity. Closing evidence: an administrator's export of dataset IAM bindings for dbt_prod and dbt_prod_dbt_test__audit.
- N/A OPS-07: no custom API jobs; all ingestion through Fivetran.
- N/A CHN-07 for O3: Revenue by channel uses HubSpot's Original Traffic Source, not the warehouse channel field.

## Reconciliation results

- Google Ads, Meta and LinkedIn platform exports against stg_ads__daily_spend (copy): contract drafted, awaiting the Paid media lead. Keys 84 and 81, matched 81, missing 3, unexpected 0, duplicates 0, currency mismatches 28 (listed apart and never compared: 11,200.00 EUR on the platform, 11,200.00 USD in the warehouse), value mismatches 1. USD: signed +4,277.50, absolute 18,122.50. EUR: signed -11,200.00, absolute 11,200.00. Matched keys only, USD: -22.50 (-0.02%). Rollups 2026-10: Google Ads -10.71% FAIL (matched-only 0.00%), Meta -0.05% PASS, LinkedIn not comparable, FAIL (currency).
- Pipeline in Looker Studio against the dbt metric (copy): 7.4% apart on October (MET-02).
- GA4 sign_up against backend trials (different systems): unexplained gap 3.1% after consent evidence.
- HubSpot deals against Stripe (different systems): booked, invoiced, cash and balance measures kept apart; residual 1.8% of billed value, no duplicate associations.
- Meta claims against backend trials (bridge): 520 claimed, less 160 view-through, 12 period edge and 48 cross-platform overlap; residual 74 against 226 trials (32.7%), with modelled conversions left in the residual because Meta reports no count of them.
- Google Ads cost against finance (bridge): platform cost 64,400.00 against invoiced media 63,950.00, after a 450.00 invalid-activity credit for September clicks; residual 0.00.
- Qualified-demo upload (selected, sent, accepted): 214, 214, 208 over the last 7 days; 6 failed (2.8%).

## Source behaviour notes

- Google Ads: account time zone America/Los_Angeles; primary actions use 90-day click windows; the Fivetran rollback is 30 days, so September is frozen below the platform (FRS-05).
- GA4: daily tables update for 3 days; peak 930,412 events per day is near the 1,000,000 standard limit.
- Meta: prebuilt reports roll back 9 days; no removed view windows requested.
- Stripe: amounts in minor units, converted in staging; livemode filter applied.

## Manifest

- google_ads 1234567890: America/Los_Angeles, USD, Fivetran, reload 30 days, owner Paid media lead, from list_accessible_customers on 2026-11-01.
- facebook_ads act_1234567890: America/New_York, USD, Fivetran prebuilt reports, reload 9 days, owner Paid media lead, from the Ads Manager export of 2026-11-01.
- linkedin_ads 500000001: America/New_York, EUR, Fivetran, reload 30 days, owner not recorded, from the Campaign Manager export of 2026-11-01.
- ga4 123456789, hubspot (one portal), stripe (live account): owners Analytics engineer, RevOps lead and Finance lead.

## Proposed martech-context updates (not written until approved)

- stack.md: the manifest above with an owner for LinkedIn and the evidence source per account.
- metrics.md: complete contracts for Pipeline and Qualified demo.
- decisions.md: FX policy (monthly average, USD per one original unit), Unassigned threshold, deal source rule, and the upload pause if the Paid media lead agrees.

## Fix list

- fix-list.md for this run: P1 11, P2 4, P3 12, P4 15. First fixes FX-1 to FX-5.
- Reported numbers expected to move: October Google Ads spend +6,900.00 USD; pipeline -280,000.00 USD; LinkedIn spend restated from 11,200.00 "USD" to its converted value; the Exec KPIs headline from 1,214 to 803. Owners tell report users before each release.

Nothing was changed by this audit.