Google BigQuery

​

Step 1: Choose a project and enable the BigQuery API

1. Open the Google Cloud Console and create or select a project.
2. Copy the project ID (not the project name). It is the lowercase identifier shown in IAM & Admin > Settings.
3. In APIs & Services > Library, search for BigQuery API and click Enable.

​

Step 2: Create the dataset

1. Open BigQuery > Explorer, select the project, and choose Create dataset.
2. Choose a dataset ID, such as openrouter.
3. Choose the dataset location carefully. A dataset’s region cannot be changed after creation, so pick a location allowed by your data-residency requirements.

​

Step 3: Create the traces table

​

Step 4: Create a service account

1. Open IAM & Admin > Service Accounts in the project and click Create service account (e.g., openrouter-broadcast).
2. Grant it the BigQuery Data Editor role on the trace dataset (not at the organization or project level): open the dataset’s menu in BigQuery, choose Share > Permissions, add the service account email, and select BigQuery Data Editor. Do not grant BigQuery Job User — this destination uses streaming inserts and does not create query jobs.
3. Open the service account’s Keys tab, choose Add key > Create new key, select JSON, and download the key.

​

Step 5: Enable Broadcast in OpenRouter

​

Step 6: Configure BigQuery

• Google Cloud project ID: The project ID containing the dataset.
• Service-account key JSON: The complete contents of the downloaded JSON key file.
• BigQuery dataset: The dataset ID created above (default: openrouter).
• BigQuery table: The table ID created above (default: openrouter_traces).

​

Step 7: Test and save

​

Step 8: Send a test trace

SELECT
  trace_id,
  span_id,
  timestamp,
  model,
  status,
  total_tokens,
  total_cost
FROM `my-gcp-project.openrouter.openrouter_traces`
ORDER BY timestamp DESC
LIMIT 20;

​

Example queries

​

Cost analysis by model

SELECT
  DATE(timestamp) as day,
  model,
  SUM(total_cost) as total_cost,
  SUM(total_tokens) as total_tokens,
  COUNT(*) as request_count
FROM `my-gcp-project.openrouter.openrouter_traces`
WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND status = 'ok'
GROUP BY day, model
ORDER BY day DESC, total_cost DESC;

​

User activity analysis

SELECT
  user_id,
  COUNT(DISTINCT trace_id) as trace_count,
  COUNT(DISTINCT session_id) as session_count,
  SUM(total_tokens) as total_tokens,
  SUM(total_cost) as total_cost,
  AVG(duration_ms) as avg_duration_ms
FROM `my-gcp-project.openrouter.openrouter_traces`
WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY user_id
ORDER BY total_cost DESC;

​

Error analysis

SELECT
  trace_id,
  timestamp,
  model,
  level,
  finish_reason,
  metadata,
  input,
  output
FROM `my-gcp-project.openrouter.openrouter_traces`
WHERE status = 'error'
  AND timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
ORDER BY timestamp DESC;

​

Provider performance comparison

SELECT
  provider_name,
  model,
  AVG(duration_ms) as avg_duration_ms,
  APPROX_QUANTILES(duration_ms, 100)[OFFSET(50)] as p50_duration_ms,
  APPROX_QUANTILES(duration_ms, 100)[OFFSET(95)] as p95_duration_ms,
  COUNT(*) as request_count
FROM `my-gcp-project.openrouter.openrouter_traces`
WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND status = 'ok'
GROUP BY provider_name, model
HAVING request_count >= 10
ORDER BY avg_duration_ms;

​

Usage by API key

SELECT
  api_key_name,
  COUNT(DISTINCT trace_id) as trace_count,
  SUM(total_cost) as total_cost,
  SUM(prompt_tokens) as prompt_tokens,
  SUM(completion_tokens) as completion_tokens
FROM `my-gcp-project.openrouter.openrouter_traces`
WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY api_key_name
ORDER BY total_cost DESC;

​

Accessing JSON columns

SELECT
  trace_id,
  JSON_VALUE(metadata, '$.custom_field') as custom_value,
  JSON_VALUE(attributes, '$."gen_ai.request.model"') as requested_model
FROM `my-gcp-project.openrouter.openrouter_traces`
WHERE JSON_VALUE(metadata, '$.custom_field') IS NOT NULL;

SELECT
  trace_id,
  JSON_VALUE(input, '$.messages[0].role') as first_message_role,
  JSON_VALUE(input, '$.messages[0].content') as first_message_content
FROM `my-gcp-project.openrouter.openrouter_traces`
LIMIT 10;

​

Schema design

​

Typed columns

• Identifiers: trace_id, user_id, session_id, etc.
• Timestamps: TIMESTAMP columns for time-series analysis
• Model Info: For cost and performance analysis
• Metrics: Tokens and costs for billing

​

JSON columns

• attributes: Full OTEL attribute set
• input/output: Variable message structures
• metadata: User-defined key-values
• model_parameters: Model-specific configurations

​

Custom Metadata

​

Supported Metadata Keys

​

Example

{
  "model": "openai/gpt-4o",
  "messages": [{ "role": "user", "content": "Forecast next quarter revenue..." }],
  "user": "user_12345",
  "session_id": "session_abc",
  "trace": {
    "trace_name": "Revenue Forecasting",
    "generation_name": "Generate Forecast",
    "department": "finance",
    "quarter": "Q2-2026",
    "model_version": "v3"
  }
}

​

Querying Custom Metadata

SELECT
  trace_id,
  JSON_VALUE(metadata, '$.department') as department,
  JSON_VALUE(metadata, '$.quarter') as quarter,
  JSON_VALUE(metadata, '$.model_version') as model_version,
  total_cost,
  total_tokens
FROM `my-gcp-project.openrouter.openrouter_traces`
WHERE JSON_VALUE(metadata, '$.department') IS NOT NULL
ORDER BY timestamp DESC;

​

Additional Context

• The user field maps to the user_id typed column
• The session_id field maps to the session_id typed column
• All custom metadata keys from trace are stored in the metadata JSON column for flexible querying

​

Troubleshooting

• Project not found or permission denied: Confirm that the configured project ID is the project containing the dataset and that the service account belongs to the expected project.
• Table not found: Confirm the dataset and table IDs and that the table was created in the configured dataset location.
• 403 permission denied: Grant the service account BigQuery Data Editor on the dataset. Project-level access may be restricted by organization policy, so verify the dataset permission directly.
• 400 invalid or schema mismatch: Compare the table schema with the DDL from the setup instructions. In particular, timestamps must be TIMESTAMP, nested trace fields must be JSON, and tags must be ARRAY<STRING>.

​

Additional resources

• BigQuery Documentation
• Streaming Data into BigQuery
• JSON Functions in GoogleSQL

​

Privacy Mode