--- name: openrouter-usage-analytics description: 'Track and analyze OpenRouter API usage patterns, costs, and performance. Use when building dashboards, optimizing spend, or reporting on AI usage. Triggers: ''openrouter analytics'', ''openrouter usage'', ''openrouter metrics'', ''track openrouter spend''. ' allowed-tools: Read, Write, Edit, Grep, Bash(python3:*), Bash(curl:*), Bash(jq:*) version: 1.20.0 license: MIT author: Jeremy Longshore tags: - saas - openrouter - analytics - cost-optimization - monitoring compatibility: Designed for Claude Code --- # OpenRouter Usage Analytics ## Overview OpenRouter provides usage data through three endpoints: `GET /api/v1/auth/key` (credit balance and rate limits), `GET /api/v1/generation?id=` (per-request cost and metadata), and response `usage` fields (token counts). This skill covers collecting metrics from these sources, building analytics pipelines, cost reporting, and performance dashboards. ## Prerequisites - An OpenRouter API key (`sk-or-v1-...`) exported as `OPENROUTER_API_KEY` — see the `openrouter-install-auth` skill for setup - Python 3.8+ with the OpenAI SDK plus `requests` (used to fetch exact per-request cost from the generation endpoint); `sqlite3` (stdlib) backs the analytics database - `curl` and `jq` for the Credit Balance Monitoring one-liner - `HTTP-Referer` / `X-Title` headers set on the client if you also want OpenRouter dashboard attribution ## Instructions 1. Route completions through `tracked_completion` (Collect Per-Request Metrics) — it times each call, then fetches the exact `total_cost` from `GET /api/v1/generation?id=` and emits a JSON metric with tokens, latency, and `model_requested` vs `model_used`. 2. Initialize `openrouter_analytics.db` with `init_analytics_db` (Analytics Database) and persist every metric via `store_metric` — the `generation_id` unique constraint plus `INSERT OR IGNORE` deduplicates retries. 3. Query the store with the Analytics Queries: daily cost summary, cost by model, top users by spend, hourly request pattern, and 30-day cost trend. 4. Watch remaining credits with the Credit Balance Monitoring snippet — curl + jq against `/api/v1/auth/key` reports `credits_used`, `credit_limit`, and `remaining`. 5. Generate the Weekly Report Generator output for stakeholders (totals plus top 5 models by cost). 6. Apply the retention and alerting policies from Enterprise Considerations (aggregate raw rows after 30 days, alert when daily cost exceeds 2x the historical average). ## Collect Per-Request Metrics ```python import os, time, json, logging from datetime import datetime, timezone from openai import OpenAI import requests as http_requests log = logging.getLogger("openrouter.analytics") client = OpenAI( base_url="https://openrouter.ai/api/v1", api_key=os.environ["OPENROUTER_API_KEY"], default_headers={"HTTP-Referer": "https://my-app.com", "X-Title": "my-app"}, ) def tracked_completion(messages, model="openai/gpt-4o-mini", user_id="system", **kwargs): """Make a completion and capture full analytics.""" start = time.monotonic() response = client.chat.completions.create( model=model, messages=messages, **kwargs ) latency = (time.monotonic() - start) * 1000 # Fetch exact cost from generation endpoint cost = 0.0 try: gen = http_requests.get( f"https://openrouter.ai/api/v1/generation?id={response.id}", headers={"Authorization": f"Bearer {os.environ['OPENROUTER_API_KEY']}"}, timeout=5, ).json() cost = float(gen.get("data", {}).get("total_cost", 0)) except Exception: pass metric = { "timestamp": datetime.now(timezone.utc).isoformat(), "generation_id": response.id, "model_requested": model, "model_used": response.model, "prompt_tokens": response.usage.prompt_tokens, "completion_tokens": response.usage.completion_tokens, "total_cost": cost, "latency_ms": round(latency, 1), "user_id": user_id, } log.info(json.dumps(metric)) return response, metric ``` ## Analytics Database ```python import sqlite3 def init_analytics_db(db_path: str = "openrouter_analytics.db"): conn = sqlite3.connect(db_path) conn.execute(""" CREATE TABLE IF NOT EXISTS metrics ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL, generation_id TEXT UNIQUE, model_requested TEXT, model_used TEXT, prompt_tokens INTEGER, completion_tokens INTEGER, total_cost REAL, latency_ms REAL, user_id TEXT ) """) conn.execute("CREATE INDEX IF NOT EXISTS idx_metrics_ts ON metrics(timestamp)") conn.execute("CREATE INDEX IF NOT EXISTS idx_metrics_model ON metrics(model_used)") conn.execute("CREATE INDEX IF NOT EXISTS idx_metrics_user ON metrics(user_id)") conn.commit() return conn def store_metric(conn, metric: dict): conn.execute( """INSERT OR IGNORE INTO metrics (timestamp, generation_id, model_requested, model_used, prompt_tokens, completion_tokens, total_cost, latency_ms, user_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""", (metric["timestamp"], metric["generation_id"], metric["model_requested"], metric["model_used"], metric["prompt_tokens"], metric["completion_tokens"], metric["total_cost"], metric["latency_ms"], metric["user_id"]), ) conn.commit() ``` ## Analytics Queries ```sql -- Daily cost summary SELECT date(timestamp) as day, COUNT(*) as requests, SUM(prompt_tokens + completion_tokens) as total_tokens, ROUND(SUM(total_cost), 4) as total_cost, ROUND(AVG(latency_ms)) as avg_latency_ms FROM metrics WHERE timestamp > datetime('now', '-7 days') GROUP BY day ORDER BY day DESC; -- Cost by model (this week) SELECT model_used, COUNT(*) as requests, ROUND(SUM(total_cost), 4) as cost, ROUND(AVG(latency_ms)) as avg_ms, SUM(prompt_tokens) as total_prompt, SUM(completion_tokens) as total_completion FROM metrics WHERE timestamp > datetime('now', '-7 days') GROUP BY model_used ORDER BY cost DESC; -- Top users by spend SELECT user_id, COUNT(*) as requests, ROUND(SUM(total_cost), 4) as total_cost, ROUND(AVG(total_cost), 6) as avg_cost_per_request FROM metrics WHERE timestamp > datetime('now', '-30 days') GROUP BY user_id ORDER BY total_cost DESC LIMIT 20; -- Hourly request pattern (for capacity planning) SELECT strftime('%H', timestamp) as hour, COUNT(*) as requests, ROUND(AVG(latency_ms)) as avg_latency FROM metrics WHERE timestamp > datetime('now', '-7 days') GROUP BY hour ORDER BY hour; -- Cost trend (daily, last 30 days) SELECT date(timestamp) as day, ROUND(SUM(total_cost), 4) as cost FROM metrics WHERE timestamp > datetime('now', '-30 days') GROUP BY day ORDER BY day; ``` ## Credit Balance Monitoring ```bash # Current credit status curl -s https://openrouter.ai/api/v1/auth/key \ -H "Authorization: Bearer $OPENROUTER_API_KEY" | jq '{ credits_used: .data.usage, credit_limit: .data.limit, remaining: ((.data.limit // 0) - .data.usage), daily_burn_rate: "check analytics DB" }' ``` ## Weekly Report Generator ```python def weekly_report(conn) -> str: """Generate a text-based weekly analytics report.""" summary = conn.execute(""" SELECT COUNT(*) as requests, ROUND(SUM(total_cost), 2) as cost, ROUND(AVG(latency_ms)) as avg_latency, SUM(prompt_tokens + completion_tokens) as tokens FROM metrics WHERE timestamp > datetime('now', '-7 days') """).fetchone() top_models = conn.execute(""" SELECT model_used, COUNT(*) as n, ROUND(SUM(total_cost), 4) as cost FROM metrics WHERE timestamp > datetime('now', '-7 days') GROUP BY model_used ORDER BY cost DESC LIMIT 5 """).fetchall() report = f""" === OpenRouter Weekly Report === Period: Last 7 days Requests: {summary[0]:,} Total Cost: ${summary[1]:.2f} Avg Latency: {summary[2]:.0f}ms Total Tokens: {summary[3]:,} Avg Cost/Request: ${summary[1]/max(summary[0],1):.4f} Top Models by Cost: """ for model, count, cost in top_models: report += f" {model}: {count} requests, ${cost:.4f}\n" return report ``` ## Output - A JSON-logged metric per request: timestamp, `generation_id`, `model_requested` vs `model_used`, prompt/completion tokens, exact `total_cost`, `latency_ms`, and `user_id` - An `openrouter_analytics.db` sqlite store with an indexed `metrics` table ready for the daily/model/user/hourly queries - A credit-status JSON from the curl + jq snippet: `credits_used`, `credit_limit`, and `remaining` - A weekly text report with request count, total cost, average latency, total tokens, avg cost/request, and the top 5 models by cost ## Examples Track a single call and read the captured metric: ```python response, metric = tracked_completion( [{"role": "user", "content": "Summarize HTTP/2 in one line"}], model="openai/gpt-4o-mini", user_id="alice", max_tokens=60, ) print(metric["model_used"], metric["total_cost"], metric["latency_ms"]) # openai/gpt-4o-mini 8.4e-05 912.3 ``` After a week of stored metrics, `print(weekly_report(conn))` renders the `=== OpenRouter Weekly Report ===` block with totals and top models. More worked examples: `references/examples.md`. ## Error Handling | Error | Cause | Fix | |-------|-------|-----| | Missing cost data | Generation endpoint fetch failed | Retry after 1-2s; log warning | | Metric storage growing too fast | No aggregation or retention | Aggregate to hourly/daily; retain raw data 30 days | | Stale dashboard | Query pipeline lagging | Add data freshness check; alert on >5 min staleness | | Duplicate metrics | Retry caused duplicate generation_ids | Use `INSERT OR IGNORE` with generation_id unique constraint | ## Enterprise Considerations - Query `/api/v1/generation?id=` after each request for exact cost (don't estimate from token counts) - Aggregate raw metrics to hourly/daily summaries after 30 days to manage storage growth - Build automated weekly reports with cost trends, top users, and anomaly detection - Set alerts on daily cost exceeding 2x historical average (anomaly detection) - Track `model_requested` vs `model_used` to monitor fallback frequency - Use the hourly request pattern to capacity-plan API key rate limits ## References - Examples | Errors - Generation API | [Auth API](https://openrouter.ai/docs/api/reference/authentication)