Integration Specifications
This page will help you learn about how Daasity replicates data from Shopify, the limitations of the data we can extract, and where the data is stored in the Shopify schema.
Integration Overview
Shopify is a leading e-commerce platform that empowers businesses to easily create and manage online stores, offering built-in analytics and seamless integration with third-party tools for data-driven insights.
This document provides context on what kind of data is being gathered through this extractor, which endpoints that data is coming from, and how the extracted tables relate to each other.
Integration Availability
This integration is available for:
Enterprise
Growth
API Endpoints
The Daasity Shopify extractor is based on the Shopify REST API and Shopify GraphQL documentation.
Daasity is currently using the 2022-04 version of the REST and GraphQL APIs.
The following endpoints are used by Daasity to replicate data from Shopify:
Customers
Discounts
Plus (Available only with the Daasity Shopify Plus extractor)
User (Please contact support@daasity.com to enable this endpoint.)
Products
Shopify Payments
Transactions (Please contact support@daasity.com to enable this endpoint.)
Store Properties
Tender Transaction
Tender Transaction (Please contact support@daasity.com to enable this endpoint.)
The Orders endpoint runs each hour and all other endpoints are updated daily unless requested otherwise.
Entity Relationship Diagram (ERD)
Click here to view the ERD for the Daasity Shopify integration illustrating the different tables and keys to join across tables.
Shopify & Shopify Plus Schema
The Daasity Shopify extractor creates these tables using the endpoints and replication methods listed. The data is mapped from the source API endpoint to the table based on the mapping logic outlined in each table.
Collects
Endpoint: Collect
Update Method: UPSERT
Table Name: [
shopify.collects]
| JSON Element | Database Column |
|---|---|
id | collect_id |
collection_id | collection_id |
product_id | product_id |
position | position |
created_at | created_at |
updated_at | updated_at |
shop::id | shop_id |
Custom Collections
Endpoint: Custom Collection
Update Method: UPSERT
Table Name: [
shopify.custom_collections]
| JSON Element | Database Column |
|---|---|
id | custom_collection_id |
handle | handle |
title | title |
sort_order | sort_order |
published_at | published_at |
published_scope | published_scope |
updated_at | updated_at |
shop::id | shop_id |
Customer Addresses
Endpoint: Customer
Update Method: UPSERT
Table Name: [
shopify.customer_addresses]
| JSON Element | Database Column |
|---|---|
id | address_id |
customer_id | customer_id |
first_name | first_name |
last_name | last_name |
company | company |
address1 | address1 |
address2 | address2 |
city | city |
province | state |
country | country |
zip | zipcode |
phone | phone_number |
province_code | state_code |
country_code | country_code |
country_name | country_name |
shop::id | shop_id |
Customers
Endpoint: Customer
Update Method: UPSERT
Table Name: [
shopify.customers]
| JSON Element | Database Column |
|---|---|
id | customer_id |
accepts_marketing | accepts_marketing |
default_address::id | address_id |
default_address::company | company |
default_address::address1 | address1 |
default_address::address2 | address2 |
default_address::city | city |
default_address::province | state |
default_address::country | country |
default_address::zip | zipcode |
default_address::phone | phone_number |
default_address::province_code | state_code |
default_address::country_code | country_code |
default_address::country_name | country_name |
first_name | first_name |
last_name | last_name |
note | note |
state | account_status |
tags | tags |
verified_email | verified_email |
created_at | created_at |
updated_at | updated_at |
shop::id | shop_id |
Discount Codes
Endpoint: Discount Codes
Update Method: UPSERT
Table Name: [
shopify.discount_codes]
| JSON Element | Database Column |
|---|---|
id | discount_code_id |
code | discount_code |
price_rule_id | price_rule_id |
created_at | created_at |
updated_at | updated_at |
shop::id | shop_id |
Disputes
Endpoint: Dispute
Update Method: UPSERT
Table Name: [
shopify.disputes]
| JSON Element | Database Column |
|---|---|
id | dispute_id |
order_id | order_id |
type | type |
currency | currency |
amount | amount |
reason | reason |
network_reason_code | network_reason_code |
status | status |
initiated_at | initiated_at |
evidence_due_by | evidence_due_by |
evidence_sent_on | evidence_sent_on |
finalized_on | finalized_on |
Fulfillment Order Items
Endpoint: Order
Update Method: UPSERT
Table Name: [
shopify.fulfillment_order_items]
| JSON Element | Database Column |
|---|---|
id | fulfillment_id |
order_id | order_id |
line_item::id | order_line_id |
line_item::variant_id | variant_id |
line_item::sku | sku |
line_item::quantity | quantity |
line_item::fulfillable_quantity | fulfillable_quantity |
line_item::fulfillment_status | fulfillment_status |
created_at | created_at |
updated_at | updated_at |
shop::id | shop_id |
MD5(fulfillment_id + order_line_id + order_id) | __sync_key |
Gift Cards
Endpoint: Gift Card
Update Method: UPSERT
Table Name: [
shopify.gift_cards]
NOTE: This table is only available for merchants on Shopify Plus.
Please contact support@daasity.com to enable this table, if you are a Shopify Plus merchant.
| JSON Element | Database Column |
|---|---|
id | id |
balance | balance |
created_at | created_at |
updated_at | updated_at |
currency | currency |
initial_value | initial_value |
disabled_at | disabled_at |
line_item_id | line_item_id |
api_client_id | api_client_id |
user_id | user_id |
customer_id | customer_id |
note | note |
expires_on | expires_on |
template_suffix | template_suffix |
last_characters | last_characters |
order_id | order_id |
shop::id | shop_id |
Gift Card Adjustments
Endpoint: Gift Card
Update Method: UPSERT
Table Name: [
shopify.gift_card_adjustments]
NOTE: This table is only available for merchants on Shopify Plus.
Please contact support@daasity.com to enable this table, if you are a Shopify Plus merchant.
| JSON Element | Database Column |
|---|---|
id | id |
gift_card_id | gift_card_id |
number | number |
amount | amount |
note | note |
remote_transaction_ref | remote_transaction_ref |
remote_transaction_url | remote_transaction_url |
api_client_id | api_client_id |
user_id | user_id |
order_transaction_id | order_transaction_id |
created_at | created_at |
updated_at | updated_at |
processed_at | processed_at |
shop::id | shop_id |
MD5(shop_id + order_id + tax_type) | __sync_key |
Inventory Items
Endpoint: Inventory Items
Update Method: UPSERT
Table Name: [
shopify.inventory_items]
| JSON Element | Database Column |
|---|---|
id | inventory_item_id |
variant_id | variant_id |
sku | sku |
cost | sku_cost |
requires_shipping | required_shipping |
tracked | tracked |
country_code_of_origin | country_of_origin |
created_at | created_at |
updated_at | updated_at |
Inventory Items Country Harmonized System Codes
Endpoint: Inventory Items
Update Method: UPSERT
Table Name: [
shopify.inventory_items_country_harmonized_system_codes]
| JSON Element | Database Column |
|---|---|
inventory_item_id | inventory_item_id |
harmonized_system_code | harmonized_system_code |
country_code | country-code |
country_harmonized_system_code | country_harmonized_system_code |
Inventory Levels
Endpoint: Inventory Levels
Update Method: UPSERT
Table Name: [
shopify.inventory_levels]
| JSON Element | Database Column |
|---|---|
inventory_item_id | inventory_item_id |
location_id | location_id |
available | inventory_quantity |
updated_at | updated_at |
shop::id | shop_id |
MD5(shop_id + inventory_item_id) | _key |
Locations
Endpoint: Locations
Update Method: UPSERT
Table Name: [
shopify.locations]
| JSON Element | Database Column |
|---|---|
id | location_id |
name | location_name |
address1 | address1 |
address2 | address2 |
city | city |
province | state |
country | country |
zip | zipcode |
phone | phone_number |
deleted_at | deleted_at |
created_at | created_at |
updated_at | updated_at |
shop::id | shop_id |
Orders
Endpoint: Order
Update Method: UPSERT
Table Name: [
shopify.orders]
| JSON Element | Database Column |
|---|---|
id | order_id |
app_id | app_id |
browser_ip | browser_ip |
cancel_reason | cancel_reason |
cancelled_at | cancelled_at |
cart_token | cart_token |
currency | currency |
customer.id | customer_id |
customer_locale | customer_locale |
financial_status | financial_status |
fulfillment_status | fulfillment_status |
tags | tags |
name | order_code |
note | note |
phone | phone_number |
referring_site | referring_site |
source_name | source_name |
subtotal_price | subtotal_price |
total_discounts | total_discounts |
total_line_items_price | total_line_items_price |
total_price | amount_charged |
total_tax | total_tax |
total_weight | total_weight |
location_id | location_id |
billing_address::name | billing_name |
billing_address::company | billing_company |
billing_address::address1 | billing_address1 |
billing_address::address2 | billing_address2 |
billing_address::city | billing_city |
billing_address::province | billing_state |
billing_address::country | billing_country |
billing_address::zip | billing_zipcode |
billing_address::phone | billing_phone |
billing_address::province_code | billing_state_code |
billing_address::country_code | billing_country_code |
billing_address::first_name | billing_first_name |
billing_address::last_name | billing_last_name |
billing_address::latitude | billing_latitude |
billing_address::longitude | billing_longitude |
shipping_address::name | shipping_name |
shipping_address::company | shipping_company |
shipping_address::address1 | shipping_address1 |
shipping_address::address2 | shipping_address2 |
shipping_address::city | shipping_city |
shipping_address::province | shipping_state |
shipping_address::country | shipping_country |
shipping_address::zip | shipping_zipcode |
shipping_address::phone | shipping_phone |
shipping_address::province_code | shipping_state_code |
shipping_address::country_code | shipping_country_code |
shipping_address::first_name | shipping_first_name |
shipping_address::last_name | shipping_last_name |
shipping_address::latitude | shipping_latitude |
shipping_address::longitude | shipping_longitude |
processed_at | processed_at |
created_at | created_at |
updated_at | updated_at |
shop::id | shop_id |
Order Discount Applications
Endpoint: Order
Update Method: UPSERT
Table Name: [
shopify.order_discount_applications]
| JSON Element | Database Column |
|---|---|
order_id | order_id |
shop::id | shop_id |
discount_application_index | discount_application_index |
value | value |
value_type | value_type |
allocation_method | allocation_method |
target_selection | target_selection |
target_type | target_type |
title | title |
description | description |
Order Discount Codes
Endpoint: Order
Update Method: UPSERT
Table Name: [
shopify.order_discount_codes]
| JSON Element | Database Column |
|---|---|
order_id | order_id |
amount | amount |
code | code |
type | type |
shop::id | shop_id |
MD5(shop_id + order_id + code) | __sync_key |
Order Fulfillments
Endpoint: Order
Update Method: UPSERT
Table Name: [
shopify.order_fulfillments]
| JSON Element | Database Column |
|---|---|
id | order_fulfillment_id |
order_id | order_id |
location_id | location_id |
status | fulfillment_status |
shipment_status | shipment_status |
tracking_company | tracking_company |
tracking_number | tracking_number |
created_at | created_at |
updated_at | updated_at |
shop::id | shop_id |
Order Line Items
Endpoint: Order
Update Method: UPSERT
Table Name: [
shopify.order_line_items]
| JSON Element | Database Column |
|---|---|
id | order_line_id |
order_id | order_id |
fulfillment_status | fulfillment_status |
fulfillment_id | fulfillment_id |
price | price |
product_id | product_id |
quantity | quantity |
sku | sku |
requires_shipping | requires_shipping |
title | product_title |
variant_id | variant_id |
vendor | vendor |
name | product_name |
taxable | taxable_flag |
total_discount | discount_amount |
gift_card | gift_card |
_tax_lines_total | tax_lines_total |
shop::id | shop_id |
Order Line Duties
Endpoint: Order
Update Method: UPSERT
Table Name: [
shopify.order_line_duties]
| JSON Element | Database Column |
|---|---|
id | id |
order_id | order_id |
order_line_id | order_line_id |
admin_graphql_api_id | admin_graphql_api_id |
country_code_of_origin | country_code_of_origin |
harmonized_system_code | harmonized_system_code |
amount | amount |
shop::id | shop_id |
Order Line Duty Tax Lines
Endpoint: Order
Update Method: UPSERT
Table Name: [
shopify.order_line_duty_tax_lines]
| JSON Element | Database Column |
|---|---|
order_id | order_id |
order_line_id | order_line_id |
order_line_duty_id | order_line_duty_id |
price | price |
rate | rate |
title | title |
shop::id | shop_id |
Order Line Item Discount Allocations
Endpoint: Order
Update Method: UPSERT
Table Name: [
shopify.order_line_item_discount_allocations]
| JSON Element | Database Column |
|---|---|
order_id | order_id |
order_line_id | order_line_id |
amount | amount |
discount_application_index | discount_application_index |
shop::id | shop_id |
MD5(shop_id + order_id + order_line_id + discount_applicaton_index) | __sync_key |
Order Line Item Properties
Endpoint: Order
Update Method: UPSERT
Table Name: [
shopify.order_line_item_properties]
| JSON Element | Database Column |
|---|---|
id | order_line_id |
order_id | order_id |
name | name |
value | value |
MD5(shop_id + order_id + order_line_id + name) | _key |
Order Note Attributes
Endpoint: Order
Update Method: UPSERT
Table Name: [
shopify.order_note_attributes]
| JSON Element | Database Column |
|---|---|
order_id | order_id |
name | name |
value | value |
shop::id | shop_id |
MD5(shop_id + order_id + name) | _key |
Order Payment Gateway Names
Endpoint: Order
Update Method: UPSERT
Table Name: [
shopify.order_payment_gateway_names]
| JSON Element | Database Column |
|---|---|
order_id | order_id |
value | value |
shop::id | shop_id |
MD5(shop_id + order_id + value) | __sync_key |
Order Refund Transactions
Endpoint: Refund
Update Method: UPSERT
Table Name: [
shopify.order_refund_transactions]
| JSON Element | Database Column |
|---|---|
order_id | order_id |
refund_id | refund_id |
amount | refund_amount |
currency | currency_code |
gateway | gateway |
source_name | source_name |
status | status |
created_at | created_at |
updated_at | updated_at |
shop::id | shop_id |
MD5(shop_id + order_id + tax_type) | __sync_key |
Order Sales Agreements
Endpoint: Order (GraphQL)
Update Method: UPSERT
Table Name: [
shopify.order_sales_agreements]
| JSON Element | Database Column |
|---|---|
id | id |
happened_at | happened_at |
order_id | order_id |
sale_id | sale_id |
action_type | action_type |
line_type | line_type |
quantity | quantity |
sale::totalAmount::shopMoney::amount | shop_money_amount |
sale::totalAmount::presentmentMoney::amount | presentment_money_amount |
sale::lineItem::id | line_id |
sale::lineItem::name | line_name |
shop::id | shop_id |
Order Shipping Lines
Endpoint: Order
Update Method: UPSERT
Table Name: [
shopify.order_shipping_lines]
| JSON Element | Database Column |
|---|---|
id | shipping_line_id |
order_id | order_id |
code | shipping_code |
price | shipping_amount |
source | source |
title | shipping_title |
carrier_identifier | carrier_identifier |
SUM(tax_line::amount) | tax_lines_total |
SUM(discount_allocations::amount) | discount_allocations_total |
shop::id | shop_id |
Order Shipping Line Tax Lines
Endpoint: Order
Update Method: UPSERT
Table Name: [
shopify.order_shipping_line_tax_lines]
| JSON Element | Database Column |
|---|---|
order_id | order_id |
shipping_line_id | shipping_line_id |
channel_liable | channel_liable |
price | price |
presentment_money_amount | presentment_money_amount |
presentment_money_currency_code | presentment_money_currency_code |
shop_money_amount | shop_money_amount |
shop_money_currency_code | shop_money_currency_code |
rate | rate |
title | title |
shop::id | shop_id |
Order Tax Lines
Endpoint: Order
Update Method: UPSERT
Table Name: [
shopify.order_tax_lines]
| JSON Element | Database Column |
|---|---|
order_id | order_id |
price | tax_amount |
rate | tax_rate |
title | tax_type |
shop::id | shop_id |
MD5(shop_id + order_id + tax_type) | __sync_key |
Payment Transactions
Endpoint: Transaction
Update Method: UPSERT
Table Name: [
shopify.payment_transactions]
| JSON Element | Database Column |
|---|---|
id | id |
type | type |
test | test |
payout_id | payout_id |
payout_status | payout_status |
currency | currency |
amount | amount |
fee | fee |
net | net |
source_id | source_id |
source_type | source_type |
source_order_transaction_id | source_order_transaction_id |
source_order_id | source_order_id |
processed_at | processed_at |
shop::id | shop_id |
Payouts
Endpoint: Payouts
Update Method: UPSERT
Table Name: [
shopify.payouts]
| JSON Element | Database Column |
|---|---|
id | payout_id |
status | status |
date | payout_date |
currency | currency |
amount | amount |
summary::adjustments_fee_amount | adjustments_fee_amount |
summary::adjustments_gross_amount | adjustments_gross_amount |
summary::charges_fee_amount | charges_fee_amount |
summary::refunds_fee_amount | refunds_fee_amount |
summary::refunds_gross_amount | refunds_gross_amount |
summary::reserved_funds_fee_amount | reserved_fee_amount |
summary::reserved_funds_gross_amount | reserved_gross_amount |
summary::retried_payouts_fee_amount | retried_payouts_fee_amount |
summary::retried_payouts_gross_amount | retried_payouts_gross_amount |
Price Rules
Endpoint: Price Rules
Update Method: UPSERT
Table Name: [
shopify.price_rules]
| JSON Element | Database Column |
|---|---|
id | price_rule_id |
title | title |
target_type | target_type |
target_selection | target_selection |
allocation_method | allocation_method |
value_type | value_type |
value | value |
once_per_customer | once_per_customer |
usage_limit | usage_limit |
customer_selection | customer_selection |
starts_at | price_rule_starts_at |
ends_at | price_rule_ends_at |
created_at | created_at |
updated_at | updated_at |
shop::id | shop_id |
Products
Endpoint: Product
Update Method: UPSERT
Table Name: [
shopify.products]
| JSON Element | Database Column |
|---|---|
id | product_id |
handle | product_handle |
product_type | product_type |
tags | tags |
title | product_name |
vendor | vendor_name |
published_scope | published_scope |
published_at | published_at |
created_at | created_at |
updated_at | updated_at |
shop::id | shop_id |
Product Images
Endpoint: Product
Update Method: UPSERT
Table Name: [
shopify.product_images]
| JSON Element | Database Column |
|---|---|
id | product_image_id |
product_id | product_id |
position | position |
src | image_url |
width | image_width |
height | image_height |
created_at | created_at |
updated_at | updated_at |
Product Variants
Endpoint: Product
Update Method: UPSERT
Table Name: [
shopify.product_variants]
| JSON Element | Database Column |
|---|---|
id | variant_id |
barcode | barcode |
inventory_item_id | inventory_item_id |
price | price |
product_id | product_id |
sku | sku |
title | product_name |
weight | weight |
weight_unit | weight_unit |
created_at | created_at |
updated_at | updated_at |
shop::id | shop_id |
Refunds
Endpoint: Refund
Update Method: UPSERT
Table Name: [
shopify.refunds]
| JSON Element | Database Column |
|---|---|
id | refund_id |
order_id | order_id |
note | note |
restock | restock |
processed_at | processed_at |
created_at | created_at |
shop::id | shop_id |
Refund Line Items
Endpoint: Refund
Update Method: UPSERT
Table Name: [
shopify.refund_line_items]
| JSON Element | Database Column |
|---|---|
id | refund_line_item_id |
refund::order_id | order_id |
refund::id | refund_id |
line_item_id | line_item_id |
quantity | quantity |
refund::created_at | created_at |
subtotal | subtotal |
total_tax | total_tax |
line_item::fulfillment_service | line_item_fulfillment_service |
shop::id | shop_id |
Refund Order Adjustments
Endpoint: Refund
Update Method: UPSERT
Table Name: [
shopify.refund_order_adjustments]
| JSON Element | Database Column |
|---|---|
id | refund_order_adjustment_id |
order_id | order_id |
refund_id | refund_id |
amount | amount |
kind | kind |
reason | reason |
tax_amount | tax_amount |
shop::id | shop_id |
Shops
Endpoint: Shop
Update Method: UPSERT
Table Name: [
shopify.shops]
| JSON Element | Database Column |
|---|---|
id | id |
name | name |
customer_email | customer_email |
domain | domain |
myshopify_domain | myshopify_domain |
address1 | address1 |
city | city |
province | state |
country | country |
zip | zipcode |
phone | phone_number |
country_code | country_code |
latitude | latitude |
longitude | longitude |
currency | currency |
iana_timezone | timezone_name |
shop_owner | shop_owner |
multi_location_enabled | multi_location_enabled |
created_at | created_at |
updated_at | updated_at |
shop::id | shop_id |
Tender Transactions
Endpoint: Tender Transaction
Update Method: UPSERT
Table Name: [
shopify.tender_transactions]
NOTE: Please contact support@daasity.com to enable this table.
| JSON Element | Database Column |
|---|---|
id | id |
order_id | order_id |
amount | amount |
currency | currency |
user_id | user_id |
test | test |
processed_at | processed_at |
remote_reference | remote_reference |
payment_details::credit_card_number | payment_details_credit_card_number |
payment_details::credit_card_company | payment_details_credit_card_company |
payment_method | payment_method |
shop::id | shop::id |
Transaction Fee
Endpoint: Transactions
Update Method: UPSERT
Table Name: [
shopify.transaction_fee]
NOTE: Please contact support@daasity.com to enable this table.
| JSON Element | Database Column |
|---|---|
id | id |
order_id | order_id |
fee:amount | amount |
fee:amount::currencyCode | amount_currency_code |
fee:flatFee::amount | flat_fee_amount |
fee:flatFee::currencyCode | flat_fee_currency_code |
fee:flatFeeName | flat_fee_name |
fee:rate | rate |
fee::rateName | rate_name |
fee:taxAmount::amount | tax_amount |
fee:taxAmount::currencyCode | tax_amount_currency_code |
fee:type | type |
shop::id | shop_id |
Transactions
Endpoint: Transactions
Update Method: UPSERT
Table Name: [
shopify.transactions]
| JSON Element | Database Column |
|---|---|
id | transaction_id |
order_id | order_id |
amount | transaction_amount |
authorization | authorization |
currency | currency_code |
error_code | error_code |
gateway | gateway |
kind | transaction_type |
payment_details::avs_result_code | avs_result_code |
payment_details::credit_card_bin | credit_card_bin |
payment_details::credit_card_company | credit_card_company |
payment_details::cvv_result_code | cvv_result_code |
receipt::gift_card_id | receipt_gift_card_id |
source_name | source_name |
status | status |
test | test_transaction |
processed_at | processed_at |
created_at | created_at |
shop::id | shop_id |
Transactions Receipt
Endpoint: Transactions
Update Method: UPSERT
Table Name: [
shopify.transactions_receipt]
NOTE: Please contact support@daasity.com to enable this table.
| JSON Element | Database Column |
|---|---|
transaction_id | transaction_id |
order_id | order_id |
name | name |
value | value |
Users
Endpoint: User
Update Method: UPSERT
Table Name: [
shopify.users]
NOTE: This table is only available for merchants on Shopify Plus.
Please contact support@daasity.com to enable this table, if you are a Shopify Plus merchant.
| JSON Element | Database Column |
|---|---|
id | id |
account_number | account_number |
bio | bio |
first_name | first_name |
im | im |
last_name | last_name |
phone | phone |
receive_announcements | receive_announcements |
screen_name | screen_name |
url | url |
locale | locale |
user_type | user_type |
store | store |
shop::id | shop_id |
User Permissions
Endpoint: User
Update Method: UPSERT
Table Name: [
shopify.users]
NOTE: This table is only available for merchants on Shopify Plus.
Please contact support@daasity.com to enable this table, if you are a Shopify Plus merchant.
| JSON Element | Database Column |
|---|---|
user_id | user_id |
permission | value |
shop::id | shop_id |
Last updated