# Worked example: Acme Cloud (fictional)

Acme Cloud sells self-serve and sales-assisted plans at example.com. It runs BigQuery (location US) fed by Fivetran from Google Ads, Meta, LinkedIn, HubSpot and Stripe, the GA4 export (daily and streaming), a dbt project on the dbt platform (Core engine 1.10), Looker Studio dashboards and a Hightouch upload of qualified demos to Google Ads offline conversions.

Files:

- [../assets/example-acme-stack.md](../assets/example-acme-stack.md): the `stack.md` read first.
- [../assets/example-acme-config.json](../assets/example-acme-config.json): outputs O1 to O5, sources, window, query budget, run folder.
- [../assets/example-acme-findings.csv](../assets/example-acme-findings.csv): the 62-row findings register.
- Script fixtures: `example-acme-ads-platform.csv` and `example-acme-ads-warehouse.csv`; `example-acme-sessions-by-source.csv`, `example-acme-utm-registry.csv` and `example-acme-warehouse-channel-map.csv`; `example-acme-dbt-manifest.json`, `example-acme-dbt-run-results.json` and `example-acme-dbt-sources.json`.
- [../assets/example-acme-report.md](../assets/example-acme-report.md): the filled report.
- In a real run every one of these files sits in the run folder in the user's project (`audits/marketing-data-audit-2026-11-01/`); the fixtures stand in for its query exports.

## Scope and window

- Run date 2026-11-01. Business time zone America/New_York. Reporting currency USD.
- Window 2026-10-01 to 2026-10-28: 28 days ending at run date minus 4 days, the newest GA4 daily table past its update window. Monthly checks use 2026-10; FRS-05 also uses 2026-09.
- Query budget agreed with the Marketing ops lead: 10 GB billed in total, 1 GB per query.
- Outputs: O1 Paid media daily (paid_media_performance, Paid media lead, 09:00 daily budget shifts); O2 Pipeline by source (pipeline_and_lead_source, RevOps lead, Monday review); O3 Revenue by channel (revenue_attribution, Finance lead, month close); O4 Qualified-demo upload to Google Ads (activation_audience_or_upload, daily 07:00); O5 Exec KPIs (executive_kpi, CEO, Monday 08:00). All in use.
- Manifest: Google Ads `list_accessible_customers` (the account is not under a manager, so no `customer_client` listing was needed), plus Meta and LinkedIn account exports dated 2026-11-01, give three ad accounts: Google Ads 1234567890 (USD, America/Los_Angeles), Meta act_1234567890 (USD), LinkedIn 500000001 (EUR). The `stack.md` list had been built from warehouse distinct values and lacked a LinkedIn owner (FRS-01 WARN), but no account was missing.

## Script runs

Run from the skill folder, so the `assets/` paths resolve. A real run uses the form in the procedure's step 5, from the run folder.

```bash
python3 scripts/reconcile_exports.py assets/example-acme-ads-platform.csv assets/example-acme-ads-warehouse.csv \
  --key platform,account_id,date --measures spend --currency-col currency \
  --label-a platform --label-b warehouse --rollup platform,account_id,month \
  --month-from date --mature-through 2026-10-28
```

- Keys: platform 84, warehouse 81, matched 81, missing in warehouse 3 (Google Ads 1234567890 on 2026-10-10, 2026-10-11 and 2026-10-12), unexpected 0, duplicates 0.
- Currency mismatches: 28 keys, all LinkedIn 500000001, EUR on the platform and USD in the warehouse. They are listed apart and never compared: 11,200.00 EUR on the platform, 11,200.00 USD in the warehouse.
- Value mismatches: 1, Meta act_1234567890 on 2026-10-27, 1,500.00 against 1,477.50 (-22.50, -1.50%).
- Totals: USD platform 106,400.00, warehouse 110,677.50, signed +4,277.50, absolute 18,122.50; EUR platform 11,200.00, warehouse 0.00, signed -11,200.00, absolute 11,200.00. Matched keys only, USD: 99,500.00 against 99,477.50 (-0.02%).
- Rollups for 2026-10: Google Ads -10.71% FAIL (matched-only 0.00%), Meta -0.05% PASS with one flagged day, LinkedIn not comparable, FAIL (currency). Exit code 1.
- How the agent scores it: the three missing days go to FRS-03 (6,900.00 USD, 6.5% of USD value), the EUR label to KEY-08, and only the matched-key disagreement to REC-02 (WARN: one account-day outside 1%, every month within 0.5%). The signed USD difference looks like an overstatement; it is the LinkedIn EUR rows labelled USD, which the per-currency totals and the absolute differences expose.

