Data Querying

Data Querying

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

Approach

0. Orient catalog-first — before querying, consult what already exists: the injected <data-dictionary> and data-source status tell you which sources are configured and which table/columns/join paths to use. Use them to pick the one source that owns the fact instead of fanning out blind queries.
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.

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:

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:

```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 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

// 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
• Sentry for error rates and trends
• 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)

Capture it immediately using save-memory or by writing to LEARNINGS.md via the resources tool:

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.