Explore the Shopify data model

See how the various Shopify endpoints relate to each other, using our handy Entity Relationship Diagram.

Try our interactive ERD Or, read the docs below

Pulling data from Shopify is only the first part of your reporting. You also need to understand the relationships between different endpoints - something that APIs typically fail to convey.

At SyncHub, we not only pull your Shopify endpoints down into a reportable format, we also describe their relationships (e.g. in terms of foreign keys) so that you can quickly get a grasp of your data and how it fits together. This page is intended as technical documentation of these relationships.

Endpoints

Customer

Documentation: https://shopify.dev/docs/admin-api/rest/reference/customers/customer

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
Email string - -
AcceptsMarketing boolean - -
FirstName string - -
LastName string - -
OrdersCount int - -
State string -
  • 12 Aug 2020 Note that customers created manually in the Shopify backend are 'disabled' status by default. I don't think that this warrants a status of 'deleted' here though
TotalSpent decimal - -
LastOrderID long - -
Note string - -
VerifiedEmail boolean - -
MultipassIdentifier string - -
TaxExempt boolean - -
Phone string - -
Tags string - -
LastOrderName string - -
Currency string - -
AcceptsMarketingUpdatedAt datetime - -
MarketingOptInLevel string - -
DefaultAddressID long CustomerAddress → ID -
CreatedAt datetime - -
UpdatedAt datetime - -

InventoryLevel

Documentation: https://shopify.dev/docs/admin-api/rest/reference/inventory/inventorylevel

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
InventoryItemID long - -
LocationID long Location → ID -
Available decimal - -
CreatedAt datetime - -
UpdatedAt datetime - -

Location

Documentation: https://shopify.dev/docs/admin-api/rest/reference/inventory/location

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
Name string - -
Address1 string - -
Address2 string - -
City string - -
Zip string - -
Province string - -
Country string - -
Phone string - -
CountryCode string - -
CountryName string - -
ProvinceCode string - -
Legacy boolean - -
Active boolean - -
LocalizedCountryName string - -
LocalizedProvinceName string - -
CreatedAt datetime - -
UpdatedAt datetime - -

Product

Documentation: https://shopify.dev/docs/admin-api/rest/reference/products/product

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
Title string - -
BodyHtml string - -
Vendor string - -
ProductType string - -
Handle string - -
PublishedAt datetime - -
TemplateSuffix string - -
PublishedScope string - -
Tags string -
  • 19 Aug 2020 Have confirmed that Shopify does not allow commas in individual tags
CreatedAt datetime - -
UpdatedAt datetime - -

ProductVariant

Column Type Relates to Notes
RemoteID string - -
ProductRemoteID string Product → RemoteID -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
ProductID long - -
Title string - -
Price decimal - -
Sku string - -
Position int - -
InventoryPolicy string - -
CompareAtPrice decimal - -
FulfillmentService string - -
InventoryManagement string - -
Option1 string - -
Option2 string - -
Option3 string - -
Taxable boolean - -
Barcode string - -
Grams decimal - -
ImageID long - -
Weight decimal - -
WeightUnit string - -
InventoryItemID long - -
InventoryQuantity decimal - -
OldInventoryQuantity decimal - -
RequiresShipping boolean - -
CreatedAt datetime - -
UpdatedAt datetime - -

ProductOption

Column Type Relates to Notes
RemoteID string - -
ProductRemoteID string Product → RemoteID -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
Name string - -
ProductID long - -
Position int - -

ProductTag

Column Type Relates to Notes
RemoteID string - -
ProductRemoteID string Product → RemoteID -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ProductID long - -
Tag string - -

Order

