Query usage analytics

Bursar records metered charges in PostgreSQL usage rollups so applications can query spend without scanning the ledger. This tutorial seeds usage, groups it by account and model, inspects daily totals, and reads aggregate statistics.

Learning objectives

After completing this tutorial, you can:

Query spend by account, model, and day
Rank high-usage accounts
Read aggregate usage statistics
Distinguish usage rollups from canonical ledger entries

Prerequisites

Complete the credit lifecycle tutorial
Start the notebook server from samples/python/notebooks/

Setup

Three demo users start on the pro plan, which has no free allowance, so every deduction becomes real spend against their purchased credits. (The free plan's monthly allowance would otherwise cover small deducts and they would not show up as charged spend.)

import atexit
from shared import start_postgres_store, cleanup, base_config, publish_config, USER_ADA, USER_ALEX, USER_JAMAL
from datetime import UTC, datetime, timedelta
from decimal import Decimal
from bursar.metrics import UsageMetrics

store, pgdata = start_postgres_store()
atexit.register(cleanup, pgdata)
bursar = publish_config(store, base_config())
credits = bursar.credits

for user, account in (
    (USER_ADA, "acct_ada"),
    (USER_ALEX, "acct_alex"),
    (USER_JAMAL, "acct_jamal"),
):
    bursar.accounts.on_account_created(user, account)
    credits.set_user_plan(user, "pro")
    credits.add_credits(
        user, Decimal(5000), idempotency_key=f"analytics:seed:{account}"
    )
print("users on pro plan")

Seeding spend

Each deduction is priced by the standard rate card: input tokens at 0.0025 per 1M, output at 0.0100 per 1M, cached reads at 0.00125 per 1M, and one execution job at 0.04 per job.

credits.deduct(USER_ADA, UsageMetrics(
    operation="completion",
    measures={"input_tokens": Decimal(100000), "output_tokens": Decimal(20000)},
    dimensions={"model": "gpt-4o"},
), idempotency_key="analytics:ada:completion:1")
credits.deduct(USER_ADA, UsageMetrics(
    operation="completion",
    measures={
        "input_tokens": Decimal(200000),
        "output_tokens": Decimal(50000),
        "cache_read_tokens": Decimal(100000),
    },
    dimensions={"model": "gpt-4o-mini"},
), idempotency_key="analytics:ada:completion:2")
credits.deduct(USER_ALEX, UsageMetrics(
    operation="completion",
    measures={"input_tokens": Decimal(50000), "output_tokens": Decimal(10000)},
    dimensions={"model": "gpt-4o"},
), idempotency_key="analytics:alex:completion:1")
credits.deduct(USER_JAMAL, UsageMetrics(
    operation="execution",
    measures={"jobs": Decimal(5), "compute_seconds": Decimal(30)},
    dimensions={"model": "gpt-4o"},
), idempotency_key="analytics:jamal:execution:1")

start = datetime.now(UTC) - timedelta(days=1)
end = datetime.now(UTC) + timedelta(days=1)
print("seeded")

Spend by user and by model

spend_by_user and spend_by_model aggregate charged spend (never allowance-covered usage) over a time window.

for row in credits.spend_by_user(start, end):
    print(row.user_id[:8], row.total_spend, "entries:", row.entry_count)

for row in credits.spend_by_model(start, end):
    print(row.model, row.total_spend, "entries:", row.entry_count)

Top users and daily spend

top_users ranks accounts by spend in the window. daily_spend projects the same data per UTC day — currently the Postgres backend surfaces rollup days as date objects while the typed row expects str, so validation raises; the other analytics methods are unaffected.

for row in credits.top_users(5, start, end):
    print(row.user_id[:8], row.total_spend)

try:
    for row in credits.daily_spend(start, end):
        print(row.date, row.total_spend, row.entry_count)
except Exception as exc:
    print(type(exc).__name__, str(exc)[:60])

Aggregate statistics

aggregate_stats collapses the window into one row: total credits consumed, active users, average daily spend, and the top model and user.

stats = credits.aggregate_stats(start, end)
print("total consumed:", stats.total_credits_consumed)
print("active users:", stats.active_users)
print("avg daily spend:", stats.avg_daily_spend)
print("top model:", stats.top_model)
print("top user:", stats.top_user[:8])

The metered charges

list_usage_charges returns the raw usage-charge rows behind the rollups, including allowance_covered (how much of the charge was covered by free allowance) and the idempotency key that makes webhook redelivery safe.

page = credits.list_usage_charges(USER_ADA, limit=10)
print("charges:", len(page.items))
for charge in page.items:
    print(charge.model, charge.operation, "requested:", charge.requested, "charged:", charge.charged,
          "allowance covered:", charge.allowance_covered, "idem:", charge.idempotency_key[:24])
print("all done")

Learning objectives​

Prerequisites​

Setup​

Seeding spend​

Spend by user and by model​

Top users and daily spend​

Aggregate statistics​

The metered charges​