insights/services/mcp/shared/guidelines.md

Querying data in Insights

Use the insights:execute-sql MCP tool to execute InsightsQL queries. InsightsQL is Insights's variant of SQL that supports most of Datastore SQL. We use terms "InsightsQL" and "SQL" interchangeably. More info is available in the querying-insights-data skill.

Do not assume that data exists. Use the SQL tool proactively to find the right data.

Search types

Proactively use different search types depending on a task:

• Grep-like (regex search) with match(), LIKE, ILIKE, position, multiMatch, etc.
• Full-text search with hasToken, hasTokenCaseInsensitive, etc. Make sure you pass string constants to hasToken* functions.
• Dumping results to a file and using bash commands to process potentially large outputs.

Substring search on events-table strings is a full scan: LIKE '%term%', ILIKE '%term%', and position() read the column for every row in the time range, and a leading % makes indexes useless. Before fuzzy-matching a property value, try read-data-schema (event_property_values) to find common exact values. If the requested value is not returned, or the user needs true contains semantics, keep the timestamp window tight, filter event first, and use the substring predicate.

Data Groups

Insights has two distinct groups of data you can query:

1. System Data (Insights-Created Data)

Data created directly in Insights by users - metadata about Insights setup.

All these tables are prefixed with system.. The most-used entities are system.insights, system.dashboards, system.cohorts, system.feature_flags, system.experiments, system.surveys, system.actions, and system.notebooks — list the full, current set (it drifts as products are added) via information_schema, covered in Schema discovery below.

Example - List insights:

SELECT id, name, short_id FROM system.insights WHERE NOT deleted LIMIT 10

Example - Count insight variables:

SELECT count() AS total FROM system.insight_variables

All entities are scoped by a team by default. You cannot access data of another team unless you switch a team.

2. Captured Data (Analytics Data)

Data collected via the Insights SDK - used for analytics.

Table | Description events | Recorded events from SDKs persons | Individuals captured by the SDK. "Person" = "user" groups | Groups of individuals (organizations, companies, etc.) sessions | Session data captured by the SDK Data warehouse tables | Connected external data sources and custom views

Discover the columns and relationships of these tables with information_schema, covered in Schema discovery below.

Key concepts:

• Events: Standardized events/properties start with $ (e.g., $pageview). Custom ones start with any other character.
• Properties: Key-value metadata accessed via properties.foo.bar or properties.foo['bar'] for special characters
• Person properties: Access via events.person.properties.foo or persons.properties.foo
• Person property modes: person.properties.* behavior depends on the project's person-on-events setting. Check the project metadata to determine if values are event-time (value at ingestion) or query-time (current value). See Person property modes for details.
• Unique users: Use events.person_id for counting unique users

Example - Weekly active users:

SELECT toStartOfWeek(timestamp) AS week, count(DISTINCT person_id) AS users
FROM events
WHERE event = '$pageview'
  AND timestamp > now() - INTERVAL 8 WEEK
GROUP BY week
ORDER BY week DESC

3. Document Embeddings (Semantic Search)

The document_embeddings table stores text content with vector embeddings, partitioned by model_name. To discover what kinds of data are available:

SELECT product, document_type, count() as cnt
FROM document_embeddings
WHERE model_name = 'text-embedding-3-small-1536'
  AND timestamp >= now() - INTERVAL 1 MONTH
GROUP BY product, document_type
ORDER BY cnt DESC

Run separately for each model. Available models: 'text-embedding-3-small-1536', 'text-embedding-3-large-3072'. You MUST filter on exactly one model_name per query — it routes to the correct underlying Datastore table. IN clauses and cross-model queries will fail.

Use embedText(text, model_name) and cosineDistance() for semantic search. See the signals skill for detailed query patterns around the signals product specifically, including required deduplication and metadata extraction.

If higher-priority project guidance requires a semantic lookup before data discovery, follow it first. Otherwise use the schema workflow below.

Schema discovery (information_schema)

