# Checks: UTM and channel classification (CHN), controls and ownership (OPS)

Conventions are the same as in [checks-freshness-and-keys.md](checks-freshness-and-keys.md). Shares below are of sessions in the window unless the check says key events.

## GA4 default channel rules for manual traffic (checked 2026-11-01)

The GA4 BigQuery export has no channel group field. `session_traffic_source_last_click.cross_channel_campaign` holds session source, medium and campaign name, so classify_channels.py reproduces the rules from Google's default channel group page. Google lists the manual-traffic rules in the order below and states no evaluation order, so the script treats the list as first match wins:

1. Direct: source exactly `(direct)` and medium `(not set)` or `(none)`.
2. Cross-network: campaign name contains `cross-network`.
3. Paid Shopping: (shopping source, or campaign matches `^(.*(([^a-df-z]|^)shop|shopping).*)$`) and paid medium.
4. Paid Search: search source and paid medium.
5. Paid Social: social source and paid medium.
6. Paid Video: video source and paid medium.
7. Display: medium is display, banner, expandable, interstitial or cpm.
8. Paid Other: paid medium.
9. Organic Shopping: shopping source or shopping campaign.
10. Organic Social: social source, or medium social, social-network, social-media, sm, social network or social media.
11. Organic Video: video source, or medium contains video.
12. Organic Search: search source, or medium organic.
13. AI Assistant: medium ai-assistant.
14. Referral: medium referral, app or link.
15. Email: source or medium email, e-mail, e_mail or e mail.
16. Affiliates: medium affiliate. 17. Audio: medium audio. 18. SMS: source or medium sms.
19. Mobile Push Notifications: medium ends with push or contains mobile or notification, or source firebase.
20. Otherwise Unassigned.

- Paid medium: `^(.*cp.*|ppc|retargeting|paid.*)$`. Matching is not case-sensitive.
- Order-dependent cases worth knowing, read from that list, so compare one with the GA4 report before a finding rests on it: facebook / email lands in Organic Social before Email; a hyphenated paid-social still matches `paid.*`, so linkedin / paid-social is Paid Social in GA4 even when warehouse logic says Organic Social; an unknown source with a paid medium lands in Paid Other, not Paid Social.
- AI Assistant arrived on 13 May 2026: GA4 assigns medium ai-assistant and campaign (ai-assistant) when it recognises the referring assistant. A hand-tagged medium such as ai or chatgpt does not match and lands in Unassigned.
- Google Ads auto-tagged traffic is classified by ad network type, which the export does not hold. The script predicts google / cpc with the manual rules and flags it.
- Source categories come from Google's downloadable list linked on the same page. Load it with `--source-categories`; without it the script uses a small seed list and flags rows whose channel could change. Meta's `{{site_source_name}}` values (fb, ig, an, msg) need the full list.

## Click IDs available in the export

- `collected_traffic_source` holds gclid, dclid and srsltid. There is no gbraid or wbraid field; msclkid appears only in the `page_location` query string.
- srsltid marks Merchant Center links on free listings; it is not proof of a paid click.
- Do not treat fbclid as proof of a paid click; it is not documented as ad-only. Identify Meta paid traffic by its UTMs.

## Cost-to-traffic join order (CHN-06)

1. Platform campaign ID: utm_id, or `session_traffic_source_last_click.google_ads_campaign.campaign_id` for auto-tagged Google Ads sessions.
2. Then the registry's exact campaign name, compared in lowercase.
3. Never fuzzy-match names. Renamed campaigns and unresolved macros break name joins, which is why IDs come first.

## CHN-01 Source and medium vocabulary conformance (B)

- Evidence: classify_channels.py with the registry from `naming-rules.md` or `tracking-plan/utm-allowed-pairs.csv` on Q15 output or a GA4 export.
- PASS: unapproved pairs at most 2% of sessions and no single unapproved pair at 0.5% or more.
- WARN: over 2% and up to 5%, or a single unapproved pair at 0.5% or more.
- FAIL: over 5% of sessions.
- Pairs are exact and case-sensitive; `*` as source approves a medium for any source. (not set) rows are missing data and score in CHN-02. GA4's automatic pairs, (direct) / (none) and any source with medium organic, referral or ai-assistant, are outside the UTM vocabulary and never count as unapproved. Without a registry, check the lowercase rule and known media, mark WARN and hand off to utm-convention-and-registry.

## CHN-02 Unassigned and unknown share (B)

- Evidence: score from one source only. Either GA4's session default channel group (Data API or the GA4 MCP `run_report`) or classify_channels.py predictions on the export (Q15), never one source's Unassigned over the other's total. Reports estimate sessions with HyperLogLog++ and the export counts them exactly, so when you cross-check the two, compare shares, not counts. The warehouse unknown or other share comes from Q16.
- PASS: at most 1% of sessions and 1% of key events.
- WARN: over 1% and up to 5%.
- FAIL: over 5% of sessions or key events, or over 5% of any paid campaign's sessions.
- Check the denominator first: a falling total raises the share. Causes belong to ga4-unassigned-not-set-diagnosis; this check measures.

