analyzing-expensive-users

Files (1)

• SKILL.md

SKILL.md (first lines)

# 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

From PostHog/skills, MIT.