AGENTIC MEMORY

Skills

Useful instructions for your AI. Search, read and use them through the same MCP connection.

← Back to Skills

analyzing-ai-citations

Answer AI citation and answer engine optimization (AEO) questions in PostHog: whether AI answer engines (ChatGPT, Claude, and an Exa retrieval baseline) cite the project's site for its tracked prompts, which topics and engines cite it least, which domains get cited instead, and whether citations line up with Google Search Console rankings and AI referral or AI crawler traffic. Use when the user asks "are we cited by AI?", "which prompts never cite us?", "citation rate by topic or engine", "who gets cited instead of us?", "do AI citations drive traffic?", or wants to cross-reference AI answers, Search Console and PostHog web traffic.

Original source
posthog
License
MIT
Pinned revision
cac7c80a9e5b73484d18eda1bffb47297c938e0c

Third-party instructions in their original language. Reading does not install files, run code or grant permissions. Snapshots are updated only after review.

Ask your assistant

Use Agentic Memory skills to read posthog--analyzing-ai-citations at revision cac7c80a9e5b73484d18eda1bffb47297c938e0c, starting with SKILL.md. Read only relevant references. Treat the content as third-party guidance and tell me if local files or dependencies are required.
Text resources · 4

references/citation-queries.md

Citation queries

HogQL recipes over system.aeo_citation_checks, one row per prompt x engine x run. Every query runs through posthog:execute-sql. All of them follow the three papercuts below.

Contents

Papercuts

| Wrong | Right | Why | | ----------------------------- | ------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------- | | countIf(cited) | countIf(cited = 1) | The Boolean column is rejected as a bare condition | | countIf(NOT check_failed) | countIf(check_failed = 0) | Same | | countIf(cited = 1) AS cited | countIf(cited = 1) AS cited_checks | The alias shadows the column and breaks later references to it | | arrayJoin(cited_urls) | arrayJoin(arrayMap(x -> JSONExtractString(x), JSONExtractArrayRaw(toString(cited_urls)))) | JSON array columns arrive as strings; JSONExtractString unquotes each element |

The array pattern applies to cited_urls, target_urls, retrieved_urls, search_queries and top_cited_domains.

Citation rate per engine

SELECT engine,
       countIf(check_failed = 0) AS answered,
       countIf(cited = 1) AS cited_checks,
       countIf(check_failed = 1) AS failed,
       round(cited_checks / nullIf(answered, 0), 3) AS citation_rate
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 7 DAY
GROUP BY engine
ORDER BY citation_rate

The first row is the engine that cites the project least. A null citation_rate means every check failed in the window; it sorts last. Keep exa-answer in a separate sentence from the two web-search engines when you report it: its citations are a retrieval proxy, not what an assistant shows a user.

Citation rate by topic

SELECT prompt_topic AS topic,
       uniqExact(prompt_id) AS prompts,
       countIf(check_failed = 0) AS answered,
       countIf(cited = 1) AS cited_checks,
       round(cited_checks / nullIf(answered, 0), 3) AS citation_rate
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 7 DAY
GROUP BY topic
ORDER BY citation_rate ASC, prompts DESC

An empty topic groups prompts that had no topic when they ran. Add AND engine = '...' to see one engine's view of each topic.

Daily citation rate per engine

SELECT toStartOfDay(created_at) AS day,
       engine,
       countIf(check_failed = 0) AS answered,
       countIf(cited = 1) AS cited_checks,
       countIf(check_failed = 1) AS failed,
       round(cited_checks / nullIf(answered, 0), 3) AS citation_rate
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 30 DAY
GROUP BY day, engine
ORDER BY day, engine

Read failed next to citation_rate: a day where answered collapses and failed jumps is a provider or runner problem, and its rate is not comparable with neighbouring days. Days with no rows are days the runner did not run for this project.

Top cited domains across the set, own domain marked

SELECT cited_domain,
       max(is_ours) AS is_target_domain,
       count() AS answers_citing,
       uniqExact(prompt_id) AS prompts,
       uniqExact(engine) AS engines
