Connect Shopify to Snowflake with Windsor.ai
+
Store all your Shopify merchant data in Snowflake
About Shopify
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. Merchants can sell Physical products, Digital products, Services and consultations, Memberships, Ticketed experiences and Rentals on the Shopify platform.
About Snowflake
Snowflake is a cloud data warehouse. Contrary to on premise data bases it eliminates the need to maintain hardware infrastructure. I’s architecture allow it to scale dynamically depending on the resource consumption needed by the users. The outcome of working with a cloud data warehouse like Snowflake is a greatly increased productivity of end users working with Snowflake data.
First Step:
Follow the Tutorial Below:
- Add all the columns that are returned by your connector’s URL.
- Create the WINDOSR_UPLOAD role (name has no importance)
- Create a user and add the previously created role to that user
- Grant rights to the DATABASE to the created user
- Grand the rights to the destination table
- Get the rerquired fields from Snowflake to use the Snowflake destination in Onboard Form
- Get the username
- Get the password
- Get the account
- Get the warehouse
- Get the database
- Get the schema
- Get the table name
Second step:
1.You need to select Shopify as a Data Source and Grant Access to Windsor.ai.
Installing the Shopify Connector
The Shopify Connector can only be installed through the Shopify App Store
Search for Windsor connector on the Shopify App Store and install the connector.
Using the Shopify Connector
- Once the the Windsor connector app is installed, select the store you want to connect.
- Click on 2. Select Destination
- Search for the Shopify metrics and dimensions you would like to use in the field list on the right side and select your data destination.
- In Select Destination select Snowflake as destination
- Enter all the credentials required and click Save
- Your data will now be streamed to Snowflake. In case there is an error you will be notified.
You should also read:
Addressing attribution problems in Shopify for subscription products
Shopify metrics and dimensions available for streaming into Snowflake
| Name | Type | Description |
|---|---|---|
| Account ID | TEXT | Shopify Account URL |
| Account Name | TEXT | Shopify Account URL |
| Marketing Event Breadcrumb ID | TEXT | The breadcrumb ID of the event. |
| Marketing Event Budget Type | TEXT | The budget type of the event. |
| Customer Average Order Value | NUMERIC | Average order value is the average dollar amount customer spend each time an order is placed on your ecommerce website. |
| Customer Created at (Timestamp) | TIMESTAMP | The date and time (ISO 8601 format) when the customer was created. |
| Customer Currency | TEXT | The three-letter code (ISO 4217 format) for the currency that the customer used when they paid for their last order. Defaults to the shop currency. Returns the shop currency for test orders. |
| Customer Created At (Date) | DATE | The date and time (ISO 8601 format) when the customer information was last updated. |
| Customer Default Address | OBJECT | The customer's default address. |
| Customer E-Mail Address | TEXT | The unique email address of the customer. Attempting to assign the same email address to multiple customers returns an error. |
| Customer First Name | TEXT | The customer's first name. |
| Customer ID | TEXT | A unique identifier for the customer. |
| Customer Last Name | TEXT | The customer's last name. |
| Customer Last Order ID | TEXT | The ID of the customer's last order. |
| Customer Last Order Name | TEXT | The name of the customer's last order. This is directly related to the name field on the Order resource. |
| Customer Multipass Identifier | NUMERIC | A unique identifier for the customer that's used with Multipass login. |
| Customer Note | TEXT | A note about the customer. |
| Customer Orders Count | NUMERIC | The number of orders associated with this customer. |
| Customer Phone Number | TEXT | The unique phone number (E.164 format) for this customer. |
| Customer state | TEXT | The state of the customer's account with a shop (Can be disabled, invited, enabled and declined) |
| Customer Tags | TEXT | Tags that the shop owner has attached to the customer, formatted as a string of comma-separated values. |
| Customer Tax Exempt | BOOLEAN | Whether the customer is exempt from paying taxes on their order. If true, then taxes won't be applied to an order at checkout. If false, then taxes will be applied at checkout. |
| Customer Total Spent | NUMERIC | The total amount of money that the customer has spent across their order history. |
| Customer Updated at (Timestamp) | TIMESTAMP | The date and time (ISO 8601 format) when the customer information was last updated. |
| Customer Email Verified | BOOLEAN | Whether the customer has verified their email address. |
| Date | DATE | The date and time (ISO 8601 format) of the queried object. |
| Order Email | TEXT | The customers email address. |
| Marketing Ended At | TIMESTAMP | The event end datetime. |
| Marketing Event Type | TEXT | The event type. |
| Line Item Discount Allocations | TEXT | An ordered list of amounts allocated by discount applications. Eachdiscount allocation is associated with a particular discount application.amount: The discount amount allocated to the line in the shop currency.discount_application_index: The index of the associated discount application in the order'sdiscount_applications list.amount_set: The discount amount allocated to the line item in shop and presentmentcurrencies. |
| Line Item Duties | TEXT | A list of duty objects, each containing information about a duty on the line item. |
| Line Item Fulfillable Quantity | NUMERIC | The amount available to fulfill, calculated as follows:quantity - max(refunded_quantity, fulfilled_quantity) - pending_fulfilled_quantity - open_fulfilled_quantity |
| Line Item Fulfillment Service | TEXT | The service provider that's fulfilling the item. Valid values: manual,or the name of the provider, such as amazon or shipwire. |
| Line Item Fulfillment Status | TEXT | How far along an order is in terms line items fulfilled. Valid values:null, fulfilled, partial, and not_eligible. |
| Line Item Gift Card | TEXT | Whether the item is a gift card. If true, then the item is not taxed orconsidered for shipping charges. |
| Line Item Grams | NUMERIC | The weight of the item in grams. |
| Line Item Id | TEXT | The ID of the line item. |
| Line Item Name | TEXT | The name of the product variant. |
| Line Item Origin Location | TEXT | The location of the line item’s fulfillment origin.id: The location ID of the line item’s fulfillment origin. Used by Shopify to calculateapplicable taxes. This is not the ID of the location where the order was placed. You can use the FulfillmentOrderresource to determine the location an item will be sourced from.country_code: The two-letter code (ISO 3166-1 format) forthe country of the item's supplier.province_code: The two-letter abbreviation for the region of the item's supplier.name: The name of the item's supplier.address1: The street address of the item's supplier.address2: The suite number of the item's supplier.city: The city of the item's supplier.zip: The zip of the item's supplier. |
| Line Item Price | NUMERIC | The price of the item before discounts have been applied in the shop currency. |
| Line Item Price Set | TEXT | The price of the line item in shop and presentment currencies. |
| Line Item Product Id | TEXT | The ID of the product that the line item belongs to. Can be null ifthe original product associated with the order is deleted at a later date. |
| Line Item Properties | TEXT | An array of custom information for the item that has been added to the cart. Oftenused to provide product customization options. |
| Line Item Quantity | NUMERIC | The number of items that were purchased. |
| Line Item Requires Shipping | TEXT | Whether the item requires shipping. |
| Line Item SKU | TEXT | The item's SKU (stock keeping unit). |
| Line Item Tax Lines | TEXT | A list of tax line objects, each of which details a tax applied to the item.title: The name of the tax.price: The amount added to the order for this tax in the shop currency.price_set: The amount added to the order for this tax in shop and presentment currencies.rate: The tax rate applied to the order to calculate the tax price.channel_liable: Whether the channel that submitted the tax line is liable for remitting. Avalue of null indicates unknown liability for the tax line. |
| Line Item Taxable | TEXT | Whether the item was taxable. |
| Line Item Tip Payment Gateway | TEXT | The payment gateway used to tender the tip, such asshopify_payments. Present only on tips. |
| Line Item Tip Payment Method | TEXT | The payment method used to tender the tip, such as Visa.Present only on tips. |
| Line Item Title | TEXT | The title of the product. |
| Line Item Total Discount | NUMERIC | The total amount of the discount allocated to the line item in the shopcurrency. This field must be explicitly set using draft orders, Shopify scripts, or the API. Instead of usingthis field, Shopify recommends using discount_allocations, which provides the same information. |
| Line Item Total Discount Set | NUMERIC | The total amount allocated to the line item in the presentment currency.Instead of using this field, Shopify recommends using discount_allocations, which provides the sameinformation. |
| Line Item Variant ID | TEXT | The ID of the product variant. |
| Line Item Variant Title | TEXT | The title of the product variant. |
| Line Item Vendor | TEXT | The name of the item's supplier. |
| Marketing Event Manage URL | TEXT | The manage URL of the event. |
| Marketing Event Activity ID | TEXT | The activity ID of the event. |
| Marketing Event Channel | TEXT | The markerting channel of the event. |
| Marketing Event Budget | NUMERIC | The budget of the event. |
| Marketing Event Currency | NUMERIC | The currency of the event. |
| Marketing Event Description | TEXT | The event description. |
| Marketing Event Paid | BOOLEAN | The flag that indicates if an event is paid. |
| Order App ID | TEXT | The ID of the app that created the order. |
| Order Billing Address | OBJECT | The orders billing address. |
| Order Cancel Reason | TEXT | The reason why the order was canceled. |
| Order Cancelled At | TIMESTAMP | The date and time ( ISO 8601 format) when the order was canceled. |
| Order Closed At | TIMESTAMP | The date and time (ISO 8601 format) when the order was closed. |
| Order Count | NUMERIC | The number of orders. Default value is 1, but for refunds, the value is zero. |
| Order Created At | TIMESTAMP | The autogenerated date and time (ISO 8601 format) when the order was created in Shopify. The value for this property cannot be changed. |
| Order Currency | TEXT | The three-letter code (ISO 4217 format) for the shop currency. |
| Order Current Subtotal Price | NUMERIC | The current subtotal price of the order in the shop currency. The value of this field reflects order edits, returns, and refunds. |
| Order Current Total Discounts | NUMERIC | The current total discounts on the order in the shop currency. The value of this field reflects order edits, returns, and refunds. |
| Order Current Total Price | NUMERIC | The current total price of the order in the shop currency. The value of this field reflects order edits, returns, and refunds. |
| Order Current Total Tax | NUMERIC | The current total taxes charged on the order in the shop currency. The value of this field reflects order edits, returns, or refunds. |
| Order Email | TEXT | The customers email address. |
| Order Payment Status | TEXT | The status of payments associated with the order. Can only be set when the order is created. |
| Order Fullfillable Quantity | NUMERIC | The fullfillable quantity of the order. |
| Order Fulfillment Service | TEXT | The order fulfillment service. |
| Order Gross Sales | NUMERIC | The order gross sales (before taxes, shipping, discounts, and returns). |
| Order ID | TEXT | The unique identifier of the order |
| Total invetory item cost of an order | NUMERIC | Total invetory item cost of an order. |
| Inventory Item Id | TEXT | The ID of the inventory item. |
| Order Line Items | OBJECT | The line items of the order. |
| Order name | TEXT | The name of the order |
| Order Net Margin | NUMERIC | The order net margin. |
| Order Net Sales | NUMERIC | The order net sales (Equates to order_gross_sales - order_total_discounts - order_refunds_net). |
| Order note | TEXT | An optional note that a shop owner can attach to the order. |
| Order Product Price | NUMERIC | The order price. |
| Order Quantity | NUMERIC | The order quantity. |
| Order Refunds | TEXT | A list of refunds applied to the order. |
| Aggregated Order Refunds Net | NUMERIC | Sum of order_refunds_subtotal and order_refunds_refunds_total_tax. |
| Aggregated Order Refunds Subtotal | NUMERIC | The subtotal of the refund line items. |
| Aggregated Order Refunds Total Tax | NUMERIC | The total tax on the refund line items. |
| Order Sales Channel (A duplicate of `order_app_id`) | TEXT | The ID of the app that created the order. |
| Order Shipping Address | OBJECT | The orders shipping address. |
| Order tags | TEXT | Tags attached to the order |
| Order Total Discounts | NUMERIC | The total discounts applied to the price of the order in the shop currency. |
| Order Total Discounts Set | NUMERIC | The total discounts applied to the price of the order in shop and presentment currencies. |
| Order Total Price | NUMERIC | The sum of all line item prices, discounts, shipping, taxes, and tips in the shop currency. Must be positive. |
| Order Updated At | TIMESTAMP | The date and time (ISO 8601 format) when the order was last modified. |
| Marketing Event Preview URL | TEXT | The preview URL of the event. |
| Product Created At Datetime | TIMESTAMP | The date and time the product was created. |
| Product Description | TEXT | Description of the product. |
| Product handle | TEXT | The product handle. |
| Product ID | TEXT | The product variant SKU number. |
| Product Image URL | TEXT | The image URL of the product. |
| Product Inventory Quantity | NUMERIC | The product quantity in inventory. |
| Product Inventory Value | NUMERIC | The sell value of the product inventory. |
| Product Price | NUMERIC | The price of the product. |
| Product Tags | TEXT | Tags attached to the product. |
| Product Title | TEXT | The title of the product. |
| Product Type | TEXT | The type of the product. |
| Product Vendor | TEXT | The vendor of the product. |
| Marketing Event Referring Domain | TEXT | The referring domain of the event. |
| Marketing Remote ID | TEXT | The remote id. |
| Marketing Event Scheduled To End At | TIMESTAMP | The scheduled end date of the event. |
| Variant SKU number | TEXT | The product variant SKU number. |
| Marketing Started At | TIMESTAMP | The event start datetime. |
| Marketing Event UTM Campaign | TEXT | The UTM campaign of the event. |
| Marketing Event UTM Medium | TEXT | The UTM medium of the event. |
| Marketing Event UTM Source | TEXT | The UTM source of the event. |
| Variant ID | TEXT | The unique identifier of the product variant. |
| Variant Title | TEXT | The title of the product variant. |
Windsor.ai: Easily connect Shopify to Snowflake
See the value and return on every marketing touchpoint
Providing 50+ marketing data streams, we make sure that all the data we integrate is fresh and accessible by marketeers, whenever they want.
Spend less time on manual data loading and spreadsheets. Focus on delighting your customers.