> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sourcemedium.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.
# SM BigQuery Analyst
> Query SourceMedium BigQuery safely with auditable SQL receipts. SELECT-only, cost-guarded.
Query SourceMedium-hosted BigQuery safely. Every answer comes with copy/paste SQL, a guaranteed dry-run cost estimate, and explicit metric definitions — no black-box results.
## Prerequisites
The install command requires `npx`, which comes with Node.js 18+. Run `node -v` to check. If you see "command not found," install Node.js from [nodejs.org](https://nodejs.org) first.
The skill itself also requires the `gcloud` and `bq` CLIs and BigQuery read access to your SourceMedium project. See [BigQuery Access Request Template](/ai-analyst/agent-skills/bigquery-access-request-template) if you need access.
## Install
```bash theme={null}
npx skills add source-medium/skills --skill sm-bigquery-analyst
```
The `skills` CLI installs into supported agent environments. If your agent does not support automatic install, copy the skill folder into that agent's configured skills directory.
## Update
If you already installed the skill, update before using the latest setup verification, discovery, and custom-data helpers:
```bash theme={null}
npx skills update sm-bigquery-analyst -y
```
***
## Quick Start (Copy/Paste)
Copy the block below and paste it into your coding agent to install, verify access, and run your first analysis.
```
Install and use the SourceMedium BigQuery Analyst skill.
Context:
- My SourceMedium project ID is: sm-[your-tenant-id]
- Default datasets are sm_metadata and sm_transformed_v2.
Tasks:
1. Install: npx skills add source-medium/skills --skill sm-bigquery-analyst
2. Run setup verification to confirm my BigQuery access is working.
3. Discover available tables, freshness, and relevant metrics.
4. Answer my first question with a SQL receipt and dry-run bytes.
My first question is: [ask your question here]
```
***
## What It Does
**Setup verification** — confirms gcloud/bq CLIs, authentication, active project, and table-level read access before writing any SQL. If something is broken, it surfaces the exact failing step and points to the [access request template](/ai-analyst/agent-skills/bigquery-access-request-template).
**Warehouse discovery** — reads `sm_metadata.dim_data_dictionary` to find which tables exist and have fresh data, and `sm_metadata.dim_semantic_metric_catalog` to resolve metric names, formulas, and 180+ pre-defined calculations. The agent discovers what's available before writing SQL rather than guessing.
**Safe SQL generation** — all queries are SELECT-only, dry-run first, and cost-capped at 1 GB by default (most queries cost well under a dollar at standard BigQuery rates). No mutations, no unbounded scans.
**SQL receipts** — a SQL receipt is a bundled audit package: the exact SQL used, the pre-run byte estimate, the metric definition, timeframe, scope, and a verify command you can re-run yourself. Every answer includes one.
**Hybrid data joins** — can safely join your own BigQuery tables to SourceMedium data. The skill runs a cardinality check before any join to prevent silent metric inflation.
**No fabrication** — if access fails or a table is unavailable, returns the exact error and stops rather than inventing numbers.
***
## Example Questions
After installing, ask your coding agent:
```
What was my revenue by channel last month?
```
```
Show me new customer acquisition by source over the past 30 days.
```
```
What's my customer LTV by cohort for the last 24 months?
```
```
Summarize ad performance by platform — spend, platform ROAS, and blended MER.
```
```
Join my inventory table to SourceMedium orders to show sell-through rate by SKU.
```
***
## After Installing
### First thing to do
Ask the agent to verify your setup and discover what's available:
```
Run the SourceMedium BigQuery setup verification, then discover available
tables, stores, and revenue metrics for my project sm-[your-tenant-id].
```
This confirms access is working and shows you what analysis is possible before you start asking questions.
### Custom or prefixed datasets
If your warehouse uses tenant-prefixed dataset names (common in shared projects), tell the agent:
```
My metadata dataset is [tenant]_sm_metadata and my transformed dataset
is [tenant]_sm_transformed_v2. Use these instead of the defaults.
```
### Joining your own tables
If you have your own BigQuery tables alongside SourceMedium data:
```
I have a table [your-project].[your-dataset].[your-table] that contains
[describe what the table tracks and what column links to an order or customer].
Join it to SourceMedium orders and show [your question].
```
The agent will document the table, verify it won't inflate metrics, and write a safe join query.
***
## Debugging
**Inflated numbers (3× what you'd expect)**
Usually means an LTV table query is missing a required filter. Ask:
```
Revenue looks about 3× too high. Check whether the LTV table query
is missing a sm_order_line_type filter and fix it.
```
The LTV tables store multiple row types per cohort — without filtering to exactly one, all metrics multiply. If the numbers are still off after this, share your agent prompt and a screenshot of the response with support.
**Wrong channel distribution**
Channel mapping depends on order tags, UTMs, and source system. Ask:
```
Too much revenue is landing in one channel bucket. Run the channel
mapping debug query to show the raw mapping inputs for recent orders.
```
**Zero rows**
```
There are no rows in the result. Check table freshness in
dim_data_dictionary and verify the date filter matches available data.
```
**Setup failures**
Run setup verification first — most failures are auth or permissions and the doctor script tells you exactly which step broke. If it's a permissions issue, use the [access request template](/ai-analyst/agent-skills/bigquery-access-request-template).
***
## SQL Receipt Format
Every answer from this skill includes:
| Field | What it means |
| ------------- | -------------------------------------------------------------------- |
| Answer | Plain-English conclusion |
| SQL | Copy/paste BigQuery Standard SQL |
| Notes | Metric definition, timeframe, scope, timezone, what was excluded |
| Verify | `bq query --dry_run` command to re-validate independently |
| Bytes scanned | Pre-run cost estimate; agent confirms before running if over the cap |
***
## No Access Yet?
See [BigQuery Access Request Template](/ai-analyst/agent-skills/bigquery-access-request-template) for the minimum IAM roles and a copy/paste message for your internal admin.
***
## Related
Setup and first-query fundamentals.
SourceMedium SQL templates and patterns.
Schema-level documentation for core tables.
180+ pre-defined metrics with calculations.
Table availability, freshness, and column stats.
MTA models and experimental tables.