SM BigQuery Analyst

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 first. The skill itself also requires the gcloud and bq CLIs and BigQuery read access to your SourceMedium project. See BigQuery Access Request Template if you need access.

Install

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:

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

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 for the minimum IAM roles and a copy/paste message for your internal admin.

BigQuery Essentials

Setup and first-query fundamentals.

SQL Query Library

SourceMedium SQL templates and patterns.

Table Docs

Schema-level documentation for core tables.

Metric Catalog

180+ pre-defined metrics with calculations.

Data Dictionary

Table availability, freshness, and column stats.

Multi-Touch Attribution

MTA models and experimental tables.

Getting Started

Workflows

Diagnostics

Agent Skills

Reference

Prerequisites

Install

Update

Quick Start (Copy/Paste)

What It Does

Example Questions

After Installing

First thing to do

Custom or prefixed datasets

Joining your own tables

Debugging

SQL Receipt Format

No Access Yet?

BigQuery Essentials

SQL Query Library

Table Docs

Metric Catalog

Data Dictionary

Multi-Touch Attribution

​Prerequisites

​Install

​Update

​Quick Start (Copy/Paste)

​What It Does

​Example Questions

​After Installing

​First thing to do

​Custom or prefixed datasets

​Joining your own tables

​Debugging

​SQL Receipt Format

​No Access Yet?

​Related

BigQuery Essentials

SQL Query Library

Table Docs

Metric Catalog

Data Dictionary

Multi-Touch Attribution

Prerequisites

Install

Update

Quick Start (Copy/Paste)

What It Does

Example Questions

After Installing

First thing to do

Custom or prefixed datasets

Joining your own tables

Debugging

SQL Receipt Format

No Access Yet?

Related