Shopify snapshot

A high-level look at Stitch's Shopify (v1.0) integration, including release status, useful links, and the features supported in Stitch.

STITCH
Release Status

Released

Supported By

Stitch

Stitch Plan

Free

Singer GitHub Repository

Shopify Repository

DATA SELECTION
Table Selection

Supported

Column Selection

Supported

REPLICATION SETTINGS
Anchor Scheduling

Supported

Table-level Reset

Unsupported

Configurable Replication Methods

Unsupported

TRANSPARENCY
Extraction Logs

Supported

Loading Reports

Supported

Connecting Shopify

Step 1: Add Shopify as a Stitch data source

  1. Sign into your Stitch account.
  2. On the Stitch Dashboard page, click the Add Integration button.

  3. Click the Shopify icon.

  4. Enter a name for the integration. This is the name that will display on the Stitch Dashboard for the integration; it’ll also be used to create the schema in your destination.

    For example, the name “Stitch Shopify” would create a schema called stitch_shopify in the destination. Note: Schema names cannot be changed after you save the integration.

  5. In the Shopify Shop field, enter the name of the shop you want to connect to Stitch. For example: If the shop URL was stitch-data.shopify.com, you’d enter stitch-data into this field.

Step 2: Define the historical sync

The Sync Historical Data setting will define the starting date for your Shopify integration. This means that data equal to or newer than this date will be replicated to your data warehouse.

Change this setting if you want to replicate data beyond Shopify’s default setting of 1 year. For a detailed look at historical replication jobs, check out the Syncing Historical SaaS Data guide.

Step 3: Create a replication schedule

In the Replication Frequency section, you’ll create the integration’s replication schedule. An integration’s replication schedule determines how often Stitch runs a replication job, and the time that job begins.

Stitch offers two methods of creating a replication schedule:

  • Replication Frequency: This method requires selecting the interval you want replication to run for the integration. Start times of replication jobs are based on the start time and duration of the previous job. Refer to the Replication Frequency documentation for more information and examples.
  • Anchor scheduling: Based on the Replication Frequency, or interval, you select, this method “anchors” the start times of this integration’s replication jobs to a time you select to create a predictable schedule. Anchor scheduling is a combination of the Anchor Time and Replication Frequency settings, which must both be defined to use this method. Additionally, note that:

    • A Replication Frequency of at least one hour is required to use anchor scheduling.
    • An initial replication job may not begin immediately after saving the integration, depending on the selected Replication Frequency and Anchor Time. Refer to the Anchor Scheduling documentation for more information.

To help prevent overages, consider setting the integration to replicate less frequently. See the Understanding and Reducing Your Row Usage guide for tips on reducing your usage.

Step 4: Authorize Stitch to access Shopify

  1. Next, you’ll be prompted to sign into your Shopify account. Enter your Shopify credentials.
  2. Click Log in.
  3. After the authorization process is successfully completed, you’ll be directed back to Stitch.
  4. Click All Done.

Step 5: Set tables and columns to replicate

To complete the setup, you’ll need to select the tables and columns you want to replicate to your data warehouse.

Check out the Schema section to learn more about the available tables in Shopify and how they replicate.

  1. In the list of tables that displays - or in the Tables to Replicate tab, if you skipped this step during setup - locate a table you want to replicate.
  2. To track a table, click the checkbox next to the table’s name. A green checkmark means the table is set to replicate.

  3. To track a column, click the checkbox next to the column’s name. A green checkmark means the column is set to replicate.

  4. Repeat this process for all the tables and columns you want to replicate.
  5. When finished, click the Finalize Your Selections button at the bottom of the screen to save your selections.

Note: If you change these settings while a replication job is still in progress, they will not be used until the next job starts.

Initial and historical replication jobs

After you finish setting up Shopify, its Sync Status may show as Pending on either the Stitch Dashboard or in the Integration Details page.

For a new integration, a Pending status indicates that Stitch is in the process of scheduling the initial replication job for the integration. This may take some time to complete.

Free historical data loads

The first seven days of replication, beginning when data is first replicated, are free. Rows replicated from the new integration during this time won’t count towards your quota. Stitch offers this as a way of testing new integrations, measuring usage, and ensuring historical data volumes don’t quickly consume your quota.


Shopify table schemas

abandoned_checkouts

Replication Method :

Key-based Incremental

Replication Key :

updated_at

Primary Key :

id

API endpoint :

Retrieve all abandoned checkouts

The abandoned_checkouts table contains info about abandoned checkouts. Shopify considers a checkout to be abandoned when a customer has entered billing and shipping details, but hasn’t completed the purchase.

id
INTEGER

The abandoned checkout ID.

updated_at
DATE-TIME

The date and time in ISO 8601 format when the checkout was last updated.

abandoned_checkout_url
STRING

The recovery URL that is sent to a customer so they can recover their checkout.

buyer_accepts_marketing
BOOLEAN

Indicates whether the customer wants to receive email updates from the shop.

cart_token
STRING

The ID of the cart that is attached to the checkout.

closed_at
DATE-TIME

The date and time in ISO 8601 format when the checkout was closed. If the checkout wasn’t closed, this value will be null.

completed_at
DATE-TIME

The date and time in ISO 8601 format when the checkout was completed. For abandoned checkouts, this value will always be null.

created_at
DATE-TIME

The date and time in ISO 8601 format when the checkout was created.

currency
STRING

The three-letter ISO 4217 code of the shop’s default currency at the time of checkout.

For the currency the customer used at checkout, see presentment_currency.

customer_id
INTEGER

The ID of the customer associated with the abandoned checkout.

customer_locale
STRING

The two or three-letter language code, optionally followed by a region modifier.

device_id
INTEGER

