Shopify data model: how orders, customers and products fit together
Trace a Shopify revenue figure back to the order, customer, payment and shipment rows behind it.
- Open source. OWOX Data Marts is open source: you can read the code that loads and models your data, and run it yourself.
- You own the data. Shopify tables load into your data warehouse, under your account. If you leave OWOX, they stay where they are.
- MCP-ready. Once the data is in, Claude or ChatGPT can work from your Data Marts through OWOX MCP.
Don't wire up Shopify by hand. Have your AI assistant load it into your data warehouse.
Your assistant sets up the Shopify connector with you and loads the tables you pick; OWOX keeps them in your data warehouse, ready to use.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /data-sources/shopify.md. The task: set up the Shopify connector in OWOX Data Marts, let me pick the tables I need, load them into my data warehouse and show me the first rows. Tag the links you give me with utm_campaign=ai-task and utm_term=data-source. Use what you know about my context and goals
Prefer to start on your own? Get started free
The Shopify data model
Orders is the centre of the Shopify model, and every revenue question starts there.
- Its
customerIdpoints to Customers; Tender Transactions, Fulfillment Orders and Order Metafields point back to it on the order id. - Products hold the catalog and Product Variants the sellable units: each variant names its product and, one to one, the Inventory Item behind its SKU.
- Fulfillment Orders also name the Locations they ship from, and Abandoned Checkouts record checkouts a customer started and did not complete.
- Blogs, Articles and Pages hold the store's content, and ten metafield tables, one for each kind of owner, carry the custom fields that apps and merchants add, joining on
ownerId. - Ids are Shopify global IDs, strings that begin with
gid://and name the object, not numbers. - What has no column of its own, such as line items, refunds and discount codes, stays as JSON text to unnest in SQL.
How the tables join (18)
Abandoned Checkouts joins to Customerson
customerId = idN:1 (many to one)The customer who started the checkout; may be empty.
Abandoned Checkouts schemaCustomers schema
Fields of Abandoned Checkouts as the connector defines them (GitHub)
Article Metafields joins to Articleson
ownerId = idN:1 (many to one)The row in Articles that the metafield is attached to.
Article Metafields schemaArticles schema
Fields of Article Metafields as the connector defines them (GitHub)
Articles joins to Blogson
blogId = idN:1 (many to one)The blog the article is published in.
Blog Metafields joins to Blogson
ownerId = idN:1 (many to one)The row in Blogs that the metafield is attached to.
Blog Metafields schemaBlogs schema
Fields of Blog Metafields as the connector defines them (GitHub)
Collection Metafields joins to Collectionson
ownerId = idN:1 (many to one)The row in Collections that the metafield is attached to.
Collection Metafields schemaCollections schema
Fields of Collection Metafields as the connector defines them (GitHub)
Customer Metafields joins to Customerson
ownerId = idN:1 (many to one)The row in Customers that the metafield is attached to.
Customer Metafields schemaCustomers schema
Fields of Customer Metafields as the connector defines them (GitHub)
Fulfillment Orders joins to Orderson
orderId = idN:1 (many to one)The order being fulfilled.
Fulfillment Orders schemaOrders schema
Fields of Fulfillment Orders as the connector defines them (GitHub)
Fulfillment Orders joins to Locationson
assignedLocationId = idN:1 (many to one)The location the shipment is assigned to.
Location Metafields joins to Locationson
ownerId = idN:1 (many to one)The row in Locations that the metafield is attached to.
Location Metafields schemaLocations schema
Fields of Location Metafields as the connector defines them (GitHub)
Order Metafields joins to Orderson
ownerId = idN:1 (many to one)The row in Orders that the metafield is attached to.
Order Metafields schemaOrders schema
Fields of Order Metafields as the connector defines them (GitHub)
Orders joins to Customerson
customerId = idN:1 (many to one)The customer who placed the order; empty for guest checkouts.
Page Metafields joins to Pageson
ownerId = idN:1 (many to one)The row in Pages that the metafield is attached to.
Page Metafields schemaPages schema
Fields of Page Metafields as the connector defines them (GitHub)
Product Metafields joins to Productson
ownerId = idN:1 (many to one)The row in Products that the metafield is attached to.
Product Metafields schemaProducts schema
Fields of Product Metafields as the connector defines them (GitHub)
Product Variant Metafields joins to Product Variantson
ownerId = idN:1 (many to one)The row in Product Variants that the metafield is attached to.
Product Variant Metafields schemaProduct Variants schema
Fields of Product Variant Metafields as the connector defines them (GitHub)
Product Variants joins to Productson
productId = idN:1 (many to one)The product the variant belongs to.
Product Variants schemaProducts schema
Fields of Product Variants as the connector defines them (GitHub)
Product Variants joins to Inventory Itemson
inventoryItemId = id1:1 (one to one)The stock record behind the variant. Each variant has one inventory item, so the join does not repeat rows.
Shop Metafields joins to Shopon
ownerId = idN:1 (many to one)The shop the metafield is attached to.
Shop Metafields schemaShop schema
Fields of Shop Metafields as the connector defines them (GitHub)
Tender Transactions joins to Orderson
orderId = idN:1 (many to one)The order the payment belongs to; empty when the order was deleted.
Tender Transactions schemaOrders schema
Fields of Tender Transactions as the connector defines them (GitHub)
Land the tables in your warehouse, then import the model as Data Marts so these joins become the definition every report shares.
Shopify revenue you can check against your books
Revenue questions about a Shopify store rarely stop at Shopify: finance wants the payments to match the books, and marketing wants the orders next to what the ads cost. Loading the store into your warehouse puts its orders, payments and refunds on the same side of a join as QuickBooks and your ad platforms.
- Payments you can reconcile. Tender Transactions record each movement of money with its currency, a remote payment reference and a flag for test transactions. Set them against the orders, and against your books on whatever reference the two sides share.
- Orders next to ad spend. Orders, with their source and customer, sit in the same data warehouse as the Facebook Ads, Google Ads and TikTok Ads data OWOX loads. Match them by day, using the store's time zone from the Shop table, and spend per day beside orders per day is one query.
- Custom fields as rows. A metafield attached to a product or an order arrives as a row in its owner's metafield table, ready to join on ownerId; the connector reads the first 250 metafields of each owner.
- Nested data, labelled. The Admin GraphQL API returns nested objects. The model shows which become tables of their own and which stay as JSON text, so you know what to unnest and where.
- Prices live on variants. The product row holds only the lowest and highest price, so price questions start at Product Variants, and the model draws the way from a product to its variants and their SKUs.
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
Shopify Abandoned Checkouts schema
20 fieldsTime seriesSchema on GitHubshopify_abandoned_checkouts
Checkout sessions that did not complete — customer info, items, and pricing for recovery campaigns.
Questions this table answers
- How many checkouts were abandoned each week, and what was their total value?
- Which countries have the most abandoned checkouts?
- How many abandoned checkouts belong to customers who have ordered before?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID for the abandoned checkout. |
abandonedCheckoutUrl | STRING | The URL for the buyer to recover their checkout. |
completedAt | TIMESTAMP | The date and time when the checkout was completed. |
createdAt | TIMESTAMP | The date and time when the checkout was created. |
updatedAt | TIMESTAMP | The date and time when the checkout was last updated. |
defaultCursor | STRING | A default cursor that returns the single next record, sorted ascending by ID. |
lineItemsQuantity | INTEGER | The total number of line items in the checkout. |
totalPriceSet | STRING | The total price of the checkout including taxes, discounts, and shipping (MoneyBag as JSON string). |
billingAddressCity | STRING | City from the billing address. |
billingAddressCountry | STRING | Country from the billing address. |
billingAddressProvince | STRING | Province or state from the billing address. |
billingAddressZip | STRING | Postal/ZIP code from the billing address. |
shippingAddressCity | STRING | City from the shipping address. |
shippingAddressCountry | STRING | Country from the shipping address. |
shippingAddressProvince | STRING | Province or state from the shipping address. |
shippingAddressZip | STRING | Postal/ZIP code from the shipping address. |
customerId | STRING | Customer globally-unique ID. |
customerFirstName | STRING | Customer first name. |
customerLastName | STRING | Customer last name. |
customerEmail | STRING | Customer email address. |
Shopify Article Metafields schema
9 fieldsSchema on GitHubshopify_metafield_articles
Custom metadata attached to articles — namespace, key, value, and type.
Questions this table answers
- Which metafield namespaces and keys exist on articles, and how many rows does each have?
- Which articles have a given metafield set, and to what value?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
namespace | STRING | The namespace of the metafield. |
key | STRING | The key of the metafield. |
value | STRING | The value of the metafield. |
type | STRING | The type of the metafield. |
ownerId | STRING | The ID of the owner resource. |
ownerType | STRING | The type of the owner resource. |
createdAt | TIMESTAMP | The date and time when the metafield was created. |
updatedAt | TIMESTAMP | The date and time when the metafield was last updated. |
Shopify Articles schema
15 fieldsSchema on GitHubshopify_articles
Blog articles — title, author, publication status, and timestamps.
Questions this table answers
- How many articles were published per blog and month?
- Which authors wrote the most articles?
- Which articles are still unpublished, and since when?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
title | STRING | The title of the article. |
handle | STRING | A unique, human-friendly string for the article URL. |
body | STRING | The text of the article body, complete with HTML markup. |
summary | STRING | A summary of the article, which can include HTML markup. |
authorName | STRING | The name of the author of the article. |
blogId | STRING | The ID of the blog containing the article. |
blogTitle | STRING | The title of the blog containing the article. |
isPublished | BOOLEAN | Whether or not the article is visible. |
publishedAt | TIMESTAMP | The date and time when the article became or will become visible. |
tags | STRING | A comma-separated list of tags. |
templateSuffix | STRING | The name of the template the article is using. |
imageUrl | STRING | The URL of the image associated with the article. |
createdAt | TIMESTAMP | The date and time when the article was created. |
updatedAt | TIMESTAMP | The date and time when the article was last updated. |
Shopify Blog Metafields schema
9 fieldsSchema on GitHubshopify_metafield_blogs
Custom metadata attached to blogs — namespace, key, value, and type.
Questions this table answers
- Which metafield namespaces and keys exist on blogs, and how many rows does each have?
- Which blogs have a given metafield set, and to what value?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
namespace | STRING | The namespace of the metafield. |
key | STRING | The key of the metafield. |
value | STRING | The value of the metafield. |
type | STRING | The type of the metafield. |
ownerId | STRING | The ID of the owner resource. |
ownerType | STRING | The type of the owner resource. |
createdAt | TIMESTAMP | The date and time when the metafield was created. |
updatedAt | TIMESTAMP | The date and time when the metafield was last updated. |
Shopify Blogs schema
6 fieldsSchema on GitHubshopify_blogs
Blogs in the store — title, handle, and template settings.
Questions this table answers
- How many blogs does the store have, and when was each last updated?
- Which blogs use a custom template?
- Which blog was created first?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
title | STRING | The title of the blog. |
handle | STRING | A unique, human-friendly string for the blog URL. |
templateSuffix | STRING | The name of the template the blog is using. |
createdAt | TIMESTAMP | The date and time when the blog was created. |
updatedAt | TIMESTAMP | The date and time when the blog was last updated. |
Shopify Collection Metafields schema
9 fieldsSchema on GitHubshopify_metafield_collections
Custom metadata attached to collections — namespace, key, value, and type.
Questions this table answers
- Which metafield namespaces and keys exist on collections, and how many rows does each have?
- Which collections have a given metafield set, and to what value?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
namespace | STRING | The namespace of the metafield. |
key | STRING | The key of the metafield. |
value | STRING | The value of the metafield. |
type | STRING | The type of the metafield. |
ownerId | STRING | The ID of the owner resource. |
ownerType | STRING | The type of the owner resource. |
createdAt | TIMESTAMP | The date and time when the metafield was created. |
updatedAt | TIMESTAMP | The date and time when the metafield was last updated. |
Shopify Collections schema
10 fieldsSchema on GitHubshopify_collections
Product collections — title, sort order, product count, and last updated.
Questions this table answers
- Which collections hold the most products?
- Which collections were updated most recently?
- How are collections sorted, and how many use each sort order?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
title | STRING | The title of the collection. |
handle | STRING | A unique, human-friendly string for the collection URL. |
description | STRING | The description of the collection, including any HTML tags and formatting. |
descriptionHtml | STRING | The description of the collection in HTML format. |
sortOrder | STRING | The order in which the products in the collection are displayed. |
templateSuffix | STRING | The name of the template the collection is using. |
productsCount | INTEGER | The number of products in the collection. |
imageUrl | STRING | The URL of the image associated with the collection. |
updatedAt | TIMESTAMP | The date and time when the collection was last updated. |
Shopify Customer Metafields schema
9 fieldsSchema on GitHubshopify_metafield_customers
Custom metadata attached to customers — namespace, key, value, and type.
Questions this table answers
- Which metafield namespaces and keys exist on customers, and how many rows does each have?
- Which customers have a given metafield set, and to what value?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
namespace | STRING | The namespace of the metafield. |
key | STRING | The key of the metafield. |
value | STRING | The value of the metafield. |
type | STRING | The type of the metafield. |
ownerId | STRING | The ID of the owner resource. |
ownerType | STRING | The type of the owner resource. |
createdAt | TIMESTAMP | The date and time when the metafield was created. |
updatedAt | TIMESTAMP | The date and time when the metafield was last updated. |
Shopify Customers schema
22 fieldsTime seriesSchema on GitHubshopify_customers
Customer accounts — contact info, marketing consent, order history, and lifetime spend.
Questions this table answers
- How many customers placed one order, two orders, or more, and what did each group spend?
- Which countries do customers with email marketing consent live in?
- How many customers were created each month, and how many of them have ordered?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
firstName | STRING | The customer first name. |
lastName | STRING | The customer last name. |
displayName | STRING | The full name of the customer. |
email | STRING | The customer email address. |
phone | STRING | The customer phone number. |
state | STRING | The state of the customer account. |
locale | STRING | The customer locale. |
taxExempt | BOOLEAN | Whether the customer is exempt from being charged taxes. |
verifiedEmail | BOOLEAN | Whether the customer has verified their email. |
emailMarketingConsentState | STRING | The current email marketing state for the customer. |
smsMarketingConsentState | STRING | The current SMS marketing state for the customer. |
numberOfOrders | INTEGER | The number of orders associated with this customer. |
amountSpent | STRING | The total amount spent by the customer (JSON string with amount and currencyCode). |
tags | STRING | A comma-separated list of tags associated with the customer. |
note | STRING | A note about the customer. |
defaultAddressCity | STRING | City from the default address. |
defaultAddressCountry | STRING | Country from the default address. |
defaultAddressProvince | STRING | Province from the default address. |
defaultAddressZip | STRING | Postal/ZIP code from the default address. |
createdAt | TIMESTAMP | The date and time when the customer was created. |
updatedAt | TIMESTAMP | The date and time when the customer was last updated. |
Shopify Discount Codes schema
12 fieldsSchema on GitHubshopify_discount_codes
Discount codes — type, status, usage limits, and validity window.
Questions this table answers
- Which discount codes are active now, and which expire this month?
- How many codes of each discount type exist?
- Which codes are close to their usage limit?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
code | STRING | The discount code string. |
discountType | STRING | The type of discount (DiscountCodeBasic, DiscountCodeBxgy, DiscountCodeFreeShipping). |
title | STRING | The title of the discount. |
status | STRING | The status of the discount. |
startsAt | TIMESTAMP | The date and time when the discount becomes active. |
endsAt | TIMESTAMP | The date and time when the discount expires. |
usageLimit | INTEGER | The maximum number of times the discount can be used. |
appliesOncePerCustomer | BOOLEAN | Whether the discount can only be used once per customer. |
asyncUsageCount | INTEGER | The number of times the discount has been used. |
createdAt | TIMESTAMP | The date and time when the discount was created. |
updatedAt | TIMESTAMP | The date and time when the discount was last updated. |
Shopify Fulfillment Orders schema
11 fieldsSchema on GitHubshopify_fulfillment_orders
Fulfillment orders — items to ship, assigned location, delivery method, and status.
Questions this table answers
- How many fulfillment orders are open, in progress or closed, and which locations hold the open ones?
- How long do fulfillment orders take from creation to closing, per location?
- Which orders were split into more than one fulfillment order?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
status | STRING | The status of the fulfillment order. |
requestStatus | STRING | The request status of the fulfillment order. |
orderId | STRING | The ID of the order associated with this fulfillment order. |
orderName | STRING | The name of the order. |
assignedLocationId | STRING | The ID of the location assigned to fulfill this order. |
assignedLocationName | STRING | The name of the assigned location. |
fulfillAt | TIMESTAMP | The date and time at which the fulfillment order will be ready to be fulfilled. |
deliveryMethod | STRING | The delivery method of the fulfillment order. |
createdAt | TIMESTAMP | The date and time when the fulfillment order was created. |
updatedAt | TIMESTAMP | The date and time when the fulfillment order was last updated. |
Shopify Inventory Items schema
9 fieldsSchema on GitHubshopify_inventory_items
Inventory items (SKUs) — tracking status, shipping requirements, and country of origin.
Questions this table answers
- Which inventory items are not tracked, and which of them require shipping?
- Which countries of origin appear most often across inventory items?
- Which items have no harmonized system code?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
sku | STRING | The unique SKU (stock keeping unit) of the inventory item. |
tracked | BOOLEAN | Whether inventory levels are tracked for the item. |
requiresShipping | BOOLEAN | Whether the item requires shipping. |
harmonizedSystemCode | STRING | The harmonized system code of the item. |
countryCodeOfOrigin | STRING | The ISO 3166-1 alpha-2 country code of where the item originated from. |
variantId | STRING | The ID of the variant associated with this inventory item. |
createdAt | TIMESTAMP | The date and time when the inventory item was created. |
updatedAt | TIMESTAMP | The date and time when the inventory item was last updated. |
Shopify Location Metafields schema
9 fieldsSchema on GitHubshopify_metafield_locations
Custom metadata attached to locations — namespace, key, value, and type.
Questions this table answers
- Which metafield namespaces and keys exist on locations, and how many rows does each have?
- Which locations have a given metafield set, and to what value?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
namespace | STRING | The namespace of the metafield. |
key | STRING | The key of the metafield. |
value | STRING | The value of the metafield. |
type | STRING | The type of the metafield. |
ownerId | STRING | The ID of the owner resource. |
ownerType | STRING | The type of the owner resource. |
createdAt | TIMESTAMP | The date and time when the metafield was created. |
updatedAt | TIMESTAMP | The date and time when the metafield was last updated. |
Shopify Locations schema
19 fieldsSchema on GitHubshopify_locations
Physical and virtual store locations — activity status, fulfillment capabilities, and address.
Questions this table answers
- Which locations are active and fulfill online orders?
- Which locations hold active inventory but do not ship it?
- How many locations are there per country?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
name | STRING | The name of the location. |
isActive | BOOLEAN | Whether the location is active. |
isPrimary | BOOLEAN | Whether the location is the primary location. |
isFulfillmentService | BOOLEAN | Whether the location is a fulfillment service location. |
fulfillsOnlineOrders | BOOLEAN | Whether the location can fulfill online orders. |
hasActiveInventory | BOOLEAN | Whether the location has active inventory. |
shipsInventory | BOOLEAN | Whether the location ships inventory. |
addressLine1 | STRING | The first line of the address. |
addressLine2 | STRING | The second line of the address. |
city | STRING | The city of the location. |
province | STRING | The province or state of the location. |
provinceCode | STRING | The province or state code. |
country | STRING | The country of the location. |
countryCode | STRING | The country code of the location. |
zip | STRING | The postal or ZIP code. |
phone | STRING | The phone number of the location. |
createdAt | TIMESTAMP | The date and time when the location was created. |
updatedAt | TIMESTAMP | The date and time when the location was last updated. |
Shopify Order Metafields schema
9 fieldsSchema on GitHubshopify_metafield_orders
Custom metadata attached to orders — namespace, key, value, and type.
Questions this table answers
- Which metafield namespaces and keys exist on orders, and how many rows does each have?
- Which orders have a given metafield set, and to what value?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
namespace | STRING | The namespace of the metafield. |
key | STRING | The key of the metafield. |
value | STRING | The value of the metafield. |
type | STRING | The type of the metafield. |
ownerId | STRING | The ID of the owner resource. |
ownerType | STRING | The type of the owner resource. |
createdAt | TIMESTAMP | The date and time when the metafield was created. |
updatedAt | TIMESTAMP | The date and time when the metafield was last updated. |
Shopify Orders schema
96 fieldsTime seriesSchema on GitHubshopify_orders
Orders placed in the store — financial and fulfillment status, customer info, totals, and timestamps.
Questions this table answers
- What were net sales, tax and shipping per month, and how much of it was refunded?
- Which discount codes brought in the most revenue, and what share of orders used one?
- How many orders sit in each financial and fulfillment status, and how old is the oldest unfulfilled one?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
name | STRING | The order name, generated by combining the order_number with the shop order prefix. |
email | STRING | The email address of the customer. |
phone | STRING | The phone number of the customer. |
displayFinancialStatus | STRING | The financial status of the order. |
displayFulfillmentStatus | STRING | The fulfillment status of the order. |
confirmed | BOOLEAN | Whether the order is confirmed. |
closed | BOOLEAN | Whether the order is closed. |
cancelledAt | TIMESTAMP | The date and time when the order was cancelled. |
cancelReason | STRING | The reason for cancellation. |
currencyCode | STRING | The currency of the order. |
subtotalPrice | NUMBER | The subtotal price of the order. |
totalPrice | NUMBER | The total price of the order. |
totalTax | NUMBER | The total tax of the order. |
totalShippingPrice | NUMBER | The total shipping price. |
totalDiscounts | NUMBER | The total discounts applied. |
discountCodes | STRING | Discount codes applied to the order as JSON array (code, amount, type). |
discountApplications | STRING | All discount applications (codes, automatic, manual, script) as JSON array, ordered by index. Shipping discounts allocate to shipping lines, not to lineItems discountAllocations. |
totalRefunded | NUMBER | The total amount refunded. |
totalOutstanding | NUMBER | The total amount not yet transacted for the order. |
totalWeight | NUMBER | The total weight of the order. |
fullyPaid | BOOLEAN | Whether the order is fully paid. |
taxesIncluded | BOOLEAN | Whether taxes are included in the order subtotal. |
taxExempt | BOOLEAN | Whether the order is tax exempt. |
customerId | STRING | The ID of the customer. |
customerEmail | STRING | The email of the customer. |
shippingAddressCity | STRING | City from the shipping address. |
shippingAddressCountry | STRING | Country from the shipping address. |
shippingAddressProvince | STRING | Province from the shipping address. |
shippingAddressZip | STRING | Postal/ZIP code from the shipping address. |
billingAddressCity | STRING | City from the billing address. |
billingAddressCountry | STRING | Country from the billing address. |
billingAddressProvince | STRING | Province from the billing address. |
billingAddressZip | STRING | Postal/ZIP code from the billing address. |
tags | STRING | A comma-separated list of tags. |
note | STRING | The note attached to the order. |
sourceName | STRING | The source of the order. |
checkoutToken | STRING | Token of the checkout that created the order. Useful for correlating the order with Web Pixel checkout events. NULL when no checkout is associated. |
cartToken | STRING | Token of the cart that was used to create the order. NULL when no cart is associated. |
processedAt | TIMESTAMP | The date and time when the order was processed. |
closedAt | TIMESTAMP | The date and time when the order was closed. |
createdAt | TIMESTAMP | The date and time when the order was created. |
updatedAt | TIMESTAMP | The date and time when the order was last updated. |
customer | STRING | Full customer object as JSON (id, email, phone, defaultAddress). |
currentTotalDiscounts | NUMBER | The current total discounts (after edits). |
currentTotalTax | NUMBER | The current total tax (after edits). |
number | INTEGER | The order number used to generate the order name (with the shop's order number prefix/suffix). Not guaranteed to be unique. |
confirmationNumber | STRING | A customer-facing order identifier, shown in some surfaces instead of the sequential order name. Not guaranteed to be unique. |
legacyResourceId | STRING | The ID of the corresponding resource in the REST Admin API. |
test | BOOLEAN | Whether the order was placed in test mode. |
edited | BOOLEAN | Whether the order has had any edits applied after creation. |
originalTotalPrice | NUMBER | The total price of the order at the time it was created. |
currentTotalPrice | NUMBER | The total price of the order, after returns. |
netPayment | NUMBER | The net payment for the order (total received minus total refunded). |
totalReceived | NUMBER | The total amount received from the customer before returns. |
totalCapturable | NUMBER | The authorized amount that hasn't been captured yet. |
totalTipReceived | NUMBER | The sum of all tip amounts for the order. |
currentSubtotalPrice | NUMBER | The subtotal price of the order, after returns and refunds. |
cartDiscountAmount | NUMBER | The total order-level (cart) discount amount applied at the time the order was created. Does not include line-level discounts. |
currentCartDiscountAmount | NUMBER | The current order-level (cart) discount amount after returns, refunds, order edits and cancellations. Does not include line-level discounts. |
currentShippingPrice | NUMBER | The current shipping price, after refunds and discounts. |
totalRefundedShipping | NUMBER | The total amount of shipping that was refunded. |
currentTotalDuties | NUMBER | The current total import duties for the order, after returns or modifications. |
originalTotalDuties | NUMBER | The total import duties calculated when the order was created. |
currentTotalAdditionalFees | NUMBER | The current total of all additional fees for the order. |
originalTotalAdditionalFees | NUMBER | The total of all additional fees calculated when the order was created. |
refundDiscrepancy | NUMBER | The difference between the suggested and the actual refunded amount. Positive values favor the merchant, negative values favor the customer. |
presentmentCurrencyCode | STRING | The currency the customer used when placing the order. Amount fields in this table are in the shop currency (currencyCode). |
unpaid | BOOLEAN | Whether no payments have been made for the order. |
refundable | BOOLEAN | Whether the order can be refunded based on its payment transactions. |
capturable | BOOLEAN | Whether an authorized payment on the order can be captured. |
canMarkAsPaid | BOOLEAN | Whether the order can be manually marked as paid. |
paymentGatewayNames | STRING | The names of all payment gateways used for the order, comma-separated. |
estimatedTaxes | BOOLEAN | Whether taxes on the order are estimated. |
fulfillable | BOOLEAN | Whether the order has any line items that can be fulfilled. |
requiresShipping | BOOLEAN | Whether the order requires physical shipping. |
subtotalLineItemsQuantity | INTEGER | The sum of quantities for all line items contributing to the subtotal. |
currentSubtotalLineItemsQuantity | INTEGER | The current sum of quantities for all line items contributing to the subtotal, after returns. |
currentTotalWeight | NUMBER | The total weight of the order in grams, after returns and refunds. |
restockable | BOOLEAN | Whether any line items on the order can be restocked into inventory. |
canNotifyCustomer | BOOLEAN | Whether order notifications can be sent to the customer. |
statusPageUrl | STRING | The URL where the customer can check the order's current status. |
hasTimelineComment | BOOLEAN | Whether the merchant added a timeline comment to the order. |
merchantEditable | BOOLEAN | Whether the order can be edited by the merchant. |
merchantEditableErrors | STRING | Reasons why the order can't be edited, comma-separated. Empty when the order is editable. |
dutiesIncluded | BOOLEAN | Whether duties are included in the subtotal price of the order. |
poNumber | STRING | The purchase order number associated with the order. |
sourceIdentifier | STRING | A unique POS or third-party order identifier. |
registeredSourceUrl | STRING | The URL of the source the order originated from. |
productNetwork | BOOLEAN | Whether the customer purchased items from other stores in the same network. |
customerAcceptsMarketing | BOOLEAN | Whether the customer agreed to receive marketing emails at the time of purchase. |
customerLocale | STRING | The customer's language and region preference at the time of purchase. |
billingAddressMatchesShippingAddress | BOOLEAN | Whether the billing address matches the shipping address. False when either address is missing. |
lineItems | STRING | The line items in the order as JSON array, with discount amounts and per-discount allocations (discountAllocations; join to discountApplications by index). See the connector guide for how the amounts differ. |
shippingLines | STRING | Shipping lines as JSON array. |
refunds | STRING | Refunds as JSON array. |
Shopify Page Metafields schema
9 fieldsSchema on GitHubshopify_metafield_pages
Custom metadata attached to pages — namespace, key, value, and type.
Questions this table answers
- Which metafield namespaces and keys exist on pages, and how many rows does each have?
- Which pages have a given metafield set, and to what value?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
namespace | STRING | The namespace of the metafield. |
key | STRING | The key of the metafield. |
value | STRING | The value of the metafield. |
type | STRING | The type of the metafield. |
ownerId | STRING | The ID of the owner resource. |
ownerType | STRING | The type of the owner resource. |
createdAt | TIMESTAMP | The date and time when the metafield was created. |
updatedAt | TIMESTAMP | The date and time when the metafield was last updated. |
Shopify Pages schema
10 fieldsSchema on GitHubshopify_pages
Static store pages — title, handle, publication status, and timestamps.
Questions this table answers
- Which pages are unpublished?
- Which pages changed most recently?
- How many pages use a custom template?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
title | STRING | The title of the page. |
handle | STRING | A unique, human-friendly string for the page URL. |
body | STRING | The body content of the page. |
bodySummary | STRING | A summary of the body content. |
isPublished | BOOLEAN | Whether the page is published. |
templateSuffix | STRING | The name of the template the page is using. |
publishedAt | TIMESTAMP | The date and time when the page was published. |
createdAt | TIMESTAMP | The date and time when the page was created. |
updatedAt | TIMESTAMP | The date and time when the page was last updated. |
Shopify Product Metafields schema
9 fieldsSchema on GitHubshopify_metafield_products
Custom metadata attached to products — namespace, key, value, and type.
Questions this table answers
- Which metafield namespaces and keys exist on products, and how many rows does each have?
- Which products have a given metafield set, and to what value?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
namespace | STRING | The namespace of the metafield. |
key | STRING | The key of the metafield. |
value | STRING | The value of the metafield. |
type | STRING | The type of the metafield. |
ownerId | STRING | The ID of the owner resource. |
ownerType | STRING | The type of the owner resource. |
createdAt | TIMESTAMP | The date and time when the metafield was created. |
updatedAt | TIMESTAMP | The date and time when the metafield was last updated. |
Product Variant Metafields schema
Shopify Product Variant Metafields schema
9 fieldsSchema on GitHubshopify_metafield_product_variants
Custom metadata attached to product variants — namespace, key, value, and type.
Questions this table answers
- Which metafield namespaces and keys exist on product variants, and how many rows does each have?
- Which product variants have a given metafield set, and to what value?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
namespace | STRING | The namespace of the metafield. |
key | STRING | The key of the metafield. |
value | STRING | The value of the metafield. |
type | STRING | The type of the metafield. |
ownerId | STRING | The ID of the owner resource. |
ownerType | STRING | The type of the owner resource. |
createdAt | TIMESTAMP | The date and time when the metafield was created. |
updatedAt | TIMESTAMP | The date and time when the metafield was last updated. |
Shopify Product Variants schema
18 fieldsSchema on GitHubshopify_product_variants
Product variants — SKU, price, inventory quantity, and availability.
Questions this table answers
- How many variants does each product have, and what is the price spread inside a product?
- Which variants are available for sale but have zero inventory quantity?
- Which variants are priced below their compare-at price, and by how much?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
title | STRING | The title of the variant. |
displayName | STRING | The display name of the variant. |
sku | STRING | The SKU of the variant. |
barcode | STRING | The barcode of the variant. |
price | NUMBER | The price of the variant. |
compareAtPrice | NUMBER | The compare at price of the variant. |
position | INTEGER | The position of the variant in the list. |
inventoryQuantity | INTEGER | The total inventory quantity. |
inventoryPolicy | STRING | The inventory policy. |
inventoryItemId | STRING | The ID of the inventory item. |
productId | STRING | The ID of the product. |
productTitle | STRING | The title of the product. |
taxable | BOOLEAN | Whether the variant is taxable. |
taxCode | STRING | The tax code. |
availableForSale | BOOLEAN | Whether the variant is available for sale. |
createdAt | TIMESTAMP | The date and time when the variant was created. |
updatedAt | TIMESTAMP | The date and time when the variant was last updated. |
Shopify Products schema
21 fieldsTime seriesSchema on GitHubshopify_products
Products in the store catalog — vendor, type, status, inventory, and price range.
Questions this table answers
- Which vendors and product types have the most active products?
- Which active products have no inventory left?
- How many products were published each month, and which are still drafts?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
title | STRING | The title of the product. |
handle | STRING | A unique, human-friendly string for the product URL. |
description | STRING | The description of the product. |
descriptionHtml | STRING | The description of the product in HTML format. |
vendor | STRING | The name of the product vendor. |
productType | STRING | The product type. |
status | STRING | The status of the product. |
tags | STRING | A comma-separated list of tags. |
templateSuffix | STRING | The name of the template the product is using. |
totalInventory | INTEGER | The total inventory across all variants. |
variantsCount | INTEGER | The number of variants. |
featuredImageUrl | STRING | The URL of the featured image. |
priceRangeMinAmount | NUMBER | The minimum price of the product. |
priceRangeMaxAmount | NUMBER | The maximum price of the product. |
seoTitle | STRING | The SEO title. |
seoDescription | STRING | The SEO description. |
onlineStoreUrl | STRING | The URL of the product on the online store. |
publishedAt | TIMESTAMP | The date and time when the product was published. |
createdAt | TIMESTAMP | The date and time when the product was created. |
updatedAt | TIMESTAMP | The date and time when the product was last updated. |
Shopify Shop schema
20 fieldsSchema on GitHubshopify_shop
Store settings — domain, currency, timezone, and plan details.
Questions this table answers
- Which currency, time zone and plan is the store set up with?
- Are taxes included in prices, and is shipping taxed?
- What is the store's primary domain and billing country?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
name | STRING | The name of the shop. |
email | STRING | The email address of the shop. |
myshopifyDomain | STRING | The myshopify.com domain of the shop. |
primaryDomain | STRING | The primary domain of the shop. |
currencyCode | STRING | The currency code of the shop. |
timezoneAbbreviation | STRING | The timezone abbreviation. |
ianaTimezone | STRING | The IANA timezone. |
weightUnit | STRING | The default weight unit. |
billingAddressCity | STRING | City from the billing address. |
billingAddressCountry | STRING | Country from the billing address. |
billingAddressProvince | STRING | Province from the billing address. |
billingAddressZip | STRING | Postal/ZIP code from the billing address. |
plan | STRING | The shop plan name. |
contactEmail | STRING | The contact email of the shop. |
customerAccountsV2 | STRING | The customer accounts version. |
taxesIncluded | BOOLEAN | Whether taxes are included in prices. |
taxShipping | BOOLEAN | Whether shipping is taxed. |
createdAt | TIMESTAMP | The date and time when the shop was created. |
updatedAt | TIMESTAMP | The date and time when the shop was last updated. |
Shopify Shop Metafields schema
9 fieldsSchema on GitHubshopify_metafield_shops
Custom metadata attached to the shop — namespace, key, value, and type.
Questions this table answers
- Which metafield namespaces and keys exist on the shop, and how many rows does each have?
- What values do the shop's metafields hold, by namespace and key?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
namespace | STRING | The namespace of the metafield. |
key | STRING | The key of the metafield. |
value | STRING | The value of the metafield. |
type | STRING | The type of the metafield. |
ownerId | STRING | The ID of the owner resource. |
ownerType | STRING | The type of the owner resource. |
createdAt | TIMESTAMP | The date and time when the metafield was created. |
updatedAt | TIMESTAMP | The date and time when the metafield was last updated. |
Shopify Tender Transactions schema
7 fieldsSchema on GitHubshopify_tender_transactions
Payment tender transactions — amount, currency, order reference, and processing time.
Questions this table answers
- How much money moved per day, by currency, excluding test transactions?
- Which orders have no tender transaction?
- How many test transactions are mixed into the data?
| Field | Type | Description |
|---|---|---|
idPK | STRING | A globally-unique ID. |
amount | NUMBER | The amount of the tender transaction. |
currencyCode | STRING | The currency code. |
remoteReference | STRING | The remote gateway reference. |
test | BOOLEAN | Whether the transaction is a test transaction. |
orderId | STRING | The ID of the order. |
processedAt | TIMESTAMP | The date and time when the transaction was processed. |
What Shopify data answers
Across the tables
- How do orders, refunds and discounts add up to net sales per month and per customer?
- Which customers placed repeat orders, and which started a checkout they never finished?
- Which products and variants sell, and what stock record and location stand behind them?
Where Shopify data goes
Data warehouses
AI assistants
Spreadsheets
Dashboards
Messages
API
- 0.41.0Rotating a connector key is one change: Sources share reusable Credentials
- 0.38.0Shopify connector: more orders fields, including line-item discounts
- 0.33.0Checkout and cart tokens for Shopify orders
- 0.32.0Google Ads, LinkedIn Ads, LinkedIn Pages, and Shopify connectors use supported API versions
- 0.28.0Shopify totalOutstanding field
You are booked. The invitation is on its way to , with the calendar invite and the meeting link.