KPI Reporting via Direct Warehouse Queries

When client data lives in BigQuery or Snowflake, querying the warehouse directly is more reliable than scraping dashboards for automated KPI reporting. The approach: pre-write the SQL that defines KPIs, give the agent access to run it, and let it format the results. The query returns a deterministic result set with no UI fragility or session management overhead.

The Basic Pattern

The approach is the same one used in BigQuery failure monitoring and Snowflake cost monitoring — pre-write SQL that calculates exactly what you want, then let the agent run it and format the output.

For BigQuery:

bq query --format=json --use_legacy_sql=false \
  "SELECT ... FROM \`project.dataset.table\` WHERE ..."

For Snowflake:

snowsql -q "SELECT ... FROM database.schema.table WHERE ..."

The agent receives the JSON output, reads the rows, and formats a summary. The agent’s natural language capabilities handle the interpretation step that would otherwise require parsing code.

Writing the KPI Query

The pre-written SQL is where the real work happens. A useful weekly KPI query does three things:

1. Returns this week’s metrics alongside last week’s in a single query (avoid two separate queries the agent has to mentally join)
2. Uses DATE_TRUNC or similar to align on clean weekly boundaries
3. Produces human-readable column names the agent can narrate without translation

A weekly revenue summary for a client might look like this:

SELECT
  DATE_TRUNC(order__ordered_at, WEEK) AS week,
  COUNT(DISTINCT order__id) AS orders,
  ROUND(SUM(order__revenue), 2) AS total_revenue,
  ROUND(AVG(order__revenue), 2) AS avg_order_value
FROM `client-project.analytics.mrt__sales__orders`
WHERE order__ordered_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY)
GROUP BY 1
ORDER BY 1 DESC

This returns two rows: this week and last week. The agent gets the data, calculates the percentage change between them, and includes it in the summary. The 14-day lookback window captures both weeks in a single query rather than requiring two separate calls with different date ranges.

The ROUND() on monetary values prevents the agent from reporting 47291.4872 when the client expects €47,291.49. Small formatting details in the SQL avoid prompt-engineering the agent’s number formatting later.

Structuring Multiple KPI Queries

For a client whose weekly report covers sessions, conversions, and revenue from three different models, you have two options: one compound query that joins everything, or separate queries per KPI area.

Separate queries are usually better for agent-driven reporting. A single monolithic query that joins ga4 sessions to order data to ad spend returns a result set that’s harder for the agent to parse and narrate. Separate queries return clean, simple result sets. The agent runs them in sequence and composes the summary from multiple inputs.

The cron message specifies the sequence:

Run the weekly KPI report for Acme Corp:
1. Query BigQuery for GA4 session metrics using the sessions query in ~/reports/acme/ga4-weekly.sql
2. Query BigQuery for revenue metrics using ~/reports/acme/revenue-weekly.sql
3. Compare this week to last week for both metrics
4. Format the results as a Slack summary following the format in ~/reports/acme/summary-template.md
5. Post to the Acme reporting channel

The SQL files live alongside the cron configuration. The agent finds them, runs them, and assembles the output. Storing queries as files rather than inlining them in the cron message makes them easier to version, edit, and reuse.

The Reliability Advantage Over Dashboard Scraping

Direct warehouse queries are more reliable than dashboard scraping on every dimension that matters for scheduled reporting:

Deterministic output. The query returns exactly what it’s told to return. There’s no risk that the agent extracted numbers from the wrong section of a page, no dependence on CSS selectors that change when a developer updates the UI.

Explicit failure. If the query fails — wrong credentials, missing table, BigQuery outage — you get an error, not wrong data. Silent failures, which are the central danger of dashboard scraping, don’t apply to SQL queries.

Access to underlying data. Dashboards show a pre-filtered view. Warehouse queries give you the underlying data, which means you can calculate metrics exactly the way you want rather than being limited to whatever the dashboard exposes.

No session management. There are no browser sessions to refresh, no OAuth tokens for a UI login to expire. Warehouse credentials have their own lifecycle (service account keys, password rotation), but they’re simpler to manage than browser session persistence.

The tradeoff is that you need warehouse credentials for each client, and you need to write and maintain the SQL. Dashboard scraping requires none of that — just a URL and a browser session. For quick setups or clients without warehouse access, scraping may genuinely be the only option. For anything client-facing and recurring, warehouse queries are worth the setup cost.

Managing SQL Across Clients

For a consulting practice managing 3-5 clients, the practical question is how to organize queries so you’re not hunting for the right file when setting up or debugging a cron job.

A simple directory structure per client works well:

~/openclaw-reports/
├── acme/
│   ├── ga4-weekly.sql
│   ├── revenue-weekly.sql
│   └── summary-template.md
├── client-b/
│   ├── snowflake-sessions.sql
│   ├── conversion-weekly.sql
│   └── summary-template.md
└── client-c/
    └── bigquery-pipeline-stats.sql

Version-control this directory. SQL queries change — new KPIs get added, column names in the mart models change, clients want different cuts of the data. Treating these files like code (with commits, change history, and the ability to roll back) is better than editing them in place and hoping you remember what changed when a report looks different next week.

The Math Reliability Caveat

Pre-written SQL handles the heavy lifting of calculation, which reduces one of the most important risks in agent-driven reporting: LLM math errors.

If you ask the agent to calculate a percentage change from two numbers in its context — “sessions went from 11,895 last week to 12,847 this week” — it will sometimes get the percentage wrong. Not because the model is bad at arithmetic, but because LLMs are statistical text predictors, and they predict plausible-sounding numbers rather than calculating them. The same query results can produce different percentage claims in different runs.

Do the math in SQL instead. Add a calculated column:

SELECT
  this_week.orders,
  last_week.orders AS orders_last_week,
  ROUND(
    (this_week.orders - last_week.orders) * 100.0 / last_week.orders,
    1
  ) AS orders_pct_change
FROM ...

The agent receives a row that already says orders_pct_change: 8.0. It reports ↑ 8%. No arithmetic in the LLM.

This is the general principle for any agent-driven analytics workflow: push calculations into the data layer, give the agent pre-computed numbers to narrate. The agent’s job is interpretation and formatting, not math. See Slack KPI Summary Format for how this applies to the output side.

Credential Requirements

Querying the warehouse requires credentials. For BigQuery, that typically means a service account key file and the bq CLI configured with GOOGLE_APPLICATION_CREDENTIALS. For Snowflake, it means snowsql installed and a connection profile configured.

For multi-client setups, each client’s warehouse access requires its own credentials. This is where the security picture gets complicated — see Multi-Client Agent Reporting Architecture for how to handle per-client credential isolation, and OpenClaw Security Risks — What’s Documented for why plaintext credential storage on a single machine is a meaningful risk when you scale past one client.

The short version: warehouse credentials stored in ~/.openclaw/config/ or in environment variables on the host machine are at risk if the machine is compromised. A dedicated machine per client (or at minimum, separate user accounts with separate config directories) is the right architecture for agency work, not a shared machine with all five clients’ keys in the same place.