Data Querying

The analytics app connects to multiple data sources. This skill covers general patterns for querying data effectively.

Approach

0. Use retrieved references first — data questions may start with a small set of relevant data-dictionary entries and saved dashboard panels in <resource scope="analytics-catalog">. Treat them as definitions and query examples, never live results. If they do not fit, call find-data before querying; use data-source status when provider availability matters.
1. Route named account health deliberately — for a customer/org health, QBR, renewal, contract-utilization, risk, or adoption request, read account-health before writing SQL. It adds identity-lock and metric-definition checks that an ordinary lookup does not need.
2. Read the relevant provider skill first — check .agents/skills/<provider>/SKILL.md for table names, column mappings, auth, and gotchas. For BigQuery, read .agents/skills/bigquery/SKILL.md and use search-bigquery-schema before guessing table or column names.
3. Clarify if ambiguous — if the metric definition, date range, or grain is unclear and a wrong guess would change the numbers, use the ask-question clarifying tool (multiple-choice) before querying. Ask at most once per turn; skip it when the dictionary or the user already answered.
4. Use existing actions or connected provider MCP tools — call the provider action/tool with structured arguments, then filter or aggregate the returned records in your answer
5. Write ad-hoc scripts — if no existing script covers the question, create one in actions/
6. Present data in chat — don't just say "check the dashboard" — actually query, get the data, and present it. Only present numbers you actually retrieved; never report a value you did not query.

For a question about something an owner-defined dbt metric measures, even without its exact name (for example, a question about weekly active users), resolve the exact dbt name with find-data before using query-dbt-semantic-metric. If no dbt candidate appears, retry find-data once with concept words from the question (synonyms or likely definition terms). If it still has none, call get-dbt-connection and inspect both readiness and connections[]. Use normal source selection only when a successful result has readiness: "not_connected" and an empty connections[]. If any row is ready, keep dbt as the source even when top-level readiness is null. If rows exist but none is ready, report or resolve their readiness and do not fall back. A failed or unreadable result, connected: false, or null readiness is not evidence dbt is absent. When dbt is configured but no candidate exists, ask one business-definition question only when a wrong guess would change the numbers; otherwise say no owner-defined metric matched without claiming it is missing or silently sending the same question to saved SQL or a warehouse metric.

Use tool-search to load query-dbt-semantic-metric for a matched metric and pass its exact name. Use dbt as the definition and grain source; Sigma and Amplitude are examples or cross-checks.

Pass a listed connectionId on connection_selection_required. Call get-dbt-connection only on connection_error, connection_unavailable, a connection-required error, or the no-candidate case above, and report its actual readiness or error, keeping unknown distinct from disconnected. Resume results that carry queryId and nextPage with their metricName, queryId, and nextPage, and no query scope. pending resumes while it stays pending, up to five resumes in one turn; then stop and report it as still running, with its queryId. A truncated success resumes while nextPage is present, stopping after five resumes in one turn or when a resume returns the same nextPage again; then report it as partial. A recoverable error that carries them (connection_error, provider_error, empty_semantic_manifest, permission_denied, or deployment_not_ready) gets one resume before other handling; if the resume fails again, report it as returned, and for connection_error also call get-dbt-connection. Handle the other statuses:

• success: if truncated is true, resume first (above) so the answer covers the full result. If the resume fails or nextPage is absent, answer from the rows you have and say they are partial.
• pending: resume as above. Do not resend the original groupBy, filters, or grain.
• metadata_truncated: call again with its metricName and returned nextPage, and no query scope (no groupBy, timeRange, or allTime), to read the next metadata page, stopping after five resumes in one turn or when a resume returns the same nextPage again. This is separate from the not_found recheck.
• not_found: search find-data for the exact name once, and retry the query only if it returns a different exact name. If that returns no dbt entry, follow the no-candidate rule above. If the retried query returns not_found again, stop and report that no owner-defined metric matched, without claiming it is missing from dbt. A not_found result alone does not confirm a missing definition.
• ambiguous_metric: list the names find-data returns for it and ask which one is meant.
• no_metrics_available: report as returned.
• permission_denied, provider_error: resume once when they carry the cursors (above); otherwise report as returned. permission_denied concerns the workspace dbt token, not this user.
• deployment_not_ready, empty_semantic_manifest: resume once when they carry the cursors (above); otherwise report the state. A warehouse figure is given only when the user asks for one, labeled as a warehouse calculation.
• Any other status: report its returned message.