Documentation: https://shopify.dev/docs/admin-api/rest/reference/orders/order

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
Name string - -
BillingAddressFirstName string - -
BillingAddressLastName string - -
BillingAddressName string - -
BillingAddressCompany string - -
BillingAddressCountry string - -
BillingAddressPhone string - -
BillingAddressProvince string - -
BillingAddressAddress1 string - -
BillingAddressAddress2 string - -
BillingAddressZip string - -
BillingAddressLatitude string - -
BillingAddressLongitude string - -
BillingAddressCountryCode string - -
BillingAddressProvinceCode string - -
MailingAddressFirstName string - -
MailingAddressLastName string - -
MailingAddressName string - -
MailingAddressCompany string - -
MailingAddressCountry string - -
MailingAddressPhone string - -
MailingAddressProvince string - -
MailingAddressAddress1 string - -
MailingAddressAddress2 string - -
MailingAddressZip string - -
MailingAddressLatitude string - -
MailingAddressLongitude string - -
MailingAddressCountryCode string - -
MailingAddressProvinceCode string - -
Email string - -
ClosedAt datetime - -
Number int - -
Note string - -
Token string - -
Gateway string - -
Test boolean - -
TotalPrice decimal - -
SubtotalPrice decimal - -
TotalWeight decimal - -
TotalTax decimal - -
TaxesIncluded boolean - -
Currency string - -
FinancialStatus string - -
Confirmed boolean - -
TotalDiscounts decimal - -
TotalLineItemsPrice decimal - -
CartToken string - -
BuyerAcceptsMarketing boolean - -
ReferringSite string - -
LandingSite string - -
CancelledAt datetime - -
CancelReason string - -
TotalPriceUsd decimal - -
CheckoutToken string - -
Reference string - -
UserID long - -
LocationID long - -
SourceIdentifier string - -
SourceUrl string - -
ProcessedAt datetime - -
DeviceID long - -
Phone string - -
CustomerLocale string - -
AppID long - -
BrowserIp string - -
LandingSiteRef string - -
OrderNumber int - -
ProcessingMethod string - -
CheckoutID long - -
SourceName string - -
FulfillmentStatus string - -
Tags string - -
ContactEmail string - -
OrderStatusUrl string - -
PresentmentCurrency string - -
TotalLineItemsPriceSetShopAmount decimal - -
TotalLineItemsPriceSetShopCurrencyCode string - -
TotalLineItemsPriceSetPresentmentAmount decimal - -
TotalLineItemsPriceSetPresentmentCurrencyCode string - -
TotalDiscountsSetShopAmount decimal - -
TotalDiscountsSetShopCurrencyCode string - -
TotalDiscountsSetPresentmentAmount decimal - -
TotalDiscountsSetPresentmentCurrencyCode string - -
TotalShippingPriceSetShopAmount decimal - -
TotalShippingPriceSetShopCurrencyCode string - -
TotalShippingPriceSetPresentmentAmount decimal - -
TotalShippingPriceSetPresentmentCurrencyCode string - -
SubTotalPriceSetShopAmount decimal - -
SubTotalPriceSetShopCurrencyCode string - -
SubTotalPriceSetPresentmentAmount decimal - -
SubTotalPriceSetPresentmentCurrencyCode string - -
TotalPriceSetShopAmount decimal - -
TotalPriceSetShopCurrencyCode string - -
TotalPriceSetPresentmentAmount decimal - -
TotalPriceSetPresentmentCurrencyCode string - -
TotalTaxSetShopAmount decimal - -
TotalTaxSetShopCurrencyCode string - -
TotalTaxSetPresentmentAmount decimal - -
TotalTaxSetPresentmentCurrencyCode string - -
CreatedAt datetime - -
UpdatedAt datetime - -

OrderLineItem

Documentation: https://shopify.dev/docs/storefront-api/reference/object/orderlineitem

Column Type Relates to Notes
RemoteID string - -
OrderRemoteID string Order → RemoteID -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
Name string - -
OrderID long Order → ID -
VariantID long - -
Title string - -
Quantity decimal - -
Sku string - -
VariantTitle string - -
Vendor string - -
FulfillmentService string - -
ProductID long Product → ID -
RequiresShipping boolean - -
Taxable boolean - -
GiftCard boolean - -
VariantInventoryManagement string - -
ProductExists boolean - -
FulfillableQuantity decimal - -
Grams decimal - -
Price decimal - -
TotalDiscount decimal - -
FulfillmentStatus string - -
PriceSetShopAmount decimal - -
PriceSetShopCurrencyCode string - -
PriceSetPresentmentAmount decimal - -
PriceSetPresentmentCurrencyCode string - -
TotalDiscountSetShopAmount decimal - -
TotalDiscountSetShopCurrencyCode string - -
TotalDiscountSetPresentmentAmount decimal - -
TotalDiscountSetPresentmentCurrencyCode string - -

Fulfillment

Documentation: https://shopify.dev/docs/admin-api/rest/reference/shipping-and-fulfillment/fulfillment

Column Type Relates to Notes
RemoteID string - -
OrderRemoteID string Order → RemoteID -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
Name string - -
OrderID long Order → ID -
Status string - -
Service string - -
TrackingCompany string - -
ShipmentStatus string - -
LocationID long Location → ID -
TrackingNumber string - -
TrackingUrl string - -
CreatedAt datetime - -
UpdatedAt datetime - -

FulfillmentLineItem

Column Type Relates to Notes
RemoteID string - -
FulfillmentRemoteID string Fulfillment → RemoteID -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long OrderLineItem → ID -
OrderID long Order → ID -
FulfillmentID long - -

SmartCollection

Documentation: https://shopify.dev/docs/admin-api/rest/reference/products/collection

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
Handle string - -
Title string - -
BodyHtml string - -
PublishedAt datetime - -
SortOrder string - -
TemplateSuffix string - -
PublishedScope string - -
ImageCreatedAt datetime - -
ImageAlt string - -
ImageWidth int - -
ImageHeight int - -
ImageSrc string - -
CreatedAt datetime - -
UpdatedAt datetime - -

SmartCollectionProduct

Documentation: https://shopify.dev/docs/admin-api/rest/reference/products/product

Column Type Relates to Notes
RemoteID string - -
SmartCollectionRemoteID string SmartCollection → RemoteID -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
ProductID long Product → ID -
SmartCollectionID long SmartCollection → ID -
CreatedAt datetime - -
UpdatedAt datetime - -

