/analyzing-expensive-users

name: analyzing-expensive-users
description: >
  Analyze the most expensive users in AI observability and explain why they cost so much.
  Use when the user asks about top spenders, expensive users, per-user LLM cost,
  user-level cost drivers, or patterns behind high AI observability spend.

Analyzing expensive users

Use this skill when the user wants to understand the most expensive users in AI observability. The job is not just to rank users by cost. The useful answer explains what makes the top users expensive: volume, model choice, prompt size, output size, cache behavior, retries/errors, trace type, feature or tenant dimensions, and representative trace examples.

For general cost rollups, also use `exploring-llm-costs`. For reading individual traces, also use `exploring-llm-traces`.

Tools

| Tool | Purpose | | ------------------------------- | ------------------------------------------------------------------ | | `posthog:execute-sql` | Rank users and compare their metrics against the project baseline | | `posthog:query-llm-traces-list` | Find high-cost traces for a specific user (by person UUID) | | `posthog:query-llm-trace` | Read representative traces to explain what actually happened | | `posthog:read-data-schema` | Discover custom event or person properties before grouping by them | | `posthog:generate-app-url` | Build region- and project-qualified links back to the UI |

Core rules

• **Start with a bounded time range.** If the user does not specify one, use the

last 30 days and say so. If the user provides a link or existing filters, preserve the date range, test-account filter, and property filters.

• **Start from generated-call spend.** The per-user ranking query groups

`$ai_generation` rows by `distinct_id`, with `traces`, `generations`, `errors`, `total_cost`, `first_seen`, and `last_seen`. This is the best first pass for finding expensive users.

• **For full spend by user, include embeddings deliberately.** Broader cost

rollups should include `event IN ('$ai_generation', '$ai_embedding')`, but call out when the event set changes.

• **Filter trace-id defaults when interpreting users.** Some SDKs use

`$ai_trace_id` as `distinct_id` when no user is set. For identified users, exclude `distinct_id = properties.$ai_trace_id` and flag how much spend becomes unattributed.

• **Do not guess custom dimensions.** Discover event and person properties

before grouping by `feature`, `tenant_id`, `plan`, `workflow_name`, or similar customer-specific fields.

• **Read traces before explaining causality.** Aggregates identify suspects;

representative traces show whether the user is expensive because of a real workflow, retries, loops, large context, tool-heavy generations, or other behavior.

Workflow

1. Rank users by generated-call spend

Use this first when the question asks for the most expensive users:

posthog:execute-sql
SELECT
    distinct_id,
    argMax(email, timestamp) AS email,
    argMax(name, timestamp) AS name,
    argMax(person_id, timestamp) AS person_uuid,
    countDistinctIf(ai_trace_id, notEmpty(ai_trace_id)) AS traces,
    count() AS generations,
    countIf(notEmpty(ai_error) OR ai_is_error = 'true') AS errors,
    round(sum(ai_total_cost_usd), 4) AS total_cost,
    round(avg(ai_total_cost_usd), 6) AS avg_cost_per_generation,
    sum(ai_input_tokens) AS input_tokens,
    sum(ai_output_tokens) AS output_tokens,
    min(timestamp) AS first_seen,
    max(timestamp) AS last_seen
FROM (
    SELECT
        distinct_id,
        timestamp,
        person_id,
        toString(properties.$ai_trace_id) AS ai_trace_id,
        toFloat(properties.$ai_total_cost_usd) AS ai_total_cost_usd,
        toString(properties.$ai_error) AS ai_error,
        toString(properties.$ai_is_error) AS ai_is_error,
        toInt(properties.$ai_input_tokens) AS ai_input_tokens,
        toInt(properties.$ai_output_tokens) AS ai_output_tokens,
        toString(person.properties.email) AS email,
        toString(person.properties.name) AS name
    FROM events
    WHERE event = '$ai_generation'
        AND timestamp >= now() - INTERVAL 30 DAY
)
GROUP BY distinct_id
ORDER BY total_cost DESC
LIMIT 25

If the user is asking for identified users, add this inside the inner `WHERE` clause:

AND (
    properties.$ai_trace_id IS NULL
    OR distinct_id != properties.$ai_trace_id
)

Project only the explicit label columns you need, such as `email` and `name`. Never select the raw `person.properties` object or a tuple containing it: it serializes the full property blob into the result and leaks personal data far beyond a label. If a user has no email or name, fall back to `distinct_id`.

Keep `person_uuid` for each user you plan to explain. Step 5 needs it. Name it differently from the inner `person_id` column. A bare `person_id` alias nests `argMax()` inside itself and fails the query.

2. Establish the baseline

The top user is only meaningful relative to everyone else. Run a per-user baseline so you can say whether a user is expensive because they have more generations, more traces, higher cost per generation, longer prompts, longer outputs, or a higher error rate.

posthog:execute-sql
WITH per_user AS (
    SELECT
        distinct_id,
        count() AS generations,
        countDistinctIf(toString(properties.$ai_trace_id), notEmpty(toString(properties.$ai_trace_id))) AS traces,
        countIf(notEmpty(toString(properties.$ai_error)) OR toString(properties.$ai_is_error) = 'true') AS errors,
        sum(toFloat(properties.$ai_total_cost_usd)) AS total_cost,
        avg(toFloat(properties.$ai_total_cost_usd)) AS avg_cost_per_generation,
        avg(toInt(properties.$ai_input_tokens)) AS avg_input_tokens,
        avg(toInt(properties.$ai_output_tokens)) AS avg_output_tokens
    FROM events