Name each source you use; never silently fall back to saved SQL or a warehouse query.

Use the metric's returned dimensions and queryableGranularities for a breakdown. Pass exact dimension names in groupBy; grouped MetricFlow queries require an inclusive timeRange no longer than 366 days. Use metric_time and an explicit grain for a time series. The action validates dimensions and grains against dbt metadata, and returns the executed querySpec; preserve it when interpreting rows or continuing with the returned queryId and nextPage. MetricFlow names grouped time columns metric_time__<GRAIN> in the returned rows; preserve that key when presenting the result. When activity could mean Builder product events or Agent-Native app opens, keep the populations separate and ask which one the user intends if the source is ambiguous.

Metric semantics and source status

• Keep event families separate unless the dbt metric or exact saved query defines their union. A UI click and a submitted message are different events even if a dashboard labels both as activity.
• Verify population filters and exclusions from model/metric SQL or tests. Similar aggregate totals do not prove that a model excludes staff, internal users, or any other group.
• A bounded date or partition scan supports conclusions only for the scanned range. State that range and any bytes or query limit; an empty result in those partitions does not establish absence elsewhere.
• For a source inventory, call data-source-status and distinguish configured credentials from a healthy workspace connection. A provider appearing in the tool catalog is not evidence that this workspace has connected it.

For events recorded by the analytics template itself via its /track endpoint, use pnpm action query-agent-native-analytics --sql "SELECT ... FROM analytics_events ...". This includes pageviews, site/app traffic, template usage, app usage, and event counts collected by this analytics app. Pageviews and traffic can also live in GA4, BigQuery/warehouse tables, Mixpanel, PostHog, Amplitude, or another configured provider, so choose the source from the user's wording, connected-source status, existing dashboards, data dictionary, and user/org resources. Ask one concise clarification only when multiple configured sources are plausible and the choice would change the numbers; otherwise name the source you used. Do not use db-query for data-source analysis; db-query is only for internal app tables and will confuse analytics questions. The shipped agent-native-templates-first-party SQL dashboard is the template engagement dashboard for the first-party collector source.

For first-party counts, active-user, and retention questions, prefer the compact tenant-scoped daily event and user-day rollups. Use analytics_events only for a bounded recent drill-down with an explicit date/time range. For the Builder.io production organization after the BigQuery cutover, these logical tables are served by partitioned BigQuery data and views; the source still does not require an end user's separate warehouse connection.

The Builder.io BigQuery path translates a restricted PostgreSQL-compatible SQL subset. Extract an event property with properties::jsonb ->> 'key'; bare JSON operators are not translated. Avoid LATERAL, unnest, generate_series, FILTER (WHERE ...), DISTINCT ON, and ILIKE, which the BigQuery path rejects. Use || explicitly for string concatenation. Rewrite FILTER (WHERE ...) as a conditional aggregate such as SUM(CASE WHEN ... THEN value ELSE 0 END), ILIKE as LOWER(left) LIKE LOWER(right), and DISTINCT ON as ROW_NUMBER() in a CTE with an outer WHERE filter. After an unsupported-syntax error, make one corrected attempt and do not repeat unchanged SQL. Follow the first-party query's typed recovery instruction: schema errors call for find-data or search-bigquery-schema; permission failures stop; billed-byte failures need a narrower scan; only timeout, rate-limit, and temporary backend failures permit one unchanged retry. Use the separate BigQuery action for GoogleSQL-only SQL.

First-party reads exclude test identities (QA/E2E accounts): ingest never stores their events or replays, and the read scope filters user_id (events, replays) and user_key (user-days). Pass includeTestIdentities: true to query-agent-native-analytics only to debug those accounts. Daily event rollups and BigQuery-source panels that query the raw warehouse table directly are clean from ingest onward but are not filtered at read time. The one exception is the legacy @app_events table, which keeps a test identity's $exception rows marked JSON_VALUE(data, '$.test_identity') = 'true'; exclude those from any metric over it.

When the user rules out BigQuery, skip the health check below, which queries BigQuery on a BigQuery-backed scope. Call get-first-party-analytics-backend to see which tables Postgres serves, then pass backend: "postgres" to query-agent-native-analytics. If it fails with first_party_backend_not_postgres, this scope reads those tables only from BigQuery: say so and let the user decide.

