Example queries

These queries show common analysis patterns you can copy and adapt. For clauses, data sources, and field access, see SQL query structure. For the full function and operator catalog, see Functions and operators.

Track token usage

This query helps you monitor token consumption across your application.

SELECT
  day(created) AS time,
  sum(metrics.total_tokens) AS total_tokens,
  sum(metrics.prompt_tokens) AS input_tokens,
  sum(metrics.completion_tokens) AS output_tokens
FROM project_logs('<YOUR_PROJECT_ID>')
WHERE created > '<ISO_8601_TIME>'
GROUP BY 1
ORDER BY time ASC

-- Alternative using date_trunc function
SELECT
  date_trunc('day', created) AS time,
  sum(metrics.total_tokens) AS total_tokens,
  sum(metrics.prompt_tokens) AS input_tokens,
  sum(metrics.completion_tokens) AS output_tokens
FROM project_logs('<YOUR_PROJECT_ID>')
WHERE created > '<ISO_8601_TIME>'
GROUP BY date_trunc('day', created)
ORDER BY time ASC

-- Custom interval multiples for finer-grained buckets
SELECT
  date_trunc('15 minutes', created) AS time,
  sum(metrics.total_tokens) AS total_tokens,
  sum(metrics.prompt_tokens) AS input_tokens,
  sum(metrics.completion_tokens) AS output_tokens
FROM project_logs('<YOUR_PROJECT_ID>')
WHERE created > '<ISO_8601_TIME>'
GROUP BY date_trunc('15 minutes', created)
ORDER BY time ASC

The response shows daily token usage:

{
  "time": "2024-11-09T00:00:00Z",
  "total_tokens": 100000,
  "input_tokens": 50000,
  "output_tokens": 50000
}

Analyze cost breakdown

Use estimated_cost_component() to break total cost into its components. This is useful for understanding how much of your spend comes from cached reads vs. uncached prompt tokens vs. completions.

-- Cost breakdown by component, model, and day
SELECT
  metadata.model AS model,
  day(created) AS date,
  sum(estimated_cost_component('promptUncachedTokensCost'))      AS uncached_prompt_cost,
  sum(estimated_cost_component('promptCachedTokensCost'))        AS cached_read_cost,
  sum(estimated_cost_component('promptCacheCreationTokensCost')) AS cache_write_cost,
  sum(estimated_cost_component('completionTokensCost'))          AS completion_cost,
  sum(estimated_cost()) AS total_cost
FROM project_logs('<PROJECT_ID>')
WHERE created > now() - interval 7 day
  AND span_attributes.type = 'llm'
GROUP BY metadata.model, day(created)
ORDER BY date DESC, total_cost DESC

For models with two-tier cache creation (e.g. Anthropic), replace promptCacheCreationTokensCost with promptCacheCreation5mTokensCost and promptCacheCreation1hTokensCost to split by tier.

Use estimated_cost_breakdown() when you want all components at once as a JSON object, for example to export or inspect the full breakdown per span.

SELECT id, metadata.model, estimated_cost_breakdown() AS cost_breakdown
FROM project_logs('<PROJECT_ID>')
WHERE created > now() - interval 1 day
  AND span_attributes.type = 'llm'
LIMIT 100

Monitor model quality

Track model performance across different versions and configurations.

-- Compare factuality scores across models
SELECT
  metadata.model AS model,
  day(created) AS date,
  avg(scores.Factuality) AS avg_factuality,
  percentile(scores.Factuality, 0.05) AS p05_factuality,
  percentile(scores.Factuality, 0.95) AS p95_factuality,
  count(1) AS total_calls
FROM project_logs('<PROJECT_ID>')
WHERE created > '2024-01-01'
GROUP BY 1, 2
ORDER BY date DESC, model ASC

-- Find potentially problematic responses
SELECT *
FROM project_logs('<PROJECT_ID>')
WHERE scores.Factuality < 0.5
  AND metadata.is_production = true
  AND created > now() - interval 1 day
ORDER BY scores.Factuality ASC
LIMIT 100

-- Compare performance across specific models
SELECT *
FROM project_logs('<PROJECT_ID>')
WHERE metadata.model IN ('gpt-4', 'gpt-4-turbo', 'claude-3-opus')
  AND scores.Factuality IS NOT NULL
  AND created > now() - interval 7 day
