2024 is the year of the connector - vote here

Explore the Shopify API 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 the Shopify API 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 sync your Shopify API 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

Collection

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

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
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 -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
ProductID long Product → ID -
CollectionID long Collection → ID -
CreatedAt datetime - -
UpdatedAt datetime - -

Customer

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

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
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 - -

CustomerAddress

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

Column Type Relates to Notes
RemoteID string - -
CustomerRemoteID string Customer → RemoteID -
WhenUpsertedIntoDataStore datetime - -
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 - -

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 -
WhenUpsertedIntoDataStore datetime - -
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 - -

InventoryLevel

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

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
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 - -
WhenUpsertedIntoDataStore datetime - -
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 - -

Order

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

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
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 - -
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 - -
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 -
WhenUpsertedIntoDataStore datetime - -
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 - -

Product

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

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
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 - -

ProductImage

Column Type Relates to Notes
RemoteID string - -
ProductRemoteID string Product → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
ProductID long Product → ID -
Position int - -
Width string - -
Height string - -
Src string - -
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 -
WhenUpsertedIntoDataStore datetime - -
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 - -

Shop

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

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
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 - -

SmartCollection

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

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
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 -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ID long - -
ProductID long Product → ID -
SmartCollectionID long SmartCollection → ID -
CreatedAt datetime - -
UpdatedAt datetime - -

TenderTransaction

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

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
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 - -

FulfillmentLineItem

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

OrderTaxLine

Column Type Relates to Notes
RemoteID string - -
OrderRemoteID string Order → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
OrderID long Order → ID -
ChannelLiable boolean - -
Price decimal - -
Rate decimal - -
Title string - -
ShopAmount decimal - -
ShopCurrencyCode string - -
PresentmentAmount decimal - -
PresentmentCurrencyCode string - -

OrderLineItemTaxLine

Column Type Relates to Notes
RemoteID string - -
OrderLineItemRemoteID string OrderLineItem → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
OrderLineItemID long OrderLineItem → ID -
ChannelLiable boolean - -
Price decimal - -
Rate decimal - -
Title string - -
ShopAmount decimal - -
ShopCurrencyCode string - -
PresentmentAmount decimal - -
PresentmentCurrencyCode string - -

ProductVariant

Column Type Relates to Notes
RemoteID string - -
ProductRemoteID string Product → RemoteID -
WhenUpsertedIntoDataStore datetime - -
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 - -

ProductTag

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

ProductOption

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

RefundLineItem

Column Type Relates to Notes
RemoteID string - -
RefundRemoteID string Refund → RemoteID -
WhenUpsertedIntoDataStore datetime - -
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