Collect Shopify data in Google BigQuery
Load Shopify orders, customers, products and payments into your own BigQuery tables with OWOX Data Marts: one table per endpoint, a business-friendly schema, refreshed on a schedule. Join it with your accounting data so every team reports the same revenue.
Shopify orders in several currencies get added up as one number, and the monthly revenue figure is quietly wrong.
Paste this into your AI assistant. It loads Shopify orders into Google BigQuery tables with OWOX Data Marts and keeps currency, discounts and refunds as their own columns, so revenue is summed correctly.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /connectors/free-shopify-connector-to-google-bigquery.md. The task: load Shopify orders into Google BigQuery and report monthly revenue per currency code, with total price, discounts and refunds as separate columns and net revenue per currency. Tag the links you give me with utm_campaign=ai-task and utm_term=data-foundation. Use what you know about my context and goals
Shopify Endpoints
25 Shopify endpoints you can import. Each lands in its own table in your data warehouse.
Abandoned Checkouts
Checkout sessions that did not complete — customer info, items, and pricing for recovery campaigns.
20 fieldsTime seriesArticle Metafields
Custom metadata attached to articles — namespace, key, value, and type.
9 fieldsArticles
Blog articles — title, author, publication status, and timestamps.
15 fieldsBlog Metafields
Custom metadata attached to blogs — namespace, key, value, and type.
9 fieldsBlogs
Blogs in the store — title, handle, and template settings.
6 fieldsCollection Metafields
Custom metadata attached to collections — namespace, key, value, and type.
9 fieldsCollections
Product collections — title, sort order, product count, and last updated.
10 fieldsCustomer Metafields
Custom metadata attached to customers — namespace, key, value, and type.
9 fieldsCustomers
Customer accounts — contact info, marketing consent, order history, and lifetime spend.
22 fieldsTime seriesDiscount Codes
Discount codes — type, status, usage limits, and validity window.
12 fieldsFulfillment Orders
Fulfillment orders — items to ship, assigned location, delivery method, and status.
11 fieldsInventory Items
Inventory items (SKUs) — tracking status, shipping requirements, and country of origin.
9 fieldsLocation Metafields
Custom metadata attached to locations — namespace, key, value, and type.
9 fieldsLocations
Physical and virtual store locations — activity status, fulfillment capabilities, and address.
19 fieldsOrder Metafields
Custom metadata attached to orders — namespace, key, value, and type.
9 fieldsOrders
Orders placed in the store — financial and fulfillment status, customer info, totals, and timestamps.
96 fieldsTime seriesPage Metafields
Custom metadata attached to pages — namespace, key, value, and type.
9 fieldsPages
Static store pages — title, handle, publication status, and timestamps.
10 fieldsProduct Metafields
Custom metadata attached to products — namespace, key, value, and type.
9 fieldsProduct Variant Metafields
Custom metadata attached to product variants — namespace, key, value, and type.
9 fieldsProduct Variants
Product variants — SKU, price, inventory quantity, and availability.
18 fieldsProducts
Products in the store catalog — vendor, type, status, inventory, and price range.
21 fieldsTime seriesShop
Store settings — domain, currency, timezone, and plan details.
20 fieldsShop Metafields
Custom metadata attached to the shop — namespace, key, value, and type.
9 fieldsTender Transactions
Payment tender transactions — amount, currency, order reference, and processing time.
7 fields
Why join Shopify with your accounting and ad data
Ask what revenue was last month and Shopify gives one number while the accounting system gives another. Refunds, discounts, taxes, currencies and payment timing each move the figure. To see where the two differ, both have to sit in one warehouse.
- Reconcile Shopify orders with accounting records in one query
- Separate gross sales from discounts, refunds and cancellations
- Set daily orders beside daily ad spend
Step 1: Connect Shopify and pick the fields
Create a Data Mart, choose Shopify as the source, add your shop domain and Admin API access token, open an endpoint and tick only the fields you need.
- 25 endpoints and 337 fields to choose from
- Orders alone has 47 fields: totals, discounts, refunds, status, customer
Step 2: Load Shopify into a BigQuery table
The dataset and table in your storage are pre-filled, and you can change them. Run it and the first load lands in your own BigQuery project.
- Each endpoint gets its own table, not one table for the whole store
- Every run is logged in Run History
Step 3: Get a business-friendly output schema
After the first run you get the output schema: every field with a readable description. Your quality checks can read it too.
- Descriptions say what a field holds, for example the total amount refunded
- Finance and marketing read the same column names
Step 4: Keep the Shopify tables linked
Customers, orders and payment transactions stay separate tables, joined by ids. Together they become one Shopify model, ready to join to accounting.
- Orders carry customerId; tender transactions carry orderId
- Add accounting, ad spend and web analytics tables to the same model
Step 5: Schedule the refresh and backfill history
Set it once. A daily scheduled refresh keeps the tables current, and a manual backfill loads any past period.
- Daily runs with no pipeline to maintain
- Backfill order history the first time you connect
Step 6: Reuse the same table everywhere
One table feeds your model, , , assistants like Claude and ChatGPT, and scheduled AI insights in Slack or email.
- Define revenue once
- Every report reads the same table
- 0.43.0Parallel reads of a SQL Data Mart on Google BigQuery no longer fail with a rate limit error
- 0.41.0Rotating a warehouse key is one change: Storages share reusable Credentials
- 0.41.0Shared SQL logic stays in one place: build a Data Mart on top of another
- 0.41.0Rotating a connector key is one change: Sources share reusable Credentials
- 0.39.0Reload a year of history in one backfill, not twelve
Why OWOX for Shopify in BigQuery
Shopify reports, hand-built API scripts or a generic ETL tool each get the orders out. OWOX also keeps them yours, readable and ready to join to accounting.
- You own the data. It lands in your own data warehouse, in tables you control.
- A business-friendly schema. Readable field names and descriptions, not API identifiers.
- Ready for a data model. Separate tables per endpoint, joined by ids, built to connect to accounting, ad spend and web analytics.
- Maintained for you. No scripts to fix when Shopify changes its API.
- Free to self-host. The open-source OWOX Data Marts runs in your own infrastructure.
You are booked. The invitation is on its way to , with the calendar invite and the meeting link.