Don't guess table or column names — they differ per entity and drift over time. Discover the live schema for every data group above (system, captured, and data-warehouse tables) by querying system.information_schema via execute-sql. It exposes four self-describing virtual tables — every column below is selectable:

• tables — table_catalog, table_schema, table_name, table_type, description, row_count. table_type is one of system, data_warehouse, view, insights (built-in analytics tables like events / persons), or information_schema.
• columns — table_schema, table_name, column_name, ordinal_position, data_type, is_nullable, is_array, field_kind, description.
• relationships — source_table, source_column, target_table, target_column, relationship_kind, via.
• data_types — type_name, description.

It describes itself, so the full column set is always discoverable: SELECT column_name, data_type FROM system.information_schema.columns WHERE table_name = 'system.information_schema.columns'.

List tables — filter table_type to target a group (system, data_warehouse, view, insights):

SELECT table_name, description
FROM system.information_schema.tables
WHERE table_type = 'system'
ORDER BY table_name

Find a table by what its docs say — names are often opaque (especially data-warehouse tables), so search the description text instead of guessing names. The documentation lives in system.information_schema.tables.description (the catalog) — not on the system.data_warehouse_tables entity, which only holds connection metadata:

SELECT table_name, description
FROM system.information_schema.tables
WHERE table_type = 'data_warehouse' AND description ILIKE '%canonical mrr%'

Column docs are searchable the same way via information_schema.columns.description. Prefer an ILIKE filter over dumping the whole catalog and scanning it yourself.

Inspect a table's columns:

SELECT column_name, data_type, is_nullable, description
FROM system.information_schema.columns
WHERE table_name = 'events'

This works for system.* entity tables too — query them by full name, e.g. WHERE table_name = 'system.insights'. Their column sets differ per entity, so confirm columns before projecting them.

Discover how a table joins to others:

SELECT source_table, source_column, target_table, target_column, relationship_kind
FROM system.information_schema.relationships
WHERE source_table = 'events'

relationship_kind is either lazy_join (a foreign-key-style join to a related table, e.g. events.person_id to persons) or field_traverser (an alias that hops to another field on the same row); via names the resolver when one applies.

Interpret a data type — data_types describes the possible values of columns.data_type (String, Integer, Float, Decimal, Boolean, Date, DateTime, UUID, JSON, Array, Struct, Expression, VirtualTable, Unknown):

SELECT type_name, description FROM system.information_schema.data_types

information_schema covers table and column structure. To verify which events, properties, and property values actually exist in captured data, use read-data-schema (see Schema verification below).

Querying guidelines

Schema verification

Before writing analytical queries, always verify that:

• The required event names or actions exist.
• Properties and property values of events, persons, sessions, and groups data exist.

Follow this workflow:

1. Fetch the tool schema - Use insights:read-data-schema to get the latest schema from the MCP.
2. Verify data exist - Use insights:read-data-schema with different data types to check if the data you need is captured
3. Only then write the query - Once you've confirmed the data exists, write and execute your analytical query

User: how many times the tool search was used? Assistant: 1. First, verify the events exist: - Call `insights:read-data-schema` with `kind: events` - Look for events/actions matching the request 2. If required events don't exist, inform the user immediately instead of running queries that will return empty results 3. If events exist, like "tool executed", verify the properties: - Call `insights:read-data-schema` with `kind: event_properties` and `event_name: tool executed` - Look for properties indicating a tool 4. Check other events/actions or return if required properties don't exist 5. If events exist, like "tool_name", verify the property values: - Call `insights:read-data-schema` with `kind: event_property_values`, `event_name: tool executed`, and `property_name: tool_name` - Follow the pattern from the sample or dig deeper into existing properties with SQL queries. 6. Only then write and execute the analytical SQL query Assistant should verify the data schema to write a correct SQL query, as the data schema varies over time. User: how many users have chatted with the AI assistant from the US? Assistant: I'll help you find the number of users who have chatted with the AI assistant from the US. Let me create a todo list to track this implementation. 1. Find the relevant events to "chatted with the AI assistant" 2. Find the relevant properties of the events and persons to narrow down data to users from specific country 3. Retrieve the sample property values for found properties 4. Create the insight schema by using the data retrieved in the previous steps 5. Generate the insight 6. Analyze retrieved data The task list helps the assistant to stay on track.

