Orders Template Column Headers & Fields explained
See what each field/column from our “Orders” template means including possible allowed values and examples.
Fields marked with “Exported only” tag cannot be imported/set in Shopify and but can be exported with Excelify export.
Need a quick start?
Excel Sheet Name
Orders
To tell the app that you are importing Orders you need:
- If using CSV file then the file name needs to contain the word “Order”, for example, “my-shopify-orders.csv”
- If using XLSX file then the sheet/tab name in the file needs to be “Orders”. The file name for the XLSX file does not matter.
Columns Available For Order Export And Import
Basic Columns
| Column | Description |
|---|---|
| ID
Exported only |
ID is automatically generated number by Shopify to identify the item. It is filled in by an Export. When creating new Orders, leave this value empty.Number 11920047631 |
| Name | Name of the Order to import or export.
Shopify allows several orders to have the same name, however, the app uses the Name as the “key” to identify the existing order. So it works in a similar way as Handle for Products. When you are importing an order with the same Name that already exists in your store, then the app will try to update or replace it (depending on what Command you have). When migrating orders from one Shopify store to another, this Name field is the only way to tell that the order is the same as in the other store. To be safe, be sure to have your Order names unique when importing. If you need Shopify to generate the next Name automatically for you, leave the Name column empty, or delete it entirely from the import file. If you need your Order to consist of several rows, the next Order is determined when Number column value changes. The maximum length of the Name is 255 characters. If you import a name longer than that, then Shopify will cut it off at that maximum. Name cannot be updated, it can be only set when creating a new order or replacing one. Text #1039
|
| Command | When importing Order, it tells the app, what to do with it.
NEW UPDATE MERGE REPLACE DELETE IGNORE REPLACE |
| Send Receipt | Tells whether to send a notification receipt Email to the customer when Order is created or updated.
The default is FALSE. See column “Fulfillment: Send Receipt” for sending fulfillment receipts. TRUE FALSE FALSE |
| Inventory Behaviour | The behavior to use when updating inventory. (default: bypass).
bypass decrement_ignoring_policy decrement_obeying_policy bypass |
| Number
Exported only |
When importing, Shopify will automatically increment it.
Order internal number. If the ID and Name are empty when creating new Order, his Number determines which rows belong to which Order, and where the next Order rows begin. Number 1042 |
| Note | Order notes.
It can be a large text going over several lines. Text Here I can write any Order notes. |
| Tags | Tags allow you to add additional elements or “properties” to your order, which you can use for filtering.
See this tutorial on how to bulk manage your Tags. Comma Separated List Urgent, To Review |
| Tags Command | Tell the app, what to do with Tags:
If Tags Command is not set, the app will assume the REPLACE command by default. You can also have Tags listed in multiple rows within the item and with different Tags Commands. See this tutorial on how to bulk manage your Tags. MERGE DELETE REPLACE REPLACE |
| Created At
Exported only |
It will be automatically set to the time when the Order was actually created. When migrating from another store, this time will always be the time of migration.
Date & Time 2018-02-06 23:03:00 UTC |
| Updated At
Exported only |
It will be automatically updated, whenever you change the Order data.
Date & Time 2018-02-06 23:03:00 UTC |
| Processed At | The date and time when an order was processed. This value is the date that appears on your orders and that’s used in the analytic reports. By default, it matches the created_at value.
When importing new order you can set this date and time and it will show in the Shopify Admin. For API created orders it also can be updated. Date & Time 2018-02-06 23:03:00 UTC |
| Cancelled At | This column will show the time when the order was canceled.
It will be empty if the order is not canceled. Date & Time 2018-02-06 23:03:00 UTC |
| Cancel: Reason | Shows the reason, why the order was canceled.
It will be empty if the order is not canceled. When the order is canceled, the cancel reason will be one of the following:
Text other |
| Cancel: Send Receipt | Whether to send the e-mail to the customer telling that the Order was canceled.
If the Order is already canceled then it will fail – because it cannot send the e-mail to already canceled Order. TRUE FALSE FALSE |
| Cancel: Refund | When you are canceling the Order, if you set this to TRUE then this Order will be also fully refunded.
TRUE FALSE FALSE |
| Closed At | This column will show the time when the order was archived.
Date & Time 2018-02-07 23:03:00 UTC |
| Currency | Currency of the Order.
Currency code in ISO 4217 format USD |
| Source | Cannot be updated, can be only set when creating a new order or replacing one.
When exported, will have the original channel, where the order came from, like: web, pos, iphone, android, shopify_draft_order. When importing as new or replaced order, you can set any value (except the following Shopify reserved words: web, pos, iphone, android, shopify_draft_order). If you leave this value blank when creating the order, it will be set to “Excelify.io App”. Text shopify_draft_order |
| User ID
Exported only |
Shopify internal user ID, who created the order.
When importing, Shopify will set it to the ID of the user who initiated the import. Number 118774735 |
| Checkout ID
Exported only |
Shopify internal ID for the checkout that customer went through.
Number 12615473235 |
| Cart Token
Exported only |
For orders that were initiated through the shopping cart, this will be the reference to the cart from which the order was created.
When importing, this value cannot be set. Hash 68778783ad298f1c80c3bafcddeea |
| Token
Exported only |
Token key, how the order is identified in the Shopify system.
It’s unique to all stores. You cannot set its value, it is always set by Shopify. Token is used in the Order status URL. Hash 11a70068657e9f32ddcd11b7da8d797c |
| Order Status URL
Exported only |
The URL, where a customer can view their order status.
URL https://checkout.shopify.com/20255113
/orders/11a70068657e9f32ddcd11b7da8d797c
|
| Weight Total
Exported only |
Total weight of the ordered items, in grams.
Number 4000 |
| Price: Total Line Items
Exported only |
Total price of all line items. This is calculated from actual line items, and cannot be set as a value in the import.
Currency 1200 |
| Price: Subtotal
Exported only |
Value of the order, including discounts, but before shipping and taxes.
Currency 1000 |
| Tax 1: Title Tax 2: Title Tax 3: Title Exported only |
To import Taxes – fill those for the Line Items.
Title of each applied tax. Each order can have up to 3 different taxes applied, therefore all those are split into separate tax columns. Text VAT |
| Tax 1: Rate Tax 2: Rate Tax 3: Rate Exported only |
To import Taxes – fill those for the Line Items.
The rate of the tax applied. Number 0.21 |
| Tax 1: Price Tax 2: Price Tax 3: Price Exported only |
To import Taxes – fill those for the Line Items.
The calculated tax in the order currency. Currency 210.00 |
| Tax: Included | Cannot be updated, it can be only set when creating a new order or replacing one.
Tells whether the order subtotal has taxes included or not. TRUE FALSE TRUE |
| Tax: Total
Exported only |
Total amount of the tax applied to the order.
Currency 300.43 |
| Price: Total
Exported only |
Total amount of the order, including line items, discounts, shipping cost, and taxes.
Currency 1300.43 |
| Payment: Status | Financial status of the order. Will be one of the following:
When importing, you can set this value, but this will just mark the status on Admin, however, it will not generate the respective Transactions or Refund Transaction, so the “Paid by customer” will show 0. To have the amount visible in the “Paid by customer” you need to add the respective “Transaction” or “Refund” lines. By default, it is set to “paid”. pending authorized partially_paid paid partially_refunded refunded voided paid |
| Payment: Processing Method
Exported only |
The method used to process the payment. Will be one of the following:
The method is deduced by the actual transactions on the order. checkout direct manual offsite express direct |
| Order Fulfillment Status
Exported only |
Summarized status of the order fulfillment.
Will be one of the following:
To import Order with a certain Fulfillment Status, you need to create Fulfillment Lines and fill in Fulfillment columns. See documentation further. Tutorial – Bulk Unfulfill your Shopify Orders – cancel those Fulfillments. <empty> fulfilled partial fulfilled |
| Additional Details | Additional details collected on the order from the customer – either from the product page or from the checkout page.
Each added property will be shown as “<name>: <value>”, and will include all of those properties as several lines inside the Excel cell. When prepared in the same format, those details also can be imported. Those are visible in Shopify Admin, on each Order, section “Additional Details”. This section shows only if there are any details added, otherwise it’s not visible there. Read more about collecting additional data for orders: Lines of name: value Gift wrapped: yes Door code: 1234 |
| Row #
Exported only |
Will have row number from 1 till the end for each row. So that if you change the sorting in your exported file to easier update your data, you can sort it back by “Row #” and return it to the original sorting. Because, you know, all the item data should be together – and if you change the sorting, you need to sort it back. |
| Top Row
Exported only |
It will have value “TRUE” for each “top row” of the item. That can help you filter in Excel to get only the base rows, in case you need to get rid of repeated rows for each item. Some sheets already had this as Primary Row column earlier, but we have added it now to all the sheets now and renamed to Top Row so it’s more clear. |
Customers
Each Order can have one customer or no customer at all.
When importing Orders with Customer data, Shopify will link them by Email to existing customers, or create new Customers if they don’t exist yet.
If you need to change the fields of existing Customer, you cannot change them through Draft Order – you need to import the update through the Customers sheet.
| Column | Description / Example Value |
|---|---|
| Customer: ID | Shopify generated Customer ID. If specified existing customer ID from that store, Order will be linked to that customer.If you don’t have an ID, then leave it empty, and Shopify will link to existing customer by Customer Email.
Cannot be updated, can be only set when creating a new order or replacing one. Number 1234567890 |
| Customer: Email | Customer e-mail who purchased the Order.The app will link to existing store Customer by this e-mail or Phone number if e-mail is empty. If the app will not be able to find existing customer to link to, it will create a new customer.
Shopify is very strong about validating e-mail addresses – it is checking if e-mail actually exists in real life. If you import e-mails and get the error that Email is not valid then first you should check that this e-mail is really correct. If it happens to be some old customer Email which you still need to import then you can move that Email value to Note field and leave this Email field empty. When updating – will update only contact information on the Order, but will not change the data of the Customer. [email protected] |
| Customer: Phone | Phone number must be full – with the country code and must be valid for the respective country.You can prefix phone numbers with ‘ (apostrophe) to allow Excel to show the + (plus) sign. But the + (plus sign) is not required.
The app will clean out any non-digit characters from the Phone field. If e-mail is empty, the app will search existing customer by Phone number – and if will find, then will attach this order to that customer. To change for an existing Order – change the data of the Customer. Phone Number '+15417543000 |
| Customer: First Name | The first name of the Customer.To change for an existing Order – change the data of the Customer.
Text John |
| Customer: Last Name | The last name of the Customer.To change for an existing Order – change the data of the Customer.
Text Smith |
| Customer: Note | Whatever is written on the Customer card, accessible from Shopify Admin.To change for an existing Order – change the data of the Customer.
Text Your notes go here. |
| Customer: Orders Count
Exported only |
Total count of orders made by this Customer.
Number 100 |
| Customer: State | The Customer status – one of those:
To change for an existing Order – change the data of the Customer. disabled invited enabled declined enabled |
| Customer: Total Spent
Exported only |
Total amount the customer has spent at the moment of the export.
Amount 1024.99 |
| Customer: Tags | Tags allow you to add additional elements or “properties” to your Customer, which you can use for filtering, for developing some additional logic for those Customers in your Store Theme.To change for an existing Order – change the data of the Customer.
Comma Separated List Friend, Migrated |
| Customer: Accepts Marketing | Whether Customer accepted that he can be sent Marketing e-mails.To change for an existing Order – change the data of the Customer.
TRUE FALSE TRUE |
Customers – Billing Address Columns
All the following fields will be shown in the Order in the Billing Address block. When imported, will be added as one of the addresses for the Customer, too.
| Column | Description |
|---|---|
| Billing: First Name | The first name of the Customer in the Billing Address.
Text
John |
| Billing: Last Name | The last name of the Customer.
Required. If empty, the address will not be created. Text Smith |
| Billing: Company | Company name, if the billing address is a company.
Text Smith Inc. |
| Billing: Phone | Phone number to use for this address.
Phone Number '+15417543002 |
| Billing: Address 1 | Address 1st line.
Required. If empty, the address will not be created. Text Calvin Street 5 |
| Billing: Address 2 | Address 2nd line.
Text 2nd Floor |
| Billing: Zip | Zip number of the address. Shopify doesn’t check its correctness.
Zip ZIP-1234 |
| Billing: City | City name. Is not checked, so it’s possible to enter any city name.
Required. If empty, the address will not be created. Text Dough |
| Billing: Province | Province names are checked against Shopify geographic classifier. If provided province will not be valid, then Shopify will accept it, but it will be empty on the customer card.
If you are not sure, what are available values, go to Shopify Admin, edit the Address, and click the drop-down to choose the Province for chosen Country. If Province Code is filled, this can be left empty, then Province will be set from there. You can see list of all Shopify approved Countries, Provinces, and their codes in Excelify Countries and Provinces documentation. Province Name Ontario |
| Billing: Province Code | In Shopify classifier there are Province Codes. If provided province code will not be valid, then Shopify will accept it, but it will be empty on the customer card.
If Province is filled, this can be left empty, then Province will be set from there. You can see list of all Shopify approved Countries, Provinces, and their codes in Excelify Countries and Provinces documentation. Province Code ON |
| Billing: Country | Country names are checked against Shopify geographic classifier. If the provided country will not be valid, then Shopify will accept it, but it will be empty on the customer card.
If you are not sure, what are available values, go to Shopify Admin, edit the Address, and click the drop-down to choose the Country. If Country Code is filled, this can be left empty, then Country will be set from there. You can see list of all Shopify approved Countries, Provinces, and their codes in Excelify Countries and Provinces documentation. Required Country or Country Code. If empty, the address will not be created. Country Name Canada |
| Billing: Country Code | Country international code, consisting of 2 letters.
If Country is filled, this can be left empty, then Country will be set from there. You can see list of all Shopify approved Countries, Provinces, and their codes in Excelify Countries and Provinces documentation. Country Code CA |
Customers – Shipping Address Columns
All the following fields will be shown in the Order in the Shipping Address block. When imported, it will be added as one of the addresses for the Customer, too.
Those columns are the same, as for Billing Address, just the column name starts with “Shipping: …”, so those will not be repeated here.
Customers – Browser Related Columns
All the following columns can just be exported, but they provide valuable information about the customer, the source the customer came from, even the marketing campaign.
| Column | Description |
|---|---|
| Browser: IP
Exported only |
The IP address of the customer at the moment of purchase.
IP Address 159.148.78.222 |
| Browser: Width
Exported only |
Width of the customer browser window, in pixels.
Number 1024 |
| Browser: Height
Exported only |
Height of the customer browser window, in pixels.
Number 768 |
| Browser: User Agent
Exported only |
Full user agent string of the customer browser. From this string, it is possible to reconstruct further details like the used browser and the operating system.
There are different online resources that allow decoding this information, for Example: https://useragentapi.com Text Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_2) AppleWebKit/604.4.7 (KHTML, like Gecko) Version/11.0.2 Safari/604.4.7 |
| Browser: Landing Page
Exported only |
The first page the client landed on your site, before making the purchase. This allows you to analyze, which pages are the ones that drive most purchases eventually, and also allows you to draw conclusions about the effectiveness of your marketing efforts.
URL https://yourdomain.com/products/this-nice-tshirt |
| Browser: Referrer
Exported only |
The site, where your customer came from when eventually made a purchase order. This allows you to understand, what are your most valuable traffic sources.
URL https://www.google.com?g=398798234 |
| Browser: Referrer Domain
Exported only |
Since the Referrer URLs will be listed with all the URL parameters, it might be hard to conclude, which of the traffic sources give the most valuable traffic. That’s why this field contains just the domain of the referrer so that you can group them together when doing your analysis.
Domain google.com |
| Browser: Search Keywords
Exported only |
Keywords used in the search engines, when your store was found, which led to the order. This is an even deeper insight into how your customers find your store, and which keywords sell.
Text blue tshirt with thousand colors |
| Browser: Ad URL
Exported only |
The destination URL that was included in your Ad. This allows you to know and measure, which URLs from your ad campaigns are most effective to produce orders.
URL https://yourdomain.com/some-page |
| Browser: UTM Source
Exported only |
Source, extracted from UTM parameter “utm_source”.
For AdWords campaigns, those parameters are set automatically, but you can also send traffic to your store by filling them according to your needs. Read more about UTM parameters here: https://ga-dev-tools.appspot.com/campaign-url-builder/ Text |
| Browser: UTM Medium
Exported only |
Medium, extracted from UTM parameter “utm_medium”.
Text cpc |
| Browser: UTM Campaign
Exported only |
Campaign, extracted from UTM parameter “utm_campaign”.
Text spring_sale |
| Browser: UTM Term
Exported only |
Term, extracted from UTM parameter “utm_term” – identifies the paid search keywords entered by the customer, when showing your ad campaign.
Text tshirt |
| Browser: UTM Content
Exported only |
Content, extracted from UTM parameter “utm_content” – identifies the ad variation, or the specific element or version you are testing for your campaign.
Text button-a |
Line Items
Each Order must have at least one line item.
| Column | Description |
|---|---|
| Line: Type | An order consists of “Line Items”, where each of them as a certain line type. That allows to group items together in a fashion that can be reconciled with actual order data, and most importantly – cross-check that all amounts match.
Some of the line types will have details in the Line Item columns, but some will have details in their own respective columns.
This column is validated by the app. If an invalid value is entered then that Order will fail to import. Line Item Discount Shipping Line Transaction Refund Line Refund Shipping Fulfillment Line Ignore Line Item |
| Line: ID
Exported only |
The line identifier.
When exporting, the Shopify generated IDs will be used. Also, when importing, the IDs will be re-mapped to new IDs, which Shopify will generate. Some lines need ID, some don’t, depending on the line type:
Number 728763138063 |
| Line: Command | The command for what to do with the “Line Item” row types, when updating an existing Order.
Allowed values:
Note that Shopify doesn’t allow adding Line Items to an Archived Order – that’s why we will unarchive, add, and archive your Order if it’s Archived. Example DEFAULT |
| Line: Product ID | Product ID of the line item, generated by Shopify – if the line item is a product.
Line items can be custom, without a link to the Product, in that case, the Product ID will be empty. When importing an Order, can have a real product ID from the store. If such ID will not be found in the store (e.g. when copying Orders from another store), then the app will try to match it by “Line: Product Handle” column value. Number 200005386255 |
| Line: Product Handle | This is the secondary way, how to identify the product if it can’t be found by Product ID.
It is used in cases when copying orders from one store to another, which have the same Products – then the right product will be linked to the order by the Handle. If no Product Handle is set in the file, then the app will try to link Line Item to the product by the Line: SKU column. Text tshirt-with-picture-of-me |
| Line: Title | Has a different value, depending on the Line Type:
If Title will contain any HTML tags then they will be removed by the app upon import. Text Tshirt With Picture Of Me |
| Line: Name | Has a different value, depending on the Line Type:
Text Tshirt With Picture Of Me - Red / XS |
| Line: Variant ID | Variant ID of the line item, generated by Shopify – if the line item is a product.
In the case of the a custom line item, this ID will be empty. When importing an Order, you can have a real variant ID from the store. If such ID will not be found in the store for that product (e.g. when copying Orders from another store), then the app will try to match it by “Line: SKU” column value. If you only have the “Variant: ID” value – you can still link that variant to the Product Variant in the Line Item by filling this value with the corresponding Product “Variant ID” value. Number 1834691428367 |
| Line: Variant Title | Has a different value, depending on the Line Type:
Text Red / XS |
| Line: SKU | Has a different value, depending on the Line Type:
Text SKU-1234567 |
| Line: Quantity | Has a different value, depending on the Line Type:
Having positive and negative quantities in their respective lines allows summing this quantity column and will always give a quantity that still needs to be either fulfilled or refunded. Good for reporting. Number 100 |
| Line: Price | Has a different value, depending on the Line Type:
Currency 5.49 |
| Line: Discount | Has a different value, depending on the Line Type:
Amounts in this column are organized so that you can sum all discount column per order, and get the correct total discount value for that Order. Currency 49.00 |
| Line: Total | Has a different value, depending on the Line Type:
Amounts in this column are organized so that you can sum all Line Totals per order, and get the correct total value for that Order. If the order is fully refunded, then the sum of all total lines for that Order must be 0. Currency 500.00 |
| Line: Grams | Has a different value, depending on the Line Type:
Number 250 |
| Line: Requires Shipping | Has a different value, depending on the Line Type:
TRUE FALSE TRUE |
| Line: Vendor | Vendor name of the product purchased.
Text Puma |
| Line: Properties | Additional details collected on the order from the customer about the line item – either from the product page or from the checkout page.
Each added property will be shown as “<name>: <value>”, and will include all of those properties as several lines inside the Excel cell. When prepared in the same format, those details also can be imported. Those are visible in Shopify Admin, on Order, for each Line Item. Those properties show only if there are any added, otherwise, they are not shown here. Read more about collecting additional properties for order line items: Lines of name: value Color: Red Engraving: My Name |
| Line: Gift Card | TRUE if the line item is a gift card.
If Line Item is Gift Card then by default Shopify automatically fulfills the Gift Card Line Items once the order is paid. TRUE FALSE FALSE |
| Line: Gift Card Send to Customer | Sending (fulfilling) gift cards is Shopify setting that can be disabled in a store, but it turned on by default. This column is for additional safety so that Gift Cards are not sent for old orders when migrating from store to store.Default and exported value is “No” – Importing with “No” will import the line item as a custom line item so that Shpify does not automatically send out actual gift cards to customers.Importing value “Yes” – allows creating a fulfillment of the Gift Card as a real one, and send it to the Customer. Might fail if Shopify also is auto-fulfilling Gift Cards as well.Importing value “Shopify” – creates a real Gift Card line item, and delegate the fulfillment action to whatever is configured in Shopify Admin -> Settings -> Checkout. No Yes Shopify No |
| Line: Taxable | TRUE if the line item is taxable.
TRUE FALSE TRUE |
| Line: Tax 1 Title Line: Tax 2 Title Line: Tax 3 Title |
Title of each applied tax.
Each order can have up multiple taxes, therefore all those are split into separate tax columns. You can import up to 100 Taxes for each Line Item, but export only the first 3. Text VAT |
| Line: Tax 1 Rate Line: Tax 2 Rate Line: Tax 3 Rate |
The rate of tax applied.
You can import up to 100 Taxes for each Line Item, but export only the first 3. Decimal Number 0.21 |
| Line: Tax 1 Price Line: Tax 2 Price Line: Tax 3 Price |
The calculated tax in the order currency.
You can import up to 100 Taxes for each Line Item, but export only the first 3. Currency 105.00 |
| Line: Fulfillable Quantity
Exported only |
The quantity of the line item that is still left to be fulfilled.
For import this is calculated from actual fulfillments. Number 50 |
| Line: Fulfillment Service | Service provider handle who is doing the fulfillment.
To find out the handle, easiest is to enter one through the Shopify Admin, and then look in the export file. If empty, will be assumed as “manual” by default. manual amazon shipware <other provider handle> manual |
| Line: Fulfillment Status
Exported only |
Summary of the line item fulfillment status:
fulfilled partial <empty> fulfilled |
Line Items – Shipping Origin Columns
Shipping origin columns extend the rows of line type “Line Item” with additional information – the location and the warehouse from where the line items are being shipped. It can only be exported, as it is tied to the shipping provider actually used.
To be used for reporting and tracking of shipments.
| Column | Description |
|---|---|
| Shipping Origin: Name
Exported only |
The shipping origin name. If no shipping provider us used, then this will be the Shopify store contact address |
| Shipping Origin: Country Code
Exported only |
2 character country code |
| Shipping Origin: Province Code
Exported only |
2 character province code |
| Shipping Origin: City
Exported only |
Name of the city |
| Shipping Origin: Address 1
Exported only |
Address line 1 |
| Shipping Origin: Address 2
Exported only |
Address line 2 |
| Shipping Origin: Zip
Exported only |
Zip code |
Line Items Product Data
Those columns are only exported. They will get ignored if being imported back.
Note that if you change the data of your Shopify Product, the Order Line items will remain as they were when the order was placed, but the Product Data columns will contain the current product data.
| Column | Description |
|---|---|
| Line: Product Type
Exported only |
Product Type
Text T-shirt |
| Line: Product Tags’
Exported only |
Product Tags
Text Color: Red, Eatable, Featured, Type: Skirt |
| Line: Variant SKU
Exported only |
Product Variant SKU
Text SKU-1133 |
| Line: Variant Barcode
Exported only |
Product Variant Barcode
Text 123456789 |
| Line: Variant Weight
Exported only |
Product Variant Weight
Number 20.99 |
| Line: Variant Weight Unit
Exported only |
Product Variant Weight Unit
Text lb |
| Line: Variant Inventory Qty
Exported only |
Product Variant Inventory Quantity
If you have the inventory distributed across several locations, then this quantity will show the total sum of available inventory of this variant across all your locations. Number 99 |
| Line: Variant Cost
Exported only |
Product Variant Cost
Number 14.99 |
Refunds
Refund columns extend the rows of line type “Refund Line”, and contain additional data about refunds.
Only used if:
- “Refund: Restock” is set to TRUE
- Restocking Line Item that is partially fulfilled
- Restocking only part of the line item, not the whole quantity.
If restocking fulfilled part of the Line Item set value “return”.
If restocking unfulfilled part of the Line Item set value “cancel”.
Example:
Line Item with 10 quantity – 5 Fulfilled and 5 Unfulfilled. You are Refunding with a restock of less than 10 quantity.
By setting value in this column you can tell the app if you wish to Restock part that is fulfilled or unfulfilled. If you need to restock some quantity from fulfilled and some from unfulfilled you need to use two rows with all the same information except quantity you wish to restock and restock type cancel
| Column | Description |
|---|---|
| Refund: ID
Exported only |
The ID of the refund, generated by Shopify.
When importing, need to be the same number for all the refund lines that belong to the same Refund. This number will be used to link those refund lines together and include them in that refund transaction. Number 123123123 |
| Refund: Created At | Date and time when the refund was created and processed.
In the export, this field will be when the Refund was actually created in Shopify. When importing new refund, the app will set this date as Refund Processed At date in Shopify. Date & Time 2018-02-06 23:03:00 UTC |
| Refund: Note | Note attached to the refund, usually explaining the refund reason.
Text Agreed with customer. |
| Refund: Restock | TRUE if the line items are being restocked. Meaning that quantities are increased back in stock.
TRUE FALSE TRUE |
| Refund: Restock Type | If “Refund: Restock” is set as TRUE then you can specify the restock type.
cancel return return |
| Refund: Restock Location | If you have the “Refund: Restock” set as TRUEthen you can set here the name of the location to which the restocked items should be sent. If you leave this empty then the items will be sent back to the default location.
Read more about Multi-Location Inventory. Location Name Main Warehouse |
| Refund: Send Receipt | When you create a Refund, set this to TRUE if you want the customer to receive a notification receipt of that refund.
By default this is set to FALSE. TRUE FALSE FALSE |
Transactions
Transaction columns extend the rows of line type “Transaction”, and contain data about transactions.
| Column | Description |
|---|---|
| Transaction: ID
Exported only |
The ID of the transaction, generated by Shopify.
When the transaction for a refund is created, it needs to have also the column “Refund: ID” filled with the same refund ID that was assigned to all refund line items. That way the transaction is linked to the refund. Number 280328273935 |
| Transaction: Kind |
authorization sale capture refund void sale |
| Transaction: Processed At | Date and time when the transaction was created.
Can be also imported – and reports should show the Transactions on the correct dates. Date & Time 2018-02-06 23:03:00 UTC |
| Transaction: Amount | The transaction amount.
For the capture transactions, the amount can be less than authorization amount, so there can be several capture transactions. For refund transactions the transaction amount can be different from the line items total amount being refunded, because sometimes there can be non-full refunds, or refunds that are bigger than the cost of line items. Currency 123.99 |
| Transaction: Currency | The currency of the transaction.
Since the Shopify store can operate in just one currency, it’s best to have the transaction currency the same as the Order currency, and as the Store default currency. Shopify doesn’t do any currency conversions. Currency code in ISO 4217 format USD |
| Transaction: Status | The transaction status is mainly for the export.
When importing, there is no point importing transactions with statuses other than success. success pending failure error success |
| Transaction: Message
Exported only |
Transaction comment generated by the Shopify. Usually the name of the reply of the used payment gateway.
Text PayPal Confirmed |
| Transaction: Gateway | The name of the used payment gateway.
When importing void or refund transaction, must match the same gateway as the original transaction. Text paypal |
| Transaction: Test | TRUE if this is a test transaction.
When setting the Transaction as Test – then it will not show up in your financial reports, in Shopify Admin. Use this if you don’t want your Transactions to impact your reports. TRUE FALSE FALSE |
| Transaction: Authorization
Exported only |
Authorization code provided by the payment gateway.
Number 53433 |
| Transaction: Error Code
Exported only |
The error code, indicating the reason for failed transaction. The same codes for any payment gateway.
incorrect_number invalid_number invalid_expiry_date invalid_cvc expired_card incorrect_cvc incorret_zip incorrect_address card_declined processing_error call_issuer pick_up_card expired_card |
| Transaction: CC AVS Result
Exported only |
The Response code from AVS the address verification system. The code is a single letter; see this chart for the codes and their definitions.
One character |
| Transaction: CC Bin
Exported only |
The issuer identification number (IIN), formerly known as bank identification number (BIN) of the customer’s credit card. This is made up of the first few digits of the credit card number.
Number 1 |
| Transaction: CC CVV Result
Exported only |
The Response code from the credit card company indicating whether the customer entered the card security code, a.k.a. card verification value, correctly. The code is a single letter or empty string; see this chart for the codes and their definitions.
One character |
| Transaction: CC Number
Exported only |
The customer’s credit card number, with most of the leading digits masked with •.
Masked Card Number •••• •••• •••• 4242 |
| Transaction: CC Company
Exported only |
The name of the company who issued the customer’s credit card.
Text Visa |
Transactions – Risk Columns
Each Order has an associated fraud risk evaluation from Shopify or other risk assessment service, which is exported to risk those columns.
| Column | Description |
|---|---|
| Risk: Source | The source of the risk assessment.
Text Shopify |
| Risk: Score | A number between 0 and 1 that is assigned to the order. The closer the score is to 1, the more likely it is that the order is fraudulent.
Decimal number 0.5 |
| Risk: Recommendation | Suggested recommendation for the action to take on the Order in terms of mitigating the risk:
cancel investigate accept accept |
| Risk: Cause Cancel | This will indicate that this risk was severe enough to force the cancellation of the order.
TRUE FALSE FALSE |
| Risk: Message | A message explaining the risk.
Text This order came from an anonymous proxy. |
Fulfillments
Each Order can have none, one or many fulfillments, which are essential – the deliveries of ordered line items. Each fulfillment can contain one or many line items of different quantities grouped by the “Fulfillment: ID” column.
You can fill “Fulfillment: …” columns in the rows that have “Line: Type” set as “Line Item” and the app will fulfill that line item for its full quantity.
| Column | Description |
|---|---|
| Fulfillment: ID
Exported only |
Fulfillment ID generated by Shopify.
Is used to group together line items into fulfillments. Those columns need to be filled for the Line: Type “Fulfillment Line”. Rows with the same Fulfillment ID will be grouped into the same fulfillment. When importing, you can invent your own Fulfillment ID, which will allow you to group line items into fulfillments, but it will not be set in the Shopify, it will be only used by the app to group Fulfillments. Number 311117676559 |
| Fulfillment: Status | The status of the fulfillment. Valid values are:
pending open success cancelled error failure success |
| Fulfillment: Created At
Exported only |
Date and time when the fulfillment was created.
When importing, the fulfillment will be dated at the moment when it is being imported. Date & Time 2018-02-06 23:03:00 UTC |
| Fulfillment: Updated At
Exported only |
Date and time when the fulfillment was last updated.
Date & Time 2019-02-09 15:28:11 UTC |
| Fulfillment: Processed At
Imported only |
Date and time for your last Fulfillment Event.
If you have “Fulfillment: Shipment Status” set to “delivered” or “failure” then this is the delivery or delivery failure date that is displayed in Shopify. Shopify displays this value in the UTC time-zone even if you have saved it in your time-zone. Date & Time 2019-02-08 06:56:27 UTC |
| Fulfillment: Tracking Company | The name of the tracking company – either empty or written exactly as in this list:
Text FedEx |
| Fulfillment: Location | The name of your Location where the fulfillment is being shipped from.
If you leave it empty then the app will assume the first location from your Locations. Read more about Multi-Location Inventory. Important: If your inventory is managed by external service, like Amazon or Oberlo – then using this location to create a fulfillment, will actually initiate fulfillment in that fulfillment service – meaning, it will ship those line items to the customer. Location Name Main Warehouse |
| Fulfillment: Shipment Status | The status of the shipment.
You can also set date for your Shipment Status in the “Fulfillment: Processed At” field. label_printed label_purchased attempted_delivery ready_for_pickup confirmed in_transit out_for_delivery delivered failure delivered |
| Fulfillment: Tracking Number | Tracking number provided by the shipping company.
Semicolon-separated strings 112345Z2345; 12362z2n568ssdg; abc123987qwe |
| Fulfillment: Tracking URL | The URL to track the shipment.
If provided, then it will be imported as given. If empty, then Shopify will generate it from the tracking company and tracking number combination. Semicolon-separated strings https://mypost.com/mail.php?code=123; https://mypost.com/mail.php?code=abc123 |
| Fulfillment: Send Receipt | Tells whether to send a notification Email to the Customer when the Fulfillment is created or updated.
The default is FALSE. TRUE FALSE FALSE |
Optional – Shipping Columns
- It does not matter what is the “Line: Type” where you list this Shipping.
- Only information about one shipping can be added thru these columns if multiple rows will be filled the app will take Shipping from first that has a value in any of these columns.
- If you will also have Shipping Line then the app will add both Shipping to the Order.
Here are all the extra columns you can add to your Order import to add Shipping when creating an Order.
| Shipping Line: Title | The shipping title, written in free form.
Text Worldwide |
| Shipping Line: Code | The shipping code usually has value “custom”.
Text custom |
| Shipping Line: Source | The shipping source, usually “shopify”.
Text shopify |
| Shipping Line: Price | The price of the shipping applied to the order.
Number 5 |
| Shipping Line: Tax Title | Title of the applied tax.
Text VAT |
| Shipping Line: Tax Rate | The rate of tax applied.
Number 0.21 |
| Shipping Line: Tax Price | The calculated tax in the order currency.
Number 1.05 |
Metafields
Read more detailed documentation on Metafields page.
Updating Orders
Shopify limits the ability to update existing Orders – as you also can see in the Shopify Admin that you can update only very limited data for an existing Order.
You can update (using the UPDATE or MERGE Command) only the following columns for Orders:
| Column | Comment |
|---|---|
| Note | |
| Tags | |
| Processed At | You can change the date when Order was created. Will work only for orders created through the Apps. |
| Cancelled At | In some cases, Shopify might not allow canceling an order, for example, if it is already being fulfilled. Although you specify the date here, the Order will be made cancelled as of the moment of the import, because Shopify doesn’t set specific cancel time. |
| Cancel: Reason | Can only cancel an Order with one of the following reasons: customer, inventory, fraud, declined, other. If the cancel reason is not specified, the “other” reason will be assumed by default. |
| Cancel: Send Receipt | Set to TRUE if at the moment of cancelling the Order, you need the client to receive the e-mail notification about the cancelled payment. |
| Closed At | Makes the Order to become Archived. To unarchive, import this column as empty. Although you specify the date here, the Order will be made archived as of the moment of the import, because Shopify doesn’t set specific archive time. |
| Additional Details | |
| Customer: ID | Can only set to empty ID – that will remove the Customer from the Order. Cannot switch the Order to another Customer. |
| Customer: Email | This will change the Contact Information of that Order. |
| Customer: Phone | This will change the Contact Information of that Order. |
| Shipping: First Name | |
| Shipping: Last Name | |
| Shipping: Company | |
| Shipping: Phone | |
| Shipping: Address 1 | |
| Shipping: Address 2 | |
| Shipping: Zip | |
| Shipping: Province | |
| Shipping: Province Code | |
| Line: Type |
|
| Fulfillment: ID | |
| Fulfillment: Status | |
| Fulfillment: Tracking Company | |
| Fulfillment: Shipment Status | |
| Fulfillment: Tracking Number | |
| Fulfillment: Tracking URL | |
| Metafield: … | Any Metafields can be updated. |
You can also add the Line Items to an existing Order, by specifying the “Line: Command” as “MERGE”, and filling in the “Line: …” columns for that Order.
For specific typical Orders updating scenarios, look into Tutorials / Orders.
Good To Know
- If you need to update something beyond that is allowed to update, please use the REPLACE command and replace the whole order with another – that will allow you to set any data at the moment of order creation.
- To import several line items to Order, just repeat the row with the same ID or Name.
- When importing Orders from scratch, start by the minimal fields, and grow it up from there, using the REPLACE command.
- Minimal fields are: Line Item Title, Price and Quantity.