ORDER BY scores.Factuality DESC
LIMIT 500

Analyze errors

Identify and investigate errors in your application.

-- Error rate by model
SELECT
  metadata.model AS model,
  hour(created) AS hour,
  count(1) AS total,
  count(error) AS errors,
  count(error) / count(1) AS error_rate
FROM project_logs('<PROJECT_ID>')
WHERE created > now() - interval 1 day
GROUP BY 1, 2
ORDER BY error_rate DESC

-- Find common error patterns
SELECT
  error.type AS error_type,
  metadata.model AS model,
  count(1) AS error_count,
  avg(metrics.latency) AS avg_latency
FROM project_logs('<PROJECT_ID>')
WHERE error IS NOT NULL
  AND created > now() - interval 7 day
GROUP BY 1, 2
ORDER BY error_count DESC

-- Exclude known error types from analysis
SELECT *
FROM project_logs('<PROJECT_ID>')
WHERE error IS NOT NULL
  AND error.type NOT IN ('rate_limit', 'timeout', 'network_error')
  AND metadata.is_production = true
  AND created > now() - interval 1 day
ORDER BY created DESC
LIMIT 100

Analyze latency

Monitor and optimize response times.

-- Track p95 latency by endpoint
SELECT
  metadata.endpoint AS endpoint,
  hour(created) AS hour,
  percentile(metrics.latency, 0.95) AS p95_latency,
  percentile(metrics.latency, 0.50) AS median_latency,
  count(1) AS requests
FROM project_logs('<PROJECT_ID>')
WHERE created > now() - interval 1 day
GROUP BY 1, 2
ORDER BY hour DESC, p95_latency DESC

-- Find slow requests
SELECT
  metadata.endpoint,
  metrics.latency,
  metrics.tokens,
  input,
  created
FROM project_logs('<PROJECT_ID>')
WHERE metrics.latency > 5000  -- Requests over 5 seconds
  AND created > now() - interval 1 hour
ORDER BY metrics.latency DESC
LIMIT 20

Analyze prompts

Analyze prompt effectiveness and patterns.

-- Track prompt token efficiency
SELECT
  metadata.prompt_template AS template,
  day(created) AS date,
  avg(metrics.prompt_tokens) AS avg_prompt_tokens,
  avg(metrics.completion_tokens) AS avg_completion_tokens,
  avg(metrics.completion_tokens) / avg(metrics.prompt_tokens) AS token_efficiency,
  avg(scores.Factuality) AS avg_factuality
FROM project_logs('<PROJECT_ID>')
WHERE created > now() - interval 7 day
GROUP BY 1, 2
ORDER BY date DESC, token_efficiency DESC

-- Find similar prompts
SELECT *
FROM project_logs('<PROJECT_ID>')
WHERE input MATCH 'explain the concept of recursion'
  AND scores.Factuality > 0.8
  AND created > now() - interval 7 day
ORDER BY created DESC
LIMIT 10

Analyze based on tags

Use tags to track and analyze specific behaviors. In SQL mode, use IN and NOT IN to filter tags by exact membership:

-- Track issue resolution: tagged needs-review but not resolved
SELECT created, tags, metadata.model, scores.Factuality, output
FROM project_logs('<PROJECT_ID>')
WHERE tags IN ('needs-review')
  AND tags NOT IN ('resolved')
  AND created > now() - interval 1 day
ORDER BY scores.Factuality ASC

tags IN ('needs-review') matches rows where needs-review is one of the tags, and tags NOT IN ('resolved') matches rows without the resolved tag. NOT IN excludes rows where tags is null or absent.

MATCH (tags MATCH 'feedback') is a fuzzy approximation. It tokenizes text, so it also matches tags that contain the term as a token, such as feedback-given.

INCLUDES is not supported in SQL mode. The examples below use BTQL syntax, which supports INCLUDES for exact array membership:

-- Monitor feedback patterns
select: tags[0] AS primary_tag, metadata.model AS model, count(1) AS feedback_count, avg(scores.Factuality > 0.8 ? 1 : 0) AS high_quality_rate
| from: project_logs('<PROJECT_ID>')
| filter: tags INCLUDES 'feedback' AND created > now() - interval 30 day
| dimensions: 1, 2
| sort: feedback_count DESC

-- Track issue resolution
select: created, tags, metadata.model, scores.Factuality, output
| from: project_logs('<PROJECT_ID>')
| filter: tags INCLUDES 'needs-review' AND NOT tags INCLUDES 'resolved' AND created > now() - interval 1 day
| sort: scores.Factuality ASC

Analyze based on tags and scores

A common pattern is filtering traces by both tags and scores when automated scorers apply scores at the span level while tags exist on root spans. Use separate ANY_SPAN() clauses to match traces where any span contains the tag AND any span contains the score. Each ANY_SPAN() clause evaluates independently across all spans in a trace. The conditions don’t need to match on the same span. The first ANY_SPAN() finds traces where at least one span has the tag, while the second finds traces where at least one span has the score.

-- Find traces with a specific tag and score
select: *
| from: project_logs('<PROJECT_ID>') traces
| filter: ANY_SPAN(tags INCLUDES 'content-change') AND ANY_SPAN(scores."email sent" IS NOT NULL) AND created > now() - interval 7 day
| limit: 100

For score names with spaces or special characters, wrap the name in double quotes:

-- Filter by tag and score with spaces in name
select: *
| from: project_logs('<PROJECT_ID>') summary
| filter: ANY_SPAN(tags INCLUDES 'production') AND ANY_SPAN(scores."Email Draft vs Sent Similarity" IS NOT NULL) AND created > now() - interval 7 day
| sort: created DESC
| limit: 50

Analyze traces with span filters

Use single span filters with aggregations to analyze traces based on span-level conditions. This is useful for understanding patterns across complex, multi-step operations.

-- Find traces with both errors and LLM spans, grouped by error type
SELECT
  error.type AS error_type,
  metadata.model AS model,
  count_distinct(root_span_id) AS trace_count,
  sum(estimated_cost()) / count_distinct(root_span_id) AS avg_cost_per_trace
FROM project_logs('<PROJECT_ID>', shape => 'traces')
WHERE ANY_SPAN(error IS NOT NULL)
  AND ANY_SPAN(span_attributes.type = 'llm')
  AND created > now() - interval 7 day
GROUP BY error.type, metadata.model
ORDER BY trace_count DESC

-- Analyze average cost per trace by day for traces with errors and LLM calls
SELECT
  day(created) AS date,
  sum(estimated_cost()) / count_distinct(root_span_id) AS avg_cost_per_trace,
  count_distinct(root_span_id) AS trace_count
FROM project_logs('<PROJECT_ID>', shape => 'traces')
WHERE ANY_SPAN(error IS NOT NULL)
  AND ANY_SPAN(span_attributes.type = 'llm')
  AND created > now() - interval 7 day
GROUP BY day(created)
ORDER BY date DESC

Use FILTER_SPANS() to analyze only the spans that match specific criteria:

-- Analyze score distribution for only scorer spans
SELECT
  span_attributes.name AS scorer_name,
  avg(scores.Factuality) AS avg_score,
  count(1) AS span_count
FROM project_logs('<PROJECT_ID>', shape => 'traces')
WHERE FILTER_SPANS(span_attributes.type = 'score')
  AND created > now() - interval 7 day
GROUP BY span_attributes.name
ORDER BY avg_score DESC

Extract data from JSON strings

Use json_extract to pull values out of a field whose value is a JSON-encoded string (rather than a native SQL object). The second argument is a JSONPath expression that supports nested object keys, array indexing (including negative indices), and an optional $ root prefix.

-- Extract a top-level field
SELECT
  id,
  json_extract(metadata.config, 'api_key') AS api_key
FROM project_logs('my-project-id')
WHERE created > now() - interval 7 day

-- Traverse nested keys and array elements
SELECT
  id,
  json_extract(metadata.settings, 'user.preferences.theme') AS theme,
  json_extract(output, 'choices[0].message.content') AS first_message
FROM project_logs('my-project-id')
WHERE created > now() - interval 7 day

