Exploring LLM Costs

1---
2name: exploring-llm-costs
3description: >
4  Investigate LLM spend in PostHog — total cost over time, cost by model,
5  provider, user, trace, or custom dimension, token and cache-hit economics,
6  and cost regressions. Use when the user asks "how much are we spending on
7  LLMs?", "which model / user / feature is most expensive?", "why did cost
8  spike?", wants to build a cost dashboard or alert, or pastes a trace URL
9  and asks about its cost.
10---
11 
12# Exploring LLM costs
13 
14PostHog attaches per-call cost metadata to every `$ai_generation` and `$ai_embedding`
15event at ingestion time. Every cost question reduces to an aggregation over those
16two event types — the interesting variation is only in how you group, filter, and
17compare.
18 
19This skill covers the common cost investigations: total spend, breakdowns
20(model, provider, user, trace, custom property), token and cache-hit analysis,
21regression debugging, and materializing results as insights, dashboards, or alerts.
22 
23## Tools
24 
25| Tool                            | Purpose                                                             |
26| ------------------------------- | ------------------------------------------------------------------- |
27| `posthog:execute-sql`           | Ad-hoc HogQL for any cost aggregation — the workhorse of this skill |
28| `posthog:query-llm-traces-list` | List traces with rolled-up cost, token, and error metrics           |
29| `posthog:query-llm-trace`       | Cost breakdown of a single trace across all its events              |
30| `posthog:read-data-schema`      | Discover which custom properties exist for breakdowns               |
31| `posthog:insight-create`        | Materialize a cost chart as a saved insight                         |
32| `posthog:dashboard-create`      | Bundle cost insights into a dashboard                               |
33| `posthog:alert-create`          | Alert when cost crosses a threshold                                 |
34 
35## Core rules
36 
37Three rules cover most of what goes wrong:
38 
39- **Sum `$ai_total_cost_usd` for rollups, never the components.** Components drop
40  request and web-search fees. The UI's cost cells sum `$ai_total_cost_usd`
41  over `event IN ('$ai_generation', '$ai_embedding')`; mirror that. Full
42  schema and rationale in [cost properties](./references/cost-properties.md).
43- **Always include both `$ai_generation` and `$ai_embedding`** in cost queries
44  unless the project demonstrably does not use embeddings — missing them silently
45  under-counts. `$ai_trace` and `$ai_span` carry no rollup cost; some SDK
46  wrappers duplicate `$ai_total_cost_usd` onto `$ai_trace` so don't include
47  it in rollups or you'll double-count.
48- **Always set a time range.** Cost queries without one scan the full events table.
49 
50`$ai_total_cost_usd` is set at ingestion via one of three paths (passthrough,
51custom pricing, automatic lookup). When a cost looks wrong, read
52`$ai_cost_model_source` first — see [cost sources](./references/cost-sources.md)
53for the precedence rules and a diagnostic query.
54 
55Cache-hit math depends on whether the provider reports cache tokens inclusively
56or exclusively of `$ai_input_tokens`. Always branch on the per-event
57`$ai_cache_reporting_exclusive` flag, never on provider name — see
58[cache accounting](./references/cache-accounting.md) for the exclusive-vs-inclusive
59formula.
60 
61`distinct_id` is the canonical user dimension. Customers often attach custom
62properties (`feature`, `tenant_id`, `workflow_name`) — discover them with
63`posthog:read-data-schema` before grouping. Don't guess names.
64 
65## Workflow: total spend in a window
66 
67```sql
68posthog:execute-sql
69SELECT round(sum(toFloat(properties.$ai_total_cost_usd)), 4) AS total_cost_usd
70FROM events
71WHERE event IN ('$ai_generation', '$ai_embedding')
72    AND timestamp >= now() - INTERVAL 30 DAY
73```
74 
75## Workflow: cost breakdowns
76 
77Every cost question is a variation of the same template — group by a dimension,
78aggregate `$ai_total_cost_usd`. See [breakdown patterns](./references/breakdown-patterns.md)
79for ready-to-run recipes:
80 
81- Cost over time (daily)
82- Cost by model
83- Cost by user (top spenders)
84- Cost by trace (top expensive traces)
85- Cost by custom dimension
86- Cost-per-call distribution
87- Input vs output vs cache economics
88 
89## Workflow: inspect a single trace's cost
90 
91When the user pastes a trace URL and asks about its cost, fetch the trace and
92surface the per-event breakdown:
93 
94```json
95posthog:query-llm-trace
96{ "traceId": "<trace_id>", "dateRange": {"date_from": "-30d"} }
97```
98 
99Sum `$ai_total_cost_usd` across the returned events, grouped by span name or
100model, to show which step(s) drove the cost. The trace response already
101includes `totalCost` as a convenience.
102 
103## Workflow: debug a cost regression
104 
105"Our LLM bill jumped — why?" is almost always one of: more calls, bigger
106prompts, a new model, or a change in cache-hit rate. Work through them in
107order — see [regression debugging](./references/regression-debugging.md) for
108the 5-step playbook.
109 
110## Workflow: materialize as an insight, dashboard, or alert
111 
112After ad-hoc queries answer the question, persist them as insights, bundle
113into a dashboard, or wire up alerts. See [materializing](./references/materializing.md)
114for ready-to-run JSON for `posthog:insight-create`, `posthog:dashboard-create`,
115and `posthog:alert-create`.
116 
117## Constructing UI links
118 
119- **Dashboard**: `https://app.posthog.com/ai-observability/dashboard`
120- **Traces list** (sort by cost): `https://app.posthog.com/ai-observability/traces`
121- **Generations list**: `https://app.posthog.com/ai-observability/generations`
122- **Users list** (per-user cost): `https://app.posthog.com/ai-observability/users`
123- **Single trace**: `https://app.posthog.com/ai-observability/traces/<trace_id>?timestamp=<url_encoded_iso>`
124 
125Always surface a UI link so the user can verify visually.
126 
127## Keeping this skill current
128 
129Provider reporting behavior (which tokens are inclusive vs exclusive,
130which costs show up where) shifts over time and can differ between SDK
131versions for the same provider. To avoid rot:
132 
133- Branch on event-level flags (`$ai_cache_reporting_exclusive`,
134  `$ai_cost_model_source`) rather than hardcoded provider or model names.
135  Those flags are ingestion's resolved answer for the specific event and
136  are the right source of truth.
137- `$ai_total_cost_usd` is always authoritative for rollups — prefer it
138  over summing components, which can drift as new cost categories are
139  added.
140- For anything not covered here (new cost categories, changes to
141  pricing lookup, provider additions), run `posthog:docs-search` for
142  "calculating costs" or "AI observability" first rather than trusting a
143  hardcoded rule in this file.
144- If you find this skill contradicting the UI, trust the UI and flag
145  the skill for an update.
146 
147## Tips
148 
149- Always set a time range — cost queries without one scan the full events table
150- Token, cost, model, and `$ai_trace_id` properties are on `events` — but message _content_ (`$ai_input` / `$ai_output_choices`) lives only on the `posthog.ai_events` table; see the traces skill's [event reference](../exploring-llm-traces/references/events-and-properties.md) if you need content alongside cost
151- Always include `$ai_embedding` alongside `$ai_generation` when summing cost; embeddings are cheap per-call but add up at scale
152- Costs are written at ingestion (see [Calculating LLM costs](https://posthog.com/docs/ai-observability/calculating-costs)) — if `$ai_total_cost_usd` is missing or zero, read `$ai_cost_model_source` first: `passthrough` means the SDK supplied costs; `custom` means custom token prices; `openrouter` / `manual` mean automatic lookup; missing means the model wasn't matched (unusual custom model, fine-tune). Grep: `countIf(properties.$ai_total_cost_usd IS NULL)` per `(model, source)`
153- Custom pricing uses **per-token** prices, not per-million — if a custom-priced model looks ~1M× too expensive or too cheap, that's almost always the bug
154- Exclude errored calls from cost totals only when explicitly asked — providers still charge for many error modes, and including them gives the truthful bill
155- For per-user totals, exclude rows where `distinct_id = properties.$ai_trace_id` — some SDKs default distinct_id to the trace ID when no user is set
156- Cost is additive across `$ai_generation` + `$ai_embedding` events within a trace; summing on `$ai_span` gives zero. `$ai_trace` may carry `$ai_total_cost_usd` from some SDK wrappers — don't include it in rollups or you'll double-count. `$ai_evaluation` events also carry cost but are not part of the stock UI rollups; include them only when the user explicitly wants evaluation spend in the total
157- Cache-hit rate depends on `$ai_cache_reporting_exclusive` — branch on the event-level flag rather than on provider or model name. Provider behavior and SDK versions drift; the flag is ingestion's resolved answer for that specific event
158- When answering "why is X expensive?", show the cost **and** the token split — the user almost always wants to know whether to shrink prompts, shrink outputs, or switch models
159- Before building a custom dashboard, check whether the stock `/ai-observability/dashboard` tiles already answer the question — re-creating them is churn
160- For large tenants, materialize common cost queries as insights and reuse via `insight-query`; ad-hoc SQL is fine for one-offs but re-running it on every dashboard load is expensive
161 
162## References
163 
164- [cost properties](./references/cost-properties.md) — full property schema, total-cost rationale, event-set rules
165- [cost sources](./references/cost-sources.md) — how costs get set at ingestion plus a diagnostic query
166- [cache accounting](./references/cache-accounting.md) — exclusive vs inclusive providers, cache-hit-rate formula
167- [breakdown patterns](./references/breakdown-patterns.md) — SQL recipes for every common breakdown
168- [regression debugging](./references/regression-debugging.md) — 5-step playbook for cost spikes
169- [materializing](./references/materializing.md) — insight, dashboard, and alert JSON
170