Analyzing Expensive Users

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

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:

sqlCopy code
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:

sqlCopy code
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.

sqlCopy code
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
    WHERE event = '$ai_generation'
        AND timestamp >= now() - INTERVAL 30 DAY
    GROUP BY distinct_id
)
SELECT
    count() AS users,
    round(sum(total_cost), 4) AS project_total_cost,
    round(avg(total_cost), 4) AS avg_cost_per_user,
    round(quantile(0.5)(total_cost), 4) AS p50_user_cost,
    round(quantile(0.9)(total_cost), 4) AS p90_user_cost,
    round(quantile(0.99)(total_cost), 4) AS p99_user_cost,
    round(avg(avg_cost_per_generation), 6) AS mean_user_cost_per_generation,
    round(avg(avg_input_tokens), 0) AS mean_user_input_tokens,
    round(avg(avg_output_tokens), 0) AS mean_user_output_tokens,
    round(sum(errors) / nullIf(sum(generations), 0), 4) AS error_rate
FROM per_user

The outer aggregate columns are named differently from the CTE columns they aggregate (project_total_cost, not total_cost). HogQL resolves a bare total_cost inside the outer sum()/avg() back to the output alias of the same name, which nests one aggregate inside another and fails the query with Aggregate function sum(per_user.total_cost) is found inside another aggregate function. Keep the two levels of names distinct.

If the baseline query still errors, report that the baseline is unavailable and say so in the response. Do not fabricate p50/p90/p99 figures or claim a user is "Nx above the median" without them — rank by absolute cost and share of spend instead, and note that the per-user distribution could not be computed.

When reporting top users, include each user's share of total spend and how many multiples above p50/p90 they are. That makes the skew obvious.

3. Decompose the top user's cost drivers

For each top user worth explaining, break their spend down by model and token economics.

sqlCopy code
posthog:execute-sql
SELECT
    toString(properties.$ai_provider) AS provider,
    toString(properties.$ai_model) AS model,
    count() AS generations,
    countDistinctIf(toString(properties.$ai_trace_id), notEmpty(toString(properties.$ai_trace_id))) AS traces,
    round(sum(toFloat(properties.$ai_total_cost_usd)), 4) AS total_cost,
    round(avg(toFloat(properties.$ai_total_cost_usd)), 6) AS avg_cost_per_generation,
    sum(toInt(properties.$ai_input_tokens)) AS input_tokens,
    sum(toInt(properties.$ai_output_tokens)) AS output_tokens,
    sum(toInt(properties.$ai_reasoning_tokens)) AS reasoning_tokens,
    sum(toInt(properties.$ai_cache_read_input_tokens)) AS cache_read_tokens,
    sum(toInt(properties.$ai_cache_creation_input_tokens)) AS cache_write_tokens,
    round(sum(toFloat(properties.$ai_input_cost_usd)), 4) AS input_cost,
    round(sum(toFloat(properties.$ai_output_cost_usd)), 4) AS output_cost,
    round(sum(toFloat(properties.$ai_request_cost_usd)), 4) AS request_cost,
    round(sum(toFloat(properties.$ai_web_search_cost_usd)), 4) AS web_search_cost,
    countIf(notEmpty(toString(properties.$ai_error)) OR toString(properties.$ai_is_error) = 'true') AS errors
FROM events
WHERE event = '$ai_generation'
    AND timestamp >= now() - INTERVAL 30 DAY
    AND distinct_id = '<distinct_id>'
GROUP BY provider, model
ORDER BY total_cost DESC

Interpret the result using this decision tree:

• High generations, ordinary cost per generation means volume is the driver.
• High cost per generation, ordinary volume means expensive models, long context, long outputs, reasoning tokens, web-search fees, or request fees are the driver.
• High input tokens usually points to context bloat, repeated conversation history, large retrieved documents, or missing truncation.
• High output or reasoning tokens points to verbose answers, chain-of-thought style reasoning models, missing output limits, or tool loops.
• Low cache reuse with high repeated input points to missed prompt caching. Use the cache formula from exploring-llm-costs/references/cache-accounting.md.
• High errors or many high-cost traces points to retries, failed tool calls, or loops. Read traces before saying which one.
• High request or web-search cost points to provider flat fees or tool-heavy generations, not token volume alone.

4. Compare the top user against everyone else

Run the same model or token breakdown for the whole project, then compare. Do not rely on raw totals only. You want statements like "this user used the same models as everyone else, but had 9x more generations" or "their volume was normal, but 82% of spend went to a high-cost model that is rare elsewhere."

Useful comparisons:

• Top user's share of total project cost
• Top user's generations and traces versus p50/p90 user
• Average cost per generation versus project average
• Input tokens per generation versus project average
• Output or reasoning tokens per generation versus project average
• Error rate versus project average
• Model mix versus global model mix
• Cache-hit rate versus global cache-hit rate for the same model

5. Find the user's expensive traces

Use SQL for the ranked trace list, then read representative traces with posthog:query-llm-trace. Both steps take a distinct_id or a trace ID, so they do not need a person UUID.

sqlCopy code
posthog:execute-sql
SELECT
    toString(properties.$ai_trace_id) AS trace_id,
    count() AS generations,
    round(sum(toFloat(properties.$ai_total_cost_usd)), 4) AS total_cost,
    round(avg(toFloat(properties.$ai_total_cost_usd)), 6) AS avg_cost_per_generation,
    sum(toInt(properties.$ai_input_tokens)) AS input_tokens,
    sum(toInt(properties.$ai_output_tokens)) AS output_tokens,
    countIf(notEmpty(toString(properties.$ai_error)) OR toString(properties.$ai_is_error) = 'true') AS errors,
    min(timestamp) AS started_at,
    max(timestamp) AS ended_at
