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"). If that error goes on with "Request one at <url>", the slugs are right but the user has no access window on the project: give them that URL, and do not retry or try other slugs. 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.

After delivering work — a question answered, an audit finished — call send_feedback. Do not call it after a failed query: fold those issues into the next debrief. If the user continues the same main_task with new indications, call it again.

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 table the catalog does not serve returns a 4xx Unknown table family, not an empty result. Confirm existence via tables_list before retrying. A logs month outside the retention window answers the same way (see "Retention" below).
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). Name columns such as bot_name are listed by tables_schema.
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} — logs, every month in one table. 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. A filter on ts is mandatory (see "Log date ranges" below), and only part of the history is served (see "Retention"). The monthly names (logs_google_crawls_YYYYMM, `logs_google_crawls_*`) still resolve but are no longer listed, and the months before the retention floor no longer resolve at all.
• search_console — GSC data at URL × keyword × device × country × date grain (search_console_by_property at the property level). 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: anchor "last week" windows on the last day with data (time_filter.date_end in tables_list / tables_schema), not on today. The former names search_console_flat and search_console_by_property_flat still resolve.
• 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}_{crawls|visits}). Same hash as crawl/logs/PageWorkers; SpeedWorkers must join via urlFnv64Hash, not urlHash (xxhash).

Core SQL patterns

Log date ranges. A logs table spans every month, but is stored one table per UTC month of ts: the filter on ts picks the months read (and billed). The first and last days with logs are the table's time_filter.date_start / date_end. tables_query rejects a query reading a logs table unless the WHERE clause of the SELECT reading it (or an inner join condition) bounds ts from below, comparing ts or DATE(ts) with constant values: literals or CURRENT_DATE() arithmetic, no column and no subquery. DATE(ts) BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' and ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) work; a filter on a CTE wrapping the table, EXTRACT(MONTH FROM ts) = 7 or DATE(ts, 'Europe/Paris') do not. Retention. Some tables only serve part of their history, the same window the Botify app serves: tables_list and tables_schema report it as retention_floor, naming the column it applies to and the oldest date served. Data before it is not returned here or anywhere else in Botify: a range reaching further back simply starts at the floor, and the months and partitions before it are never read. Anchor windows on retention_floor.date when it is present, and say so in the deliverable rather than reporting a drop to zero.

Which families carry one:

• Logs crawls, visits and segmented metrics — bounded on ts (date for segmented metrics), at the retention window of the project's plan.
• SpeedWorkers served and indexed pages, PageWorkers served pages — bounded on date, three years back.
• Search Console, the crawls, Analytics — no floor. They report time_filter.date_start (the first day with data), which is not an enforced bound.

For the logs, whole months before the floor leave the catalog: naming one (catalog.logs_google_crawls_202301) is an Unknown table family error, not an empty result, and tables_list no longer lists it. The table spanning the months and the `logs_google_crawls_*` wildcard stay, bounded to the months still served, so a query reaching further back comes back with the rows from the floor onward.

For multi-family aggregations, filter each branch of the UNION ALL before grouping:

WITH hits AS (
  SELECT 'openai' AS family, ts, bot_id, http_code, url_hash
  FROM catalog.logs_openai_crawls
  WHERE DATE(ts) BETWEEN '2026-06-28' AND '2026-07-09'
  UNION ALL
  SELECT 'other_ai', ts, bot_id, http_code, url_hash
  FROM catalog.logs_other_ai_bots_crawls
  WHERE DATE(ts) BETWEEN '2026-06-28' AND '2026-07-09'
)
SELECT family, DATE(ts) AS day, http_code, COUNT(*) AS hits
FROM hits
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 g
  ON g.url_hash = c.url_hash AND DATE(g.ts) BETWEEN '2026-07-01' AND '2026-07-31'
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 g
  ON g.url_hash = c.url_hash AND DATE(g.ts) BETWEEN '2026-07-01' AND '2026-07-31'
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 id in WHERE (WHERE notDeliveredReason = 5), which keeps partition and cluster pruning, and use the name column to display or group. When tables_schema lists a *_name / *Name column right after an id (bot_name after bot_id on logs, botName / searchEngineName / notDeliveredReasonName on SpeedWorkers, …), select and group on it instead of writing a CASE. It returns the enum name, 'unknown' for codes missing from the enum, and NULL when the id is NULL. An array of ids gets an array of names: campaign_names follows campaign_ids on ai_visibility_results.
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 (Googlebot Desktop is bot_id 2 in logs_*, botId 1 in SpeedWorkers).

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 compare each side's bot name (bot_name on logs, botName on SpeedWorkers) instead.

In logs_google_*, mobile Googlebot is 10 (Google Smartphone), most Google hits. 5 (Google Mobile) is retired and empty: filtering on it drops nearly all of the crawl.

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 and its searchEngineName on speedworkers_served_pages — the cheapest way to aggregate SpeedWorkers traffic by engine without listing every bot.

Recommended workflow for an audit

1. 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. 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.
6. Call send_feedback after delivering findings (not after each failed query). Call it again if the user continues the same audit with new indications.

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.