The ID of the Shopify POS device that created the checkout.

discount_codes

The discount codees applied to the checkout.

If your destination doesn't natively support nested data, this data may be denested into a subtable named abandoned_checkouts__discount_codes. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The abandoned checkout ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

amount
NUMBER

The amount of the discount.

code
STRING

The discount code.

type
STRING

The type of discount. Possible values are:

  • percentage
  • shipping
  • fixed_amount
email
STRING

The customer’s email address.

gateway
STRING

The payment gateway used by the checkout. For abandoned checkouts, this value will always be null.

landing_site
STRING

The URL for the page where the customer entered the shop.

line_items

A list of line items in the checkout.

If your destination doesn't natively support nested data, this data may be denested into a subtable named abandoned_checkouts__line_items. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The abandoned checkout ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

id
STRING

The line item ID.

applied_discount
INTEGER

The amount of the discount applied to the line item.

compare_at_price
STRING

The price of a line item before a sale or discount.

destination_location_id
INTEGER

The ID of the destination location.

discount_allocations

An ordered list of amounts allocated by discount applications. Each discount allocation is associated to a particular appliction.

If your destination doesn't natively support nested data, this data may be denested into a subtable named abandoned_checkouts__line_items__discount_allocations. Refer to the Singer schema for details on possible attributes.

fulfillable_quantity
INTEGER

The amount available to fulfill, calculated as follows:

quantity - max(refunded_quantity, fulfilled_quantity) - pending_fulfilled_quantity - open_fulfilled_quantity

fulfillment_service
STRING

The fulfillment service provider for the item.

fulfillment_status
STRING

The status of the item’s fulfillment. Possible values are:

  • fulfilled
  • null
  • partial
gift_card
BOOLEAN

Indicates if the line item is a gift card.

grams
INTEGER

The weight of the item in grams.

key
STRING

A unique identifier for the line item, constructed from the line item’s variant_id plus a hash of the line item’s properties, even if the item has no additional properties.

line_price
STRING

This field has been deprecated by Shopify.

name
STRING

The name of the line item.

origin_location_id
INTEGER

The ID of the origin location associated with the line item.

pre_tax_price
NUMBER

The pre-tax price of the item.

price
NUMBER

The price of the item before discounts were applied.

product_exists
BOOLEAN

Indicates if the product exists.

product_id
INTEGER

The product ID of the item.

Reference:

properties

Details about custom info for the item.

If your destination doesn't natively support nested data, this data may be denested into a subtable named abandoned_checkouts__line_items__properties. Refer to the Singer schema for details on possible attributes.

quantity
INTEGER

The number of items purchased.

requires_shipping
BOOLEAN

Indicates if the item requires shipping.

sku
STRING

The item’s SKU.

tax_code
STRING
tax_lines

Details about the line item’s tax lines, each of which details a tax applicable to this line item.

If your destination doesn't natively support nested data, this data may be denested into a subtable named abandoned_checkouts__line_items__tax_lines. Refer to the Singer schema for details on possible attributes.

taxable
BOOLEAN

Indicates if the item is taxable.

title
STRING

The title of the product.

total_discount
NUMBER

The total of any discounts applied to the line item.

variant_id
INTEGER

The product variant ID.

variant_inventory_management
STRING

The name of the inventory management system.

variant_title
STRING

The title of the product variant.

vendor
STRING

The name of the item’s supplier.

location_id
INTEGER

The ID of the physical location where the checkout was processed.

name
STRING

The order name as represented by a number.

note
STRING

An optional note attached to the order.

phone
STRING

The customer’s phone number.

presentment_currency
STRING

The three-letter ISO 4217 code of the currency that the customer used at checkout.

referring_site
STRING

The website that referred the cusomter to the shop.

shipping_lines

Details about the shipping methods associated with the order.

If your destination doesn't natively support nested data, this data may be denested into a subtable named abandoned_checkouts__shipping_lines. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The abandoned checkout ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

id
INTEGER

The shipping line ID.

carrier_identifier
STRING

The shipping carrier’s identifier.

code
STRING

A reference to the shipping method.

delivery_category
STRING
markup
STRING
phone
STRING

The phone number associated with the shipping method.

price
NUMBER

The price of the shipping method.

requested_fulfillment_service_id
STRING

A reference to the fulfillment service being requested for the shipping method. Present if the shipping method requires processing by a third party fulfillment service; null otherwise.

source
STRING

The channel where the checkout originated.

tax_lines

Details about the shipping line’s tax lines, each of which details a tax applicable to this shipping line.

If your destination doesn't natively support nested data, this data may be denested into a subtable named abandoned_checkouts__shipping_lines__tax_lines. Refer to the Singer schema for details on possible attributes.

title
STRING

The title of the shipping method.

validation_context
STRING
source
STRING

The channel where the checkout originated.

source_identifier
STRING
source_name
STRING

Where the checkout originated. Possible values are:

  • web
  • pos
  • iphone
  • android
source_url
STRING
subtotal_price
NUMBER

The price of the checkout before shipping and taxes.

tax_lines

Details about the taxes applicable to the checkout.

If your destination doesn't natively support nested data, this data may be denested into a subtable named abandoned_checkouts__tax_lines. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The abandoned checkout ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

compare_at
STRING
position
INTEGER
price
NUMBER

The amount of tax to be charged.

rate
NUMBER

The rate of tax to be applied.

source
STRING
title
STRING

The name of the tax.

zone
STRING
taxes_included
BOOLEAN

Indicates if taxes are included in the price.

token
STRING

A unique ID for the checkout.

total_discounts
NUMBER

The total amount of discounts to be applied.

total_line_items_price
NUMBER

The sum of the prices of all line items in the checkout.

