Skip to main content
How-to

Manage Reports for Multiple Merchants

Overview​

Agencies, consultants, and operators running several brands can keep every client in one repository. One .ultracart-bq.json lists all the merchants, each client's reports live in their own directory, and each report carries its own delivery targets, so a single scheduled workflow serves all of them.

Client separation is enforced below the tool. Each merchant maps to its own BigQuery project, and BigQuery grants access per project, so one client's report cannot read another's data even if the SQL asked for it.

Lay out the repository​

Report directories are already scoped by merchant, so the structure follows from the config:

myconsultingco-reports/
├── .ultracart-bq.json              # every merchant, no secrets
├── .github/workflows/weekly-reports.yml
└── reports/
    ├── CLNT1/
    │   ├── revenue-by-channel/     # report.yaml carries CLNT1's delivery targets
    │   ├── customer-ltv/
    │   └── decks/weekly-summary.yaml
    ├── CLNT2/
    │   ├── revenue-by-channel/     # same report name, CLNT2's targets
    │   └── subscription-churn/
    └── CLNT3/
        └── revenue-summary/

Two clients can have a report with the same name without colliding, because the merchant ID is part of the path.

Configure every merchant in one file​

{
  "default_merchant": "CLNT1",
  "merchants": {
    "CLNT1": { "taxonomy_level": "medium", "dataset": "ultracart_dw" },
    "CLNT2": { "taxonomy_level": "standard", "dataset": "ultracart_dw" },
    "CLNT3": {
      "taxonomy_level": "medium",
      "dataset": "ultracart_dw",
      "external_projects": {
        "ads": {
          "project_id": "gamma-marketing",
          "description": "Google Ads data",
          "datasets": { "google_ads": ["campaigns", "ad_groups"] }
        }
      }
    }
  },
  "default_output_dir": "./reports",
  "output_format": "png",
  "max_query_bytes": 10737418240
}

Taxonomy level is per merchant, because UltraCart grants it per merchant. A client who has only authorized standard access gets standard here regardless of what the others allow.

The file contains merchant IDs and taxonomy levels and no credentials, so it belongs in the repository. Add merchants without editing it by hand:

uc-bq config add-merchant CLNT4 --taxonomy=medium

Target one merchant on any command with -m or --merchant:

uc-bq schema --list -m CLNT2
uc-bq run revenue-by-channel -m CLNT2
uc-bq run-all -m CLNT2 --deliver

Give each client their own delivery targets​

Delivery is per report, so two clients running the same report send to different places:

# reports/CLNT1/revenue-by-channel/report.yaml
delivery:
  slack:
    channels: ["C0111111111"]
  email:
    to: ["ceo@clnt1-example.com", "analytics@clnt1-example.com"]
    subject: "Weekly: Revenue by Channel"
    provider: "sendgrid"
# reports/CLNT2/revenue-by-channel/report.yaml
delivery:
  slack:
    channels: ["C0222222222"]
  email:
    to: ["owner@clnt2-example.com"]
    subject: "Weekly: Revenue by Channel"
    provider: "postmark"

Different clients can use different email providers, as long as each provider's key is present in the environment. Channel IDs and email addresses are not secrets and stay in the manifests; tokens and API keys stay in your secret store.

Register one service account across clients​

A single Google Cloud service account can read every client's warehouse, provided each client's UltraCart administrator has registered its email address on their account. Create it once, following Authenticate with a service account, then ask each client to register it.

That registration is also the offboarding switch. When a client removes the service account from their UltraCart account, your access to their data ends immediately, with nothing to clean up on your side.

Run every client from one workflow​

A matrix runs the clients in parallel. If all your clients have channels in your own Slack workspace, one bot token covers them all:

name: Weekly Client Reports

on:
  schedule:
    - cron: '0 11 * * 1'
  workflow_dispatch:

jobs:
  reports:
    runs-on: ubuntu-latest
    timeout-minutes: 20
    strategy:
      matrix:
        client:
          - { id: 'CLNT1', name: 'Client 1' }
          - { id: 'CLNT2', name: 'Client 2' }
          - { id: 'CLNT3', name: 'Client 3' }

    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 }}' }

      - name: Run and deliver for ${{ matrix.client.name }}
        env:
          SLACK_BOT_TOKEN: ${{ secrets.SLACK_BOT_TOKEN }}
          EMAIL_FROM: ${{ vars.EMAIL_FROM }}
          SENDGRID_API_KEY: ${{ secrets.SENDGRID_API_KEY }}
        run: uc-bq run-all --merchant=${{ matrix.client.id }} --deliver --no-analysis

Each job reads the delivery targets out of that client's manifests, so no routing logic lives in the workflow.

Handle separate Slack workspaces​

When each client has their own Slack workspace, each needs its own bot token. Store them as SLACK_TOKEN_CLNT1, SLACK_TOKEN_CLNT2, and so on, then select the right one per matrix entry:

      - name: Run and deliver for ${{ matrix.client.name }}
        env:
          SLACK_BOT_TOKEN: ${{ secrets[format('SLACK_TOKEN_{0}', matrix.client.id)] }}
          EMAIL_FROM: ${{ vars.EMAIL_FROM }}
          SENDGRID_API_KEY: ${{ secrets.SENDGRID_API_KEY }}
          POSTMARK_API_KEY: ${{ secrets.POSTMARK_API_KEY }}
        run: uc-bq run-all --merchant=${{ matrix.client.id }} --deliver --no-analysis

A token for one workspace cannot post to another, which keeps the isolation honest rather than conventional.

Deliver one deck per client​

Clients usually prefer a single branded document to five attachments. Run the reports first, then build and deliver that client's deck:

      - name: Run all reports for ${{ matrix.client.name }}
        run: uc-bq run-all --merchant=${{ matrix.client.id }} --no-analysis

      - name: Generate and deliver deck for ${{ matrix.client.name }}
        env:
          SLACK_BOT_TOKEN: ${{ secrets[format('SLACK_TOKEN_{0}', matrix.client.id)] }}
          EMAIL_FROM: ${{ vars.EMAIL_FROM }}
          SENDGRID_API_KEY: ${{ secrets.SENDGRID_API_KEY }}
        run: uc-bq deck run weekly-summary --merchant=${{ matrix.client.id }} --deliver --no-analysis
        continue-on-error: true

continue-on-error lets clients without that deck defined pass through without failing the job. Each client's deck YAML carries their own company name and logo for the cover page. See Decks and dashboards.

Add or remove a client​

To add one, register the merchant with uc-bq config add-merchant CLNT6 --taxonomy=medium, design their reports in Claude Code so the files land under reports/CLNT6/, add SLACK_TOKEN_CLNT6 to your secrets if they have their own workspace, add the client to the workflow matrix, then commit. The next scheduled run picks them up, delivery included.

To remove one, delete reports/CLNT6/, run uc-bq config remove-merchant CLNT6, drop the matrix entry, delete the secret, and commit. Ask them to remove your service account from their UltraCart account as well.

What it costs at scale​

Five clients with three reports each, running weekly:

ComponentWeeklyMonthly
GitHub ActionsAbout 10 minutesAbout 40 minutes, against a 2,000 minute free tier
BigQueryAbout $0.15About $0.60
Analysis with a small modelAbout $0.03About $0.12
Analysis with a large modelAbout $0.45About $1.80
Slack and emailFreeFree at this volume

That is roughly $0.60 a month with no analysis and about $2.40 with a large model writing every report. Twenty clients at five reports each still lands under $10 a month.

Was this page helpful?