FROM events
WHERE event = '$ai_generation'
    AND timestamp >= now() - INTERVAL 30 DAY
    AND distinct_id = '<distinct_id>'
    AND notEmpty(toString(properties.$ai_trace_id))
GROUP BY trace_id
ORDER BY total_cost DESC
LIMIT 10

Open at least the top 2-3 traces for the user:

jsonCopy code
posthog:query-llm-trace
{
  "traceId": "<trace_id>",
  "dateRange": { "date_from": "-30d" }
}

To list the user's traces with posthog:query-llm-traces-list, pass the person_uuid from step 1 as personId. Do not pass the distinct_id. personId accepts only a full person UUID, so a distinct_id fails the query. Preserve the test-account filter used in the SQL ranking, including any filter supplied by the user. Set filterTestAccounts to true when the ranking excludes test accounts, and false when it includes them. The example below uses false to match the unfiltered SQL ranking shown above. The list also includes traces from the person's other distinct IDs, so its totals can differ from the SQL ranking.

jsonCopy code
posthog:query-llm-traces-list
{
  "personId": "<person_uuid>",
  "dateRange": { "date_from": "-30d" },
  "filterTestAccounts": false
}

If you did not keep person_uuid, look it up for the selected distinct_id:

sqlCopy code
posthog:execute-sql
SELECT
    person_id AS person_uuid,
    count() AS generations,
    max(timestamp) AS last_seen
FROM events
WHERE event = '$ai_generation'
    AND timestamp >= now() - INTERVAL 30 DAY
    AND distinct_id = '<distinct_id>'
GROUP BY person_uuid
ORDER BY generations DESC

If this returns more than one row, the distinct_id maps to more than one person. Use the row with the most generations and say so in the caveats. If it returns no row, do not continue with query-llm-traces-list. Use the SQL trace ranking above and open each trace with posthog:query-llm-trace instead.

Look for the first concrete pattern that explains the aggregate:

• repeated tool calls or retry loops
• large context windows or repeated retrieved documents
• long multi-turn sessions
• expensive model selected for ordinary tasks
• many small calls from the same workflow
• verbose outputs or unconstrained reasoning
• web-search or request-fee-heavy calls
• errors that still incurred model cost

6. Check custom dimensions when the aggregate is ambiguous

If the top user appears expensive but the model/token breakdown does not explain why, discover custom event properties on $ai_generation and group by the likely product dimensions. Common examples are feature, tenant_id, organization_id, workflow_name, agent, route, or environment, but do not guess.

1. Call posthog:read-data-schema with kind: "event_properties" and event_name: "$ai_generation".
2. For promising fields, call posthog:read-data-schema with kind: "event_property_values" to confirm actual values.
3. Group the top user's cost by the discovered property.

sqlCopy code
posthog:execute-sql
SELECT
    toString(properties.<property_name>) AS dimension,
    count() AS generations,
    countDistinctIf(toString(properties.$ai_trace_id), notEmpty(toString(properties.$ai_trace_id))) AS traces,
    round(sum(toFloat(properties.$ai_total_cost_usd)), 4) AS total_cost,
    round(avg(toFloat(properties.$ai_total_cost_usd)), 6) AS avg_cost_per_generation
FROM events
WHERE event = '$ai_generation'
    AND timestamp >= now() - INTERVAL 30 DAY
    AND distinct_id = '<distinct_id>'
    AND isNotNull(properties.<property_name>)
GROUP BY dimension
ORDER BY total_cost DESC
LIMIT 20

This is often the difference between "user 123 is expensive" and "their contract-review workflow is expensive because every run feeds a 90k-token document to the most costly model."

Constructing UI links

Use posthog:generate-app-url for links. Do not hardcode the host because the project may be in a different region.

• Traces list: generate-app-url { "url": "/ai-observability/traces" }
• Single trace: generate-app-url { "url": "/ai-observability/traces/{id}", "params": { "id": "<trace_id>" } }

For a single trace, append ?timestamp=<url_encoded_started_at> when you have the trace timestamp so the UI opens the right time window.

Response shape

Lead with the answer, not the queries. A good response has:

1. Top users - ranked by total cost, with total cost, share of spend, generations, traces, average cost per generation, and error rate. Identify each user by a label only (email, name, or distinct_id). Do not print raw person.properties objects or other personal fields the user did not ask for.
2. Why they are expensive - one or two concrete drivers per user, compared against the baseline.
3. Evidence - model/token/cache/custom-dimension breakdowns plus linked example traces you read.
4. Likely levers - specific optimization ideas tied to the observed driver: reduce context, cap output, use a cheaper model for a workflow, improve caching, fix retry loops, or split a feature's traffic.
5. Caveats - whether the result includes embeddings, excludes trace-id defaults, or uses a different event set than the initial ranking.

Avoid generic advice. "Use cheaper models" is not useful unless the data shows that model mix is the driver. "Reduce prompt size" is not useful unless input tokens are high relative to the baseline.

Related skills

• exploring-llm-costs — project-wide spend: totals, breakdowns, and cost regressions
• exploring-llm-traces — read the traces behind a user's expensive generations