total_price
NUMBER

The sum of the prices of all line items in the checkout, discounts, shipping costs, and taxes

total_tax
NUMBER

The sum of all the taxes applied to the checkout.

total_weight
INTEGER

The sum of all the weights in grams of the line items in the checkout.

user_id
INTEGER

The ID of the user who created the checkout.


Replication Method :

Key-based Incremental

Replication Key :

updated_at

Primary Key :

id

API endpoint :

Retrieve all collects for the shop

The collects table contains info about collects, which are used to manage relationships between products and custom collections. For every product in a custom collection, there’s a collect that tracks the ID of both the product and the custom collection.

id
INTEGER

The collect ID.

updated_at
DATE-TIME

The date and time in ISO 8601 format when the collect was last updated.

collection_id
INTEGER

The ID of the custom collection containing the product.

Reference:

created_at
DATE-TIME

The date and time in ISO 8601 format when the collect was created.

featured
BOOLEAN

Indicates whether the collect is featured.

position
INTEGER

The position of the product in a manually sorted custom collection.

product_id
INTEGER

The ID of the product in the custom collection.

Reference:

sort_value
STRING

The same value as position, but padded with leading zeroes to make it alphanumeric-sortable.


custom_collections

Replication Method :

Key-based Incremental

Replication Key :

updated_at

Primary Key :

id

API endpoint :

Retrieve a list of custom collections

The custom_collections table contains info about custom collections. A custom collection is a grouping of products that a merchant creates to make their store easier to browse.

id
INTEGER

The custom collection ID.

Reference:

updated_at
STRING

The date and time in ISO 8601 format when the custom collection was last updated.

body_html
STRING

The description of the custom collection in HTML format.

handle
STRING

A human-readable string for the custom collection, automatically generated from its title.

published_at
STRING

The date and time in ISO 8601 format when the custom collection was published. This will be null for a hidden custom collection.

published_scope
STRING

Indicates if the collection is published to the Point of Sale channel. Possible values are:

  • web - The custom collection is published to the Online Store channel, but not to the Point of Sale channel.
  • global - The custom collection is published to both the Online Store and Point of Sale channels.
sort_order
STRING

The order in which products in the custom collection appear. Possible values are:

  • alpha-asc - Alphabetically, in ascending order (A - Z).
  • alpha-desc - Alphabetically, in descending order (Z - A).
  • best-selling - By best-selling products.
  • created - By date created, in ascending order (oldest - newest).
  • created-desc - By date created, in descending order (newest - oldest).
  • manual - Order created by the shop owner.
  • price-asc - By price, in ascending order (lowest - highest).
  • price-desc - By price, in descending order (highest - lowest).
template_suffix
STRING

The suffix of the Liquid template being used. For example: If the value is custom, the collection is using the collection.custom.liquid template.

title
STRING

The name of the custom collection.


Replication Method :

Key-based Incremental

Replication Key :

updated_at

Primary Key :

id

API endpoint :

Retrieve a list of customers

The customers table contains info about the shop’s customers. This includes their contact details, order history, and email marketing preferences.

Customer metafield data

To replicate customer metafield data, you must set this table and the metafields table to replicate.

id
INTEGER

The customer ID.

Reference:

updated_at
DATE-TIME

The date and time in ISO 8601 format when the customer was last updated.

accepts_marketing
BOOLEAN

Indicates if the customer has consented to receive marketing emails.

addresses

A list of the 10 most recently updated addresses for the customer.

If your destination doesn't natively support nested data, this data may be denested into a subtable named customers__addresses. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The customer ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

address1
STRING

The street address.

address2
STRING

The second line of the street address.

city
STRING

The city, town, or village.

company
STRING

The company associated with the address.

country
STRING

The country associated with the address.

country_code
STRING

The two-letter country code corresponding to the country.

country_name
STRING

The normalized country name.

customer_id
INTEGER

The customer’s ID.

Reference:

default
BOOLEAN

Indicates if the address is the default address for the customer.

first_name
STRING

The customer’s first name.

id
INTEGER

The address ID.

last_name
STRING

The customer’s last name.

name
STRING

The customer’s first and last names.

phone
STRING

The customer’s phone number at this address.

province
STRING

The customer’s region name.

province_code
STRING

The two-letter province code for the region.

zip
STRING

The customer’s postal or zip code.

created_at
DATE-TIME

The date and time in ISO 8601 format when the customer was created.

email
STRING

The customer’s email address.

first_name
STRING

The customer’s first name.

last_name
STRING

The customer’s last name.

last_order_id
INTEGER

The ID of the customer’s last order.

Reference:

last_order_name
STRING

The name of the customer’s last order.

multipass_identifier
STRING

The ID of the cu stomer’s Multipass login.

note
STRING

A note about the customer.

orders_count
INTEGER

The number of orders associated with the customer.

phone
STRING

The phone number for the customer, in E.164 format.

state
STRING

The state of the customer’s account with a shop. Possible values are:

  • disabled - The customer doesn’t have an active account.
  • invited - The customer has received an email invite to create an account.
  • enabled - The customer has created an account.
  • declined - The customer has declined the eamil invite to create an account.
tags
STRING

Tags that the shop owner has attached to the customer.

tax_exempt
BOOLEAN

Indicates if the customer is exempt from paying taxes on their order. If true, taxes will not be applied to an order at checkout.

total_spent
STRING

The total amount of money that the customer has spent across their order history.

verified_email
BOOLEAN

Indicates if the customer has verified their email address.


Replication Method :

Key-based Incremental

Replication Key :

updated_at

Primary Key :

id

API endpoint :

Retrieve a list of metafields that belong to a resource