Collection

Documentation: https://shopify.dev/docs/admin-api/rest/reference/products/collection

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
Handle string - -
Title string - -
BodyHtml string - -
PublishedAt datetime - -
SortOrder string - -
TemplateSuffix string - -
ProductsCount int - -
ImageCreatedAt datetime - -
ImageAlt string - -
ImageWidth int - -
ImageHeight int - -
ImageSrc string - -
CreatedAt datetime - -
UpdatedAt datetime - -

CollectionProduct

Documentation: https://shopify.dev/docs/admin-api/rest/reference/products/product

Column Type Relates to Notes
RemoteID string - -
CollectionRemoteID string Collection → RemoteID -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
ProductID long Product → ID -
CollectionID long Collection → ID -
CreatedAt datetime - -
UpdatedAt datetime - -

Shop

Documentation: https://shopify.dev/docs/admin-api/rest/reference/store-properties/shop

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
Name string - -
Domain string - -
IanaTimezone string - -
Email string - -
Province string - -
Country string - -
Address1 string - -
Zip string - -
City string - -
Phone string - -
Latitude decimal - -
Longitude decimal - -
PrimaryLocale string - -
Address2 string - -
CountryCode string - -
CountryName string - -
Currency string - -
CustomerEmail string - -
Timezone string - -
ShopOwner string - -
MoneyFormat string - -
MoneyWithCurrencyFormat string - -
WeightUnit string - -
ProvinceCode string - -
TaxesIncluded boolean - -
TaxShipping boolean - -
CountyTaxes boolean - -
PlanDisplayName string - -
PlanName string - -
HasDiscounts boolean - -
HasGiftCards boolean - -
MyshopifyDomain string - -
GoogleAppsDomain string - -
GoogleAppsLoginEnabled boolean - -
MoneyInEmailsFormat string - -
MoneyWithCurrencyInEmailsFormat string - -
EligibleForPayments boolean - -
RequiresExtraPaymentsAgreement boolean - -
PasswordEnabled boolean - -
HasStorefront boolean - -
EligibleForCardReaderGiveaway boolean - -
Finances boolean - -
CookieConsentLevel string - -
VisitorTrackingConsentPreference string - -
ForceSsl boolean - -
CheckoutApiSupported boolean - -
MultiLocationEnabled boolean - -
SetupRequired boolean - -
PreLaunchEnabled boolean - -
CreatedAt datetime - -
UpdatedAt datetime - -

TenderTransaction

Documentation: https://shopify.dev/docs/admin-api/rest/reference/tendertransaction

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
OrderID long Order → ID -
Amount decimal - -
Currency string - -
UserID long -
  • 25 Aug 2020 Foreign Key - References `User` (not implemented)
Test boolean - -
ProcessedAt datetime - -
RemoteReference string - -
PaymentDetailsCreditCardNumber string - -
PaymentDetailsCreditCardCompany string - -
PaymentMethod string - -
UpdatedAt datetime - -
CreatedAt datetime - -

CustomerAddress

Documentation: https://shopify.dev/docs/admin-api/rest/reference/customers/customer

Column Type Relates to Notes
RemoteID string - -
CustomerRemoteID string Customer → RemoteID -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
Name string - -
CustomerID long Customer → ID -
FirstName string - -
LastName string - -
Company string - -
Address1 string - -
Address2 string - -
City string - -
Province string - -
Country string - -
Zip string - -
Phone string - -
ProvinceCode string - -
CountryCode string - -
CountryName string - -
Default boolean - -
CreatedAt datetime - -
UpdatedAt datetime - -

Refund

Documentation: https://shopify.dev/api/admin-rest/2022-01/resources/refund#resource-object

Column Type Relates to Notes
RemoteID string - -
OrderRemoteID string Order → RemoteID -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
OrderID long Order → ID -
Note string - -
UserID long - -
ProcessedAt datetime - -
Restock boolean - -
CreatedAt datetime - -
UpdatedAt datetime - -

RefundLineItem

Column Type Relates to Notes
RemoteID string - -
RefundRemoteID string Refund → RemoteID -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
RefundID long Refund → ID -
Quantity decimal - -
LineItemID long OrderLineItem → ID -
LocationID long Location → ID -
RestockType string - -
Subtotal decimal - -
TotalTax decimal - -
SubTotalShopMoneyAccount decimal - -
SubTotalShopMoneyCurrencyCode string - -
SubTotalPresentmentMoneyAccount decimal - -
SubTotalPresentmentMoneyCurrencyCode string - -
TotalTaxShopMoneyAccount decimal - -
TotalTaxShopMoneyCurrencyCode string - -
TotalTaxPresentmentMoneyAccount decimal - -
TotalTaxPresentmentMoneyCurrencyCode string - -

Need more?

SyncHub lives and breathes APIs and data. Keep exploring using the links below.

Connect your own Shopify data Interactive ERD Querying Shopify using regular SQL