Otherwise, before a large or historical first-party query, call get-first-party-analytics-health. Keep Neon as the default while its status is healthy or monitor; a recommend_bigquery result means the app has observed 1M+ events, repeated slow queries, or a timeout/30-second query. Treat that status as a compatibility name for an external-backend recommendation, not as a requirement to use BigQuery. The health result lists the supported options:

• BigQuery for warehouse SQL and complete historical analysis.
• Amplitude for product analytics, funnels, and retention.

A recommend_bigquery result is advice, not a stop: answer the bounded question first. Use the setup link only for a historical or high-volume request with no suitable backend configured, via its returned link or data-source-status --key <provider>, to guide the user through the existing Data Sources walkthrough. Connecting a query backend alone does not move the collector or copy existing Neon events. For the Builder.io production organization, the hidden migrate-first-party-analytics-to-bigquery action is the explicit state machine: prepare dual-write, backfill through bounded, newest-first UTC time shards with per-shard leases, then cut over with confirmation. The worker excludes http.response by default and uses BigQuery insertId values for retry-safe writes. After cutover, /track writes and event queries use BigQuery; public-key metadata, derived exception issues, and session-replay data remain in SQL.

After cutover, an org admin can point charts at a scheduled warehouse events model with configure-first-party-events-model; run check first, which dry-runs as the app and saves nothing. With a model set, a first-party query reads analytics_events from the model only when its WHERE filters event_name to named events (= 'x' or IN (...)) in a top-level AND conjunct, or in a parenthesized OR whose every branch does, because the model has no http.response rows. Other parenthesized conditions may use OR and NOT; an OR, NOT, or CASE at the WHERE's own level keeps the query raw; its result carries eventsSource and sourceGeneration (the model build it read). Unfiltered queries, SELECT *, and queries that name public_key_id or context, read http.response, or read properties other than through JSON_VALUE, JSON_EXTRACT_SCALAR, JSON_VALUE_ARRAY, or JSON_EXTRACT_STRING_ARRAY stay on the raw table, as do alerts and onboarding journeys; eventsSource.modelSkipped says why. A model result trails the newest events by up to one rebuild, so for the last few minutes give the model's age from get-analytics-source-generation (a metadata read, no query) rather than calling the count complete.

Example pageviews query for a local calendar day:

SELECT COUNT(*) AS pageviews
FROM analytics_events
WHERE event_name = 'pageview'
  AND timestamp >= '<start-utc>'
  AND timestamp < '<end-utc>'

Convert the user's requested local date/timezone to UTC before querying. For example, May 1, 2026 in America/New_York is 2026-05-01T04:00:00Z through 2026-05-02T04:00:00Z.

LLM observability events

Agent runs are analytics_events rows with event_name = '$ai_generation'. Useful properties:

• Identity: $ai_trace_id/run_id, $ai_session_id/thread_id, $ai_model/model, $ai_provider/provider.
• Usage: $ai_input_tokens/input_tokens, $ai_output_tokens/output_tokens, cache_read_tokens, cache_write_tokens.
• Cost: $ai_total_cost_usd/cost_usd, cost_cents_x100.
• Time: duration_ms is the full run in milliseconds; $ai_latency is model time in seconds (run minus tool time).
• Tools: tool_calls, successful_tools, failed_tools, tools, tools_truncated. The bounded tools array holds names, relative start times, durations, statuses, and coarse error classes, never args or results; failed runs and interrupted tools stay queryable.
• Delegation: delegated, delegation_protocol, caller_app, delegation_task_id, a2a_task_id, parent_run_id, parent_turn_id. Agent Teams child runs use delegation_protocol = 'agent-team', keep their own run_id, and link to the launching run through parent_run_id.
• Errors: status, $ai_error (terminal code, cause, retryable, and a fixed code-derived message), $ai_error_type. Run failure messages are omitted from telemetry.

Inline Charts In Chat

For an in-chat answer, emit a live /chart embed — never generate-chart. The embed mounts a live SqlChart that re-queries when its source changes, and it doesn't choke on rigid JSON params the way the PNG action does. Reach for generate-chart only when you're building a dashboard artifact that needs a persisted report image.