The metafields table contains info about resource metafields. These are arbitrary fields used to store additional information about resources.

Metafield replication and resource types

By default, this table will include only shop-level metafield data. To replicate the metafields for a given resource type, this table and the table for the resource must be set to replicate.

For example: To replicate metafield data for Orders, the orders table must also be set to replicate.

Metafield data is available for customers, products, and orders.

id
INTEGER

The metafield ID.

updated_at
DATE-TIME

The date and time in ISO 8601 format when the metafield was last updated.

created_at
DATE-TIME

The date and time in ISO 8601 format when the customer was created.

description
STRING

A description of the info that the metafield contains.

key
STRING

The name of the metafield.

namespace
STRING

A container for a set of metafields.

owner_id
INTEGER

The ID of the resource that the metafield is attached to.

owner_resource
STRING

The type of resource that the metafield is attached to. Possible values are:

  • Customer
  • Order
  • Product
value
INTEGER, OBJECT, STRING

The information to be stored as metadata.

value_type
STRING

The metafield’s information type. Possible values are:

  • string
  • integer
  • json_strong

Replication Method :

Key-based Incremental

Replication Key :

created_at

Primary Key :

id

API endpoint :

Retrieve all refunds from a specific order

The order_refunds table contains info about refunds associated with orders.

id
INTEGER

The refund ID.

Reference:

created_at
STRING

The date and time in ISO 8601 format when the refund was created.

note
STRING

An optional note attached to a refund.

order_id
INTEGER

The ID of the order the refund is associated with.

Reference:

processed_at
STRING

The date and time in ISO 8601 format when the refund was processed.

refund_line_items

Details about the line items associated with the refund.

If your destination doesn't natively support nested data, this data may be denested into a subtable named order_refunds__refund_line_items. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The refund ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

id
INTEGER

The ID of the refund line item.

line_item__id
INTEGER

The line item ID.

line_item__discount_allocations

An ordered list of amounts allocated by discount applications. Each discount allocation is associated to a particular appliction.

If your destination doesn't natively support nested data, this data may be denested into a subtable named order_refunds__refund_line_items__line_item__discount_allocations. Refer to the Singer schema for details on possible attributes.

line_item__fulfillable_quantity
INTEGER

The amount available to fulfill, calculated as follows:

quantity - max(refunded_quantity, fulfilled_quantity) - pending_fulfilled_quantity - open_fulfilled_quantity

line_item__fulfillment_service
STRING

The service provider that fulfilled the item. Possible values are manual or the name of the provider, such as amazon or shipwire

line_item__fulfillment_status
STRING

The line item’s fulfillment status. Possible values are:

  • shipped
  • partial
  • unshipped
line_item__gift_card
BOOLEAN

Indicates whether the line item is a gift card.

line_item__grams
INTEGER

The weight of the item in grams.

line_item__name
STRING

The name of the product variant.

line_item__pre_tax_price
STRING

The pre-tax price of the item.

line_item__pre_tax_price_set
OBJECT

Presentment and shop money details associated with the pre-tax price for the line item.

line_item__price
STRING

The price of the item before discounts were applied.

line_item__price_set
OBJECT

Presentment and shop money details associated with the price for the line item.

line_item__product_exists
BOOLEAN

Indicates whether the product exists.

line_item__product_id
INTEGER

The product ID.

line_item__properties

Details about custom info for the item.

If your destination doesn't natively support nested data, this data may be denested into a subtable named order_refunds__refund_line_items__line_item__properties. Refer to the Singer schema for details on possible attributes.

line_item__quantity
INTEGER

The number of items purchased.

line_item__requires_shipping
BOOLEAN

Indicates if the line item requires shipping.

line_item__sku
STRING

The item’s SKU.

line_item__tax_lines

Details about the line item’s tax lines, each of which details a tax applicable to this line item.

If your destination doesn't natively support nested data, this data may be denested into a subtable named order_refunds__refund_line_items__line_item__tax_lines. Refer to the Singer schema for details on possible attributes.

line_item__taxable
BOOLEAN

Indicates if the item is taxable.

line_item__title
STRING

The title of the product.

line_item__total_discount
STRING

The total of any discounts applied to the line item.

line_item__total_discount_set
OBJECT

Presentment and shop money details associated with the total discount for the line item.

line_item__variant_id
INTEGER

The product variant ID.

line_item__variant_inventory_management
STRING

The name of the inventory management system.

line_item__variant_title
STRING

The title of the product variant.

line_item__vendor
STRING

The name of the item’s supplier.

line_item_id
INTEGER

The ID of the related line item in the order.

location_id
INTEGER

The ID of the physical location where the items will be restocked.

quantity
INTEGER

The quantity of the associated line item that was returned.

restock_type
STRING

Indicates how the refund line item will affect inventory levels. Possible values are:

  • no_restock - Refunding these items won’t affect inventory. The number of fulfillable units for this line item will remain unchanged. For example: A refund payment can be issued but no items will be returned or made available for sale again.
  • cancel - The items have not yet been fulfilled. The canceled quantity will be added back to the available count. The number of fulfillable units for this line item will decrease.
  • return - The items were already delivered, and will be returned to the merchant. The returned quantity will be added back to the available count. The number of fulfillable units for this line item will remain unchanged.
  • legacy_restock - Deprecated by Shopify. These items were made available for sale again.
subtotal
NUMBER

The subtotal of the refund line item.

subtotal_set__presentment_money
OBJECT

Details about the presentment amount associated with the subtotal.

subtotal_set__shop_money
OBJECT

Details about the shop amount associated with the subtotal.

total_tax
NUMBER

The total tax on the refund line item.

total_tax_set__presentment_money
OBJECT

Details about the presentment amount associated with the total tax.

