AI-Powered Reporting with Claude Code - Full Reference

Last Updated: April 08, 2026

Overview

This is the full reference for UltraCart BigQuery reporting. If you haven't set up reporting yet, start with the Getting Started guide.

This guide covers advanced configuration, connecting external data sources, automated delivery, alerting, executive report decks, scheduling, and all configuration options.

Configuration

The CLI stores its configuration in .ultracart-bq.json in your project root. Run uc-bq init for interactive setup, or create the file manually.

Basic Configuration

{
  "default_merchant": "DEMO",
  "merchants": {
    "DEMO": { "taxonomy_level": "medium", "dataset": "ultracart_dw" }
  },
  "default_output_dir": "./reports",
  "output_format": "png",
  "chart_theme": "default",
  "chart_defaults": { "width": 1200, "height": 600 },
  "max_query_bytes": 10737418240
}

Multi-Merchant Configuration

Add multiple merchants to manage several stores from one config:

{
  "default_merchant": "DEMO",
  "merchants": {
    "DEMO": { "taxonomy_level": "medium", "dataset": "ultracart_dw" },
    "DEMO2": { "taxonomy_level": "standard", "dataset": "ultracart_dw" }
  }
}

Use the --merchant / -m flag to target a specific merchant:

uc-bq schema --list -m DEMO2
uc-bq run revenue-by-category -m DEMO2

Managing Configuration via CLI

uc-bq config show                                              # Show current config
uc-bq config add-merchant <id> --taxonomy=X [--dataset=Y]      # Add a merchant
uc-bq config remove-merchant <id>                              # Remove a merchant

External Data Sources

Merchants can register external GCP projects -- such as marketing data from Funnel.io, DBT warehouses, or any other BigQuery dataset -- alongside their UltraCart data. You explicitly choose which datasets and tables to expose, and they become available for queries and reports just like your UltraCart tables.

Browsing Before Registering

Use uc-bq schema --project to explore an external project before adding it to your config:

# List datasets in the external project
uc-bq schema --project=my-marketing-warehouse

# List tables in a specific dataset
uc-bq schema --project=my-marketing-warehouse --dataset=google_ads_data --list

# Get the schema for specific tables
uc-bq schema --project=my-marketing-warehouse --dataset=google_ads_data --tables=funnel_data

Registering an External Project

Use the config commands to add the project, datasets, and tables:

# Register the project
uc-bq config add-project marketing --project-id=my-marketing-warehouse --description="Marketing data from Funnel.io"

# Add a dataset from the project
uc-bq config add-dataset marketing google_ads_data

# Expose specific tables (you choose what's visible)
uc-bq config add-tables marketing google_ads_data funnel_data

Or add it directly in .ultracart-bq.json:

{
  "default_merchant": "DEMO",
  "merchants": {
    "DEMO": {
      "taxonomy_level": "medium",
      "dataset": "ultracart_dw",
      "external_projects": {
        "marketing": {
          "project_id": "my-marketing-warehouse",
          "description": "Marketing data from Funnel.io",
          "datasets": {
            "google_ads_data": ["funnel_data"]
          }
        }
      }
    }
  }
}

Once registered, external tables appear alongside UltraCart tables in uc-bq schema --list and are available for queries and reports. When building reports with Claude Code, it can join your UltraCart e-commerce data with your external marketing, advertising, or analytics data in a single query.

Managing External Projects

# Remove a project
uc-bq config remove-project <alias>

# Add or remove datasets
uc-bq config add-dataset <alias> <dataset> [--discover]
uc-bq config remove-dataset <alias> <dataset>

# Add or remove individual tables
uc-bq config add-tables <alias> <dataset> <tables...>
uc-bq config remove-tables <alias> <dataset> <tables...>

Schema Caching

External table schemas are cached at .ultracart-bq-cache/ to avoid repeated BigQuery metadata lookups. To refresh the cache after schema changes:

uc-bq schema --refresh