```bash
python3 scripts/classify_channels.py assets/example-acme-sessions-by-source.csv \
  --registry assets/example-acme-utm-registry.csv \
  --warehouse-map assets/example-acme-warehouse-channel-map.csv
```

- 157,950 sessions and 2,810 key events.
- Unassigned 3,350 sessions (2.12%) and 45 key events (1.60%): CHN-02 WARN.
- Unapproved pairs 5,050 sessions (3.20%), with mailchimp / newsletter (1.27%) and linkedin / paid-social (1.14%) above 0.5%: CHN-01 WARN.
- linkedin / paid_social spelled three ways, 2,500 sessions (1.58%) in non-canonical spellings: CHN-04 WARN.
- Literal `{{campaign.name}}` on 1,100 sessions (0.70%) of a paid LinkedIn campaign: CHN-05 FAIL.
- 41,310 click-ID sessions, 40,950 in paid channels (99.13%): CHN-03 PASS.
- Warehouse agreement 151,550 of 157,950 sessions (95.95%), with linkedin / paid-social mapped to Organic Social, (not set) mapped to Direct and ai-assistant mapped to Referral: CHN-07 FAIL. Exit code 1.

```bash
python3 scripts/check_dbt_artifacts.py assets/example-acme-dbt-manifest.json \
  --run-results assets/example-acme-dbt-run-results.json \
  --sources assets/example-acme-dbt-sources.json \
  --config assets/example-acme-config.json --adapter bigquery --as-of 2026-11-01
```

- The config names 7 models; all are in the manifest. The script adds the 7 sources they read through depends_on. With `tag:critical` it checks 5 models and 7 sources (3 of them untagged, found through depends_on): the project never tagged `fct_deals` or `mart_sessions`, so neither model's tests would be read.
- `mart_pipeline_by_source` has no tests: OPS-01 FAIL.
- `accepted_values` on `fct_invoices.currency` warns with 3 failures: OPS-02 WARN (the job history shows it warning since 2026-10-13 with no owner).
- `ga4.events` (identifier `events_*`) and `stripe.invoice` have freshness without `loaded_at_field`, so dbt reads metadata; the wildcard source is unreliable on BigQuery: FRS-04 FAIL. `google_ads.campaign_stats` and the untagged `linkedin_ads.ad_analytics_by_campaign` read `_fivetran_synced` (an ingestion timestamp); `hubspot.deal` and `hubspot.contact` read `updated_at` and `facebook_ads.basic_ad` its report date.
- All 7 freshness results pass, which proves little for the metadata-based ones. Exit code 1.

```bash
python3 scripts/score_audit.py assets/example-acme-findings.csv \
  --config assets/example-acme-config.json --as-of 2026-11-01
```

```text
As of 2026-11-01. 52 checks from 62 findings: 11 PASS, 25 WARN, 14 FAIL, 1 N/A, 1 NOT CHECKED.
- O1 Paid media daily (paid_media_performance): Do not use until fixed. Blocking FAIL: FRS-03, FRS-05, KEY-08, MET-07, CHN-07. Caveats: MET-01, REC-02, REC-04, CHN-06.
- O2 Pipeline by source (pipeline_and_lead_source): Do not use until fixed. Blocking FAIL: KEY-04, KEY-06, IDN-05, MET-02. Caveats: KEY-03, MET-01, CHN-08.
- O3 Revenue by channel (revenue_attribution): Use with caveats. Caveats: IDN-03, MET-01, MET-05, REC-06, CHN-08.
- O4 Qualified-demo upload to Google Ads (activation_audience_or_upload): Do not use until fixed. Blocking FAIL: IDN-05, OPS-04. Also not assessed: OPS-08. Caveats: IDN-01, REC-08.
- O5 Exec KPIs (executive_kpi): Do not use until fixed. Blocking FAIL: KEY-08, MET-02, MET-04. Caveats: MET-01, REC-07.
Overall 45 (44.52), Failing, coverage 97.5%, graded.
Priorities: P1 11, P2 4, P3 12, P4 15.
```