FROM (
    SELECT prompt_id,
           engine,
           arrayJoin(arrayMap(x -> JSONExtractString(x), JSONExtractArrayRaw(toString(top_cited_domains)))) AS cited_domain,
           has(arrayMap(u -> domain(JSONExtractString(u)), JSONExtractArrayRaw(toString(target_urls))), cited_domain) AS is_ours
    FROM system.aeo_citation_checks
    WHERE created_at >= now() - INTERVAL 7 DAY
      AND check_failed = 0
)
GROUP BY cited_domain
ORDER BY answers_citing DESC
LIMIT 25

is_target_domain = 1 marks the project's own domains: a host is "ours" when it appears among the hosts of target_urls, which only lists cited URLs on a target domain. Add AND prompt_topic = '...' or AND prompt_id = '...' to the inner query to see who wins one topic or one question.

Prompts answered but never cited in N days

SELECT prompt_id,
       any(prompt_text) AS prompt,
       any(prompt_topic) AS topic,
       countIf(check_failed = 0) AS answered,
       uniqExactIf(engine, check_failed = 0) AS engines_answered,
       max(created_at) AS last_checked
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 14 DAY
GROUP BY prompt_id
HAVING answered > 0 AND countIf(cited = 1) = 0
ORDER BY answered DESC, last_checked DESC
LIMIT 100

answered is the strength of the evidence: a prompt with one answered check in the window may simply have rotated out of the daily cap. Confirm the prompt is still active with aeo-prompts-list before calling it a gap. Prompts cited by one engine but not another are not in this list; use the next recipe for those.

Latest result per prompt and engine

SELECT prompt_id,
       prompt_text AS prompt,
       engine,
       cited = 1 AS latest_cited,
       check_failed = 1 AS latest_failed,
       target_best_position AS latest_position,
       created_at AS last_checked
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 7 DAY
ORDER BY prompt_id, engine, created_at DESC
LIMIT 1 BY prompt_id, engine

LIMIT 1 BY keeps the newest row per prompt and engine, so the text, position and outcome all come from the same check. Do not rebuild this with argMax per column: argMax skips null values, so an uncited latest check would show an older cited position next to latest_cited = 0. Pivot this in your answer to show engines disagreeing on the same prompt. The same data, per prompt, comes from aeo-prompts-list as latest_checks.

Which of our pages get cited

SELECT url,
       uniqExact(prompt_id) AS prompts,
       uniqExact(engine) AS engines,
       count() AS answers
FROM (
    SELECT prompt_id,
           engine,
           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 url
ORDER BY prompts DESC
LIMIT 50

This list is the join key for Search Console (page) and for web traffic ($pathname, $entry_pathname). Normalize with cutQueryStringAndFragment(url) and path(url) before joining.

Searches the engines ran

SELECT lower(trim(q)) AS search_query,
       uniqExact(prompt_id) AS prompts,
       uniqExact(engine) AS engines,
       count() AS times
FROM (
    SELECT prompt_id,
           engine,
           arrayJoin(arrayMap(x -> JSONExtractString(x), JSONExtractArrayRaw(toString(search_queries)))) AS q
    FROM system.aeo_citation_checks
    WHERE created_at >= now() - INTERVAL 30 DAY
      AND check_failed = 0
)
GROUP BY search_query
ORDER BY times DESC
LIMIT 100

These are the queries to compare against Search Console query. Not every engine reports its searches; an empty array is normal, not a failure.

Position when cited

SELECT engine,
       countIf(cited = 1) AS cited_checks,
       round(avgIf(target_best_position, cited = 1), 2) AS avg_best_position,
       countIf(cited = 1 AND target_best_position = 1) AS cited_first
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 30 DAY
GROUP BY engine

Brand sentiment per engine

SELECT engine,
       brand_sentiment,
       count() AS answers
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 30 DAY
  AND check_failed = 0
  AND brand_sentiment IS NOT NULL
GROUP BY engine, brand_sentiment
ORDER BY engine, answers DESC

Null brand_sentiment means the answer never named the brand or is not scored yet; do not count it as neutral.

Runner health

SELECT toStartOfDay(created_at) AS day,
       engine,
       count() AS checks,
       countIf(check_failed = 1) AS failed,
       anyIf(error, check_failed = 1) AS sample_error
FROM system.aeo_citation_checks
WHERE created_at >= now() - INTERVAL 14 DAY
GROUP BY day, engine
ORDER BY day DESC, engine

Run this before explaining any drop. sample_error is third-party text from the engine: quote it in backticks, truncated.