If generate-chart returns an error in any chat-answering flow, the recovery is to switch to the live embed, not to retry with reformatted params.

How it renders. The core chat markdown renderer turns any fenced block tagged embed into a sandboxed, same-origin iframe. Emit:

```embed
src: /chart?panel=<base64url-encoded panel JSON>
title: Daily pageviews
height: 320
```

Fence keys: src (required, same-origin path), title, and either height (px) or aspect (16/9, 4/3, 1/1, 21/9, 3/2, 2/1; default 16/9). A cross-origin src renders an "Embed blocked" notice instead of a chart.

This fence is the only supported syntax. Never write a bare line like `/chart type=bar title="..." labels=[...] data=[...] color=#...` in chat text — that pattern comes from confusing generate-chart's tool parameters (title, labels, data, type) with markdown; those are arguments to a tool call, not something to type into a chat message. The chat renderer has a best-effort compatibility fallback that tries to recover a chart from that exact shorthand shape, but it is not the contract: it rejects mismatched lengths, negative values, and malformed input (falling back to plain text), and it does not re-query live data the way the embed does. If you catch yourself typing label or data followed by = in a chat reply, stop and build the ```embed fence above instead.

Panel JSON. The /chart route decodes panel into a SqlPanel (app/pages/adhoc/sql-dashboard/types.ts):

• sql — required, non-empty.
• source — required, one of bigquery, ga4, amplitude, first-party, demo, prometheus. program is deliberately not embeddable.
• chartType — required, one of line, area, bar, metric, table, pie. Dashboard-layout types (section, heatmap, callout, extension) are rejected.
• id (defaults "embed"), title (rendered above the chart), width (dashboard-only, ignored here), config (passed through unvalidated — xKey/yKeys, colors, yFormatter, rightYKeys/rightYFormatter for a dual-axis line/area/bar chart, columns, stacked, legend, …).

An unknown source/chartType or blank sql renders an error card, not a chart.

Encoding. JSON-stringify the panel, base64-encode it, then make it URL-safe: + → -, / → _, strip = padding. No further URL-encoding is needed. Keep the SQL short — it rides in a query string; if it's long, save it as a dashboard panel and link to the dashboard instead.

Full details (per-field validation, config keys, a verified round-trip example, and how this differs from generate-chart) are in references/inline-chart-embeds.md — read it with docs-search --slug "skill-data-querying--references-inline-chart-embeds".

Script Patterns

Reusing Existing Actions

# Jira tickets
pnpm action jira-search --jql="summary ~ SSO" --fields=key,summary,status

# HubSpot deals
pnpm action hubspot-deals --query="The Knot" --limit=10 --properties=dealname,amount,dealstage

# HubSpot + Gong account/deal deep dive
pnpm action account-deep-dive --query="The Knot" --days=180 --gongLimit=10 --transcriptLimit=5

# HubSpot contacts or companies
pnpm action hubspot-records --objectType=companies --query=builder.io --properties=name,domain,lifecyclestage

# Gong call evidence (quotes, counts); ask run-gong-native-insight first for "what did they say"
pnpm action gong-calls --company="The Knot" --days=180 --includeTranscripts=true --transcriptLimit=5