This prevents wasted API calls and gives users immediate feedback when the data they're looking for doesn't exist.

Progressive exploration

For unfamiliar or potentially large datasets, probe cheaply before running the expensive aggregation. Widen only if the cheap step looks reasonable:

1. Count first — SELECT count() FROM events WHERE timestamp >= now() - INTERVAL 1 DAY AND event = 'foo'. Confirms the data exists and gives a sense of volume.
2. Small sample — inspect a handful of rows (LIMIT 10) to verify property shapes and values match expectations.
3. Full query — run the real aggregation with a time range and LIMIT, having confirmed it won't scan needlessly or return empty.

This is faster than discovering an empty result or a mis-shaped property after the full aggregation, and it costs less.

Skipping index

You should use the skipping index signature to write optimized analytical queries.

Time ranges

All analytical queries and subqueries must always have time ranges set for supported tables (events). If the user doesn't state it, assume default time range based on the data volume, like a day, week, or month.

The bound must be a WHERE predicate on timestamp. A time condition that appears only inside an aggregate argument, like countIf(event = 'x' AND timestamp > now() - INTERVAL 1 DAY), filters nothing: every historical row is still read. Put the outer window in WHERE and keep only the split inside the aggregate.

Point lookups need a time bound too. Filtering on a session id, trace id, distinct_id, or a property value without a timestamp bound scans the team's entire history, because those filters don't align with the table's date-first sort key. Derive the window from context (the session's day, the incident's date), or start with a recent window and widen only if the result is empty.

How you should use time ranges

User: Find events from returning browsers - browsers that appeared both yesterday and today Assistant: ```sql SELECT event FROM events WHERE timestamp >= now() - INTERVAL 1 DAY and properties['$browser'] IN (SELECT properties['$browser'] FROM events WHERE timestamp >= now() - INTERVAL 2 DAY and timestamp < now() - INTERVAL 1 DAY) ```

How you should NOT write queries

User: List 10 events with SQL Assistant: ```sql SELECT event, timestamp, distinct_id, properties FROM events ORDER BY timestamp DESC LIMIT 10 ```

JOINs

General guidelines

Keep in mind that the right expression is loaded in memory when joining data in Datastore, so the joining query or table must always fit in memory. Common strategies:

• Analytical functions and combinators.
• Subqueries as a source or filter.
• Arrays (arrayMap, arrayJoin) and ARRAY JOIN.

A subquery used as a join/correlation source must pre-filter and, where possible, pre-aggregate — push the time range, WHERE, and any GROUP BY inside it so the right side stays small in memory. Wrapping a full table in a subquery without narrowing it gains nothing. When you only need a single match per row (enrichment lookups, e.g. attaching one attribute from system data), use LEFT ANY JOIN — it stops at the first match, using less memory and running faster than a regular join.

System data

You are allowed joining system data. Insights are the most used entity, so keep it on the left.

Analytical data

Prefer using analytical functions and subqueries for joins. Do not use raw joins on the events table.

Scan events once per question where you can: conditional aggregates (countIf, sumIf, uniqIf, argMax) or a window function over one scan replace a self-join, repeated subqueries over the same rows, and UNIONs of the same range. CTEs are inlined, not materialized: a CTE referenced twice executes twice. Subqueries that correlate different events (like the example below) are fine.

How you should join data

User: Find ai traces with feedback Assistant: ```sql SELECT g.properties.$ai_trace_id as trace_id FROM events AS g WHERE timestamp >= now() - INTERVAL 1 WEEK AND g.event = '$ai_generation' AND trace_id IN (SELECT properties.$ai_trace_id FROM events WHERE event = '$ai_feedback' AND timestamp >= now() - INTERVAL 1 WEEK) ``` A subquery is used instead of a JOIN clause. Both queries have the timestamp filters.