total_tax_set__shop_money
OBJECT

Details about the shop amount associated with the total tax.

restock
BOOLEAN

Indicates whether line items will be added back to the store’s inventory.

user_id
INTEGER

The ID of the user who performed the refund.


Replication Method :

Key-based Incremental

Replication Key :

updated_at

Primary Key :

id

API endpoint :

Retrieve a list of orders

The orders table contains info about a shop’s completed orders.

Order metafield data

To replicate order metafield data, you must set this table and the metafields table to replicate.

id
INTEGER

The order ID.

updated_at
DATE-TIME
app_id
INTEGER

The ID of the app that created the order.

billing_address__address1
STRING

The street address of the billing address.

billing_address__address2
STRING

An optional additional field for the street address of the of the billing address.

billing_address__city
STRING

The city, town or village of the billing address.

billing_address__company
STRING

The company of the person associated with the billing address.

billing_address__country
STRING

The name of the country of the billing address.

billing_address__country_code
STRING

The two-letter ISO 3166-1 code for the country of the billing address.

billing_address__first_name
STRING

The first name of the person associated with the payment method.

billing_address__last_name
STRING

The last name of the person associated with the payment method.

billing_address__latitude
NUMBER

The latitude of the billing address.

billing_address__longitude
NUMBER

The longitude of the billing address.

billing_address__name
STRING

The full name of the person associated with the payment method.

billing_address__phone
STRING

The phone number at the billing address.

billing_address__province
STRING

The name of the region of the billing address.

billing_address__province_code
STRING

The two-letter of the region of the billing address.

billing_address__zip
STRING

The postal code of the billing address.

browser_ip
STRING

The IP address of the browser used by the customer when they placed the order.

buyer_accepts_marketing
BOOLEAN

Indicates whether the customer consented to receive email updates from the shop.

cancel_reason
STRING

The reason why the order was canceled. Possible values are:

  • customer - The customer canceled the order.
  • fraud - The order was fraudulent.
  • inventory - Items in the order weren’t in inventory.
  • declined - The payment was declined.
  • other - A reason not in this list.
cancelled_at
DATE-TIME
cart_token
STRING

The ID for the cart associated with the order.

checkout_id
INTEGER

The checkout ID associated with the order.

checkout_token
STRING

The checkout token associated with the order.

client_details__accept_language
STRING

The languages and locales that the browser understands.

client_details__browser_height
INTEGER

The browser’s screen height in pixels.

client_details__browser_ip
STRING

The browser’s IP address.

client_details__browser_width
INTEGER

The browser’s screen width in pixels.

client_details__session_hash
STRING

A hash of the session.

client_details__user_agent
STRING

Details of the browsing client, including software and operating versions.

closed_at
DATE-TIME
confirmed
BOOLEAN

Indicates if the order has been confirmed.

contact_email
STRING

The contact email associated with the order.

created_at
DATE-TIME
currency
STRING

The three-letter ISO 4217 code for the currency used for the payment.

customer_locale
STRING

The two or three-letter language code, optionally followed by a region modifier.

device_id
INTEGER

The ID of the Shopify POS device that created the order.

discount_applications

Details about the discount applications associated with the order.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__discount_applications. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The order ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

allocation_method
STRING

The method used to allocate the discount application.

code
STRING

The discount code.

description
STRING

A description of the discount.

target_selection
STRING

Indicates how a discount selects line items in the cart to be discounted. Possible values are:

  • all - The discount applies to all line items.
  • entitled - The discount applies to a particular subset of line items, often defined by a condition.
  • explicit - The discount applies to a specifically selected line item or shipping line.
target_type
STRING

Indicates the type of item that a discount applies to. Possible values are:

  • line_item
  • shipping_line
title
STRING

The customer-facing name of the discount.

type
STRING

The type of the discount. Possible values are:

  • automatic
  • discount_code
  • manual
  • script
value
NUMBER

The value of the discount. This value is in the customer’s local (presentment) currency.

value_type
STRING

The value type of the discount. Possible values are:

  • fixed_amount
  • percentage
discount_codes

The discount codees applied to the checkout.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__discount_codes. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The order ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

amount
NUMBER

The amount of the discount.

code
STRING

The discount code.

type
STRING

The type of discount. Possible values are:

  • percentage
  • shipping
  • fixed_amount
email
STRING

The customer’s email address.

financial_status
STRING

The order’s financial status. Possible falues are:

  • authorized
  • pending
  • paid
  • partially_paid
  • refunded
  • voided
  • partially_refunded
  • unpaid
fulfillment_status
STRING

The order’s fulfillment status. Possible values are:

  • shipped
  • partial
  • unshipped
fulfillments

Details about the fulfillments associated with the order.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__fulfillments. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The order ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

id
INTEGER

The fulfillment ID.

created_at
DATE-TIME
line_items

Details about the line items associated with the fulfillment.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__fulfillments__line_items. Refer to the Singer schema for details on possible attributes.

location_id
INTEGER

The ID of the physical location where the fulfillment was processed.

name
STRING

The fulfillment name as represented by a number.

receipt__authorization
STRING
receipt__testcase
BOOLEAN
service
STRING
shipment_status
STRING
status
STRING
tracking_company
STRING

The name of the service performing the fulfillment.

tracking_number
STRING

The tracking number of a fulfillment, if it exists.

tracking_numbers

A list of the fulfillment’s tracking numbers.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__fulfillments__tracking_numbers. Refer to the Singer schema for details on possible attributes.

tracking_url
STRING

The URL for a tracking number.

tracking_urls

A list of URLs for the fulfillment’s tracking numbers.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__fulfillments__tracking_urls. Refer to the Singer schema for details on possible attributes.

