data-querying

# 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 naming a dbt metric, use `tool-search` to load `query-dbt-semantic-metric` and pass its exact dbt name. Use dbt as the metric- definition and grain source; Sigma and Amplitude are examples or cross-checks. If no dbt connection is available, use the source-selection path below. If the metric is not defined or its deployment is not ready, report that state and use a verified warehouse path only when it answers the same definition. 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 (...)`, ANDed, no OR or NOT), because the model has no `http.response` rows; 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: ```sql 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: ````markdown ```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 ```bash # 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 ```ts // 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