## CHN-03 Paid clicks classified as paid (B)

- Evidence: sessions with gclid or dclid in `collected_traffic_source`, or msclkid in `page_location`, by predicted channel.
- PASS: at least 98% of click-ID sessions in a paid channel (Paid Search, Paid Social, Paid Video, Paid Shopping, Paid Other, Cross-network, Display).
- WARN: 95% to under 98%. FAIL: under 95%.
- N/A: no auto-tagged ad platforms.

## CHN-04 Case and format variants (C)

- Evidence: classify_channels.py variant groups after lowercasing, trimming and treating hyphens and spaces as underscores. The canonical spelling is the registry's, else the most used.
- PASS: no variant group with non-canonical spellings at 0.1% of sessions or more.
- WARN: any group at 0.1% or more. FAIL: not used; cost-join damage scores in CHN-06.

## CHN-05 No unresolved macros or placeholders (B)

- Evidence: classify_channels.py macro flags: braces, encoded braces (%7B, %7D), double square brackets ([[...]]), merge tags (*|...|*), percent-wrapped tokens (%%...%%), double underscores around capitals, names such as CAMPAIGN_ID or campaign.name. Single square brackets are not flagged: names such as [Brand] Search - US are a common naming convention.
- PASS: none.
- WARN: under 0.1% of sessions and none on a paid campaign with spend.
- FAIL: 0.1% or more, or any paid campaign with spend. The script uses a paid channel as the proxy for spend; confirm spend with Q18.

## CHN-06 Spend joins to traffic (A)

- Evidence: Q18, the share of paid spend whose campaign joins to sessions or leads by the join order above.
- PASS: at least 95% of spend joined. WARN: 85% to under 95%. FAIL: under 85%.
- N/A: no paid media output. Report the unjoined spend by cause (renamed campaigns, macros, missing utm_id).

## CHN-07 Warehouse channel logic matches the documented rules (B)

- Evidence: classify_channels.py `--warehouse-map`, comparing the warehouse channel with the registry's expected channel, else GA4's default rules (or the documented custom group), on the same pairs.
- PASS: at least 98% session agreement, or every deviation documented as a custom rule.
- WARN: 95% to under 98%.
- FAIL: under 95%, paid traffic mapped to an organic or direct channel, or unknown coalesced to Direct.
- N/A: the output does not use a warehouse channel field (for example it uses CRM original source).
- Old CASE logic often misses AI Assistant and hyphenated paid media. Precedence: unknown coalesced to Direct in warehouse logic scores here; overwritten CRM source fields score in CHN-08.
- Wrong fix: copying GA4's numbers into the warehouse instead of fixing the rule.

## CHN-08 CRM source fields usable, unknown kept unknown (B)

- Evidence: Q17, web-form contacts and deals in the window: original source populated and not overwritten after creation; the deal source rule documented.
- PASS: at most 5% of web-form contacts with unknown or offline original source; the deal source rule is documented and applied.
- WARN: over 5% and up to 15%.
- FAIL: over 15%, unknown replaced with Direct or a default value, or source overwritten after creation by an import or integration.
- N/A: no CRM-based source reporting. Verify HubSpot or Salesforce internal property names in the portal before querying.

## dbt and pipeline controls

- Tests on a critical model: grain (`unique`, or `unique_combination_of_columns` from dbt-utils), `not_null` on every grain column, `accepted_values` on mapped categories (lifecycle stage, deal stage, channel), `relationships` on join keys, and one completeness or reconciliation test on money models.
- Severity: error-severity failures fail the run and skip downstream models in `dbt build`; warn-severity failures do not stop anything.
- `store_failures`: each run replaces the failure table, including clean runs, and failure tables can hold personal data. Keep dated evidence elsewhere, with restricted access.
- Freshness: `dbt build` does not include source freshness. A dbt platform job's "Run source freshness" checkbox runs it first but does not stop later steps; an explicit `dbt source freshness` step (`dbt freshness` in dbt v2) does. A Core project scheduled elsewhere has no checkbox: read the orchestrator's steps instead.
- Schedule: run freshness and tests at least every half SLA interval (1-hour SLA every 30 minutes, 1-day SLA every 12 hours) and count missed runs as failures.

## Publication gate drill (OPS-04)

- Only in development or on a safe copy, and only with the user's explicit approval. Never on a production sync or upload.
- Case 1: make a prerequisite fail (an error-severity test on the copy, or a freshness threshold the copy cannot meet). Run the job. Pass: the dependent sync does not start and the destination is unchanged.
- Case 2: leave yesterday's successful table in place and fail today's build. Pass: the sync does not run on yesterday's table. A table that exists is not proof of a new publication.
- Record who approved, the environment, run IDs, timestamps and the result.
- An alert is not a gate. A notification after the run does not reverse writes, and a retry is a third, separate mechanism.

