Skip to main content
Reference

BigQuery Sample Queries

Overview​

These 61 queries run against the UltraCart Data Warehouse (BigQuery) as written, once you point them at your own project. They are grouped into ten pages by subject, so you can read a whole page on auto orders or upsells rather than scrolling one long list. Every query on every page is listed under Find a query below.

Nothing here is a starting template you have to finish. Each query returns a result on its own, and each one names what it returns before the SQL, so you can decide whether it answers your question without reading the whole statement first.

If you would rather describe the report you want in plain English than write SQL, the in-app AI-Powered Report Builder generates the query for you, and AI Reporting with Claude Code does the same from the command line and keeps the result in version control.

Before you run a query​

Three things differ between these examples and your account.

The project and dataset names. The examples were written at different times against different accounts, so they refer to their tables in several ways: `my-data-warehouse.my_dataset.uc_orders`, `my-data-warehouse.ultracart_dw.uc_orders`, or a bare `ultracart_dw.uc_orders`. Replace the project and dataset with your own before running anything. Your project ID follows the pattern ultracart-dw-{merchantid}, and you will find it under Configuration → Developer → Data Warehouse (BigQuery).

The dataset decides what you can see. The dataset name carries the data access level, so ultracart_dw_medium exposes plain-text customer email where ultracart_dw exposes only billing.email_hash. You can query only the datasets your account has been granted, so a query that names a higher level than you hold will not run. The Data Sets and Streaming section of the parent page maps each dataset to its level.

Queries cost money to run. BigQuery bills for bytes scanned, so a query over the full order or session history is not free. Cohort and analytics-session queries are the expensive ones here. Check the byte estimate the BigQuery console shows for a query before running an unfamiliar one against a large account.

note

If you are writing your own queries against these tables, learn CROSS JOIN UNNEST and the rest of Google's array operations first. UltraCart data is deeply nested: an order holds its items, payments, and coupons as arrays inside the order row, and almost every query in this library flattens one of them.

Find a query​

Each page below opens with what its tables hold and how they nest, then lists its queries.

Order and Revenue Queries​

Auto Order Queries​

Cohort and Churn Queries​

Combined Auto Order Cohort Analysis​

Customer, LTV, and Loyalty Queries​

Affiliate, UTM, and Analytics Queries​

StoreFront and Upsell Queries​

Item and Inventory Queries​

Email Delivery and Engagement Queries​

Project Administration Queries​

Was this page helpful?