Skip to main content
Tutorial

AI-Powered Reporting with Claude Code - Full Reference

Last Updated: April 08, 2026

Table of Contents​

  1. Overview
  2. Configuration
  3. External Data Sources
  4. Report Structure
  5. Report Delivery
  6. Report Alarms
  7. Report Decks and Dashboards
  8. Scheduling and Automation
  9. Cost Protection
  10. Taxonomy Levels
  11. Relative Date Expressions
  12. LLM Providers
  13. Troubleshooting
  14. Related Documentation

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

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​

TypeDescriptionExample
ThresholdAlert when a metric crosses a static valueRevenue < $10,000
Percent changeAlert when a metric changes by more than X% vs the previous runRevenue drops more than 20%
Missing dataAlert when a query returns zero rowsNo 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​

SeverityBehavior
lowInline notification
highDistinct notification
criticalAdds 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' }
tip

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:

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

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

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

MethodScopeExample
--force flagSingle commanduc-bq run revenue-by-category --force
--max-bytes flagSingle commanduc-bq run revenue-by-category --max-bytes=53687091200 (50 GB)
max_query_bytes in configAll commandsSet in .ultracart-bq.json (bytes). Set to 0 to disable.

Taxonomy Levels​

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

LevelAccess
standardNo PII -- order totals, item data, analytics
lowMinimal PII
mediumIncludes email, addresses, customer details
highFull 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​

DatasetDescriptionAccess
ultracart_dwStandard tables -- no PIIAll users
ultracart_dw_mediumIncludes PII fieldsMedium/High taxonomy
ultracart_dw_streamingAnalytics sessions, screen recordingsAll users
ultracart_dw_linkedParent/child aggregated dataParent accounts only

Relative Date Expressions​

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

ExpressionMeaning
todayCurrent date
yesterdayPrevious day
-NdN days ago (e.g., -90d)
-NwN weeks ago
-NmN months ago
-NyN years ago
start_of_weekMonday of the current week
start_of_last_monthFirst day of previous month
start_of_last_quarterFirst day of previous quarter
start_of_last_yearJanuary 1 of previous year
end_of_last_monthLast day of previous month
end_of_last_quarterLast day of previous quarter
end_of_last_yearDecember 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​

ProviderAPI Key EnvDefault Analysis ModelDefault Filter Model
anthropic (default)ANTHROPIC_API_KEYclaude-sonnet-4-5-20250929claude-haiku-4-5-20251001
openaiOPENAI_API_KEYgpt-4ogpt-4o-mini
grokXAI_API_KEYgrok-2grok-2
bedrockAWS credential chainanthropic.claude-sonnet-4-5-20250929-v1:0anthropic.claude-haiku-4-5-20251001-v1:0
geminiGOOGLE_API_KEYgemini-2.0-flashgemini-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.

Was this page helpful?