Domain lines and the arithmetic: [scoring-and-verdicts.md](scoring-and-verdicts.md#worked-computation-acme-cloud-2026-11-01).

## Findings the agent had to judge

- FRS-05: the Fivetran Google Ads Conversion Window Size is 30 days while Trial started and Demo booked use 90-day click windows. September conversions in the warehouse are 387 against 412 on the platform (-6.1%), and the GA4 session model re-reads only 1 daily table. FAIL, scored here and not in REC-03.
- REC-03: compared 2026-10-03 to 2026-10-28, the days inside the 30-day rollback at the 2026-11-01 sync, with the platform export pulled after that sync. Within 1.5% per account: PASS. A calendar October comparison would have mixed in two days the last rollback no longer covered.
- KEY-04 and KEY-06: Q08 with the published model's filters on both sides shows pipeline of 1,496,000.00 in `mart_pipeline_by_source` against 1,310,000.00 at deal grain, +186,000.00 of fan-out from joining deals to contacts. Q09 finds 12 deleted deals worth 94,000.00 inside that deal-grain total. Correct pipeline: 1,216,000.00. Two FAILs, two root causes.
- IDN-05: one role address links 27 contacts across 9 companies (role-address flag true; the identifier stays in the warehouse, and the RevOps lead reruns Q13b there to find it). The qualified-demo upload and pipeline both read that mapping. FAIL.
- MET-01: field completeness across the 13 metrics on the outputs is 165 of 195 required fields (84.6%), with every money and conversion metric owned. WARN.
- MET-02: pipeline in Looker Studio (amount, all open stages) and the dbt metric (amount in home currency, excluding a reopened Closed lost stage) are 7.4% apart on October. FAIL; the business owner chooses the definition.
- MET-04: Exec KPIs show "Total conversions" as 1,214 platform-claimed conversions (Google Ads plus Meta plus LinkedIn) against 803 backend trials. FAIL.
- MET-07: Meta trials sum `omni_complete_registration` and `offsite_conversion.fb_pixel_complete_registration`, an aggregate type and its component. FAIL; verify the hierarchy for the account.
- REC-07: Meta claims 2.3 times the backend trials attributed to paid social; after named components the residual is 74 against 226 trials (32.7%), with modelled conversions left inside it because Meta reports no count of them. WARN, never FAIL (bridge in [reconciliation-bridges.md](reconciliation-bridges.md)).
- REC-08 for O4: over the last 7 days the model selected 214 rows, Hightouch sent 214 and Google Ads accepted 208 (6 failed, 2.8%, status NEEDS_ATTENTION). No consent field is mapped, so all 214 went without `consent.ad_user_data`. WARN, with the consent question for the privacy owner.
- OPS-04: the Hightouch upload runs on its own 07:00 schedule whether or not the 06:00 dbt job succeeded, and freshness runs only through the dbt platform job's checkbox. FAIL; recommend the owner pauses the upload until a gate exists.
- OPS-08: NOT CHECKED. The audit identity cannot read dataset access lists; an administrator's export of dataset IAM bindings would close it. O4 lists it as "also not assessed".
- CHN-07 for O3: N/A, because Revenue by channel uses HubSpot's Original Traffic Source, not the warehouse channel field.

## First fixes (P1)

- FX-1 (FRS-03, FRS-07): re-sync Google Ads for 2026-10-10 to 2026-10-12 and replace the zero-fill with a load-manifest join. Effect: October Google Ads spend +6,900.00 USD.
- FX-2 (KEY-04, KEY-06, closes OPS-01): rebuild `mart_pipeline_by_source` at deal grain with the deleted filter and add grain and relationships tests. Effect: pipeline falls by 280,000.00 USD; the RevOps lead tells report users first.
- FX-3 (OPS-04, IDN-05): make the upload wait for a successful run with fresh sources and exclude role-address merges from the qualification mapping. Drill on a safe copy.
- FX-4 (KEY-08, closes the MET-05 WARN once the policy is recorded): convert LinkedIn EUR at the documented monthly rate before summing.
- FX-5 (MET-04): show backend trials as the headline (803) and platform claims as labelled attributed values.

Full entries for FX-1 and FX-2: [fix-list.md](fix-list.md).

## Proposed martech-context updates

- `stack.md`: the manifest with an owner for every slice (including LinkedIn), native time zones, currencies and reload windows.
- `metrics.md`: complete contracts for Pipeline and Qualified demo from the metric contract template.
- `decisions.md`: the FX policy (monthly average, USD per one original unit), the Unassigned threshold, the deal source rule, the consent basis for the upload (privacy owner), and the decision to pause the upload until the gate exists if the owner agrees.

Nothing is written until the user approves each proposal.