updated_at
DATE-TIME
gateway
STRING

The payment gateway used by the checkout.

landing_site
STRING

The URL for the page where the customer entered the shop.

line_items

Details about the line items in the order.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__line_items. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The order ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

applied_discount
INTEGER

The discount applied to the line item, if applicable.

compare_at_price
STRING

The line item’s compare at price.

destination_location__address1
STRING

The street address.

destination_location__address2
STRING

An optional second address line.

destination_location__city
STRING

The city.

destination_location__country_code
STRING

The two letter code (ISO 3166-1 alpha-2) format for the country.

destination_location__id
INTEGER

The address ID.

destination_location__name
STRING

The first and last name of the person associated with the address.

destination_location__province_code
STRING

The two-letter province code for the region associated with the address.

destination_location__zip
STRING

The postal or zip code associated with the address.

destination_location_id
INTEGER

The ID of the destination location.

discount_allocations

An ordered list of amounts allocated by discount applications. Each discount allocation is associated to a particular appliction.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__line_items__discount_allocations. Refer to the Singer schema for details on possible attributes.

fulfillable_quantity
INTEGER

The amount available to fulfill, calculated as follows:

quantity - max(refunded_quantity, fulfilled_quantity) - pending_fulfilled_quantity - open_fulfilled_quantity

fulfillment_service
STRING

The service provider that fulfilled the item. Possible values are manual or the name of the provider, such as amazon or shipwire

fulfillment_status
STRING

Indicates how far along an order is in terms of line items fulfilled. Possible values are:

  • null
  • fulfilled
  • partial
  • not_eligible
gift_card
BOOLEAN

Indicates whether the line item is a gift card.

grams
INTEGER

The weight of the item in grams.

key
STRING

A unique identifier for the line item, constructed from the line item’s variant_id plus a hash of the line item’s properties, even if the item has no additional properties.

line_price
STRING

This field has been deprecated by Shopify.

name
STRING

The name of the product variant.

origin_location__address1
STRING

The street address.

origin_location__address2
STRING

An optional second address line.

origin_location__city
STRING

The city.

origin_location__country_code
STRING

The two letter code (ISO 3166-1 alpha-2) format for the country.

origin_location__id
INTEGER

The address ID.

origin_location__name
STRING

The first and last name of the person associated with the address.

origin_location__province_code
STRING

The two-letter province code for the region associated with the address.

origin_location__zip
STRING

The postal or zip code associated with the address.

origin_location_id
INTEGER

The ID of the origin location associated with the refund line item.

pre_tax_price
NUMBER

The pre-tax price of the item.

price
NUMBER

The price of the item before discounts were applied.

product_exists
BOOLEAN

Indicates whether the product exists.

product_id
INTEGER

The product ID.

Reference:

properties

Details about custom info for the item.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__line_items__properties. Refer to the Singer schema for details on possible attributes.

quantity
INTEGER

The number of items purchased.

requires_shipping
BOOLEAN

Indicates if the item requires shipping.

sku
STRING

The item’s SKU.

tax_code
STRING
tax_lines

Details about the line item’s tax lines, each of which details a tax applicable to this line item.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__line_items__tax_lines. Refer to the Singer schema for details on possible attributes.

taxable
BOOLEAN

Indicates if the item is taxable.

title
STRING

The title of the product.

total_discount
NUMBER

The total of any discounts applied to the line item.

variant_id
INTEGER

The product variant ID.

variant_inventory_management
STRING

The name of the inventory management system.

variant_title
STRING

The title of the product variant.

vendor
STRING

The name of the item’s supplier.

location_id
INTEGER

The ID of the physical location where the order was processed.

name
STRING

The order name as represented by a number.

note
STRING

An optional note the shop owner attached to the order.

note_attributes

Additional info added to the order, as it appears in the Additional details section of an order page.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__note_attributes. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The order ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

name
STRING
value
STRING
number
INTEGER

An identifier unique to the shop. Numbers are sequential and start at 1000.

order_adjustments

A list of order adjustments attached to the order.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__order_adjustments. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The order ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

id
INTEGER

The order adjustment ID.

order_id
INTEGER

The ID of the order associated with the order adjustment.

Reference:

refund_id
INTEGER

The ID of the refund associated with the order adjustment.

Reference:

amount
NUMBER

The value of the discrepancy between the calculated refund and the actual refund. If the kind attribute’s value is shipping_refund, then amount is the value of shipping charges refunded to the customer.

kind
STRING

The order adjustment type. Possible values are:

  • shipping_refund
  • refund_discrepancy
reason
STRING

The reason for the order adjustment.

tax_amount
NUMBER

The tax amount of the order adjustment in shop and presentment currencies.

order_number
INTEGER

A unique identifier for the order, used by the shop owner and customer. This is different from the id attribute.

order_status_url
STRING

The URL of the order’s status page.

payment_gateway_names

The list of payment gateways used for the order.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__payment_gateway_names. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The order ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

value
STRING

The payment gateway used for the order.

phone
STRING

The customer’s phone number.

processed_at
DATE-TIME
processing_method
STRING

Indicates how the payment was processed. Possible values are:

  • checkout
  • direct
  • manual
  • offsite
  • express
reference
STRING
referring_site
STRING

The website where the customer clicked a link to the shop.

refunds

A list of refunds applied to the order.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__refunds. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The order ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

id
INTEGER

Reference:

created_at
DATE-TIME
note
STRING

An optional note attached to a refund.

order_adjustments

A list of order adjustments attached to the refund. Order adjustments are generated to account for refunded shipping costs and differences between calculated and actual refund amounts.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__refunds__order_adjustments. Refer to the Singer schema for details on possible attributes.

processed_at
DATE-TIME
refund_line_items

