Skip to main content

Shopify

Ashleigh Rentz
  • Updated

Overview

Shopify is a commerce platform that allows anyone to set up an online store and sell their products. Merchants can also sell their products in person with Shopify POS. KORE’s Shopify integration adds this valuable e-commerce data into your warehouse.

During the initial setup of the integration, KORE will pull in 12 months of historical data (more can be accessed upon request). After that, data is updated daily. The integration looks at the past 30 days to ensure no data is missed.

In Sept 2025, we released an updated version to support Shopify changes to GraphQL. Those changes are outlined below. 

 

Setup

KORE provides a Shopify private app which uses the Admin API to access your data. To set up this integration:

  1. Enable private apps in Shopify.
  2. Generate credentials for Admin API access.
    • Warning: Do not enable the Storefront API. It allows anyone to access your data!
    • App name: KORE DWA
    • Email address: sfdc-shared@koresoftware.com
    • In the Admin API Permissions section, select Read access for every category.
  3. Enter the API key in our Secure Information Portal and save the URL.
  4. Enter the API password in our Secure Information Portal and save the URL.
  5. Provide both URLs and your Shopify domain name (example.myshopify.com) to your KORE Success Manager.

 

 

Shopify v2 ERD 

 

Shopify v2 - Legacy Compatible Views

To support an easy migration to Shopify V2, we created backwards compatible views matching the table structure of the legacy tables. The SQL logic for the views can be viewed in your Redshift instance by viewing the DDL on each view. 

Schema Name: shopify2_<identifier>

shopify views.png 

 

DDL views.png

 

 

Legacy ERD and Data Dictionary

(This information is provided on a best-effort basis without guarantees. For clarity, some column names may be omitted from this ERD.)

Download data dictionary (Excel file)

 

Table name Description
abandoned_checkouts Details of checkouts where a customer left after the first page without completing their purchase.
abandoned_checkouts_discount_codes Details of any discount codes applied to a checkout before it was abandoned.
abandoned_checkouts_line_items Lists of individual items from each abandoned checkout.
abandoned_checkouts_note_attributes List of note attributes, if any.
abandoned_checkouts_shipping_lines Lists the shipping method for an abandoned checkout if one was selected.
abandoned_checkouts_tax_lines Lists the calculated sales taxes for abandoned checkouts.
customers Customer details such as contact info, order history, and whether they've agreed to receive email marketing.
customer_address Stores the addresses that a customer has saved. Each customer can have multiple saved addresses.
locations Represents geographical locations where your stores, pop-up stores, headquarters, and warehouses exist. Use to track sales, manage inventory, configure the tax rates to apply at checkout, etc.
orders Details of a specific order placed by a customer. May contain multiple items purchased. Contains billing and shipping address fields—this table does not reference the customer_address table.
order_discount_applications Details of how discounts are applied to an order.
order_discount_codes Discount codes applied to an order, if any.
order_line_items Details about each product purchased as part of an order.
order_note_attributes List of note attributes, if any.
order_shipping_lines Details about the shipping method used for an order line item. This could be different if products in the same order are shipped separately.
order_tax_lines List of tax lines, with order price, taxes.
products The individual items and services for sale in the store.
product_options Custom product attributes you've configured such as size, color, or material.
product_variants The different combinations of the product's options. For example, a t-shirt product with size and color options might have a variant in small size and blue color.
shop General settings and information about the store.
tender_transactions An instance of money (tender) passing between the merchant and a customer. Positive amounts represent money paid by the customer to the merchant, and negative amounts represent refunds back to the customer.

 

Example queries

Display an overview of all items sold yesterday:

SELECT o.email, o.total_price_usd, oli.title, oli.quantity, otl.price,
   osl.title, tt.payment_method, tt.processed_at
FROM shopify.orders o
LEFT JOIN shopify.order_line_items oli ON o.id = oli.order_id
LEFT JOIN shopify.order_tax_lines otl ON o.id = otl.order_id
LEFT JOIN shopify.order_shipping_lines osl ON o.id = osl.order_id
LEFT JOIN shopify.tender_transactions tt ON o.id = tt.order_id
WHERE tt.processed_at > DATEADD('day', -1, CURRENT_DATE);

 

List the 10 best-selling items from the past 30 days, disregarding variants:

SELECT oli.product_id, oli.title, SUM(quantity) as total_quantity
FROM shopify.order_line_items oli
WHERE oli.processed_date > DATEADD('day', -30, CURRENT_DATE)
GROUP BY oli.product_id, oli.title
ORDER BY total_quantity DESC
LIMIT 10;