How you should NOT join data

User: Find ai traces with feedback Assistant: ```sql SELECT g.properties.$ai_trace_id FROM events AS g INNER JOIN (SELECT properties.$ai_trace_id as trace_id FROM events WHERE event = '$ai_feedback') AS f ON g.properties.$ai_trace_id = f.trace_id WHERE g.event = '$ai_generation' ``` Join is not necessary here. The assistant could've used a subquery.

Other constraints

• Your query results are capped at 100 rows by default. You can request up to 500 rows using a LIMIT clause. If you need more data, paginate using LIMIT and OFFSET in subsequent queries.
• You should cherry-pick properties of events, persons, or groups, so we don't get OOMs. Never select the full properties object (e.g., SELECT properties FROM events) and dump it into the conversation output. Instead, select only the specific properties you need (e.g., properties.$browser, properties.$os). If you must inspect the full properties object, dump the query results to a file and use bash commands to explore it.
• When query results contain large JSON blobs (e.g., AI trace inputs/outputs, full property objects), always dump them to a file rather than outputting them directly. Use bash commands to process the file.

InsightsQL Differences from Standard SQL

Property access

-- Simple keys
properties.foo.bar

-- Keys with special characters
properties.foo['bar-baz']

Unsupported/changed functions

Don't use | Use instead toFloat64OrNull(), toFloat64() | toFloat() toDateOrNull(timestamp) | toDate(timestamp) LAG(), LEAD() | lagInFrame(), leadInFrame() with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING count(*) | count() cardinality(bitmap) | bitmapCardinality(bitmap) split() | splitByChar(), splitByString()

JOIN constraints

Relational operators (>, <, >=, <=) are forbidden in JOIN clauses. Use CROSS JOIN with WHERE:

-- Wrong
JOIN persons p ON e.person_id = p.id AND e.timestamp > p.created_at

-- Correct
CROSS JOIN persons p WHERE e.person_id = p.id AND e.timestamp > p.created_at

Syntax extensions and InsightsQL functions

Find the reference for Sparkline, SemVer, Session replays, Actions, Translation, HTML tags and links, Text effects, and more.

Other rules

• WHERE clause must come after all JOINs
• No semicolons at end of queries
• toStartOfWeek(timestamp, 1) for Monday start (numeric, not string)
• Always handle nulls before array functions: splitByChar(',', coalesce(field, ''))
• Performance: always filter events by timestamp
• Correctness: count unique users with uniq(person_id) on events, never uniq(distinct_id) (one person has many distinct_ids, so distinct_id overcounts users)
• Memory: avoid GROUP BY on unbounded high-cardinality expressions (raw URLs, ids, free text) over wide windows: the aggregation holds every distinct value in memory regardless of LIMIT; normalize the value (strip ids from paths) or narrow the window

SQL Variables

Review the reference for SQL variables and dashboard filters.

Available InsightsQL functions

Verify what functions are available using the reference list with suitable bash commands.

Examples

Weekly active users with activation event:

SELECT week_of, countIf(weekly_event_count >= 3)
FROM (
   SELECT person.id AS person_id, toStartOfWeek(timestamp) AS week_of, count() AS weekly_event_count
   FROM events
   WHERE event = 'activation_event'
     AND properties.$current_url = 'https://example.com/foo/'
     AND toStartOfWeek(now()) - INTERVAL 8 WEEK <= timestamp
     AND timestamp < toStartOfWeek(now())
   GROUP BY person.id, week_of
)
GROUP BY week_of
ORDER BY week_of DESC

Find cohorts by name:

SELECT id, name, count FROM system.cohorts WHERE name ILIKE '%paying%' AND NOT deleted

List feature flags:

SELECT key, name, rollout_percentage
FROM system.feature_flags
WHERE NOT deleted
ORDER BY created_at DESC
LIMIT 20