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 `search-analytics-query-catalog` 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 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 if multiple configured sources are plausible. 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. 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. 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. If a suitable backend is not configured, use its returned setup 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. 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`. ## 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 content for a customer deep dive 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. For broad/corpus-wide questions ("how many", "which", "any/none across all …") prefer this raw-API + `run-code` path from the first step — fetch the full cohort with `fetchAllPages`/`saveToFile` and grep/aggregate locally — rather than stretching a capped shortcut action. ### 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 — use `gong-calls` with `includeTranscripts=true` for deep dives, objections, risks, or next steps - **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. Captures stay private to the user and, when learned in an organization, are retrieved only in that same organization. Do not call `save-memory` again for those same items. 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 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. Cite the dashboard or saved query you used when a query ran; say so when you're answering from an existing dashboard, especially a certified one. 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. - Before finalizing an analytics answer, make the evidence trail explicit enough to audit: source(s), time window, filters, sample size or row count, join or match method, caveats/gaps, and what action to take next when useful. - 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`.