AI Traffic Join · Analyzing AI Citations · PostHog/skills · Skills Docs

Skill 02 · Analyzing AI Citations

Subchapter 2.1

AI Traffic Join

references/ai-traffic-join.mdMarkdown8 KBView on GitHub

Joining citations to AI traffic

Two signals in the project’s own data say whether AI answers turn into visits. They come from different tables and mean different things; report them separately.

SELECT toStartOfWeek($start_timestamp) AS week,
       $entry_referring_domain AS referrer,
       count() AS sessions,
       countIf($is_bounce = 1) AS bounces,
       round(avg($pageview_count), 1) AS pageviews_per_session
FROM sessions
WHERE $channel_type = 'AI'
  AND $start_timestamp >= now() - INTERVAL 90 DAY
GROUP BY week, referrer
ORDER BY week, sessions DESC

SELECT $entry_pathname AS landing_path,
       count() AS sessions,
       uniqExact($entry_referring_domain) AS referrers
FROM sessions
WHERE $channel_type = 'AI'
  AND $start_timestamp >= now() - INTERVAL 30 DAY
GROUP BY landing_path
ORDER BY sessions DESC
LIMIT 50

Property	Values
`$virt_traffic_type`	`AI Agent`, `Bot`, `Automation`, `Regular`
`$virt_traffic_category`	`ai_assistant`, `ai_search`, `ai_crawler` (plus non-AI categories such as `search_crawler`)
`$virt_bot_name`	`ChatGPT`, `Claude User`, `OpenAI Search`, `Perplexity`, `GPTBot`, `Claude`, `Google AI`, …
`$virt_bot_operator`	`OpenAI`, `Anthropic`, `Perplexity`, `Google`, `Meta`, `Apple`, …
`$virt_is_bot`	`true` for every non-regular request

SELECT $virt_traffic_category AS category,
       $virt_bot_name AS agent,
       $virt_bot_operator AS operator,
       count() AS requests,
       uniqExact(properties.$pathname) AS paths
FROM events
WHERE event IN ('$http_log', '$pageview', '$screen')
  AND $virt_traffic_type = 'AI Agent'
  AND timestamp >= now() - INTERVAL 30 DAY
GROUP BY category, agent, operator
ORDER BY requests DESC

SELECT properties.$pathname AS path,
       uniqExact($virt_bot_name) AS agents,
       count() AS fetches
FROM events
WHERE event = '$http_log'
  AND $virt_traffic_category = 'ai_assistant'
  AND timestamp >= now() - INTERVAL 30 DAY
GROUP BY path
ORDER BY fetches DESC
LIMIT 50

WITH cited AS (
    SELECT path(url) AS cited_path,
           uniqExact(prompt_id) AS citing_prompts,
           count() AS citing_answers
    FROM (
        SELECT prompt_id,
               arrayJoin(arrayMap(x -> JSONExtractString(x), JSONExtractArrayRaw(toString(target_urls)))) AS url
        FROM system.aeo_citation_checks
        WHERE created_at >= now() - INTERVAL 30 DAY
          AND cited = 1
    )
    GROUP BY cited_path
),
assistant_fetches AS (
    SELECT properties.$pathname AS fetched_path,
           count() AS fetches
    FROM events
    WHERE event = '$http_log'
      AND $virt_traffic_category = 'ai_assistant'
      AND timestamp >= now() - INTERVAL 30 DAY
    GROUP BY fetched_path
),
ai_sessions AS (
    SELECT $entry_pathname AS landing_path,
           count() AS sessions
    FROM sessions
    WHERE $channel_type = 'AI'
      AND $start_timestamp >= now() - INTERVAL 30 DAY
    GROUP BY landing_path
),
ai_conversions AS (
    SELECT e.session.$entry_pathname AS landing_path,
           uniqExact(e.$session_id) AS converting_sessions
    FROM events AS e
    WHERE e.event = 'signed_up'
      AND e.session.$channel_type = 'AI'
      AND e.timestamp >= now() - INTERVAL 30 DAY
    GROUP BY landing_path
)
SELECT c.cited_path,
       c.citing_prompts,
       c.citing_answers,
       f.fetches AS assistant_fetches,
       s.sessions AS ai_sessions,
       v.converting_sessions
FROM cited AS c
LEFT JOIN assistant_fetches AS f ON f.fetched_path = c.cited_path
LEFT JOIN ai_sessions AS s ON s.landing_path = c.cited_path
LEFT JOIN ai_conversions AS v ON v.landing_path = c.cited_path
ORDER BY c.citing_prompts DESC
LIMIT 50