/exploring-llm-costs

name: exploring-llm-costs
description: >
  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?", "why did cost
  spike?", wants to build a cost dashboard or alert, or pastes a trace URL
  and asks about its cost.

Exploring LLM costs

PostHog attaches per-call cost metadata to every `$ai_generation` and `$ai_embedding` event at ingestion time. Every cost question reduces to an aggregation over those two event types — the interesting variation is only in how you group, filter, and compare.

This skill covers the common cost investigations: total spend, breakdowns (model, provider, user, trace, custom property), token and cache-hit analysis, regression debugging, and materializing results as insights, dashboards, or alerts.

Tools

| Tool | Purpose | | ------------------------------- | ------------------------------------------------------------------- | | `posthog:query-trends` | Native cost trends with supported sums, breakdowns, and comparisons | | `posthog:execute-sql` | Ad-hoc HogQL for any cost aggregation — the workhorse of this skill | | `posthog:query-llm-traces-list` | List traces with rolled-up cost, token, and error metrics | | `posthog:query-llm-trace` | Cost breakdown of a single trace across all its events | | `posthog:read-data-schema` | Discover which custom properties exist for breakdowns | | `posthog:insight-create` | Materialize a cost chart as a saved insight | | `posthog:dashboard-create` | Bundle cost insights into a dashboard | | `posthog:alert-create` | Alert when cost crosses a threshold | | `posthog:generate-app-url` | Build region- and project-qualified links back to the UI |

Core rules

Three rules cover most of what goes wrong:

• **Sum `$ai_total_cost_usd` for rollups, never the components.** Components drop

request and web-search fees. The UI's cost cells sum `$ai_total_cost_usd` over `event IN ('$ai_generation', '$ai_embedding')`; mirror that. Full schema and rationale in [cost properties](./references/cost-properties.md).

• **Always include both `$ai_generation` and `$ai_embedding`** in cost queries

unless the project demonstrably does not use embeddings — missing them silently under-counts. `$ai_trace` and `$ai_span` carry no rollup cost; some SDK wrappers duplicate `$ai_total_cost_usd` onto `$ai_trace` so don't include it in rollups or you'll double-count.

• **Always set a time range.** Cost queries without one scan the full events table.

`$ai_total_cost_usd` is set at ingestion via one of three paths (passthrough, custom pricing, automatic lookup). When a cost looks wrong, read `$ai_cost_model_source` first — see [cost sources](./references/cost-sources.md) for the precedence rules and a diagnostic query.

Cache-hit math depends on whether the provider reports cache tokens inclusively or exclusively of `$ai_input_tokens`. Always branch on the per-event `$ai_cache_reporting_exclusive` flag, never on provider name — see [cache accounting](./references/cache-accounting.md) for the exclusive-vs-inclusive formula.

`distinct_id` is the canonical user dimension. Customers often attach custom properties (`feature`, `tenant_id`, `workflow_name`) — discover them with `posthog:read-data-schema` before grouping. Don't guess names.

Workflow: total spend in a window

posthog:execute-sql
SELECT round(sum(toFloat(properties.$ai_total_cost_usd)), 4) AS total_cost_usd
FROM events
WHERE event IN ('$ai_generation', '$ai_embedding')
    AND timestamp >= now() - INTERVAL 30 DAY

Workflow: cost breakdowns

Every cost question is a variation of the same template — group by a dimension, aggregate `$ai_total_cost_usd`. See [breakdown patterns](./references/breakdown-patterns.md) for ready-to-run recipes:

• Cost over time (daily)
• Cost by model
• Cost by user (top spenders)
• Cost by trace (top expensive traces)
• Cost by custom dimension
• Cost-per-call distribution
• Input vs output vs cache economics

Workflow: inspect a single trace's cost

When the user pastes a trace URL and asks about its cost, fetch the trace and surface the per-event breakdown:

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

Sum `$ai_total_cost_usd` across the returned events, grouped by span name or model, to show which step(s) drove the cost. The trace response already includes `totalCost` as a convenience.

Workflow: debug a cost regression

"Our LLM bill jumped — why?" is almost always one of: more calls, bigger prompts, a new model, or a change in cache-hit rate. Work through them in order — see [regression debugging](./references/regression-debugging.md) for the 5-step playbook.

Workflow: materialize as an insight, dashboard, or alert

After ad-hoc queries answer the question, persist them as insights, bundle into a dashboard, or wire up alerts. See [materializing](./references/materializing.md) for ready-to-run JSON for `posthog:insight-create`, `posthog:dashboard-create`, and `posthog:alert-create`.

Constructing UI links

Never hand-write `https://app.posthog.com/...` links. That host drops the region and the project prefix, so the user is redirected to login instead of the page you meant.

• **Prefer the canonical URL the tool returns.** `query-llm-traces-list` and `query-llm-trace`

return `_posthogUrl` — surface that value. For a single trace, append `?timestamp=<url_encoded_iso>` (the trace's earliest event time) to that URL; the returned link carries no timestamp, and