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

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:

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:

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).

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.<name>. 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.

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:

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:

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.