# Data Querying

> General guidance on querying data sources, using existing scripts vs ad-hoc queries, filtering patterns, and generating charts for the analytics app.

- Skill: `builderio/data-querying` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add builderio/data-querying`
- Raw SKILL.md: https://api.skillmd.com/api/skills/builderio/data-querying/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: Builder.io (https://skillmd.com/u/builderio)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/builderio/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:

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