Report Structure

Each report is a self-contained directory, scoped by merchant:

./reports/DEMO/
├── revenue-by-category/
│   ├── report.yaml           # Manifest -- parameters, config, metadata
│   ├── query.sql             # Parameterized SQL template
│   ├── chart.js              # ECharts formatChartData() function
│   ├── chart.png             # Full visualization
│   ├── chart-dashboard.png   # 200x200 dashboard thumbnail
│   ├── report.pdf            # Combined PDF with chart + executive analysis
│   ├── analysis_prompt.md    # System prompt for analysis generation
│   ├── report.md             # Executive analysis
│   └── data.json             # Query results (optional)
├── top-products-by-revenue/
│   └── ...
└── decks/
    ├── weekly-executive.yaml           # Deck definition (committed to git)
    ├── weekly-executive.pdf            # Generated deck PDF
    └── weekly-executive-dashboard.html # Generated interactive dashboard

Report Manifest

The report.yaml manifest captures everything needed to replay a report:

name: "Revenue by Product Category"
description: "Daily revenue trends broken down by product category"
created: 2026-03-28
last_run: 2026-03-28
prompt: "Show me revenue trends by product category for the last 90 days"

parameters:
  - name: start_date
    type: date
    label: "Start Date"
    required: true
    default: "-90d"
  - name: end_date
    type: date
    label: "End Date"
    required: true
    default: "today"

delivery:
  slack:
    channels: ["C0123456789"]
  email:
    to: ["ceo@example.com", "marketing@example.com"]
    subject: "Weekly: Revenue by Product Category"
    provider: "sendgrid"

config:
  merchant_id: "DEMO"
  project_id: "ultracart-dw-DEMO"
  taxonomy_level: "medium"
  dataset: "ultracart_dw"
  tables_used: ["uc_orders"]

sql_file: "query.sql"
chart:
  type: "stacked-area"
  echarts_file: "chart.js"
  output_format: "png"
  width: 1200
  height: 600

analysis:
  landscape: true

Report Delivery

Reports can be automatically delivered to Slack channels and email recipients after generation. Add a delivery section to any report's report.yaml:

delivery:
  slack:
    channels: ["C0123456789", "C9876543210"]
  email:
    to: ["ceo@example.com", "marketing@example.com"]
    subject: "Weekly: Revenue by Payment Method"
    provider: "sendgrid"

Then use --deliver on run or run-all:

uc-bq run revenue-by-category --deliver
uc-bq run-all --deliver --no-analysis

Supported Email Providers

SendGrid, Postmark, Mailgun, Resend, and AWS SES. All via REST APIs -- no SMTP configuration required.

Managing Delivery via CLI

# Slack
uc-bq config add-slack <report> <channel-id...>
uc-bq config remove-slack <report> <channel-id...>

# Email
uc-bq config set-email <report> --to=a@example.com,b@example.com --provider=sendgrid --subject="Weekly"
uc-bq config add-email <report> <email...>
uc-bq config remove-email <report> <email...>
uc-bq config set-email-provider <report> <provider>
uc-bq config set-email-subject <report> <subject>

# View delivery config
uc-bq config show-delivery <report>

Delivery failures are logged but never crash the run. If Slack is down or an email bounces, the report still generates successfully.

See Report Delivery Setup for full provider configuration, environment variables, and multi-client delivery patterns.

Report Alarms

Alarms let you define conditions on your report data that trigger notifications when something needs attention -- management by exception. Alarms evaluate automatically as part of the normal uc-bq run pipeline.

Alarm Types

Type	Description	Example

Type	Description	Example
Threshold	Alert when a metric crosses a static value	Revenue < $10,000
Percent change	Alert when a metric changes by more than X% vs the previous run	Revenue drops more than 20%
Missing data	Alert when a query returns zero rows	No orders recorded today

Defining Alarms

Add an alarms section to your report's report.yaml:

alarms:
  - name: "Revenue Drop"
    type: pct_change
    metric: "total_revenue"
    aggregate: "sum"
    operator: "<"
    value: -20
    compare_to: "previous_run"
    severity: high
    cooldown: "24h"

delivery:
  mode: "alarm_only"              # Only deliver when alarms fire
  slack:
    channels: ["C0123456789"]
    mention_on_alarm: "@channel"   # Ping for critical alarms

Alarm Severity

Severity	Behavior

Severity	Behavior
`low`	Inline notification
`high`	Distinct notification
`critical`	Adds Slack mentions

Cooldown prevents repeated notifications for persistent conditions (e.g., "24h" means you won't be alerted again for 24 hours if the condition persists).

Managing Alarms via CLI

uc-bq config add-alarm <report> --name "..." --type threshold --metric "..." \
  --aggregate sum --operator "<" --value 10000 --severity high --cooldown 24h
uc-bq config show-alarms <report>
uc-bq config remove-alarm <report> "Alarm Name"
uc-bq config set-delivery-mode <report> alarm_only
uc-bq alarm test <report>                    # Test against current data
uc-bq alarm history <report>                 # View alarm history

See Alarms Documentation for full alarm documentation including deck alarm aggregation, GitHub Actions integration, and recipes.

Report Decks and Dashboards

Decks combine multiple reports into a single PDF with a branded cover page, clickable table of contents, and all charts and analyses in one document. Instead of sending N separate files, deliver one polished deck.

Deck Definition

Create a deck YAML file in reports/{merchant_id}/decks/:

# reports/DEMO/decks/weekly-executive.yaml
name: "Weekly Executive Briefing"
title: "DEMO Weekly Report Deck"
cover:
  company: "DEMO Commerce Inc."
  logo_url: "https://example.com/logo.png"
parameters:
  start_date: "start_of_year"
  end_date: "today"
reports:
  - revenue-by-payment-method
  - ltv-by-monthly-cohort
  - top-products-by-revenue
landscape: true
delivery:
  slack:
    channels: ["C0123456789"]
  email:
    to: ["ceo@example.com", "cfo@example.com"]
    subject: "Weekly Executive Briefing"
    provider: "sendgrid"

Deck parameters flow down to all reports as overrides. Priority: CLI flags > deck parameters > report defaults.

Deck Commands

# Generate the deck PDF
uc-bq deck run weekly-executive

# Generate and deliver the deck
uc-bq deck run weekly-executive --deliver

# Skip analysis generation
uc-bq deck run weekly-executive --no-analysis

# Override parameters for all reports in the deck
uc-bq deck run weekly-executive --start_date=2026-01-01 --end_date=2026-03-31

# List all defined decks
uc-bq deck list

# Create a new deck interactively
uc-bq deck create weekly-executive

Interactive Dashboards

Generate a self-contained interactive HTML dashboard from a deck definition. Uses ECharts loaded from CDN with all chart data inlined -- no server required:

# Generate dashboard HTML
uc-bq deck dashboard weekly-executive

# Generate and open in browser
uc-bq deck dashboard weekly-executive --open

Output: reports/DEMO/decks/weekly-executive-dashboard.html

The HTML file has responsive layout, interactive tooltips, hover effects, and zoom. Deploy it anywhere -- S3, an internal web server, or open it directly from disk.

See Decks Documentation for cover page customization, multi-client patterns, and GitHub Actions integration.

Scheduling and Automation

Since uc-bq run is pure Node.js with no LLM dependency, you can schedule it with cron or any CI/CD system.

Cron

# Refresh all reports every Monday at 6am (no analysis, no LLM cost)
0 6 * * 1 cd /path/to/project && uc-bq run-all --no-analysis --start_date=-7d --end_date=today

# With AI analysis (set the API key matching your configured provider)
0 6 * * 1 cd /path/to/project && ANTHROPIC_API_KEY=sk-... uc-bq run-all

GitHub Actions

name: Weekly Reports
on:
  schedule:
    - cron: '0 6 * * 1'
jobs:
  reports:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with: { node-version: '24' }
      - run: npm install -g @ultracart/bq-skill
      - uses: google-github-actions/auth@v2
        with: { credentials_json: '${{ secrets.GCP_SA_KEY }}' }
      - run: uc-bq run-all --deliver --no-analysis
        env:
          SLACK_BOT_TOKEN: ${{ secrets.SLACK_BOT_TOKEN }}
          EMAIL_FROM: ${{ vars.EMAIL_FROM }}
          SENDGRID_API_KEY: ${{ secrets.SENDGRID_API_KEY }}
      - uses: actions/upload-artifact@v4
        with: { name: reports, path: './reports/**/chart.png' }

For scheduled runs with AI analysis, add your LLM provider's API key to the environment variables (e.g., ANTHROPIC_API_KEY, OPENAI_API_KEY).

Service Account Authentication

For headless/automated environments, use a service account instead of personal credentials:

Create a service account in your GCP project:

gcloud iam service-accounts create uc-bq-reader \
  --display-name="UltraCart BQ Reader" \
  --project=YOUR_PROJECT_ID

gcloud iam service-accounts keys create ./sa-key.json \
  --iam-account=uc-bq-reader@YOUR_PROJECT_ID.iam.gserviceaccount.com

Register the service account in UltraCart -- go to the UltraCart dashboard and register the service account email so UltraCart can provision BigQuery access for it.
Set the environment variable:

export GOOGLE_APPLICATION_CREDENTIALS="/path/to/sa-key.json"

See GitHub Actions Documentation for complete CI/CD setup.

Cost Protection

Every query execution (query, run, run-all) automatically runs a BigQuery dry-run first to check the estimated bytes processed. If the estimate exceeds the safety limit, the query is aborted before it runs.

Default limit: 10 GB (~$0.06 at BigQuery on-demand pricing of $6.25/TB).

Overriding the Limit

Method	Scope	Example

Method	Scope	Example
`--force` flag	Single command	`uc-bq run revenue-by-category --force`
`--max-bytes` flag	Single command	`uc-bq run revenue-by-category --max-bytes=53687091200` (50 GB)
`max_query_bytes` in config	All commands	Set in `.ultracart-bq.json` (bytes). Set to `0` to disable.

Taxonomy Levels

UltraCart controls data access via taxonomy levels assigned by your account administrator:

Level	Access

Level	Access
`standard`	No PII -- order totals, item data, analytics
`low`	Minimal PII
`medium`	Includes email, addresses, customer details
`high`	Full access to all fields

Your taxonomy level determines which BigQuery datasets and columns are available. The CLI and skill automatically respect these boundaries.

Available Datasets

Dataset	Description	Access

Dataset	Description	Access
`ultracart_dw`	Standard tables -- no PII	All users
`ultracart_dw_medium`	Includes PII fields	Medium/High taxonomy
`ultracart_dw_streaming`	Analytics sessions, screen recordings	All users
`ultracart_dw_linked`	Parent/child aggregated data	Parent accounts only

Relative Date Expressions

Report parameters support relative date expressions that resolve at replay time:

Expression	Meaning

Expression	Meaning
`today`	Current date
`yesterday`	Previous day
`-Nd`	N days ago (e.g., `-90d`)
`-Nw`	N weeks ago
`-Nm`	N months ago
`-Ny`	N years ago
`start_of_week`	Monday of the current week
`start_of_last_month`	First day of previous month
`start_of_last_quarter`	First day of previous quarter
`start_of_last_year`	January 1 of previous year
`end_of_last_month`	Last day of previous month
`end_of_last_quarter`	Last day of previous quarter
`end_of_last_year`	December 31 of previous year

These expressions are evaluated each time the report runs, so a report with default: "-90d" always covers the most recent 90 days.

LLM Providers

The CLI supports multiple LLM providers for executive analysis generation on headless/scheduled runs. When using Claude Code interactively, the LLM provider config does not apply -- Claude Code itself is the LLM doing the work.

Supported Providers

Provider	API Key Env	Default Analysis Model	Default Filter Model

Provider	API Key Env	Default Analysis Model	Default Filter Model
`anthropic` (default)	`ANTHROPIC_API_KEY`	`claude-sonnet-4-5-20250929`	`claude-haiku-4-5-20251001`
`openai`	`OPENAI_API_KEY`	`gpt-4o`	`gpt-4o-mini`
`grok`	`XAI_API_KEY`	`grok-2`	`grok-2`
`bedrock`	AWS credential chain	`anthropic.claude-sonnet-4-5-20250929-v1:0`	`anthropic.claude-haiku-4-5-20251001-v1:0`
`gemini`	`GOOGLE_API_KEY`	`gemini-2.0-flash`	`gemini-2.0-flash-lite`

Configuration

Add an llm section to .ultracart-bq.json:

{
  "llm": {
    "provider": "openai",
    "api_key_env": "OPENAI_API_KEY",
    "analysis_model": "gpt-4o",
    "schema_filter_model": "gpt-4o-mini"
  }
}

All fields are optional. If the llm section is omitted entirely, the CLI defaults to Anthropic.

Override the configured provider for a single command with --llm-provider:

uc-bq run revenue-by-category --llm-provider=openai --analysis-api-key=$OPENAI_API_KEY

Troubleshooting

Could not load the default credentials

Cause: Google Cloud ADC has not been configured.

Solution: Run gcloud auth application-default login or set GOOGLE_APPLICATION_CREDENTIALS for service accounts.

Permission denied / Access Denied

Cause: Your Google account or service account hasn't been granted access in UltraCart.

Solution: Contact your UltraCart admin to provision BigQuery access.

Project not found

Cause: The project ID is derived from your merchant ID (ultracart-dw-{merchantid}). If the merchant ID in your config is wrong, the project won't be found.

Solution: Verify merchant IDs in .ultracart-bq.json. Run uc-bq init to reconfigure.

Dataset not found

Cause: Your taxonomy level may not include the dataset you're querying.

Solution: Check your taxonomy level with your UltraCart admin. Standard-level users cannot access ultracart_dw_medium.

Query exceeds safety limit

Cause: The estimated bytes processed exceeds the configured limit.

Solution: Use --force for a single command, --max-bytes for a custom limit, or adjust max_query_bytes in your config.

Delivery failures

Cause: Slack bot token expired, email provider misconfigured, or network issue.

Solution: Check your environment variables (SLACK_BOT_TOKEN, SENDGRID_API_KEY, etc.). Delivery failures are logged but never crash the report run.

External project access denied

Cause: Your Google account or service account doesn't have BigQuery Reader access on the external project.

Solution: Grant roles/bigquery.dataViewer on the external GCP project to your account or service account.

AI-Powered Reporting with Claude Code - Full Reference

Table of Contents

Overview

Configuration

Basic Configuration

Multi-Merchant Configuration

Managing Configuration via CLI

External Data Sources

Browsing Before Registering

Registering an External Project

Managing External Projects

Schema Caching

Report Structure

Report Manifest

Report Delivery

Supported Email Providers

Managing Delivery via CLI

Report Alarms

Alarm Types

Defining Alarms

Alarm Severity

Managing Alarms via CLI

Report Decks and Dashboards

Deck Definition

Deck Commands

Interactive Dashboards

Scheduling and Automation

Cron

GitHub Actions

Service Account Authentication

Cost Protection

Overriding the Limit

Taxonomy Levels

Available Datasets

Relative Date Expressions

LLM Providers

Supported Providers

Configuration

Troubleshooting

Could not load the default credentials

Permission denied / Access Denied

Project not found

Dataset not found

Query exceeds safety limit

Delivery failures

External project access denied

Related Documentation