[SPIKE] Identify which AI crawlers (ChatGPT, Gemini, Claude, Perplexity) are hitting pages in Analytics

Research Question

What is required to identify which AI crawler is hitting a page (ChatGPT, Gemini, Claude, Perplexity, …), expose it through the Analytics endpoints, and surface it in the dashboard — and what is the effort/ETA?

Product needs Analytics to answer a specific question: "Is ChatGPT / Gemini / Perplexity / Claude reading my pages?"

Today the collector parses the user agent and stores only a boolean bot flag — it does not store which bot it is. The raw user agent string is persisted, which means the identity is already in the data and needs to be extracted rather than newly captured.

Concretely, the spike must determine:

1. Current state — exactly what is stored today: the bot flag, the raw user agent, which table/columns, and where in the collector the bot determination happens.
2. Classification approach — how to turn a raw user agent into a named crawler. Use an existing maintained parser/bot list, or a curated in-house AI-crawler list? AI crawler user agents change frequently, so how does the list stay current without a code release?
3. Ingest-time vs. query-time — store a normalized crawler name at collection, or derive it at query time from the stored user agent? Trade-offs in performance, schema change, and flexibility.
4. Historical data — since the raw user agent is already stored, can existing rows be backfilled so the dashboard shows history on day one instead of starting from zero?
5. Granularity — does Product need individual agent names (GPTBot, ClaudeBot, PerplexityBot), a grouped "AI crawler" category separate from conventional crawlers (Googlebot, bingbot), or both? This changes the data model.
6. API surface — what the endpoints need in order to group by agent and filter/exclude agents.
7. Dashboard surface — how this is presented, given Product wants to "start showing that ASAP".
8. Effort & ETA — an ordered breakdown with estimates.

Timebox

4h

Acceptance Criteria

• Current state documented — confirmed what the collector stores today (bot flag, raw user agent), the exact table/columns, and where the bot determination is made in the code.
• Classification approach recommended — a concrete recommendation on how a user agent maps to a named crawler (existing library/bot list vs. curated in-house list), with a stated plan for keeping the AI-crawler list current as new agents appear, ideally without requiring a code release.
• Ingest vs. query-time decided — a recommendation with trade-offs, including whether a ClickHouse schema change is required.
• Backfill feasibility answered — stated whether existing rows can be reclassified from the already-stored user agent, and the rough cost of doing so.
• Granularity confirmed with Product — individual agent names, a grouped "AI crawler" category, or both. Confirm with @fmontes rather than assuming.
• API change scoped — identified the endpoints and parameters needed to group by agent and filter/exclude agents.
• Dashboard change scoped — identified how crawler data is surfaced, and what the minimum shippable version looks like given the "ASAP" ask.
• Detection limits stated — documented that this is user-agent-based and therefore only as reliable as self-reported user agents, so Product understands what the numbers do and do not prove.
• Implementation plan + effort/ETA delivered — ordered breakdown (classification → storage/backfill → API → UI), per-item estimates, a total, and the follow-up issues to file.

Context

The request. From @fmontes:

do we have visibility on the agent crawlers that send events... can we filter by AI (chatgpt, gemini, claude)?

and, clarifying what "done" means:

We need to know what crawlers are getting to the pages

we needs to answer if chatgpt, gemini, perplexity, claude, etc... is reading the pages

Current implementation. Per @freddyDOTCMS:

We process the user agent and save if it is a Bot or not, but we don't save which agent it is

The good news is that we have the user agent (and saved it in the database) so I suppose that a change to save which agent it is is not too complicated

So the raw signal is already being persisted — this is an extraction and exposure problem, not a new-capture problem. That also opens the door to backfilling history.

Proposed shape (to be validated). @freddyDOTCMS proposed "exclude, group by agents, and that kind of stuff in the endpoints". Product did not explicitly confirm that shape; it responded with the underlying need. Confirming the exact requirement is part of this spike.

Layer	Likely change
Collector / classification	Map the stored user agent to a named crawler
Storage	Persist the crawler identity (and/or an AI-crawler category) — possible ClickHouse schema change + backfill
API	Group-by and filter/exclude by agent
UI	Surface which crawlers are reaching the pages

Urgency and sequencing. Product asked to "start showing that ASAP", and @freddyDOTCMS flagged it as needing higher priority. It was then confirmed it can wait until the next sprint.

When asked to choose between this and the PROD-analytics-in-lower-environments spike, @fmontes ranked "Analytics in lower envs" first — so #37107 takes precedence over this issue.

Ownership. @freddyDOTCMS is focused on the Experiments migration, so this is expected to be picked up by @jcastro-dotcms, who is in support rotation next sprint. Capacity should be accounted for when the ETA is produced.

Links

• Slack thread — Product request and discussion
• Related: #37107 — [SPIKE] Allow viewing Production Analytics data from a non-Production (Authoring) environment (ranked ahead of this one by Product)

Start by tracing the collector's user-agent parsing and bot determination, then inspect the ClickHouse schema, Analytics endpoints, and dashboard surfaces described in the issue. Document the current storage, classification and backfill options, API and UI scope, detection limits, and an ordered implementation plan with estimates. Done means the acceptance criteria are answered and follow-up issues are identified.