Stitch Documentation
has moved!

Please update your bookmarks to https://www.stitchdata.com/docs

If you're not automatically redirected after 5 seconds, click here.

Expected Shopify Data

Now that Shopify is successfully connected to Stitch, what comes next? In this article, we'll cover:

Replicating Shopify Data

Historical Sync

By default, a historical sync of Shopify data goes back one year from the Stitch connection date.

Frequency

Stitch will replicate Shopify data based on the frequency you define. The default setting is every 30 minutes, but you can change it to something less frequent based on your needs.

Replication Method

The majority of Shopify data is replicated using Incremental Replication. This means that every time Shopify data is queued for replication, only new and updated rows will be replicated to your data warehouse.

The only exception to this is the collects table, which uses Full Table Replication. This means that every time this table is queued for replication, all rows in the table - including new, existing, and updated - will be replicated to your data warehouse.

Important!

Even though the majority of Shopify tables support Incremental Replication, a single Shopify integration can quickly use up your row quota if set to a higher Replication Frequency. When deciding how frequently Shopify data should replicate, we recommend keeping the following in mind: 

  • Shopify heavily nests data, meaning there are many, many subtables that have to be created by Stitch. This means that if a top-level record is updated, it's possible that associated rows in subtables will also be updated.
  • Shopify's API can only be queried by day. This means that if you choose a higher Replication Frequency - every 30 minutes, for example - data from earlier in the day will be re-replicated and count towards your row quota.

To prevent overages, we recommend setting Shopify integrations to replicate less frequently.

Want more tips for reducing your row count? Click here.


Shopify Tables in Your Data Warehouse

Below is a list of the Shopify tables and subtables that will be created in your data warehouse. Because Shopify uses nested arrays to structure data in some of its API's endpoints, Stitch will de-nest these arrays and create subtables. Refer to the Handling of Nested Data Structures doc for more info.

Click a table name to view a full list of that table's attributes in Shopify's API documentation.

checkouts
Primary Key:
id

The checkouts table contains information about abandoned checkouts. Abandoned checkouts are defined as checkouts where the customer has entered their billing and shipping information, but not completed the ordering process.

Attributes include the checkout ID (id), abandoned checkout URL, cancel reason, cart token, closed at date, completed at date, currency, applicable discount codes, payment gateway, landing site, note, referring site, source name, subtotal, tax inclusion flag, token, total discounts, total line items price, order total, tax total, weight total, and created and updated at dates.

Subtables

The composite key for the following tables - made up of the abandoned checkout ID and the row ID - can be used to tie it back to its parent table, checkouts: _sdc_source_key_id:_sdc_level_0_id.

  • checkouts__line_items - This subtable contains data about line items contained in the abandoned checkout.
  • checkouts__note_attributes - This subtable contains data about any notes applied to abandoned checkouts.
  • checkouts__shipping_lines - This subtable contains the shipping info entered by the customer in the abandoned checkout.
  • checkouts__tax_lines - This subtable contains the tax info applicable to the abandoned checkout.

collects
Primary Key:
id

The collects table contains information about collects, which is the link between products and custom collections.

Attributes include the collect ID (id), collection ID, product ID, featured flag, position, sort value, and created and updated at dates.

custom_collections
Primary Key:
id

The custom_collections table contains information about your custom collections.

Attributes include the custom collection ID (id), description, handle, image URL, published flag, scope, and date; sort order, template suffix, title, and updated at date.

customers
Primary Key:
id

The customers table contains information about your shop's customers.

Attributes include the customer ID (id), email address, first and last name, accepts marketing flag, orders count, account state, total spent, last order ID, notes, email verification setting, multipass identifier, tax exempt flag, tags, last order name, default address info, and created and updated at dates.

  • customers__addresses - This subtable contains customer address info.

    The composite key for this table - made up of the customer ID and the row ID - can be used to tie it back to its parent table, customers: _sdc_source_key_id:_sdc_level_0_id.

metafields
Primary Key:
id

The metafields table contains metadata for shop and order resources.

Attributes include the metafield ID (id), description, key, namespace, owner ID and resource, value, value type, and created and updated at dates.

order_refunds
Primary Key:
id

The order_refunds table contains information about refunds applied to transactions.

Attributes include the order refund ID (id), note, restock flag, user ID, and created at date.

Subtables

  • order_refunds__refund_line_items - This subtable contains info about the line items contained in a refund.

    The composite key for this table - made up of the customer ID and the row ID - can be used to tie it back to its parent table, order_refunds: _sdc_source_key_id:_sdc_level_0_id.
    • order_refunds__refund_line_items__properties - This subtable contains property info for the refund's line items.

      The composite key for this table can be used to tie it back to the top-level table, order_refunds: _sdc_source_key_id:_sdc_level_0_id:_sdc_level_1_id.
    • order_refunds__refund_line_items__tax_lines - This subtable contains tax info for the refund's line items.

      order_refunds__refund_line_items__properties - This subtable contains property info for the refund's line items.

      The composite key for this table can be used to tie it back to the top-level table, order_refunds: _sdc_source_key_id:_sdc_level_0_id:_sdc_level_1_id.
  • order_refunds__transactions - This subtable contains info about the transactions involved in the refund.

    The composite key for this table - made up of the customer ID and the row ID - can be used to tie it back to its parent table, order_refunds: _sdc_source_key_id:_sdc_level_0_id.