A list of refunded line items.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__refunds__refund_line_items. Refer to the Singer schema for details on possible attributes.

restock
BOOLEAN

Indicates if the line items will be added back to the store’s inventory.

user_id
INTEGER

The ID of the user who performed the refund.

shipping_address__address1
STRING

The street address of the shipping address.

shipping_address__address2
STRING

An optional additional field for the street address of the of the shipping address.

shipping_address__city
STRING

The city, town or village of the shipping address.

shipping_address__company
STRING

The company of the person associated with the shipping address.

shipping_address__country
STRING

The name of the country of the shipping address.

shipping_address__country_code
STRING

The two-letter ISO 3166-1 code for the country of the shipping address.

shipping_address__first_name
STRING

The first name of the person associated with the payment method.

shipping_address__last_name
STRING

The last name of the person associated with the payment method.

shipping_address__latitude
NUMBER

The latitude of the shipping address.

shipping_address__longitude
NUMBER

The longitude of the shipping address.

shipping_address__name
STRING

The full name of the person associated with the payment method.

shipping_address__phone
STRING

The phone number at the shipping address.

shipping_address__province
STRING

The name of the region of the shipping address.

shipping_address__province_code
STRING

The two-letter of the region of the shipping address.

shipping_address__zip
STRING

The postal code of the shipping address.

shipping_lines

Details about the shipping methods associated with the order.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__shipping_lines. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The order ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

id
INTEGER

The shipping line ID.

carrier_identifier
STRING

The shipping carrier’s identifier.

code
STRING

A reference to the shipping method.

delivery_category
STRING
discount_allocations

An ordered list of amounts allocated by discount applications. Each discount allocation is associated to a particular appliction.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__shipping_lines__discount_allocations. Refer to the Singer schema for details on possible attributes.

discounted_price
NUMBER

The price of the shipping method after discounts.

phone
STRING

The phone number associated with the shipping method.

price
NUMBER

The price of the shipping method.

requested_fulfillment_service_id
STRING

A reference to the fulfillment service being requested for the shipping method. Present if the shipping method requires processing by a third party fulfillment service; null otherwise.

source
STRING

The source of the shipping method.

tax_lines

Details about the shipping line’s tax lines, each of which details a tax applicable to this shipping line.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__shipping_lines__tax_lines. Refer to the Singer schema for details on possible attributes.

title
STRING

The title of the shipping method.

source_identifier
STRING
source_name
STRING

Where the order originated.

source_url
STRING

The URL where the order originated.

subtotal_price
NUMBER

The price of the order after discounts but before shipping, taxes, and tips.

tags
STRING

Tags attached to the order.

tax_lines

Details about the order’s tax lines, each of which is a tax applicable to the order.

If your destination doesn't natively support nested data, this data may be denested into a subtable named orders__tax_lines. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The order ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

compare_at
STRING
position
INTEGER
price
NUMBER

The amount of tax to be charged.

rate
NUMBER

The rate of tax to be applied.

source
STRING
title
STRING

The name of the tax.

zone
STRING
taxes_included
BOOLEAN

Indicates if taxes are included in the order subtotal.

test
BOOLEAN

Indicates if the order is a test order.

token
STRING

A unique identifier for the order.

total_discounts
NUMBER

The total discounts applied to the price of the order.

total_line_items_price
NUMBER

The sum of all line item prices.

total_price
NUMBER

The sum of all line item prices, discounts, shipping, taxes, and tips.

total_price_usd
NUMBER

The sum of all line item prices, discounts, shipping, taxes, and tips in USD.

total_tax
NUMBER

The sum of all the taxes applied to the order.

total_tip_received
STRING

The sum of all the tips in the order.

total_weight
INTEGER

The sum of all line item weights, in grams.

user_id
INTEGER

The ID of th euser logged into the Shopify POS that processed the order, if applicable.


Replication Method :

Key-based Incremental

Replication Key :

updated_at

Primary Key :

id

API endpoint :

Retrieve a list of products

The products table contains info about a shop’s products.

Product metafield data

To replicate product metafield data, you must set this table and the metafields table to replicate.

id
INTEGER

The product ID.

Reference:

updated_at
DATE-TIME

The date and time in ISO 8601 format when the product was last updated.

body_html
STRING

A description of the product.

created_at
DATE-TIME

The date and time in ISO 8601 format when the product was created.

handle
STRING

The human-friendly string for the product, automatically generated from the product’s title. This is used by Liquid templating to refer to objects.

images

The product images associated with the product.

If your destination doesn't natively support nested data, this data may be denested into a subtable named products__images. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The product ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

id
INTEGER

The product image ID.

alt
STRING

The alternative text for the product image.

created_at
DATE-TIME

The date and time in ISO 8601 format when the product image was created.

height
INTEGER

The height dimension of the product image.

position
INTEGER

The order of the product image in the list. The first product image is at position 1 and is the main image for the product.

src
STRING

The location of the product image.

updated_at
DATE-TIME

The date and time in ISO 8601 format when the product image was last updated.

variant_ids

The variant IDs associated with the product image.

If your destination doesn't natively support nested data, this data may be denested into a subtable named products__images__variant_ids. Refer to the Singer schema for details on possible attributes.

width
INTEGER

The width dimension of the product image.

options

The custom properties associated with the product, like Size, Color, etc.

If your destination doesn't natively support nested data, this data may be denested into a subtable named products__options. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The product ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

id
INTEGER

The custom property ID.

name
STRING

The name of the custom property.

position
INTEGER

The order of the custom property in the list.

product_id
INTEGER

The ID of the product.

Reference:

values

The values of the custom property.

If your destination doesn't natively support nested data, this data may be denested into a subtable named products__options__values. Refer to the Singer schema for details on possible attributes.

