Skip to content
Skills Library
Data & analytics · Analysis

analyzing-expensive-users

/analyzing-expensive-users

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.

Repository
posthog ↗
Licence
MIT
Last repository change
13.11.2026

Assessment and security

95/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene10 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 13.11.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

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.

Original author 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.

How it works

  1. Tools
  2. Core rules
  3. Workflow
  4. Constructing UI links
  5. Response shape
  6. Related skills
Original SKILL.md headings · 394 lines, 2,122 words
SKILL.md preview
---
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:

```sql
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:

```sql
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.

```sql
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.

```sql
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.

```sql
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:

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

If the request times out or fails with a connection error, the trace is too large for this tool.
Break its cost down with the per-trace SQL under "Cost by trace" in `exploring-llm-costs/references/breakdown-patterns.md`, with the time filter set to the same 30 days.

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.

```json
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`:

```sql
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.

```sql
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

Versions

RepositoryScoreLicence
This page · posthog/posthog95MIT
PostHog/skills100MIT

Database snapshot: 13.11.2026 · package version 92d1f81cc4cc (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100reduce-llm-cost
    Use this to cut the cost of an LLM app using observability data. Trigger on "my OpenAI/Anthropic bill is too high", "reduce token usage", "the app is expensive", "optimize LLM cost", "why am I spending so much on the API". Find the expensive spans first (measure), then apply the…
  • 100exploring-llm-costs
    Investigate LLM spend in PostHog — total cost over time, cost by model, provider, user, trace, or custom dimension, token and cache-hit economics, and cost regressions. Use when the user asks "how much are we spending on LLMs?", "which model / user / feature is most expensive?",…
  • 90databricks-cost-drivers
    Rank Databricks jobs and pipelines by cost/runtime and surface the biggest cost drivers, with trend direction. Use when the user asks what's costing the most in Databricks, which jobs got more expensive, or wants a weekly/ monthly cost review.
  • 100output-dev-cost-hooks
    Subscribe to cost events (cost:http:request, llm:generation:metering) to forward LLM and API spend to your own observability system. Use when adding cost/spend logging, building a cost observability integration, or forwarding per-request cost data to an external system.
  • 100finding-expensive-queries
    Finds and ranks expensive Snowflake queries by cost, time, or data scanned. Use when: (1) User asks to find slow, expensive, or problematic queries (2) Task mentions "query history", "top queries", "most expensive", or "slowest queries" (3) Analyzing warehouse costs or…
  • 95tma1
    Query TMA1 observability data (Claude Code, Codex, GitHub Copilot CLI, OpenClaw, any OTel SDK). Use when the user asks: how much did I spend, token usage, what has my agent been doing, agent cost, show me traces, show me events, check for errors, model comparison, tool usage.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.