-- Extract and filter
SELECT *
FROM project_logs('my-project-id')
WHERE json_extract(metadata.config, 'environment') = 'production'
  AND json_extract(metadata.config, 'version') > 2.0
  AND created > now() - interval 7 day

json_extract returns null for invalid JSON or missing keys rather than raising an error, making it safe to use in filters and aggregations. Because the path is a JSONPath expression, dots and brackets are interpreted as traversal: a.b descends into key a then key b, and a[0] indexes into an array. A JSON key whose name literally contains a dot or bracket can’t be addressed this way.

Query by classifications

Classifications are categorical labels generated by topics automations (for example, classifications.Task[0].label). Each classification object has these nested fields, accessed as classifications.<name>[<i>].<field>:

classifications.<name>[<i>].label: the category label.
classifications.<name>[<i>].metadata.distance: distance metric for the classification.
classifications.<name>[<i>].source: source of the classification.

Filter and analyze logs by topic classifications to understand patterns in your data.

SELECT
  classifications.Task[0].label as topic,
  count(*) as count
FROM project_logs('my-project-id')
WHERE classifications.Task IS NOT NULL
  AND created > now() - interval 7 day
GROUP BY topic
ORDER BY count DESC

To analyze all classifications in an array rather than just the first element, use UNPIVOT to expand the array. See Array of objects unpivot for examples.

Filter by specific topic and distance threshold:

SELECT id, facets.task, classifications.Task[0].label as topic
FROM project_logs('my-project-id')
WHERE classifications.Task[0].label = 'Creating datasets'
  AND classifications.Task[0].metadata.distance < 0.5
  AND created > now() - interval 7 day
ORDER BY classifications.Task[0].metadata.distance ASC
LIMIT 50

Facets are AI-extracted attributes that summarize logs (e.g., facets.task, facets.sentiment). They’re generated by topics automations. Query logs by facet values to identify patterns and issues.

SELECT id, created, facets.task, facets.sentiment
FROM project_logs('my-project-id')
WHERE facets.sentiment IN ('NEGATIVE', 'MIXED')
  AND created > now() - interval 7 day
ORDER BY created DESC
LIMIT 100

Analyze relationships between facets and classifications to gain deeper insights.

SELECT
  facets.sentiment,
  classifications.Task[0].label as topic,
  count(*) as count,
  avg(metrics.total_tokens) as avg_tokens
FROM project_logs('my-project-id', shape => 'traces')
WHERE facets.sentiment IS NOT NULL
  AND classifications.Task IS NOT NULL
  AND created > now() - interval 7 day
GROUP BY facets.sentiment, topic
ORDER BY count DESC
LIMIT 20

Analyze topic distribution with distance metrics:

SELECT
  classifications.Task[0].label as topic,
  count(*) as occurrences,
  avg(classifications.Task[0].metadata.distance) as avg_distance
FROM project_logs('my-project-id', shape => 'traces')
WHERE classifications.Task IS NOT NULL
  AND created > now() - interval 7 day
GROUP BY topic
HAVING count(*) > 10
ORDER BY occurrences DESC

Next steps

Learn how to structure queries in the SQL reference.
Look up syntax in Functions and operators.
Optimize slow or incorrect queries with SQL best practices.

API

CLI

SQL

Track token usage

Analyze cost breakdown

Monitor model quality

Analyze errors

Analyze latency

Analyze prompts

Analyze based on tags

Analyze based on tags and scores

Analyze traces with span filters

Extract data from JSON strings

Query by classifications

Analyze facet distributions

Combine facets and classifications

Next steps

​Track token usage

​Analyze cost breakdown

​Monitor model quality

​Analyze errors

​Analyze latency

​Analyze prompts

​Analyze based on tags

​Analyze based on tags and scores

​Analyze traces with span filters

​Extract data from JSON strings

​Query by classifications

​Analyze facet distributions

​Combine facets and classifications

​Next steps

Track token usage

Analyze cost breakdown

Monitor model quality

Analyze errors

Analyze latency

Analyze prompts

Analyze based on tags

Analyze based on tags and scores

Analyze traces with span filters

Extract data from JSON strings

Query by classifications

Analyze facet distributions

Combine facets and classifications

Next steps