product_type
STRING

The categorization for the product, used for filtering and searching.

published_at
DATE-TIME

The date and time in ISO 8601 format when the product was published.

published_scope
STRING

Indicates whether the product is published to the Point of Sale channel. Possible values are:

  • web - The product is published to the Online Store channel but not published to the Point of Sale channel.
  • global - The product is published to both the Online Store channel and the Point of Sale channel.
tags
STRING

The tags associated with the product.

template_suffix
STRING

The suffix of the Liquid template applied to the product. The default template is product.liquid; any additional templates will be product.suffix.liquid

title
STRING

The product title.

variants

The product variants associated with the product, each representing a different version of the product.

If your destination doesn't natively support nested data, this data may be denested into a subtable named products__variants. Refer to the Singer schema for details on possible attributes.

_sdc_source_key_id
INTEGER

The product ID.

_sdc_level_0_id
INTEGER

This column forms part of a composite key for the table. The value will auto-increment for each unique record, beginning with 0.

id
INTEGER

The product variant ID.

barcode
STRING

The barcode, UPC, or ISBN number for the product.

compare_at_price
STRING

The original price of the item before an adjustment or sale.

created_at
DATE-TIME

The date and time in ISO 8601 format when the product variant was created.

fulfillment_service
STRING

The fulfillment service associated with the product variant. Possible values are manual or the handle of a fulfillment service.

grams
INTEGER

The weight of the product variant in grams.

image_id
INTEGER

The ID for the product image associated with the product variant.

inventory_item_id
INTEGER

The ID for the inventory item.

inventory_management
STRING

The fulfillment service that tracks the number of items in stock for the product variant. Possible values are shopify or the handle of fulfillment service that has inventory management enabled.

inventory_policy
STRING

Indicates whether customers are allowed to place an order for the product variant when it’s out of stock. Possible values are:

  • deny - Customers are not allowed to place orders for the product variant if it’s out of stock.
  • continue - Customers are allowed to place orders for the product variant if it’s out of stock.
inventory_quantity
INTEGER

An aggregate of inventory across all locations.

old_inventory_quantity
INTEGER

This attribute has been deprecated by Shopify.

option1
STRING

A custom property option associated with the product variant.

option2
STRING

A custom property option associated with the product variant.

option3
STRING

A custom property option associated with the product variant.

position
INTEGER

The order of the product variant in the list of product variants. The first position in the list is 1.

price
STRING

The price of the product variant.

requires_shipping
BOOLEAN

Indicates whether a customer needs to provide a shipping address when placing an order for the product variant.

sku
STRING

The product variant’s Stock Keeping Unit (SKU).

tax_code
STRING

Only applicable to shops that have the Avalara AvaTax installed. The Avalara tax code for the product variant.

taxable
BOOLEAN

Indicates if a tax is charged when the product variant is sold.

title
STRING

The title of the product variant.

updated_at
DATE-TIME

The date and time in ISO 8601 format when the product variant was last updated.

weight
NUMBER

The weight of the product variant in the unit system, specified with weight_unit.

weight_unit
STRING

The unit of measurement that applies to the product variant’s weight. Possible values are:

  • g - Grams
  • kg - Kilograms
  • oz - Ounces
  • lb - Pounds
vendor
STRING

The name of the product’s vendor.


Replication Method :

Key-based Incremental

Replication Key :

created_at

Primary Key :

id

API endpoint :

Retrieve a list of transactions

The transactions table contains info about transactions.

id
INTEGER

The transaction ID.

Reference:

created_at
STRING

The date and time in ISO 8601 format when the transaction was created.

amount
NUMBER

The amount of money that the transaction was for.

authorization
STRING

The authorization code associated with the transaction.

currency
STRING

The three-letter ISO 4217 code for the currency used for payment. For example: USD

device_id
INTEGER

The ID for the device.

error_code
STRING

A standardized error code, independent of the payment provider. Possible values are:

  • incorrect_number
  • invalid_number
  • invalid_expiry_date
  • invalid_cvc
  • expired_card
  • incorrect_cvc
  • incorrect_zip
  • incorrect_address
  • card_declined
  • processing_error
  • call_issuer
  • pick_up_card
gateway
STRING

The name of the gateway the transaction was issued through. For a list of possible values, refer to Shopify’s payment gateways documentation.

kind
STRING

The transaction type. Possible values are:

  • authorization - Money that the customer has agreed to pay. The authorization period can be between seven and 30 days while a store waits for a payment to be captured.
  • capture - A transfer of money that was reserved during the authorization of a shop.
  • sale - The authorization and capture of a payment.
  • void - The cancellation of a pending authorization or capture.
  • refund - The parial or full return of captured money to the customer.
location_id
INTEGER

The ID of the location that processed the transaction.

message
STRING

A message generated by the payment provider with additional info about why the transaction succeeded or failed.

order_id
INTEGER

The ID of the order the transaction is associated with.

Reference:

parent_id
INTEGER

The ID of an associated transaction.

  • For capture transactions, the parent needs to be an authorization transaction.
  • For void transactions, the parent needs to be an authorization transaction.
  • For refund transactions, the parent needs to be a capture or sale transaction.

Reference:

receipt__authorization
INTEGER

The authorization number associated with the receipt.

source_name
STRING

The origin of the transaction, set by Shopify. Possible values are:

  • web
  • pos
  • iphone
  • android
status
STRING

The status of the transaction. Possible values are:

  • pending
  • failure
  • success
  • error
test
BOOLEAN

Indicates if the transaction was a test transaction.

user_id
INTEGER

The ID of the user who was logged into the Shopify POS device when the order was processed, if applicable.



Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.