# Data checks: report specs, probes, queries and file shapes The window for reports is 28 complete days ending two days before the run (as of 2026-11-01: 2026-10-03 to 2026-10-30), with the previous 28 days for trend rules. BigQuery settles later: Google can change a daily table for up to 3 days after its date (answers 7029846, 9358801), so a table is final on the fourth day. That is why tracking-plan-audit, which reads the export, ends its window 4 days before the run, and why BQ3's comparison skips newer tables here. One-day probes use the last complete day. `scripts/ga4_snapshot.py` runs all of these; on route A the agent runs them with `run_report`, except R08, R14 and R15, which take the count-only versions below, and R16, which runs filtered to the root path; on route C the user exports the ones marked with export labels. Low-volume minimums: 1,000 sessions for session and event shares, 100 key events for key event shares, 100 purchases for purchase integrity. Below them the script reports counts and caps the rule at WARN. Without R04, sessions are summed from R03 and labelled approximate. ## Reports Each report keeps its `metadata` block (subjectToThresholding, samplingMetadatas, dataLossFromOtherRow); G25 reads them. Each also keeps `rowCount`: when fewer rows came back, shares built on that report are approximate and a FAIL from them is capped at WARN. - R01 Event inventory: dimensions eventName, isKeyEvent; metrics eventCount, keyEvents, eventValue, sessions; limit 1,000. Feeds G03, G11, G12, G13, G14. - R02 Daily volume: date, streamId; eventCount; previous_start to end (56 days). Feeds G02, G07, G18, G25. - R03 Hostnames by stream: hostName, streamId; eventCount, keyEvents, sessions; limit 500. Feeds G02, G08. - R04 and R04P Session channels: sessionDefaultChannelGroup, sessionPrimaryChannelGroup; sessions, keyEvents (R04 adds sessionKeyEventRate); R04 current window, R04P previous window. Feeds G22 and the totals other rules use. - R05 Referral sources: sessionSource, sessionMedium; sessions, keyEvents; filter sessionMedium exactly referral; limit 250. Feeds G09, G10. - R06 Key event credit: sourceMedium, defaultChannelGroup (both event-scoped); keyEvents; limit 250. Feeds G09, G22. - R07 Outbound clicks: linkDomain, outbound; eventCount; filter eventName exactly click. Feeds G10. - R08 Transaction IDs: transactionId; eventCount; filter eventName exactly purchase; limit 100,000 with offset paging. Stored as counts only: purchases, missing_ids, distinct_ids, ids_on_more_than_one_event, rows_read, truncated. Feeds G13. - R09 Purchase currency: currencyCode; eventCount, eventValue; filter eventName exactly purchase. Feeds G13. - R10 Google Ads dimensions: sessionSourceMedium, sessionGoogleAdsCustomerId; sessions; filter sessionSourceMedium exactly google / cpc. Feeds G15. - R11 Test data filters: testDataFilterName, eventName; eventCount; limit 1,000. Gives what a Testing filter would remove per event (purchases). Feeds G06, G07, G08. - R11T Test data filter totals: testDataFilterName; eventCount; limit 50. The exact event total for filter shares, never truncated. Feeds G06, G07. - R12 User-ID coverage: signedInWithUserId, hostName; totalUsers, eventCount. Users summed over hostnames are approximate. Feeds G05. - R13A Demographic probe: userAgeBracket; activeUsers; last 7 days. Only metadata.subjectToThresholding is read. Rows are dropped before writing. Feeds G05, G25. - R13B Key event probe: eventName, date; keyEvents; filter isKeyEvent exactly true. Only metadata is read. Rows are dropped before writing. Feeds G05, G25. - R14 Custom dimension fill: for each registered event or user dimension (first 60), `customEvent:NAME` or `customUser:NAME` by eventCount over 28 days (limit 1,000) and over one day. Stored per API name: rows_28d, not_set_share_28d, distinct_1d (the one-day rowCount), email_like, phone_like. Never the values. Item-scoped dimensions are skipped (eventCount does not apply). Route B only; route A uses R14C. Feeds G20, G24. - R15 PII probe: pageLocation, then pageReferrer; eventCount; filter OR of contains @, contains %40 and contains KEY= for the stems below; limit 1,000. Stored as reduced rows (below). Route B or a route C export only; route A uses R15C. Feeds G24. - R16 Page cardinality: pagePath; sessions; limit 100. Only metadata.dataLossFromOtherRow is read. Rows are dropped before writing, because page paths can carry personal data. Route A runs it differently (below). Feeds G25. - R17A Purchase totals: no dimensions; ecommercePurchases, purchaseRevenue, grossPurchaseRevenue. Feeds G13 (the order system comparison). - R17B Item totals: no dimensions; itemsPurchased, grossItemRevenue. Compared with grossPurchaseRevenue; the gross metrics on both sides keep refunds out. Feeds G13. - R17C Unnamed items: itemName; itemsPurchased; filter itemName emptyFilter, so only the (not set) row returns and no item names are read. Feeds G13. - META getMetadata: confirms custom definition API names; route A's `get_custom_dimensions_and_metrics` returns the same. Feeds G19, G20. Verify at run time: that `isKeyEvent` returns the strings true and false, and that currencyCode shows (not set) for purchases sent without currency. If it does not, run BQ5. ## Count-only versions for route A The MCP `run_report` tool puts every returned value into the conversation, so on route A the agent never requests transactionId, pageLocation, pagePath, pageReferrer or any customEvent: or customUser: dimension (R16 below is the one exception). It filters on them instead and returns counts only. The Data API filter reference allows a dimension to appear for the first time in a filter, so the request need not return it; if a call is rejected anyway, mark that sub-check NOT CHECKED. Write the results into the snapshot in the shapes below; the script reads them as they are. - R08C missing transaction IDs: no dimensions; eventCount; filter andGroup of eventName exactly purchase and transactionId emptyFilter. Write `{"purchases": P, "missing_ids": M, "distinct_ids": null, "ids_on_more_than_one_event": null, "count_only": true}` with P from R01 purchase eventCount. Duplicate IDs stay unchecked and the report says so. - R15C PII counts: for pageLocation, then pageReferrer, three calls each with dimension hostName only and eventCount, filtered with PARTIAL_REGEXP (not case sensitive): - email-like: `[A-Za-z0-9._%+-]+(@|%40)[A-Za-z0-9.-]+\.[A-Za-z]{2,}` - PII key with a value: `[?&](e[-_]?mail([-_]?address)?|mail|phone([-_]?number)?|tel|mobile|first[-_]?name|last[-_]?name|full[-_]?name|address|dob|birth[-_]?date|date[-_]?of[-_]?birth|ssn)=[^&#]` - secret key with a value: `[?&]([a-z0-9_-]*(token|password|secret)|passwd|pwd|api[-_]?key|otp|auth|reset)=[^&#]` - Write `{"count_only": true, "rows": [{"field": "pageLocation", "host": "www.example.com", "rule": "pii key with a value", "events": 312, "severity": "FAIL"}]}`, one row per host and pattern with events. Ambiguous keys (name, zip, zip_code, zipcode, postcode, post_code, id) use the same pattern shape with severity REVIEW. - R14C custom dimension counts: per dimension, eventCount over 28 days with no filter (once for all), with the dimension's emptyFilter, with the email pattern as PARTIAL_REGEXP and with `\+\d[\d\s().-]{6,18}\d|\(?\d{3}\)?[\s.-]\d{3}[\s.-]\d{4}` as FULL_REGEXP. Write `{"customUser:NAME": {"not_set_share_28d": empty / total, "distinct_1d": null, "email_like": N, "phone_like": N, "count_only": true}}`. One-day cardinality stays unchecked. - R16 on route A: run it with dimension pagePath, filter pagePath exactly `/` and limit 1, so only the root path can come back. Read only metadata.dataLossFromOtherRow, never quote the row, and write only rowCount and metadata to the snapshot. The ResponseMetaData reference says the flag is accurate regardless of filters and limits (checked on 2026-11-06; verify at run time that it still describes the whole report). Otherwise that G25 sub-check is marked NOT CHECKED. - Route C exports for R08 and R15 are passed to the script by path; the agent never opens them, and the user deletes them after the audit. ## PII reduction rules - Key names are normalised before matching: lower case, letters and digits only, so first_name, first-name and firstName are all firstname. Matching stays exact after that. - PII keys: email, emailaddress, mail, phone, phonenumber, tel, mobile, firstname, lastname, fullname, address, dob, birthdate, dateofbirth, ssn. - Secret keys: password, passwd, pwd, token, accesstoken, authtoken, idtoken, resettoken, refreshtoken, apikey, secret, clientsecret, otp, auth, reset. Any key ending in token, password or secret is a secret key too. - Ambiguous keys for human review: name, zip, zipcode, postcode, id. - R15 stems (each with = after it): mail, address, phone, phonenumber, tel, mobile, firstname, lastname, fullname, dob, birthdate, dateofbirth, ssn, password, passwd, pwd, token, apikey, secret, otp, auth, reset. Multi-word stems also run with _ and - between the words. mail= catches email= and e-mail=, address= catches email_address=, and token=, password= and secret= catch every key ending in them. - Email-like pattern: `[A-Za-z0-9._%+-]+(@|%40)[A-Za-z0-9.-]+\.[A-Za-z]{2,}` on the URL-decoded value. - Phone-like (custom dimensions only): a leading + with 8 to 20 digits and separators, or a separated 3-3-4 pattern. Plain digit strings (IDs, timestamps) are not counted. - A probe row is kept only when a normalised query key matches a list (the CONTAINS filter also catches hotel= for tel=; the exact match drops it) or the value is email-like. - Each kept row becomes: field, host, path (path segments holding an email become REDACTED), query key names, the rule matched, event count, severity (FAIL for PII or secret keys and email-like values, REVIEW for ambiguous keys). Values are never written, printed or pasted. - Query key names that look like an email or are not plain parameter names are written as REDACTED. That covers text with no = after it, which is a value and not a key. ## BigQuery queries Run each through `execute_sql_readonly` or the console. Dry-run first (`dryRun` on the tool, using the argument spelling in the client's tool schema) and agree a budget with the user: the tool takes `jobTimeoutMs` and `labels` but has no maximum bytes billed, and a project quota is a backstop, not a per-query cap. Keep the window to 7 days and the SELECT aggregated. Replace `acme-analytics.analytics_123456789`, the region and the dates. BQ1, daily tables and freshness (G18): ```sql SELECT table_name, creation_time FROM `acme-analytics.analytics_123456789.INFORMATION_SCHEMA.TABLES` WHERE table_name LIKE 'events_2%' ORDER BY table_name DESC LIMIT 40 ``` BQ2, default table expiration of the export dataset (G04, G18; the sandbox sets 60 days). The view is region-wide, so filter on the dataset: ```sql SELECT schema_name, option_value FROM `acme-analytics`.`region-us`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS WHERE option_name = 'default_table_expiration_days' AND schema_name = 'analytics_123456789' ``` BQ2b, daily tables that carry their own expiration (G04, G18). A table keeps the expiration it was created with, so after a sandbox upgrade the old tables still expire while the dataset default looks clean. Tables without an expiration are left out of the view: ```sql SELECT table_name, option_value FROM `acme-analytics.analytics_123456789.INFORMATION_SCHEMA.TABLE_OPTIONS` WHERE option_name = 'expiration_timestamp' AND table_name LIKE 'events_2%' ORDER BY option_value LIMIT 5 ``` Record `tables_with_expiry` (run it once more with `COUNT(*)` and no LIMIT) and, from the first row, `"earliest_expiry": {"table": "events_20260926", "expires": "2026-11-25"}`, where `expires` is the date part of option_value (`TIMESTAMP "2026-11-25T00:00:00.000Z"`). Null means no daily table carries an expiration. BQ3, daily coverage against the Data API (G18). Same definitions as query QC in ga4-unassigned-not-set-diagnosis: ```sql SELECT _TABLE_SUFFIX AS day, COUNT(*) AS events, COUNTIF(privacy_info.analytics_storage IS DISTINCT FROM 'No') AS events_not_denied, COUNTIF(privacy_info.analytics_storage = 'No') AS events_denied FROM `acme-analytics.analytics_123456789.events_*` WHERE _TABLE_SUFFIX BETWEEN '20261024' AND '20261030' GROUP BY day ORDER BY day ``` Compare `events_not_denied` with R02 eventCount for the exported streams on advanced consent mode, `events` otherwise, on tables more than 3 days old (the script skips newer ones). Pass within 2%. Cookieless pings are exported (answer 9358801). Google does not say whether the limit counts them, so the export limit check takes the higher of R02 and `events` as the conservative figure. Verify on the live BigQuery Export pages. BQ4, consent state for EEA traffic (G23, optional): ```sql SELECT CASE WHEN privacy_info.analytics_storage IN ('Yes', 'No') THEN privacy_info.analytics_storage ELSE 'unset' END AS state, COUNT(*) AS events FROM `acme-analytics.analytics_123456789.events_*` WHERE _TABLE_SUFFIX BETWEEN '20261024' AND '20261030' AND geo.country IN ('Germany', 'France', 'Netherlands') GROUP BY state ``` List every EEA country the way the export spells it; the three above are a placeholder. BQ5, purchases without a currency parameter (G13 fallback): ```sql SELECT COUNT(*) AS purchases, COUNTIF((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'currency') IS NULL) AS no_currency FROM `acme-analytics.analytics_123456789.events_*` WHERE _TABLE_SUFFIX BETWEEN '20261024' AND '20261030' AND event_name = 'purchase' ``` BQ6, parameters on key events (G20): run it, drop the keys already registered or collected automatically, and put the rest in `unregistered_key_event_params`. ```sql SELECT ep.key, COUNT(*) AS events FROM `acme-analytics.analytics_123456789.events_*`, UNNEST(event_params) AS ep WHERE _TABLE_SUFFIX BETWEEN '20261024' AND '20261030' AND event_name IN ('purchase', 'generate_lead', 'sign_up') GROUP BY ep.key ORDER BY events DESC ``` ## Google Ads GAQL Through the Google Ads MCP `search` tool, one customer at a time. The tool builds the GAQL itself, so pass the query's parts as `resource`, `fields` and `conditions` with `customer_id` (digits only); read the argument names from the tool's schema: ```sql SELECT conversion_action.name, conversion_action.type, conversion_action.category, conversion_action.status, conversion_action.primary_for_goal, conversion_action.counting_type, conversion_action.click_through_lookback_window_days FROM conversion_action WHERE conversion_action.status = 'ENABLED' ``` ```sql SELECT customer.id, customer.time_zone, customer.currency_code FROM customer ``` Custom goals are optional: if campaigns use them, list the action names they include under `custom_goal_actions` (check the custom conversion goal resource fields in the API reference before querying). ## File shapes bq-results JSON (`--bq`): ```json { "dataset": "acme-analytics.analytics_123456789", "location": "US", "default_table_expiration_days": null, "tables_with_expiry": 35, "earliest_expiry": {"table": "events_20260926", "expires": "2026-11-25"}, "daily_tables": ["events_20261030", "events_20261029"], "newest_daily_table": "events_20261030", "daily_counts": [{"day": "20261030", "events": 0, "events_not_denied": 0, "events_denied": 0}], "eea_consent": {"yes": 0, "no": 0, "unset": 0}, "unregistered_key_event_params": [] } ``` ads-conversions JSON (`--ads`): ```json { "customer_id": "123-456-7890", "time_zone": "America/New_York", "currency_code": "USD", "conversion_actions": [{"name": "Website purchase", "type": "WEBPAGE", "category": "PURCHASE", "status": "ENABLED", "primary_for_goal": true, "counting_type": "MANY_PER_CLICK", "click_through_lookback_window_days": 30}], "custom_goal_actions": [] } ``` Tag coverage CSV (`--tag-coverage`): any download with one column whose header contains URL and one whose header contains status. ## Route C export labels Build each table as a free-form exploration over the audit window and export it as CSV. The parser matches these English labels (extra columns are ignored): - R01: Event name, Event count, Key events (optional: Event value, Sessions; without Sessions the duplicate check in G12 is skipped). - R02: Date, Event count (optional: Stream ID). Use a range starting 28 days before the window if G07 needs the activation date. - R03: Hostname, Event count (optional: Stream name, Key events, Sessions). - R04: Session default channel group, Sessions (optional: Key events). - R05: Session source, Session medium, Sessions (optional: Key events). Filter Session medium to referral. - R08: Transaction ID, Event count. Filter Event name to purchase. The agent passes the file to the script by path and never opens it. - R11: Test data filter name, Event name, Event count. - R15: Page location, Event count. Filter to the PII and secret keys; export only those rows. The agent passes the file to the script by path and never opens it. What a GA4 export looks like (comment header, one table, Totals row skipped): ```text # ---------------------------------------- # Example Labs # Free form 1 # ---------------------------------------- # # Start date: 20261003 # End date: 20261030 Hostname,Stream name,Event count,Key events,Sessions www.example.org,Labs web,"28,000",20,170 app.example.org,Labs web,"40,000",50,300 Totals,,"68,000",70,470 ``` Verify the export layout at run time; Google changes the header block now and then, and an exploration may not offer every dimension listed for the API.