The first-class actions above are convenience shortcuts for the common cases, not the limit of what you can do. Many providers (GitHub, Amplitude, PostHog, Mixpanel, Apollo, Common Room, Twitter/X, Notion, Pylon, GA4, plus any endpoint/filter a shortcut can't express) have no bespoke action — reach them through the shared provider API escape-hatch pattern: provider-api-catalog / provider-api-docs to learn the endpoint, then provider-api-request (or providerFetch inside run-code) against the provider's real HTTP API. Ask the provider to do the work first: use its own search or semantic tool, or a server-side filter on a bounded window (for Gong, run-gong-native-insight). Only for exhaustive coverage, exact counts, or an absence claim ("how many", "any/none across all …") stage the raw API response with stageAs/saveToFile and reduce it with query-staged-dataset or run-code, rather than stretching a capped shortcut action or paging by hand to filter locally.

Writing Ad-Hoc Scripts

When no existing script covers the question:

1. Create a new script in actions/ that imports the relevant server lib
2. Run it via pnpm action <name>
3. For one-off queries, you can delete the script after
4. For reusable queries, keep the script

// scripts/my-query.ts
import { runQuery } from "../server/lib/bigquery.js";
import { output } from "./helpers.js";

export default async function main(args: string[]) {
  const results = await runQuery("SELECT ...");
  output(results);
}

Cross-Referencing Sources

For answers that span multiple sources, follow the cross-source-analysis skill: plan which source owns each fact, fetch per source, stitch identities on BOTH a stable id AND email (ids can be reassigned), de-duplicate, and cite per-source provenance.

For complete answers, combine data from multiple sources:

• BigQuery for analytics events, signups, pageviews
• First-party Analytics (query-agent-native-analytics) for events collected through /track
• HubSpot for CRM data — hubspot-records for contacts/companies/tickets/general lookup; hubspot-deals and hubspot-metrics for pipeline and revenue analysis
• Gong for sales-call evidence — ask run-gong-native-insight first for what was said, risks, objections, or next steps; use gong-calls with includeTranscripts=true for quotes, counts, or when native operations are not connected
• Jira for engineering metrics — tickets, sprints
• GitHub for code metrics — PRs, reviews
• Agent-Native Analytics Monitoring -> Errors for first-party captured client/server issues; use list-error-issues and get-error-issue for grouped details
• Sentry for external error rates and trends when connected
• Grafana for infrastructure metrics

After Completing an Analysis — Capture New Knowledge

When you complete an analysis and discover:

• A new confirmed metric definition or how a field is actually calculated
• A provider gotcha (wrong column name, API quirk, unexpected behavior)
• A schema discovery (table exists but wasn't in the dictionary, a column name differs)
• An identity-stitching rule (how to match users across two specific sources)

Analytics automatically captures explicit user corrections and metric definitions the user confirms after the thread has been idle. State corrections plainly. Before asking for confirmation, restate the complete proposed metric definition in plain language, including its key conditions and time window or grain when applicable; a bare “yes” to a metric-name-only question is not confirmation. These automatic captures stay private to the user. Do not call save-memory again for those same items. Before writing anything to shared LEARNINGS.md or organization memory, check its audience and ask the user for approval of that shared write. Keep setup-specific findings in personal memory or the current analysis.

Use save-memory for other verified, durable personal Analytics knowledge, with a short actionable description; read the existing entry first when updating it. Do not save guesses, one-off result values, raw queries, credentials, or personal or customer-identifying details such as names, contact information, street/billing/mailing addresses, or personal identifiers. If the finding is uncertain or only applies to the current analysis, leave it in the answer instead of creating a memory.

For entries not suitable for personal memory, use the project LEARNINGS.md only after the user approves that shared write and when it contains genuinely reusable, non-sensitive guidance:

resources(action: "read", path: "LEARNINGS.md")  -- read first to merge
resources(action: "write", path: "LEARNINGS.md", content: "<updated content>")

Keep each entry short and actionable: what to do, what not to do, and why. This is the learnings flywheel — discoveries persist across sessions and improve future analyses.

Important Notes

• Always query real data — never guess or approximate. Only present numbers you actually retrieved; do not claim a figure you did not query.
• State confidence explicitly instead of refusing. Answer shape and citing the dashboard are owned by AGENTS.md "Data questions". When no live query ran this turn, label every figure "Unverified" instead of asserting it or falling back to a connect-a-source dead end. Never refuse a question just because no certified source exists — try the catalog, then a bounded query, before declining.
• Answer questions directly in chat with tables, inline charts, and findings. Never deflect to "check the dashboard" — actually run the query and present the answer.
• Evidence fields (source, window, filters, sample size, join method, caveats) go in the optional "How it's computed" note defined in AGENTS.md, not the opening line.
• Data-source status, data-dictionary reads, dashboard dry-runs, update-dashboard, and generate-chart are not data queries. For dashboard artifacts, run at least one provider query action and preserve the result evidence in the final answer or dashboard config/description.
• Use action arguments such as query, objectType, properties, owner, limit, or provider-specific filters to narrow output; if an action returns a broad batch, filter it in your analysis and cite the records used.
• Update the relevant .agents/skills/<provider>/SKILL.md when you discover new patterns.
• For BigQuery queries, check .agents/skills/bigquery/SKILL.md first; if the data dictionary does not contain the exact table/columns, call search-bigquery-schema.