## Restricted data counting (OPS-08)

- Q22a lists column names that match personal-data patterns in shared datasets and failure schemas. Q22b counts email-like values per flagged column; values are never selected.
- Who can read a dataset or schema comes from an administrator (BigQuery dataset IAM bindings, Snowflake SHOW GRANTS). The audit identity usually cannot see it: record NOT CHECKED with that closing evidence.
- Broad roles: company-wide BI groups, external agencies, "all users". Hashed identifiers (SHA-256 of a normalised email) are acceptable where activation needs them.

## OPS-01 Tests on critical models (B)

- Evidence: check_dbt_artifacts.py over manifest.json with `--config`, so the models checked are the ones the outputs read (project tags often miss some), or the equivalent configuration in another tool. A config model missing from the manifest is NOT CHECKED with the reason: a raw connector table makes OPS-01 N/A for it; a table built outside dbt has no dbt tests to count.
- PASS: every critical model tests its grain and key not_null; mapped categories have accepted_values; joins have relationships; money models have a completeness or reconciliation test.
- WARN: partial coverage on non-money models.
- FAIL: a money or conversion model feeding a critical output has no grain test.
- N/A: no transformation layer (outputs read raw tables); the KEY checks then carry the weight.

## OPS-02 Test results acted on (B)

- Evidence: check_dbt_artifacts.py over run_results.json plus job history. Use the artifact of the step that ran the tests: each command overwrites the file, so a later `dbt run` or `dbt docs generate` leaves no test results. The script reads NOT CHECKED when a test on a critical model has no result.
- PASS: latest run inside the schedule; no error-severity failure on a critical model; every warning has an owner and a date; failure evidence kept beyond the next run.
- WARN: warnings older than 14 days without an owner, or failure rows kept only in the store_failures table each run replaces.
- FAIL: an error-severity test failing on a published critical model, or tests not run in the last scheduled interval.
- N/A: no tests exist (score OPS-01 FAIL instead).

## OPS-03 Check schedule matches the SLA (B)

- Evidence: scheduler configuration for freshness and tests; missed runs.
- PASS: at least every half SLA interval, and missed runs count as failures.
- WARN: interval longer than half the SLA. FAIL: checks scheduled less often than the SLA, or not scheduled.

## OPS-04 Publication gate before syncs and uploads (A)

- Evidence: job steps and sync triggers; a drill as above.
- PASS: activation syncs and conversion uploads start only after a successful build with fresh sources (an explicit freshness step that stops later steps, or a sync that checks run ID and cutoff), proven by a drill.
- WARN: dashboards only, with no stale label.
- FAIL: a sync or upload can run on a failed or stale model, for example freshness only through the job checkbox, or an independent sync schedule without a cutoff check.
- When an activation output has this FAIL, recommend pausing it until fixed. The owner decides; the agent never pauses anything.

## OPS-05 Schema changes and new values handled (B)

- Evidence: connector schema-change policy per connection (Fivetran Allow all, Allow columns, Block all), explicit column lists in staging, accepted-values or unmapped-value monitors for mapped categories (dbt tests, or Elementary schema-change and volume tests where installed; read their configured defaults at run time).
- PASS: policy recorded, staging selects explicit columns, monitors exist for lifecycle, deal stage and channel mappings.
- WARN: monitors missing for some mapped categories.
- FAIL: a new category value is silently mapped to other or null in a critical output (unmapped share over 1%).

## OPS-06 Owners, backups and alert routing (B)

- Evidence: owner register, alert routes, last acknowledged alert or drill.
- PASS: each critical output and source has an owner and a backup; alerts reach a named person; an alert or drill was acknowledged in the last 90 days.
- WARN: owners named but no backup, or the route is untested.
- FAIL: a critical output without an owner, or alerts going to an unowned channel.

## OPS-07 API version and deprecation calendar (C)

- Evidence: custom jobs' API versions and sunset dates. Google Ads API major versions live about 12 months; Meta Marketing API versions arrive about every four months with at least 90 days on the previous one. Verify the current sunset dates at run time.
- PASS: the calendar lists every custom job and nothing sunsets within 90 days without a migration owner.
- WARN: a version sunsets within 90 days, or no calendar exists. FAIL: not used.
- N/A: no custom API jobs; all ingestion runs through managed connectors.

## OPS-08 Restricted data kept out of shared marts and failure tables (A)

- Evidence: Q22a and Q22b (column names and counts only), Q21 failure schemas, dataset or schema access lists from an administrator.
- PASS: no raw emails, phones or names in marketing marts readable by broad BI roles; failure tables restricted; hashed identifiers only where activation needs them.
- WARN: raw personal data present but limited to named roles, without documentation.
- FAIL: raw personal data readable by broad roles in marketing datasets or failure tables.
- NOT CHECKED: access lists unavailable to the audit identity; ask an administrator.