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. On every plan, the skill works through your SourceMedium connection: connect your coding agent first. Its gcloud and bq features (joins to your own tables in the warehouse, larger pulls) need direct warehouse access, part of Pro. See BigQuery 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:
- Use my SourceMedium connection for SourceMedium data.
- If I have direct warehouse access (Pro), my project is: [your project]

Tasks:
1. Install: npx skills add source-medium/skills --skill sm-bigquery-analyst
2. Confirm my SourceMedium connection (or, with direct access, run setup verification) and use the dataset names it reports.
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 your SourceMedium connection, or with direct warehouse access the gcloud/bq CLIs, authentication, dataset names, and table-level read access, before writing any SQL. If something is broken, it surfaces the exact failing step and points to the access guide. Warehouse discovery: reads dim_data_dictionary to find which tables exist and have fresh data, and dim_semantic_metric_catalog to resolve metric names and definitions, including each metric’s filter, across 180+ pre-defined metrics. 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: by your SourceMedium connection’s limits, or at 1 GB by default with direct access (most queries cost well under a dollar at standard BigQuery rates). Ratios are aggregated before dividing, so AOV, MER, and LTV match SourceMedium’s definitions. No mutations, no unbounded scans, and a result that hits the row limit is reported as incomplete instead of being silently cut off. 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: with direct warehouse access (Pro), 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:

Verify my SourceMedium connection, then discover available tables, stores,
and revenue metrics.

This confirms access is working and shows you what analysis is possible before you start asking questions.

Foundation plans

Foundation reaches your data through your SourceMedium connection, which supplies your project and dataset names. The skill’s Google-account gcloud and bq path and joining your own tables in the warehouse need Pro. Separately, an organization admin can issue a read-only app credential from Dashboards & Apps on either plan for a BI tool; see Connect Metabase.

Joining your own tables

With direct warehouse access (Pro), 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

LTV looks too high or too low The cohort LTV table has several rows per cohort: one per order type, per acquisition dimension (and value), and per sales channel. Ask:

Check the LTV query: one sm_order_line_type, acquisition_order_filter_dimension
= 'no_filters', summed across sm_channel at one months_since_first_order, and LTV
computed as SUM(cumulative_order_net_revenue) / SUM(cohort_size), not an average
of ratios.

Missing a filter multiplies the numbers; averaging ratios across channels misweights them. 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, permissions, or dataset names, and the doctor script tells you exactly which step broke. If it’s a permissions issue, see BigQuery Access.

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
Receipt	Dry-run bytes, tables read, rows returned, and whether the result was truncated

No Access Yet?

On every plan, connect your coding agent to SourceMedium. Direct BigQuery access is part of Pro and comes from your SourceMedium workspace membership, not from your own Google Cloud admin. See BigQuery Access for what to do and a copy/paste message for your workspace 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 definitions and filters.

Data Dictionary

Table availability, freshness, and column stats.

Multi-Touch Attribution

MTA models and experimental tables.

Start here

SourceMedium Agent

SourceMedium Agent Skills

Your own agent

Reference

Prerequisites

Install

Update

Quick Start (Copy/Paste)

What It Does

Example Questions

After Installing

First thing to do

Foundation plans

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

​Foundation plans

​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

Foundation plans

Joining your own tables

Debugging

SQL Receipt Format

No Access Yet?

Related