orders
Primary Key:
id

The orders table contains information about your shop's completed orders.

Attributes include the order ID (id), customer's IP address, customer's marketing flag, customer's billing/shipping addresses, customer details, discount codess, cart token, closed at date, currency code, customer email address, financial and fulfillment statuses, tags, order name, notes, number, order number, payment gateway names, processing method, referring site, source name, subtotal, tax inclusion flag, token, discount total, line items total, order total, tax total, weight total, order status URL, and processed, created, and updated at dates.

Subtables

  • orders__discount_codes - This subtable contains info about the discount codes applied to an order.

    The composite key for this table - made up of the customer ID and the row ID - can be used to tie it back to its parent table, orders: _sdc_source_key_id:_sdc_level_0_id.
  • orders__fulfillments - This subtable contains info about the fulfillments associated with an order.

    The composite key for this table - made up of the customer ID and the row ID - can be used to tie it back to its parent table, orders: _sdc_source_key_id:_sdc_level_0_id.
    • orders__fulfillments__line_items - This subtable contains info about the line items contained in the order's fulfillment.

      The composite key for this table can be used to tie it back to the top-level table, orders: _sdc_source_key_id:_sdc_level_0_id:_sdc_level_1_id.
      • orders__fulfillments__line_items__properties - This subtable contains property info for the fulfillment's line items.

        The composite key for this table can be used to tie it back to the top-level table, orders: _sdc_source_key_id:_sdc_level_0_id:_sdc_level_1_id:_sdc_level_2_id.
      • orders__fulfillments__line_items__tax_lines - This subtable contains tax info for the fulfillment's line items.

        The composite key for this table can be used to tie it back to the top-level table, orders: _sdc_source_key_id:_sdc_level_0_id:_sdc_level_1_id:_sdc_level_2_id.
  • orders__line_items - This subtable contains info about the individual items contained in an order.
    • orders__line_items__properties - This subtable contains property info for the items contained in an order.

      The composite key for this table can be used to tie it back to the top-level table, orders: _sdc_source_key_id:_sdc_level_0_id:_sdc_level_1_id.
    • orders__line_items__tax_lines - This subtable contains tax info for the items contained in an order.

      The composite key for this table can be used to tie it back to the top-level table, orders: _sdc_source_key_id:_sdc_level_0_id:_sdc_level_1_id.
  • orders__note_attributes - This subtable contains extra info added to an order.

    The composite key for this table - made up of the customer ID and the row ID - can be used to tie it back to its parent table, orders: _sdc_source_key_id:_sdc_level_0_id.
  • orders__refunds - This subtable contains info about the refunds (if any) applied to an order.

    The composite key for this table - made up of the customer ID and the row ID - can be used to tie it back to its parent table, orders: _sdc_source_key_id:_sdc_level_0_id.
    • orders__refunds__refund_line_items - This subtable contains info about the line items contained in a refund applied to an order.

      The composite key for this table can be used to tie it back to the top-level table, orders: _sdc_source_key_id:_sdc_level_0_id:_sdc_level_1_id.
      • orders__refunds__refund_line_items__properties - This subtable contains property info for the refund's line items.

        The composite key for this table can be used to tie it back to the top-level table, orders: _sdc_source_key_id:_sdc_level_0_id:_sdc_level_1_id:_sdc_level_2_id.
      • orders__refunds__refund_line_items__tax_lines - This subtable contains tax info for the refund's line items.

        The composite key for this table can be used to tie it back to the top-level table, orders: _sdc_source_key_id:_sdc_level_0_id:_sdc_level_1_id:_sdc_level_2_id.
  • orders__shipping_lines - This subtable contains the shipping info associated with an order.

    The composite key for this table - made up of the customer ID and the row ID - can be used to tie it back to its parent table, orders: _sdc_source_key_id:_sdc_level_0_id.

products
Primary Key:
id

The products table contains information about the products for sale in your shop.

Attributes include the product ID (id), description, handle, images, type, tags, published scope, published at date, template suffix, title, global metafields for title and description, vendor name, and created and updated at dates.

Subtables

The composite key for the following tables - made up of the abandoned checkout ID and the row ID - can be used to tie it back to its parent table, products: _sdc_source_key_id:_sdc_level_0_id.

  • products__images - This subtable contains info about the website images associated with a product.
  • products__options - This subtable contains data about the options - or custom properties, like color - associated with a product.
  • products__variants - This subtable contains data about the variants associated with a product. For example: if a shirt comes in multiple sizes and colors, each size/color permutation is considered a variant.

transactions
Primary Key:
id

The transactions table contains information about transactions, which are created for every order that results in money exchange. This includes authorizations, sales, captures, voids, and refunds.

Attributes include the transaction ID (id), amount, payment details, authorization code, device ID, gateway, source name, kind (ex: refund), order ID, receipt, error code, status, user ID, currency code, and created and updated at dates.

Related

Was this article helpful?
0 out of 0 found this helpful

Comments

Questions or suggestions? If something in our documentation is unclear, let us know in the comments!