--- name: botify-tables-sql description: > Query Botify's data warehouse (crawl, logs, Search Console, AI visibility) through the Botify MCP tables_* tools using BigQuery Standard SQL, and turn the results into SEO and GEO (Generative Engine Optimization) analyses. Use this skill whenever the user asks for an SEO audit, a GEO/AI-bot audit, crawl budget analysis, log file analysis, Googlebot or GPTBot behavior, Search Console trends, AI visibility / citation analysis, or any question that requires querying Botify project data — even if they don't mention SQL or the tables tools explicitly. Also use it when the user mentions tables_query, tables_schema, tables_list, the "catalog." prefix, or Botify log tables. --- # Botify Tables SQL — SEO & GEO analysis via the Botify MCP This skill explains how to reliably query Botify's BigQuery-backed data catalog through the MCP `tables_*` tools, and how to structure the queries for SEO and GEO analysis. The tools are deferred: always call `tool_search` first (e.g. `tool_search(query="tables query schema Botify")`) to load them before use. Every `tables_*` call requires `organization` and `project` slugs. If they aren't known from the conversation, call `list_projects` to enumerate accessible org/project pairs rather than guessing (a wrong slug returns "No access to this organization/project"). The connector occasionally times out with "The connector's server isn't responding" — simply retry the same call once or twice before treating it as a failure. Queries are priced in credits computed from bytes shuffled. Be mindful of bytes shuffled (e.g. through partitioning etc) to limit your footprint. ## The tables_* toolset | Tool | Role | When to use | |---|---|---| | `list_projects` | Lists accessible org/project slug pairs | When the organization/project slugs are unknown | | `tables_list` | Lists table **families** with condensed values, plus every table of the editable catalog | **Always first.** The reliable way to discover which tables/dates exist. Output can be very large (some families list every daily partition); scan only the families you need and take the max of `extract_values` for the latest snapshot. Editable-catalog tables are listed individually, under their own name and with no `extract_values` | | `tables_schema` | Fields (names + types) of one table | Before writing non-trivial SQL against an unfamiliar table | | `tables_query` | Execute BigQuery Standard SQL | The workhorse. Set `max_results` explicitly | | `tables_query_export` | Export results to CSV/Parquet | Large result sets destined for files | | `tables_create` / `tables_insert` | Create a table / insert **one row** in the editable catalog | Persist derived datasets. Prefer `CREATE TABLE AS` (below) when the rows already come from SQL | ## Non-negotiable conventions 1. **Prefix every table reference with `catalog.`** in SQL: `FROM catalog.crawl_pages_20260629`. Omitting the prefix returns an error, even for `SELECT COUNT(*)`. The `tables_schema` tool, in contrast, takes the bare table name (`{'table_name': 'crawl_pages_20260713'}`). 2. **An error usually means a bad table name**, not a server problem. Querying a non-existent table returns 500, not an empty result. Confirm existence via `tables_list` before retrying. 3. **`INFORMATION_SCHEMA` does not work reliably** with the catalog prefix. To enumerate columns, either call `tables_schema`, or run `SELECT * FROM catalog.{table} LIMIT 1` and parse the field metadata from the tool result JSON (`fields` array). 4. **Dialect is BigQuery Standard SQL**: `UNNEST()` for arrays, `COUNTIF()`, CTEs, `SAFE_DIVIDE()`, backticks only for wildcard tables. 5. **Set `max_results`** on every `tables_query` call, sized to the expected output (e.g. 50 for aggregates, 200+ for breakdowns). 6. **URL hashes are fnv64 everywhere except SpeedWorkers.** Crawl, logs, `logs_urls`, and PageWorkers use `url_hash` (fnv64). SpeedWorkers `urlHash` is **xxhash** — joining it to logs or crawls silently matches nothing. Join SW with `urlFnv64Hash` = logs/crawls/`logs_urls` `url_hash`. ## Table families and naming Discover the actual list per project with `tables_list`; typical families: - `crawl_pages_YYYYMMDD` — SiteCrawler snapshot (one table per crawl). Key fields include `url`, `url_hash`, `http_code`, `compliant__is_compliant`, depth/inlinks/content metrics. - `logs_{family}_{crawls|visits}_YYYYMM` — monthly log partitions. Families include `google`, `bing`, `openai`, `other_ai_bots`. `crawls` = bot hits on the site; `visits` = referral visits from that engine. Shared fields: `ts`, `bot_id`, `se_id`, `http_code`, `url_hash`. - `search_console_flat` — GSC data at URL × keyword × device × country × date grain. The search term column is **`keyword`** (not `query`). Other key fields: `url`, `clicks`, `impressions` (both FLOAT — CAST to INT64 for display), `avg_position`, `branded` boolean, `search_type`, and `segments__*` columns. Direct date filtering with `WHERE date >= 'YYYY-MM-DD'`. GSC data lags a few days: check `MAX(date)` first and anchor "last week" windows on it, not on today. - `ai_visibility_results` — AI visibility campaigns (prompts run against LLMs). Nested arrays (`brands_normalized`, `domains_citations`) require `UNNEST()`. - `logs_urls` — fnv64 `url_hash` → full URL lookup table (to cross with logs_{family}_*). Same hash as crawl/logs/PageWorkers; SpeedWorkers must join via `urlFnv64Hash`, not `urlHash` (xxhash). ## Core SQL patterns **Cross-month log analysis.** Tables are monthly partitions, so a range spanning months requires explicit `UNION ALL` (preferred) or wildcard syntax. Wildcards (`` catalog.`logs_google_visits_*` `` with `_TABLE_SUFFIX BETWEEN '202601' AND '202606'``) work but are less reliable than explicit UNION ALL across named tables. For multi-family aggregations, wrap the UNION ALL sources in a CTE before grouping: ```sql WITH hits AS ( SELECT 'openai' AS family, ts, bot_id, http_code, url_hash FROM catalog.logs_openai_crawls_202606 UNION ALL SELECT 'openai', ts, bot_id, http_code, url_hash FROM catalog.logs_openai_crawls_202607 UNION ALL SELECT 'other_ai', ts, bot_id, http_code, url_hash FROM catalog.logs_other_ai_bots_crawls_202607 ) SELECT family, DATE(ts) AS day, http_code, COUNT(*) AS hits FROM hits WHERE DATE(ts) BETWEEN '2026-07-03' AND '2026-07-09' GROUP BY family, day, http_code ORDER BY day, hits DESC ``` **Resolving URLs from logs.** Log tables carry `url_hash` only; `LEFT JOIN catalog.logs_urls` (or the crawl table) on `url_hash` to get the URL string. Caveat: URLs outside the crawl scope (e.g. 4xx/5xx-only URLs) won't match the crawl table — NULLs in a LEFT JOIN are expected, not a bug. **Crawl-to-crawl comparison.** Two dated snapshots, UNION ALL with a literal date column: ```sql SELECT '20260629' AS crawl, http_code, COUNT(*) AS cnt FROM catalog.crawl_pages_20260629 GROUP BY http_code UNION ALL SELECT '20260713', http_code, COUNT(*) FROM catalog.crawl_pages_20260713 GROUP BY http_code ORDER BY crawl, cnt DESC ``` **Nested arrays (AI visibility).** ```sql SELECT COUNTIF(EXISTS( SELECT 1 FROM UNNEST(brands_normalized) b WHERE b.is_own_brand = TRUE )) AS own_brand_mentions, COUNT(*) AS prompts FROM catalog.ai_visibility_results ``` ## Creating and populating derived tables User-created tables live in the **editable catalog** and are still queried as `catalog.`. `CREATE` in the SQL is what maps an unknown destination name to that dataset; `INSERT INTO catalog.new_table` on a name that does not exist yet fails with "Unknown table family". Always use a new, descriptive name — never write into crawl/logs/GSC tables. `tables_create` and `tables_load` reject names reserved by any managed table family. Once created, these tables appear in `tables_list` next to the managed families, so list them there to see what already exists rather than guessing a free name. **From a query (preferred when the rows already come from SQL).** Run `CREATE TABLE … AS` through `tables_query`. Do **not** fetch the result and loop `tables_insert`. ```sql CREATE TABLE catalog.orphans_20260713 AS SELECT c.url, c.http_code, c.depth FROM catalog.crawl_pages_20260713 c LEFT JOIN catalog.logs_google_crawls_202607 g USING (url_hash) WHERE c.compliant__is_compliant AND g.url_hash IS NULL ``` Then `SELECT * FROM catalog.orphans_20260713`. To append later, once the table exists: ```sql INSERT INTO catalog.orphans_20260713 SELECT c.url, c.http_code, c.depth FROM catalog.crawl_pages_20260727 c LEFT JOIN catalog.logs_google_crawls_202607 g USING (url_hash) WHERE c.compliant__is_compliant AND g.url_hash IS NULL ``` `CREATE TABLE AS` has no TTL. Use `tables_create` (below) when the table should expire. **From rows you already have (LLM output, another tool).** Create the schema with `tables_create`, then insert one row per `tables_insert` call. `table_name` is on `tables_create`'s `item`, but on `tables_insert`'s `config`. ``` tables_create item: { "table_name": "pdp_enrichments", "ttl_hours": 168, "columns": [ {"name": "url", "type": "STRING", "mode": "REQUIRED", "description": "Page URL"}, {"name": "title", "type": "STRING", "mode": "NULLABLE"}, {"name": "description", "type": "STRING", "mode": "NULLABLE"}, {"name": "status_code", "type": "INT64", "mode": "NULLABLE"} ] } tables_insert config: {"table_name": "pdp_enrichments"} item: { "row": { "url": "https://example.com/p/1", "title": "Product 1", "description": "Wireless headphones with 30h battery.", "status_code": 200 } } ``` Column `type` is a BigQuery type (`STRING`, `INT64`, `FLOAT64`, `BOOL`, `TIMESTAMP`, `DATE`, `JSON`, `RECORD`, …). `mode` is `NULLABLE` (default), `REQUIRED`, or `REPEATED`. If `tables_create` returns `created: false`, the name already exists — pick another or insert into the existing table. ## Interpreting Botify-specific values - Negative `http_code` values in crawl data are **fetch errors**, not HTTP statuses: `-104` = connection reset by peer (rate limiting / WAF), `-160` = blocked by robots.txt. - `http_code = 0` in logs = connection aborted/timeout. ## Enums, bitmasks and the two bot_id namespaces `tables_schema` returns, for many integer columns, an `enum` array of `{value, name, extra}` and sometimes `"bitmask": true`. Three rules: 1. **Read the enum from `tables_schema`, don't hardcode it.** The sets differ per table, per log family, and grow as bots and error codes are added. 2. **Plain enums**: filter on the integer (`WHERE notDeliveredReason = 5`) and decode in the SELECT. There is no server-side decode function; BigQuery sees plain INT64. 3. **Bitmask fields** (`"bitmask": true`): test a flag with `col & value != 0`, and use `col = 0` for the no-flag case. Never use `=` on a single flag. ### The two bot_id namespaces — do not mix them There are **two unrelated bot id catalogs**, and the same bot has different ids in each: | Bot | LogAnalyzer `bot_id` (logs_* tables) | Activation `botId` (SpeedWorkers, PageWorkers) | |---|---|---| | Googlebot Desktop | 2 | 1 | | Googlebot Mobile | 5 | 2 | | OpenAI GPTBot | 60 | 25 | | OpenAI ChatGPT-User | 61 | 26 | | Anthropic ClaudeBot | 72 | 30 | | Anthropic Claude-User | 83 | 42 | | Perplexity Bot | 76 | 36 | Never join `logs_*.bot_id` to `speedworkers_*.botId` / `pageworkers_served_pages.bot_id`, and never reuse a mapping across the two. Join on `urlFnv64Hash` ↔ `url_hash` (see `references/query-cookbook.md`, Cross-product joins) and carry each side's decoded **name** into the comparison instead. `logs_*` enums are also **family-scoped**: `logs_google_crawls_*` only lists Google bots, `logs_openai_crawls_*` only OpenAI bots (60 GPTBot, 61 ChatGPT-User, 62 SearchBot, 86 AdsBot), `logs_other_ai_bots_crawls_*` the rest (Anthropic 72/73/74/83/84, Perplexity 76/81, Bytedance 71/87, CommonCrawl 75, Meta 78/79, Youbot 80, Mistral 82, Amazon 77). Ids are unique across families, but reading the enum from the family you actually queried avoids mislabelling. The catalog additionally exposes `searchEngineId` on `speedworkers_served_pages` (1 google, 2 bing, 3 yandex, 4 baidu, 5 botify, 6 apple, 7 naver, 8 openai, 9 amazon, 10 anthropic, 11 bytedance, 12 commoncrawl, 13 meta, 14 perplexity, 15 youbot, 16 prerender, 34 mistral) — the cheapest way to aggregate SpeedWorkers traffic by engine without listing every bot. ## Recommended workflow for an audit 1. `tool_search` to load the tables tools, then `tables_list` to inventory tables and identify the **latest** crawl snapshot and log months. 2. `tables_schema` (or `SELECT * … LIMIT 1`) on unfamiliar tables. 3. Run scoped aggregate queries first (status codes, daily volumes), then drill down. 4. Cross-reference: crawl snapshot ↔ logs (via `url_hash`) ↔ `search_console_flat`. For SpeedWorkers, join `urlFnv64Hash` to that same `url_hash` — never SW `urlHash`. 5. Report findings as business impact (crawl budget waste %, blocked AI bots share, crawl-to-visit ratio), not raw tables. Quantify week-over-week or crawl-over-crawl deltas. For ready-made SEO and GEO audit query recipes (crawl budget waste, AI bot WoW deltas, GSC trend analysis, orphan pages, crawl-to-visit ratio), read `references/query-cookbook.md`.