Skip to main content
How-to

Join UltraCart Data with External BigQuery Projects

Overview​

UltraCart's warehouse knows what you sold. It does not know what you spent to sell it. Registering one of your own BigQuery projects with uc-bq puts both in reach of a single query, so a report can put revenue and ad spend on the same axis.

Any BigQuery project works: a Funnel.io or Fivetran destination, a dbt warehouse, a Google Ads transfer, or a dataset your own systems write to. You choose exactly which datasets and tables to expose, and the rest of the project stays invisible to the tool.

Before you begin​

The Google account or service account you authenticate with needs read access on the external project as well as on your UltraCart warehouse. In the external project, grant it roles/bigquery.dataViewer. UltraCart does not provision this: it is your project, so the access is yours to grant.

Both projects also have to be reachable from the same credentials, since uc-bq authenticates once per run.

Explore before you register​

You can browse any project you have access to without touching the config. Start with its datasets:

uc-bq schema --project=my-marketing-warehouse

List the tables in one of them:

uc-bq schema --project=my-marketing-warehouse --dataset=google_ads_data --list

Then look at the columns of the table you actually want:

uc-bq schema --project=my-marketing-warehouse --dataset=google_ads_data --tables=funnel_data

This is the fastest way to find out whether a table carries a join key that lines up with your UltraCart data before committing to it.

Register the project​

Register the project under a short alias, then add the datasets and tables you want visible:

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

uc-bq config add-dataset marketing google_ads_data

uc-bq config add-tables marketing google_ads_data funnel_data

The alias is what you and Claude Code refer to afterwards, so keep it short and descriptive. add-dataset accepts --discover to add every table in the dataset at once, which is convenient for a small dataset and unhelpful for a large one.

The same result written directly into .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"]
          }
        }
      }
    }
  }
}

External projects are configured per merchant, so in a multi-merchant setup each client can have their own advertising warehouse. See Managing multiple merchants.

note

uc-bq config reads and writes the config in the current working directory. Run these commands from the directory holding .ultracart-bq.json.

Confirm it worked​

Registered tables appear alongside your UltraCart tables:

uc-bq schema --list

They are addressed as alias.dataset.table, so the table above is marketing.google_ads_data.funnel_data.

Build a report across both​

Claude Code sees external tables in the schema the same way it sees UltraCart tables, so a question that spans both needs no special phrasing:

Show me ROAS by Google Ads campaign for last month, joining ad spend from the
marketing project against order revenue

It works out the join, writes SQL against both projects, and saves the result as an ordinary report you can replay and schedule like any other.

Keep the schema cache current​

External table schemas are cached under .ultracart-bq-cache/ so repeated lookups do not hit BigQuery metadata every time. After a schema change upstream, clear it:

uc-bq schema --refresh

The cache directory is generated and belongs in .gitignore.

Adjust or remove what is exposed​

uc-bq config add-tables marketing meta_ads_data campaigns ad_sets ads
uc-bq config remove-tables marketing meta_ads_data ads
uc-bq config remove-dataset marketing meta_ads_data
uc-bq config remove-project marketing

Removing a project only removes it from your config. Nothing in the external project itself is altered, and the underlying access grant stays in place until you revoke it in Google Cloud.

Troubleshooting​

An Access Denied error naming the external project means the credentials you authenticated with lack roles/bigquery.dataViewer on that project, which is separate from your UltraCart access. Other errors are covered in Troubleshooting.

Was this page helpful?