# Query cookbook — SEO & GEO recipes for Botify tables_query
 
All queries use BigQuery Standard SQL and the mandatory `catalog.` prefix.
Replace dated table names with the latest ones returned by `tables_list`.
 
## Contents
1. [SEO — crawl health](#1-seo--crawl-health)
2. [SEO — crawl budget (Googlebot logs)](#2-seo--crawl-budget-googlebot-logs)
3. [SEO — Search Console trends](#3-seo--search-console-trends)
4. [GEO — AI bot crawl behavior](#4-geo--ai-bot-crawl-behavior)
5. [GEO — AI referral traffic & crawl-to-visit ratio](#5-geo--ai-referral-traffic--crawl-to-visit-ratio)
6. [GEO — AI visibility (citations & brand mentions)](#6-geo--ai-visibility-citations--brand-mentions)
7. [SpeedWorkers — delivery health](#7-speedworkers--delivery-health)
8. [SpeedWorkers — indexation & refresh](#8-speedworkers--indexation--refresh)
9. [PageWorkers — module execution health](#9-pageworkers--module-execution-health)
10. [Cross-product joins (SW/PW ↔ logs ↔ crawl)](#10-cross-product-joins-swpw--logs--crawl)
---
 
## 1. SEO — crawl health
 
**Status code distribution of the latest crawl** (remember negative codes = fetch errors:
-104 connection reset, -160 robots.txt):
 
```sql
SELECT http_code, COUNT(*) AS urls,
       ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM catalog.crawl_pages_YYYYMMDD
GROUP BY http_code ORDER BY urls DESC
```
 
**Compliance split (indexable vs not):**
 
```sql
SELECT COUNT(*) AS total,
       COUNTIF(compliant__is_compliant) AS compliant,
       COUNTIF(NOT compliant__is_compliant) AS non_compliant
FROM catalog.crawl_pages_YYYYMMDD
WHERE http_code = 200
```
 
**Crawl-over-crawl regression check** (did a fix land?):
 
```sql
SELECT 'before' AS snapshot, http_code, COUNT(*) AS cnt
FROM catalog.crawl_pages_OLD GROUP BY http_code
UNION ALL
SELECT 'after', http_code, COUNT(*)
FROM catalog.crawl_pages_NEW GROUP BY http_code
ORDER BY http_code, snapshot
```
 
## 2. SEO — crawl budget (Googlebot logs)
 
**Daily Googlebot volume + wasted budget share** (404/redirect hits):
 
```sql
SELECT DATE(ts) AS day,
       COUNT(*) AS hits,
       ROUND(100 * COUNTIF(http_code = 404) / COUNT(*), 1) AS pct_404,
       ROUND(100 * COUNTIF(http_code BETWEEN 300 AND 399) / COUNT(*), 1) AS pct_3xx
FROM catalog.logs_google_crawls_YYYYMM
GROUP BY day ORDER BY day
```
 
**Compliant pages never crawled by Google** (crawl snapshot LEFT JOIN logs):
 
```sql
WITH crawled AS (
  SELECT DISTINCT url_hash FROM catalog.logs_google_crawls_YYYYMM
)
SELECT COUNT(*) AS never_crawled
FROM catalog.crawl_pages_YYYYMMDD c
LEFT JOIN crawled g USING (url_hash)
WHERE c.compliant__is_compliant AND g.url_hash IS NULL
```
 
**Top crawled URLs** (join to resolve URL strings; NULL joins = out-of-scope URLs, expected):
 
```sql
SELECT u.url, COUNT(*) AS hits
FROM catalog.logs_google_crawls_YYYYMM l
LEFT JOIN catalog.logs_urls u USING (url_hash)
GROUP BY u.url ORDER BY hits DESC LIMIT 50
```
 
**Googlebot mix by bot type** (mobile vs desktop vs Ads vs Inspection Tool — decode
`bot_id` from `tables_schema`; see the skill for the two bot_id namespaces):
 
```sql
SELECT bot_id, COUNT(*) AS hits,
       ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM catalog.logs_google_crawls_YYYYMM
GROUP BY bot_id ORDER BY hits DESC
```
 
Watch for `Google Inspection Tool` (URL Inspection / Search Console usage, not organic
crawl) and `Google Ads` bots polluting a "crawl budget" figure: exclude them before
reporting crawl budget, or report them separately.
 
## 3. SEO — Search Console trends
 
`search_console_flat` supports direct date filtering and a `branded` boolean. The search
term column is `keyword`; `clicks`/`impressions` are FLOAT (CAST for display). Before any
"last N days" analysis, run `SELECT MAX(date) FROM catalog.search_console_flat` — GSC data
lags a few days, so anchor the window on that date.
 
**Daily clicks/impressions, brand vs non-brand:**
 
```sql
SELECT date, branded,
       SUM(clicks) AS clicks, SUM(impressions) AS impressions,
       ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr,
       ROUND(SAFE_DIVIDE(SUM(avg_position * clicks), SUM(clicks)), 1) AS avg_position
FROM catalog.search_console_flat
WHERE date >= 'YYYY-MM-DD'
GROUP BY date, branded ORDER BY date
```
 
**Biggest losers week-over-week:**
 
```sql
WITH cur AS (
  SELECT keyword, SUM(clicks) AS c FROM catalog.search_console_flat
  WHERE date BETWEEN 'W2_START' AND 'W2_END' GROUP BY keyword
), prev AS (
  SELECT keyword, SUM(clicks) AS c FROM catalog.search_console_flat
  WHERE date BETWEEN 'W1_START' AND 'W1_END' GROUP BY keyword
)
SELECT COALESCE(cur.keyword, prev.keyword) AS keyword,
       IFNULL(prev.c, 0) AS clicks_prev, IFNULL(cur.c, 0) AS clicks_cur,
       IFNULL(cur.c, 0) - IFNULL(prev.c, 0) AS delta
FROM cur FULL OUTER JOIN prev USING (keyword)
ORDER BY delta ASC LIMIT 25
```
 
## 4. GEO — AI bot crawl behavior
 
Log families for AI: `logs_openai_crawls_YYYYMM`, `logs_other_ai_bots_crawls_YYYYMM`
(plus `_visits_` counterparts). Cross-month ranges: explicit UNION ALL.
`bot_id` now carries an enum in `tables_schema` — decode it instead of reporting raw ids
(see the skill), but keep the enum scoped to the family you queried.
 
**Week-over-week AI crawl volume by family and bot:**
 
```sql
WITH hits AS (
  SELECT 'openai' AS family, ts, bot_id, http_code
  FROM catalog.logs_openai_crawls_YYYYMM
  UNION ALL
  SELECT 'other_ai', ts, bot_id, http_code
  FROM catalog.logs_other_ai_bots_crawls_YYYYMM
)
SELECT family, bot_id,
       COUNTIF(DATE(ts) BETWEEN 'W1_START' AND 'W1_END') AS w1,
       COUNTIF(DATE(ts) BETWEEN 'W2_START' AND 'W2_END') AS w2,
       ROUND(100 * SAFE_DIVIDE(
         COUNTIF(DATE(ts) BETWEEN 'W2_START' AND 'W2_END') -
         COUNTIF(DATE(ts) BETWEEN 'W1_START' AND 'W1_END'),
         COUNTIF(DATE(ts) BETWEEN 'W1_START' AND 'W1_END')), 1) AS wow_pct
FROM hits GROUP BY family, bot_id ORDER BY w2 DESC
```
 
**Training crawlers vs answer-time fetchers.** The distinction drives the recommendation:
bulk ingestion bots (GPTBot 60, ClaudeBot 72, CommonCrawl 75, Bytedance 71/87) versus
user- or answer-triggered fetchers (ChatGPT-User 61, SearchBot 62, Claude-User 83,
Claude-SearchBot 84, Perplexity User 81, Mistral User 82). A site blocking the second
group is cutting itself out of live answers, not just out of training sets.
 
```sql
SELECT CASE
         WHEN bot_id IN (60, 72, 74, 75, 71, 87) THEN 'ingestion'
         WHEN bot_id IN (61, 62, 83, 84, 81, 82, 86) THEN 'answer_time'
         ELSE 'other'
       END AS intent,
       COUNT(*) AS hits,
       ROUND(100 * COUNTIF(http_code = 403) / COUNT(*), 1) AS pct_403
FROM (
  SELECT bot_id, http_code FROM catalog.logs_openai_crawls_YYYYMM
  UNION ALL
  SELECT bot_id, http_code FROM catalog.logs_other_ai_bots_crawls_YYYYMM
)
GROUP BY intent ORDER BY hits DESC
```
 
Confirm the ids against `tables_schema` for the project before publishing the split —
new bots are added to the catalog regularly.
 
**Blocking detection** (403 share per bot — a high 403 share means deliberate blocking,
a different remediation than 404 waste):
 
```sql
SELECT bot_id, COUNT(*) AS hits,
       ROUND(100 * COUNTIF(http_code = 403) / COUNT(*), 1) AS pct_403,
       ROUND(100 * COUNTIF(http_code = 0) / COUNT(*), 1) AS pct_aborted
FROM catalog.logs_other_ai_bots_crawls_YYYYMM
GROUP BY bot_id HAVING hits > 100 ORDER BY hits DESC
```
 
**What AI bots crawl vs. crawl scope** (detect discovery crawls of out-of-scope URLs):
 
```sql
SELECT c.url_hash IS NOT NULL AS in_crawl_scope, COUNT(*) AS hits
FROM catalog.logs_openai_crawls_YYYYMM l
LEFT JOIN catalog.crawl_pages_YYYYMMDD c USING (url_hash)
GROUP BY in_crawl_scope
```
 
## 5. GEO — AI referral traffic & crawl-to-visit ratio
 
`_visits_` tables have no `bot_id` (a visit is a human), but they do carry `device`
(INTEGER, no enum exposed — validate values against the project before labelling them).
 
```sql
WITH crawls AS (
  SELECT COUNT(*) AS n FROM catalog.logs_openai_crawls_YYYYMM
  WHERE DATE(ts) BETWEEN 'START' AND 'END'
), visits AS (
  SELECT COUNT(*) AS n FROM catalog.logs_openai_visits_YYYYMM
  WHERE DATE(ts) BETWEEN 'START' AND 'END'
)
SELECT crawls.n AS crawl_hits, visits.n AS referral_visits,
       ROUND(SAFE_DIVIDE(crawls.n, visits.n)) AS crawl_to_visit_ratio
FROM crawls, visits
```
 
A ratio in the thousands is common; report it as "ingestion vs. payoff" framing.
 
## 6. GEO — AI visibility (citations & brand mentions)
 
`ai_visibility_results` holds prompt-level results with nested arrays.
 
**Citation rate and brand mention rate:**
 
```sql
SELECT COUNT(*) AS prompts,
       ROUND(100 * COUNTIF(EXISTS(
         SELECT 1 FROM UNNEST(domains_citations) d WHERE d.is_own_domain = TRUE
       )) / COUNT(*), 1) AS citation_rate_pct,
       ROUND(100 * COUNTIF(EXISTS(
         SELECT 1 FROM UNNEST(brands_normalized) b WHERE b.is_own_brand = TRUE
       )) / COUNT(*), 1) AS brand_mention_rate_pct
FROM catalog.ai_visibility_results
```
 
(Field names inside the nested structs vary by project — confirm with `tables_schema`
before running.)
 
**Top cited domains:**
 
```sql
SELECT d.domain, COUNT(*) AS citations
FROM catalog.ai_visibility_results, UNNEST(domains_citations) d
GROUP BY d.domain ORDER BY citations DESC LIMIT 25
```
 
## 7. SpeedWorkers — delivery health
 
`speedworkers_served_pages` = one row per request reaching the SpeedWorkers dispatcher.
**Field names are camelCase** here (`notDeliveredReason`, `timeTotalMs`), unlike crawl and
log tables. There is no date suffix: filter with `WHERE DATE(date) >= '…'`.
`urlHash` is xxhash (SW-internal). To join logs or crawls, use `urlFnv64Hash` against
their `url_hash` (fnv64) — see §10.
 
Key columns: `delivered` BOOL, `cacheHit` BOOL, `deliveredFrom`
(0 Nowhere, 1 Cache, 2 Fetch, 3 LiveRender, 4 Section, 5 IgnoredParams, 6 LiveFetch),
`notDeliveredReason` (41 values), `deliveredFormat` (0 None, 1 Html, 2 Markdown, 3 Json),
`requestType` (0 Classic, 1 Preview, 2 Test, 3 Check, 4 ProxyBotHtml, 5 ProxyBotJson),
`botFilterResult` (0 NoResult, 1 MissingIp, 2 BotAllowed, 3 BotBlocked, 4 UnknownUA,
5 GoodBotIp, 6 BadBotIp, 7 MaybeBotIp), `qualityControlsFinalStatus`
(0 success, 1 warning, 2 fail, 3 error), plus a full timing breakdown.
 
**Always filter `requestType = 0`** for production reporting: previews, tests and checks
are internal traffic and will inflate or distort every rate below.
 
**Daily delivery and cache-hit rate:**
 
```sql
SELECT DATE(date) AS day,
       COUNT(*) AS requests,
       COUNTIF(delivered) AS delivered,
       ROUND(100 * COUNTIF(delivered) / COUNT(*), 1) AS pct_delivered,
       ROUND(100 * COUNTIF(cacheHit) / COUNT(*), 1) AS pct_cache_hit
FROM catalog.speedworkers_served_pages
WHERE requestType = 0 AND DATE(date) >= 'YYYY-MM-DD'
GROUP BY day ORDER BY day DESC
```
 
**Why requests were not delivered** (pair the reason with `deliveredFrom` — a
QualityCheckFailed on `deliveredFrom = 3` is a live-render problem, the same reason on
`deliveredFrom = 0` is a cache/config problem):
 
```sql
SELECT notDeliveredReason, deliveredFrom, COUNT(*) AS n
FROM catalog.speedworkers_served_pages
WHERE NOT delivered AND requestType = 0 AND DATE(date) >= 'YYYY-MM-DD'
GROUP BY notDeliveredReason, deliveredFrom
ORDER BY n DESC LIMIT 20
```
 
Decode via `tables_schema` (see the skill). Business reading of the frequent codes:
`1 NotCached` = coverage gap (page never indexed by SpeedWorkers), `16 OutOfScope` =
configuration, `21 BadToken` / `20 IpFiltered` = integration or CDN issue,
`5 QualityCheckFailed` = the safety net firing, `22 Throttled` = capacity.
 
**Speed gain vs origin** — the core SpeedWorkers business metric. `timeTotalMs` is the
SpeedWorkers response; `timeOriginalTotalMs` (and its `timeOriginalFetchMs` /
`timeOriginalRenderMs` components) is what the origin took for the same page:
 
```sql
SELECT COUNT(*) AS delivered_requests,
       APPROX_QUANTILES(timeTotalMs, 100)[OFFSET(50)] AS p50_sw_ms,
       APPROX_QUANTILES(timeTotalMs, 100)[OFFSET(95)] AS p95_sw_ms,
       APPROX_QUANTILES(timeOriginalTotalMs, 100)[OFFSET(50)] AS p50_origin_ms,
       APPROX_QUANTILES(timeOriginalTotalMs, 100)[OFFSET(95)] AS p95_origin_ms
FROM catalog.speedworkers_served_pages
WHERE delivered AND requestType = 0 AND DATE(date) >= 'YYYY-MM-DD'
```
 
Report it as a ratio ("bots get the page ~12× faster than origin"), and state the
percentile: p50 and p95 gaps often tell different stories. `timeExecPageWorkersMs` and
`totalTimeExecPwDeliveryApiMs` isolate the PageWorkers cost inside the SpeedWorkers
response — use them before blaming SpeedWorkers for a latency regression.
 
**Delivered format by engine — the GEO-relevant one.** `deliveredFormat` distinguishes
HTML from Markdown and JSON, i.e. content shaped for LLM agents rather than browsers:
 
```sql
SELECT searchEngineId, deliveredFormat, COUNT(*) AS requests,
       ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY searchEngineId), 1) AS pct_of_engine
FROM catalog.speedworkers_served_pages
WHERE delivered AND requestType = 0 AND DATE(date) >= 'YYYY-MM-DD'
GROUP BY searchEngineId, deliveredFormat
ORDER BY requests DESC
```
 
**Bot verification** — `botFilterResult` separates verified bots from UA spoofing, which
matters before attributing traffic to an engine:
 
```sql
SELECT botFilterResult, COUNT(*) AS requests,
       ROUND(100 * COUNTIF(delivered) / COUNT(*), 1) AS pct_delivered
FROM catalog.speedworkers_served_pages
WHERE requestType = 0 AND DATE(date) >= 'YYYY-MM-DD'
GROUP BY botFilterResult ORDER BY requests DESC
```
 
A large `4 UnknownUA` or `6 BadBotIp` share means the AI-bot volume reported elsewhere is
partly unverified traffic.
 
**Quality controls that failed:**
 
```sql
SELECT q.name, q.status, COUNT(*) AS occurrences
FROM catalog.speedworkers_served_pages, UNNEST(qualityControls) q
WHERE q.status IN (2, 3) AND requestType = 0 AND DATE(date) >= 'YYYY-MM-DD'
GROUP BY q.name, q.status ORDER BY occurrences DESC LIMIT 25
```
 
`status` 2 = fail (content rejected), 3 = error (the QC itself did not run) — the second
is an implementation bug, not a content problem.
 
**What SpeedWorkers actually altered** on delivery:
 
```sql
SELECT a.step, a.operation, a.action, SUM(a.count) AS total
FROM catalog.speedworkers_served_pages, UNNEST(alterations) a
WHERE a.operation IS NOT NULL AND requestType = 0 AND DATE(date) >= 'YYYY-MM-DD'
GROUP BY a.step, a.operation, a.action
ORDER BY total DESC LIMIT 20
```
 
`step`: 1 rendering, 2 preBeamResponse (at serve time). `operation`: 0 link, 1 canonical,
2 hreflang, 3 title, 4 h1, 5 description, 6 temporaryRedirect, 7 permanentRedirect,
8 alternateToMobile, 9 ogDescription, 10 element, 11 elementAttribute, 12 prev, 13 next,
14 htmlBlock (deprecated), 15 pageNotFound, 16 pageGone. `action`: 0 added, 1 changed,
2 removed. Repeated records contain NULL entries — keep the
`WHERE a.operation IS NOT NULL` guard or a phantom row appears in the aggregate.
 
**Indexability flags on delivered pages** (`pageMetadata` is a bitmask):
 
```sql
SELECT COUNTIF(pageMetadata = 0) AS clean,
       COUNTIF(pageMetadata & 2 != 0) AS noindex,
       COUNTIF(pageMetadata & 1 != 0) AS nofollow,
       COUNTIF(pageMetadata & 4 != 0) AS bad_canonical,
       COUNTIF(pageMetadata & 8 != 0) AS relative_canonical
FROM catalog.speedworkers_served_pages
WHERE delivered AND requestType = 0 AND DATE(date) >= 'YYYY-MM-DD'
```
 
## 8. SpeedWorkers — indexation & refresh
 
`speedworkers_indexed_pages` = one row per indexation event (not one row per page).
Same camelCase convention. `operation` is a **bitmask**: 1 INDEX, 2 FLUSH, 4 REFRESH,
8 ADD, 16 BLOCK, 32 UNBLOCK (so FLUSH|REFRESH = 6).
 
**Indexation outcome by source.** `InputType` (note the capital I) is the origin of the
event: 0 Unknown, 1 Crawl, 2 Batch, 3 Rawpages, 4 BotifyBatch, 5 Other, 6 RenderingFarm:
 
```sql
SELECT InputType,
       COUNT(*) AS events,
       COUNTIF(indexed) AS indexed,
       COUNTIF(deliverable) AS deliverable,
       COUNTIF(indexationPrevented) AS prevented,
       COUNTIF(jsFailed) AS js_failed
FROM catalog.speedworkers_indexed_pages
WHERE operation & 1 != 0 AND DATE(date) >= 'YYYY-MM-DD'
GROUP BY InputType ORDER BY events DESC
```
 
**Why indexed pages are not deliverable** (`notDeliverableReason` shares the
NotDeliveredReason enum of §7, so decode it the same way):
 
```sql
SELECT notDeliverableReason, COUNT(*) AS events
FROM catalog.speedworkers_indexed_pages
WHERE operation & 1 != 0 AND NOT deliverable AND DATE(date) >= 'YYYY-MM-DD'
GROUP BY notDeliverableReason ORDER BY events DESC
```
 
This is the coverage-gap query: a page counted as "indexed" but not deliverable will fall
back to origin at bot request time, and shows up as `1 NotCached` in §7.
 
**Rendering failures** — `renderStatus` is a bitmask of `JobResStatusBits`; warning bits
are small (2 WARN_OTHER, 4 WARN_RENDER_TIMEOUT, 8 WARN_TOO_BIG_TRUNCATED,
16 WARN_BAD_CONTENT_TYPE, 32 WARN_REDIRECTS) and error bits are large
(2147483648 ERR_OTHER, 68719476736 ERR_FETCH, 137438953472 ERR_FETCH_TIMEOUT,
274877906944 ERR_RENDER_CRASH, 549755813888 ERR_INJECT_JS …):
 
```sql
SELECT COUNT(*) AS events,
       COUNTIF(renderStatus & 1 != 0) AS ok,
       COUNTIF(renderStatus BETWEEN 2 AND 63) AS warnings_only,
       COUNTIF(renderStatus >= 2147483648) AS errors,
       COUNTIF(renderStatus = 0) AS no_render_bits
FROM catalog.speedworkers_indexed_pages
WHERE DATE(date) >= 'YYYY-MM-DD'
```
 
`renderStatus = 0` is normal for plain HTML fetches that never went through the rendering
farm — don't report it as a failure.
 
**Refresh pressure** (`refreshRequestPriority`: -1/NULL none, 0 EMERGENCY, 1 HIGH,
2 MEDIUM, 3 LOW):
 
```sql
SELECT refreshRequestPriority, COUNT(*) AS events,
       COUNT(DISTINCT refreshRuleId) AS rules
FROM catalog.speedworkers_indexed_pages
WHERE operation & 4 != 0 AND DATE(date) >= 'YYYY-MM-DD'
GROUP BY refreshRequestPriority ORDER BY events DESC
```
 
**Content churn** — `signature` / `signatureCritical` plus `previousPageSize` vs
`pageSize` show whether refreshes are actually changing anything, which is the argument
for tuning refresh rules rather than raising frequency.
 
## 9. PageWorkers — module execution health
 
`pageworkers_served_pages` (source "Pageworkers", the tag logs) = one row per page
served with the tag. **Field names are snake_case** here — the opposite of the
SpeedWorkers tables — and the nested enums are **strings**, not integers.
 
These tables are very large (hundreds of millions of module executions on a single
project). **Always filter on `DATE(date)`** before aggregating.
 
Key columns: `count_modules_total` / `_success` / `_failed`,
`modules_executions` (repeated: `type`, `id`, `version`, `error_type`, `error_message`,
`logger_actions`, `time_execute_ms`), `logger_messages` (`level`: 0 debug, 1 warning,
2 error — the only integer enum on this table), `bot_id` (FTL/veribot namespace,
**NULL = human browser**), `environment`, `time_total_ms`, `tag_version`,
`is_api_response_cached`, `session_id`.
 
`type` ∈ page_editor, custom_js, redirect, remove_links, no_follow_links.
`error_type` ∈ NULL (OK), unknown, invalid_config, invalid_selector, invalid_javascript,
invalid_template, element_not_found, selector_timeout, condition_not_met.
 
**Only executed modules are logged** (scope matched and device/context OK). A module
missing from `modules_executions` was not attempted — you cannot measure "should have run
but didn't" from this table; compare against the crawl scope instead.
 
**Module inventory: volume, error rate, cost:**
 
```sql
SELECT m.type, m.id, m.version,
       COUNT(*) AS executions,
       COUNTIF(m.error_type IS NOT NULL) AS errors,
       ROUND(100 * COUNTIF(m.error_type IS NOT NULL) / COUNT(*), 2) AS pct_error,
       ROUND(AVG(m.time_execute_ms), 1) AS avg_ms
FROM catalog.pageworkers_served_pages, UNNEST(modules_executions) m
WHERE DATE(date) >= 'YYYY-MM-DD'
GROUP BY m.type, m.id, m.version
ORDER BY executions DESC LIMIT 25
```
 
Group by `version` deliberately: a new version with a fresh error rate is the fastest way
to spot a regression introduced by an edit. `element_not_found` or `invalid_selector`
appearing on a previously clean module usually means the site's markup changed, not that
PageWorkers broke.
 
**Error breakdown per module type:**
 
```sql
SELECT m.type, m.error_type, COUNT(*) AS errors,
       ANY_VALUE(m.error_message) AS sample_message
FROM catalog.pageworkers_served_pages, UNNEST(modules_executions) m
WHERE m.error_type IS NOT NULL AND DATE(date) >= 'YYYY-MM-DD'
GROUP BY m.type, m.error_type ORDER BY errors DESC LIMIT 25
```
 
**What the modules actually changed on the page** (`logger_actions` mirrors the
SpeedWorkers `alterations` notions, as strings):
 
```sql
SELECT m.type, a.operation, a.action, SUM(a.count) AS total
FROM catalog.pageworkers_served_pages,
     UNNEST(modules_executions) m, UNNEST(m.logger_actions) a
WHERE DATE(date) >= 'YYYY-MM-DD'
GROUP BY m.type, a.operation, a.action ORDER BY total DESC LIMIT 25
```
 
This is the evidence that an optimization is live: titles changed, canonicals rewritten,
links removed — with counts, per module.
 
**Bots vs humans, and execution path.** `environment` is a string enum:
`browser` (browser tag), `dispatcher` (SpeedWorkers-served HTML with PageWorkers
optimizations already baked in — no JS), `contentSubmit`, `test`. `dispatcher` is
the path that matters for bots that don't run JavaScript:
 
```sql
SELECT environment,
       bot_id IS NULL AS human_browser,
       COUNT(*) AS pages,
       ROUND(AVG(time_total_ms), 1) AS avg_total_ms,
       ROUND(100 * COUNTIF(count_modules_failed > 0) / COUNT(*), 2) AS pct_pages_with_failure
FROM catalog.pageworkers_served_pages
WHERE DATE(date) >= 'YYYY-MM-DD'
GROUP BY environment, human_browser ORDER BY pages DESC
```
 
Cross-check that `dispatcher` rows correlate with bot traffic before putting the
split in a client deliverable.
 
**Tag latency for real users** (browser path only — the SEO-vs-UX trade-off question):
 
```sql
SELECT tag_version,
       COUNT(*) AS pages,
       APPROX_QUANTILES(time_total_ms, 100)[OFFSET(50)] AS p50_ms,
       APPROX_QUANTILES(time_total_ms, 100)[OFFSET(95)] AS p95_ms,
       ROUND(100 * COUNTIF(is_api_response_cached) / COUNT(*), 1) AS pct_api_cached
FROM catalog.pageworkers_served_pages
WHERE bot_id IS NULL AND DATE(date) >= 'YYYY-MM-DD'
GROUP BY tag_version ORDER BY pages DESC
```
 
`time_wait_page_loaded_ms`, `time_get_configs_ms`, `time_pw_download_ms` and
`time_links_modules_*_ms` decompose it when p95 is the problem.
 
## 10. Cross-product joins (SW/PW ↔ logs ↔ crawl)

Three grains, three join keys — and one namespace trap (never join bot ids across
the LogAnalyzer and Activation catalogs — see the skill).
 
- `pageworkers_served_pages.url_hash` lives in the same hash space as `catalog.logs_urls`
  and the log tables, so `JOIN catalog.logs_urls USING (url_hash)` works. Expect a partial
  match rate (roughly half on a sampled day of one project): URLs outside the crawl
  scope have no row in `logs_urls`. Verify before trusting a rate:
```sql
WITH pw AS (
  SELECT url, url_hash FROM catalog.pageworkers_served_pages
  WHERE DATE(date) = 'YYYY-MM-DD' LIMIT 5000
)
SELECT COUNT(*) AS sampled, COUNTIF(u.url_hash IS NOT NULL) AS matched
FROM pw LEFT JOIN catalog.logs_urls u USING (url_hash)
```
 
- SpeedWorkers `urlHash` is **xxhash**. Crawl, logs, `logs_urls`, and PageWorkers `url_hash`
  are **fnv64**. Joining `urlHash` to `url_hash` silently matches nothing. The SW column
  in the same space is `urlFnv64Hash` — use that:
  `sw.urlFnv64Hash = logs.url_hash` / `crawl.url_hash` / `logs_urls.url_hash`.
  `rawUrlHash` and `cacheUrlHash` are other SW-internal hashes; do not use them for
  cross-product joins.
- **Optimized pages vs Googlebot behavior**: does a page SpeedWorkers delivers get
  crawled more? Join SW delivery counts to log hits on the shared fnv64:
```sql
WITH sw AS (
  SELECT urlFnv64Hash AS url_hash,
         ANY_VALUE(url) AS url,
         COUNTIF(delivered) AS sw_delivered, COUNT(*) AS sw_requests
  FROM catalog.speedworkers_served_pages
  WHERE requestType = 0 AND DATE(date) BETWEEN 'START' AND 'END'
  GROUP BY urlFnv64Hash
), g AS (
  SELECT url_hash, COUNT(*) AS googlebot_hits
  FROM catalog.logs_google_crawls_YYYYMM
  WHERE DATE(ts) BETWEEN 'START' AND 'END'
  GROUP BY url_hash
)
SELECT sw.url, sw.sw_requests, sw.sw_delivered, IFNULL(g.googlebot_hits, 0) AS googlebot_hits
FROM sw LEFT JOIN g USING (url_hash)
ORDER BY sw.sw_requests DESC LIMIT 50
```
 
Note the asymmetry when interpreting: SpeedWorkers only sees requests routed through the
dispatcher, log tables see everything the origin/CDN logged. A URL with log hits and no
SpeedWorkers rows is a routing gap, which is a finding in itself.
 
- **Optimization → ranking**: aggregate `pageworkers_served_pages.logger_actions` per URL
  for the weeks before and after a version change, then join to `search_console_flat` on
  `url` for the same windows. Anchor the windows on `list_annotations` deployment dates
  when they exist, and state plainly that this is correlation.
---
 
## Reporting guidance
 
Translate query outputs into decision-ready findings: share of crawl budget wasted,
compliant-but-never-crawled page counts, blocked AI bot share, WoW deltas with dates of
inflection, and crawl-to-visit ratios. Always state the data window and the crawl
snapshot date used. Flag origin-health co-signals (rising 0/502/504 alongside crawl
spikes) as infrastructure findings distinct from SEO findings.
 
For SpeedWorkers and PageWorkers specifically, lead with the delivery-side business
metrics — delivered share, cache-hit rate, speed gain vs origin at p50 and p95, share of
bot requests falling back to origin and why — then the health metrics (QC failures,
module error rates by version). Always decode enum values into names in the deliverable,
state `requestType = 0` filtering, and keep unmapped codes visible as `code_N` rather
than dropping them.