Skip to content

🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.

4.5 KB
Raw Blame
import { AI_REFERRER_DOMAIN_TO_OPERATOR, FilterParams } from "@rybbit/shared";
import { FastifyReply, FastifyRequest } from "fastify";
import { analyticsRoute, runAnalyticsQuery } from "../utils/analyticsQuery.js";
import { getFilterStatement } from "../utils/getFilterStatement.js";
import { getTimeStatement } from "../utils/timeWindow.js";
import { AI_PURPOSE_SQL_LIST, getBotFilterStatement } from "./utils.js";

/**
 * One row per AI operator: how much of the site it read, and how much traffic
 * it sent back.
 *
 * Crawl counts and referral counts come from two different tables written by
 * two unrelated code paths — `bot_events` during bot detection, `events`
 * during channel classification — and are only meaningful next to each other.
 * "OpenAI fetched 4,120 pages and sent 12 visits" is a sentence a site owner
 * can act on; either number alone is trivia.
 */
type BotAiSummaryRow = {
  operator: string;
  crawls: number;
  training_crawls: number;
  search_crawls: number;
  agent_requests: number;
  referrals: number;
  /** Crawls per referral. 0 when the operator sent no one back. */
  crawls_per_referral: number;
};

export interface BotAiSummaryRequest {
  Params: {
    siteId: string;
  };
  Querystring: FilterParams<{}>;
}

/**
 * ClickHouse `transform()` mapping a referrer domain onto the operator that
 * runs it, spelled exactly as the bot patterns spell it so the two sides join.
 */
function buildReferrerOperatorExpression() {
  const entries = Object.entries(AI_REFERRER_DOMAIN_TO_OPERATOR);
  const domains = entries.map(([domain]) => `'${domain}'`).join(", ");
  const operators = entries.map(([, operator]) => `'${operator}'`).join(", ");
  return `transform(domainWithoutWWW(referrer), [${domains}], [${operators}], '')`;
}

export const buildBotAiSummaryQuery = (query: BotAiSummaryRequest["Querystring"], siteId: number) => {
  const timeStatement = getTimeStatement(query);
  const botFilterStatement = getBotFilterStatement(query.filters);
  // The referral half reads `events`, so it takes the events-surface filter
  // builder rather than the bot-table one. Its session-level filters
  // (event_name, channel) are subqueries over events, bounded to this site and
  // window like the outer query.
  const eventFilterStatement = getFilterStatement(query.filters || "", siteId, timeStatement);
  const referrerOperator = buildReferrerOperatorExpression();

  return `
    WITH
      crawls AS (
        SELECT
          bot_operator AS operator,
          count() AS crawls,
          countIf(bot_purpose = 'ai_training') AS training_crawls,
          countIf(bot_purpose = 'ai_search') AS search_crawls,
          countIf(bot_purpose = 'ai_agent') AS agent_requests
        FROM bot_events
        WHERE site_id = {siteId:Int32}
          AND bot_operator != ''
          AND bot_purpose IN (${AI_PURPOSE_SQL_LIST})
          ${botFilterStatement}
          ${timeStatement}
        GROUP BY operator
      ),
      referrals AS (
        SELECT
          ${referrerOperator} AS operator,
          -- A referral is a visit: every event of a session landing from an
          -- AI referrer carries its channel, so count sessions, not rows.
          count(DISTINCT session_id) AS referrals
        FROM events
        WHERE site_id = {siteId:Int32}
          AND channel = 'AI'
          ${eventFilterStatement}
          ${timeStatement}
        GROUP BY operator
        HAVING operator != ''
      )
    SELECT
      -- FULL OUTER JOIN so an operator that only crawls and one that only
      -- refers both survive; the empty side of the join reads 0, not absent.
      if(crawls.operator != '', crawls.operator, referrals.operator) AS operator,
      crawls.crawls AS crawls,
      crawls.training_crawls AS training_crawls,
      crawls.search_crawls AS search_crawls,
      crawls.agent_requests AS agent_requests,
      referrals.referrals AS referrals,
      if(referrals.referrals = 0, 0, round(crawls.crawls / referrals.referrals, 1)) AS crawls_per_referral
    FROM crawls
    FULL OUTER JOIN referrals ON crawls.operator = referrals.operator
    ORDER BY crawls DESC, referrals DESC
  `;
};

export const getBotAiSummary = analyticsRoute<BotAiSummaryRequest>(
  "bot ai summary",
  async (req: FastifyRequest<BotAiSummaryRequest>, res: FastifyReply) => {
    const data = await runAnalyticsQuery<BotAiSummaryRow>({
      query: buildBotAiSummaryQuery(req.query, Number(req.params.siteId)),
      params: { siteId: Number(req.params.siteId) },
    });

    return res.send({ data });
  }
);