Query Guide
The query tool reads EdgeComet data through a JSON relation tree called an ir. This page explains the shape. The authoritative, site-specific version, with your exact field names, custom extraction fields, and segments, comes from get_schema: its grammar key is complete even when a client truncates tool descriptions. Have the assistant call it before writing queries.
The envelope
Every call takes a websiteId, a time window (days, or from + to), an optional segmentId, and for crawl data, the crawl to read (crawl, vs, window: "crawl"). See shared arguments. query adds its own paging arguments: limit, cursor, and count_only. The same envelope applies to export, which renders the identical IR to a CSV.
A query that reads crawl data together with bot requests or Search Console has to state its span: pass window: "crawl" to use the crawl's own span, or days, or from + to. Without one, the query is refused.
The grains
A grain is the row shape a source produces.
| Data | Grain | One row is |
|---|---|---|
| Bot requests | events_event | A single request: bot, URL, status, how it was served, timing, and the page tags parsed from it |
| Bot requests | events_page | A URL, with the SEO digest from its latest bot fetches and its visit counts per bot family |
| Search Console | gsc_page | A URL's totals for the window |
| Search Console | gsc_query | A date + URL + search query row |
| Search Console | gsc_page_trend | A URL's current-window against prior-window deltas |
| Search Console | gsc_general | A day for the whole property, with the desktop and mobile split, and no URL |
| Evergreen Crawl | crawl_page | A URL as the selected crawl saw it |
| Evergreen Crawl | crawl_link | A directed link between two URLs |
| Evergreen Crawl | crawl_redirect | A URL that answered with a 3xx |
| Evergreen Crawl | crawl_page_trend | A URL in the selected crawl, its baseline, or both |
| Evergreen Crawl | crawl_issue_page | A URL carrying at least one crawl issue, with the issue IDs |
| Sitemap | sitemap_url | A URL your XML sitemap lists, with its lastmod |
The crawl grains read one Evergreen Crawl snapshot, chosen by crawl. crawl_page_trend compares it with its baseline crawl, or with the crawl named in vs: its movement field says added, removed, or stable, and most of its fields come as _curr and _prev pairs. crawl_link has no single URL, so it joins through src_url_hash or tgt_url_hash.
A few grains read differently from what their names suggest:
gsc_queryis a lower bound. Google withholds the text of rare queries, so summed clicks and impressions come out belowgsc_pagefor the same window. Compare query-grain numbers with each other.- Position and CTR are weighted.
avgofpositionorctratgsc_queryreturns the impression-weighted average, the number Search Console itself reports. gsc_generalis Google's property report, notgsc_pagesummed: a results page showing several of your URLs counts once here and once per URL there. It has no URL, so it joins to nothing and cannot be scoped to a segment.- Page tags on raw events. At
events_event, the page content columns (title,meta_description,h1, and the rest) are filled only onrender,precache, andbypassrows. Cache-served rows replay a stored response and leave them blank.events_pagehandles this for you. - Pre-cache is not a bot.
precacherows are EdgeComet warming its own cache and carry no bot. Exclude them when you count crawl volume. - Sitemap URLs are normalized lightly. An anti-join from
sitemap_urlagainst crawl or event data can under-match, so check a short result against the URL text before reporting a page as missing.
A thirteenth grain, events_page_trend, compares two windows of the event log. A plain query refuses it; for what a page looks like now, use events_page.
Nodes
Every node has a "node" key; the child relation sits under "from":
{"node": "source", "grain": "events_page"}
{"node": "filter", "from": R, "where": P}
{"node": "aggregate", "from": R, "group_by": ["url"],
"metrics": [{"fn": "sum", "field": "clicks"}],
"having": {"metric": "sum_clicks", "op": "gt", "value": 9}}
{"node": "select", "from": R, "fields": ["url", "title"]}
{"node": "join", "kind": "left", "left": R, "right": R2, "bring": ["clicks"]}
{"node": "compute", "from": R, "as": "folder",
"expr": {"fn": "path_level", "args": [{"field": "url"}, {"lit": 1}]}}
{"node": "reduce", "from": R, "to": "url_hash"}
{"node": "union", "of": [R, R2]}
{"node": "order", "from": R, "by": "sum_clicks", "dir": "desc"}
{"node": "limit", "from": R, "n": 50}order and limit are their own nodes wrapped around the tree, never keys on aggregate or select, and by names exactly one field.
Metrics
aggregate metrics take fn + field: count, count_distinct, sum, avg, min, max, median, quantile, stddev, arg_max, and arg_min. quantile also takes a level between 0 and 1, and arg_max / arg_min take by, the column the row is chosen by.
The alias is derived, and it is the name having and order reference: count stays count, count_distinct on f becomes cd_f, a quantile at 0.9 becomes p90_f, arg_max on f by b becomes argmax_f_by_b, and everything else becomes fn_field (for example sum_clicks).
A metric with a where predicate counts only the rows that match it, so one rollup can put several populations side by side. A conditional metric must name its alias with as.
"group_by": [] returns one row of totals over the whole set. A metric over no rows is null rather than zero: it fails every having comparison and sorts last.
Joins
join attaches columns from the right relation to each row of the left one, matched on url_hash. Name the columns to copy in bring; they must not repeat a left field.
| Kind | Keeps |
|---|---|
inner | Left rows that have a match |
left | Every left row; brought columns are absent where nothing matched |
semi | Left rows that have a match, with no bring |
anti | Left rows with no match, with no bring |
metric | Like inner, for a right side that only carries numbers |
An absent brought column is not 0. Tell them apart with the missing and present operators. To rank by a brought column, aggregate the join by url and wrap that in order; a plain select of a join stays in URL-hash order.
events_event rows have no per-URL key, so they cannot be the right side of a join, or the left side of one that brings columns. Connect them to pages with a membership predicate instead (see Predicates).
Derived columns
compute adds one column, which you then filter, group, or order by its as name. Available functions: length, word_count, lower, upper, ratio, abs, round, bucket, xxHash64, normalize, first, element, domain, path, query_string, query_param, cut_query, path_depth, and path_level. An argument is {"field": "<name>"} or {"lit": <value>}.
Reduce and union
reduce builds the per-URL digest from a filtered slice of events_event, so every events_page field above it (visit counts, first and last seen, page fields) covers only the matching requests. This is how you scope a per-URL question to a date range or one bot family.
union merges two or more relations of the same grain into one row per URL. For "A or B" over one relation, use an or predicate instead.
Raw events
A top-level select over events_event returns individual requests, newest first unless you wrap it in order, capped at 50 rows with no cursor. Narrow the filter or the window instead of paging.
Requests from fake bots are excluded from every read. To ask about them, set "include_fake_bots": true on the events_event source, then filter or group by ip_validated (0 not validated, 1 verified, 2 fake, 3 error).
Predicates
Comparisons use field / op / value (value2 for between). Valueless operators omit value:
{"field": "bot_type", "op": "in", "value": ["ai_bot"]}
{"field": "meta_description", "op": "empty"}Operators are tokens, never symbols, and depend on the field type:
| Type | Operators |
|---|---|
| string | eq, neq, contains, not_contains, starts_with, ends_with, regex, not_regex, in, not_in, empty, not_empty |
| int, float | eq, neq, gt, gte, lt, lte, between, in, not_in |
| enum | eq, neq, in, not_in |
| date, datetime | eq, gt, gte, lt, lte, between |
| string[] | has, not_has, has_any, has_all, any_contains, none_contains, is_empty, not_empty, len_eq, len_gt, len_lt |
missing and present test whether a value is absent: on optional fields, on custom extraction fields, and on columns a left join brought across.
Combine predicates with {"and": [...]}, {"or": [...]}, and {"not": ...}.
A value of {"field": "<name>"} compares two columns, with eq, neq, gt, gte, lt, or lte:
{"field": "canonical_url", "op": "neq", "value": {"field": "url"}}A membership predicate tests a field against another relation, a select of exactly one field. This is how a query crosses grains without a join:
{"field": "url_hash", "op": "in", "rel": {"node": "select", "fields": ["url_hash"], "from": R}}Query classes
Two recognizable shapes in Search Console data are named, and you ask for them as having leaves rather than rebuilding them from word counts:
{"query_class": "fan_out"}: queries of eight or more words that drew impressions but no clicks, the pattern AI-driven query expansion leaves.{"query_class": "trash"}: rank-tracker artifacts shaped like"12 : some keyword".
They combine with and, or, and not, and work only on a gsc_query aggregate whose group_by includes query.
Paging
- A top-level
selectreturns rows in URL-hash order, paged by thelimit+cursorcall arguments, up to 50 rows a page.orderandlimitnodes are rejected there, except overevents_event.totalcomes on the first page only. - A top-level
aggregatereturns group rows ranked by anorderwrap (default: the leading metric, descending) as a bounded top-N of up to 200 groups, with no cursor. If the result is truncated, raise the limit, tightenhaving, or filter earlier. count_only: truereturns only the total, which is the cheap way to size a question before reading rows.
Worked examples
Top search queries by clicks
Rank-tracker noise excluded.
{"node": "limit", "n": 20, "from":
{"node": "order", "by": "sum_clicks", "dir": "desc", "from":
{"node": "aggregate",
"from": {"node": "source", "grain": "gsc_query"},
"group_by": ["query"],
"metrics": [{"fn": "sum", "field": "clicks"},
{"fn": "sum", "field": "impressions"}],
"having": {"not": {"query_class": "trash"}}}}}Keywords with two or more competing URLs
The cannibalization shape.
{"node": "aggregate",
"from": {"node": "source", "grain": "gsc_query"},
"group_by": ["query"],
"metrics": [{"fn": "count_distinct", "field": "url"}],
"having": {"metric": "cd_url", "op": "gte", "value": 2}}Recent AI bot fetches
{"node": "select", "fields": ["url", "bot", "event_date_time"], "from":
{"node": "filter",
"from": {"node": "source", "grain": "events_event"},
"where": {"field": "bot_type", "op": "in", "value": ["ai_bot"]}}}Requests, errors, and slow responses per bot family
Conditional metrics and a quantile in one rollup, with pre-cache excluded.
{"node": "aggregate",
"from": {"node": "filter",
"from": {"node": "source", "grain": "events_event"},
"where": {"field": "event_type", "op": "neq", "value": "precache"}},
"group_by": ["bot_type"],
"metrics": [{"fn": "count"},
{"fn": "count", "as": "error_events",
"where": {"field": "status_code", "op": "gte", "value": 400}},
{"fn": "quantile", "field": "serve_time", "level": 0.9}]}Pages without a meta description, with their clicks
A left join keeps pages Search Console never saw, with clicks absent.
{"node": "select", "fields": ["url", "title", "clicks", "position"], "from":
{"node": "join", "kind": "left", "bring": ["clicks", "position"],
"left": {"node": "filter",
"from": {"node": "source", "grain": "events_page"},
"where": {"and": [
{"field": "has_seo", "op": "eq", "value": 1},
{"field": "status_code", "op": "eq", "value": 200},
{"field": "meta_description", "op": "empty"}]}},
"right": {"node": "source", "grain": "gsc_page"}}}Bot visits per top-level folder
No grain has a folder column, so compute derives one from the URL.
{"node": "aggregate",
"from": {"node": "compute", "as": "folder",
"expr": {"fn": "path_level", "args": [{"field": "url"}, {"lit": 1}]},
"from": {"node": "filter",
"from": {"node": "source", "grain": "events_page"},
"where": {"and": [
{"field": "has_seo", "op": "eq", "value": 1},
{"field": "status_code", "op": "eq", "value": 200}]}}},
"group_by": ["folder"],
"metrics": [{"fn": "count"},
{"fn": "sum", "field": "googlebot_visits"},
{"fn": "sum", "field": "ai_user_visits"}]}Search engine coverage in a date range
reduce rebuilds the per-URL digest from search engine requests between two dates only.
{"node": "aggregate",
"from": {"node": "reduce", "to": "url_hash",
"from": {"node": "filter",
"from": {"node": "source", "grain": "events_event"},
"where": {"and": [
{"field": "bot_type", "op": "eq", "value": "search_engine"},
{"field": "event_date", "op": "between",
"value": "2026-09-01", "value2": "2026-09-15"}]}}},
"group_by": [],
"metrics": [{"fn": "count_distinct", "field": "url_hash"},
{"fn": "min", "field": "first_seen"},
{"fn": "max", "field": "last_seen"}]}Crawled URLs that answer with an error
{"node": "aggregate",
"from": {"node": "filter",
"from": {"node": "source", "grain": "crawl_page"},
"where": {"field": "status_code", "op": "gte", "value": 400}},
"group_by": ["status_code"],
"metrics": [{"fn": "count"}]}Striking-distance pages with their internal links
Crawl data joined with Search Console. Run it with window: "crawl".
{"node": "select",
"fields": ["url", "impressions", "position", "unique_linking_pages", "inlinks_contextual"],
"from": {"node": "join", "kind": "inner", "bring": ["impressions", "position"],
"left": {"node": "filter",
"from": {"node": "source", "grain": "crawl_page"},
"where": {"field": "disposition", "op": "eq", "value": "indexable"}},
"right": {"node": "filter",
"from": {"node": "source", "grain": "gsc_page"},
"where": {"and": [
{"field": "position", "op": "between", "value": 4, "value2": 20},
{"field": "impressions", "op": "gte", "value": 20}]}}}}Pages that changed their title and lost clicks between crawls
Each side of crawl_page_trend carries the clicks from its own crawl window. Run it with vs: "previous".
{"node": "select",
"fields": ["url", "title_prev", "title_curr", "clicks_prev", "clicks_curr"],
"from": {"node": "filter",
"from": {"node": "source", "grain": "crawl_page_trend"},
"where": {"and": [
{"field": "movement", "op": "eq", "value": "stable"},
{"field": "title_curr", "op": "neq", "value": {"field": "title_prev"}},
{"field": "clicks_curr", "op": "lt", "value": {"field": "clicks_prev"}}]}}}Internal links pointing at redirects and errors
Targets ranked by how many indexable pages link to them.
{"node": "order", "by": "cd_src_url_hash", "dir": "desc", "from":
{"node": "aggregate",
"from": {"node": "filter",
"from": {"node": "source", "grain": "crawl_link"},
"where": {"and": [
{"field": "is_internal", "op": "eq", "value": 1},
{"field": "tgt_status_code", "op": "gte", "value": 300},
{"field": "src_index_status", "op": "eq", "value": 1}]}},
"group_by": ["tgt_url", "tgt_status_code"],
"metrics": [{"fn": "count_distinct", "field": "src_url_hash"}]}}Sitemap URLs Googlebot has not requested
A negated membership over the event log. Run it with days: 30.
{"node": "select", "fields": ["url", "lastmod"], "from":
{"node": "filter",
"from": {"node": "source", "grain": "sitemap_url"},
"where": {"not": {"field": "url_hash", "op": "in", "rel":
{"node": "select", "fields": ["url_hash"], "from":
{"node": "filter",
"from": {"node": "source", "grain": "events_page"},
"where": {"field": "googlebot_visits", "op": "gt", "value": 0}}}}}}}Bots being spoofed
The source declares include_fake_bots so the read sees the excluded rows.
{"node": "aggregate",
"from": {"node": "filter",
"from": {"node": "source", "grain": "events_event", "include_fake_bots": true},
"where": {"field": "ip_validated", "op": "eq", "value": 2}},
"group_by": ["bot"],
"metrics": [{"fn": "count"}]}Field names are exact per grain and per site. When a query fails on an unknown field, the error returns the legal fields, and get_schema lists them all up front.