Shopify Orders Import

Excel Sheet Name

Orders

Index

Columns Available For Export And Import

Need quick start?

Download the sample file of simplified Orders template.

Download full template file.

Base Columns

ColumnAllowed ValuesDescription / Example Value
IDNumberID 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.

11920047631
NameTextName of the Order.

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.

Name cannot be updated, it can be only set when creating a new order or replacing one.

#1049
Command
  • NEW
  • UPDATE
  • REPLACE
  • DELETE
When importing Order, it tells the app, what to do with it.

  • NEW: will try to create a new Order with the provided details. If such Order will already exist (with the same ID or same Name), then Order creation will fail.
  • UPDATE: Allows to update some of the existing Order attributes. Here is the list of what can be updated.
  • REPLACE: at first, if such Order with provided ID or Name exists, then the app will delete it. After that, it will create a new one. If an existing Order will not be found, then still the app will create a new Order.
  • DELETE: the app will delete the existing Order, which is found by ID or Name. If such order will not be found, then this order import will fail. This DELETE command can be very handy, when Orders need to be deleted in bulk, or when such orders need to be deleted, which cannot be deleted through the Shopify Admin.
REPLACE
Send Receipt
  • TRUE
  • FALSE
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. 

Inventory Behaviour
  • bypass
  • decrement_ignoring_policy
  • decrement_obeying_policy
The behavior to use when updating inventory. (default: bypass).

  • bypass: Do not claim inventory.
  • decrement_ignoring_policy: Ignore the product’s inventory policy and claim all amounts no matter what.
  • decrement_obeying_policy: Obey the product’s inventory policy.
NumberNumberOrder 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.

Exported only. When importing, Shopify will automatically increment it.

1042
NoteTextOrder notes.

Can be a large text going over several lines.

Here I can write any notes I need for the Order.
TagsComma Separated ListTags allow you to add additional elements or “properties” to your order, which you can use for filtering.

Urgent, To Review
Created AtDate & TimeThis column will only be exported.

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.

2018-02-06 23:03:00 UTC
Updated AtDate & TimeThis column will only be exported.

It will be automatically updated, whenever you change the Order data.

2018-02-06 23:03:00 UTC
Cancelled AtDate & TimeThis column will show the time when the order was canceled.

It will be empty if the order is not canceled.

2018-02-06 23:03:00 UTC
Cancel: ReasonTextShows 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:

  • customer: The customer changed or canceled the order.
  • fraud: The order was fraudulent.
  • inventory: Items in the order were not in inventory.
  • declined: The order was canceled because the payment has been declined.
  • other: The order was canceled for a reason not in the list above.
Cancel: Send Receipt
  • TRUE
  • FALSE
Whether to send the e-mail to customer telling that the Order was cancelled.

If the Order is already cancelled then it will fail – because it cannot send the e-mail to alreaday cancelled Order.

Cancel: Refund
  • TRUE
  • FALSE
When you are cancelling the Order, if you set this to TRUE then this Order will be also fully refunded.
Processed AtDate & TimeThis column will show the time when the original order was created.

When migrating from the other system, you can set this field to a date, when the order was created in the source system, and Shopify will show that date as order creation date.

2015-01-01 00:00:00 UTC
Closed AtDate & TimeThis column will show the time when the order was archived.

2018-02-07 23:03:00 UTC
CurrencyCurrency code in ISO 4217 formatCurrency of the Order.

USD
SourceTextWhen 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, iphoneandroid, shopify_draft_order).

Cannot be updated, can be only set when creating a new order or replacing one.

If you leave this value blank when creating the order, it will be set to “Excelify.io App”.

shopify_draft_order
User IDNumberShopify internal user ID, who created the order.

When importing, Shopify will set it to the ID of the user who initiated the import.

Only exported.

118774735
Cart TokenHashFor 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.

Only exported.

68778783ad298f1c80c3bafcddeea
TokenHashToken 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.

Only exported.

11a70068657e9f32ddcd11b7da8d797c
Order Status URLURLThe URL, where a customer can view their order status.

Only exported.

https://checkout.shopify.com/20255113/orders/11a70068657e9f32ddcd11b7da8d797c
Weight TotalgramsTotal weight of the ordered items, in grams.

Only exported.

4000
Price: Total Line ItemsCurrencyTotal price of all line items. This is calculated from actual line items, and cannot be set as a value in the import.

Only exported.

1200
Price: SubtotalCurrencyValue of the order, including discounts, but before shipping and taxes.

Only exported.

1000
Tax 1: Title
Tax 2: Title
Tax 3: Title
TextTitle of each applied tax.

Each order can have up to 3 different taxes applied, therefore all those are split into separate tax columns.

Only exported. To import Taxes – fill those for the Line Items.

VAT
Tax 1: Rate
Tax 2: Rate

Tax 3: Rate
NumberThe rate of the tax applied.

Only exported. To import Taxes – fill those for the Line Items.

0.21
Tax 1: Price
Tax 2: Price
Tax 3: Price
CurrencyThe calculated tax in the order currency.

Only exported. To import Taxes – fill those for the Line Items.

210.00
Tax: Included
  • TRUE
  • FALSE
Tells whether the order subtotal has taxes included or not.

Cannot be updated, can be only set when creating a new order or replacing one.

TRUE
Tax: TotalCurrencyTotal amount of the tax applied to the order.

300.43
Price: TotalCurrencyTotal amount of the order, including line items, discounts, shipping cost, and taxes.

1300.43
Payment: Status
  • pending
  • authorized
  • partially_paid
  • paid
  • partially_refunded
  • refunded
  • voided
Financial status of the order. Will be one of the following:

  • pending: The finances are pending. Payment might fail in this state. Check again to confirm whether or not the finances have been paid successfully.
  • authorized: The finances have been authorized.
  • partially_paid: The finances have been partially paid.
  • paid: The finances have been paid.
  • partially_refunded: The finances have been partially refunded.
  • refunded: The finances have been refunded.
  • voided: The finances have been voided.

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”.

paid
Payment: Processing Method
  • checkout
  • direct
  • manual
  • offsite
  • express
The method used to process the payment. Will be one of the following:

  • checkout – payment was done by the client at the Checkout.
  • direct – paid directly from the Order in the Shopify Admin, but using the actual customer payment method, for example, charged the credit card or paid by PayPal.
  • manual – marked as paid manually from the Order in the Shopify Admin, without collecting any actual payment through the Shopify.
  • offsite – paid by Shopify POS.
  • express – paid by PayPal Express.

Only exported.

The method is deduced by the actual transactions on the order.

direct
Order Fulfillment Status
  • <empty>
  • fulfilled
  • partial
Summarized status of the order fulfillment – only exported.

Will be one of the following:

  • <empty>: There are no fulfillments done on the order yet.
  • partial: Order is fulfilled partially, meaning that at least one item has been shipped.
  • fulfilled: All line items are fulfilled or returned. There is nothing more to fulfill.

To import Order with a certain Fulfillment Status, you need to create Fulfillment Lines and fill in Fulfillment columns. See documentation further.

fulfilled
Additional DetailsLines of “name: value”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:

Gift wrapped: yes
Door code: 1234

Customer Columns

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.

ColumnAllowed ValuesDescription / Example Value
Customer: IDNumberShopify 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.

1234567890
Customer: EmailE-mailCustomer 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: PhonePhone NumberPhone 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.

'+15417543000
Customer: First NameTextThe first name of the Customer.

To change for an existing Order – change the data of the Customer.

John
Customer: Last NameTextThe last name of the Customer.

To change for an existing Order – change the data of the Customer.

Smith
Customer: NoteTextWhatever is written on the Customer card, accessible from Shopify Admin.

To change for an existing Order – change the data of the Customer.

Your notes go here.
Customer: Orders CountNumberTotal count of orders made by this Customer.

Only exported.

100
Customer: State
  • disabled
  • invited
  • enabled
  • declined
The Customer status – one of those:

  • disabled: customers are disabled by default until they are invited.
  • invited: the customer has been emailed an invite to create an account.
  • enabled: the customer accepted the email invite and created an account.
  • declined: the customer declined the email invite to create an account. If Customer has declined your invitation, you cannot generate new Account Activation URL.

To change for an existing Order – change the data of the Customer.

enabled
Customer: Total SpentAmountTotal amount the customer has spent at the moment of the export.

Only exported.

1024.99
Customer: TagsComma Separated ListTags 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.

Friend, Migrated
Customer: Accepts Marketing
  • TRUE
  • FALSE
Whether Customer accepted that he can be sent Marketing e-mails.

To change for an existing Order – change the data of the Customer.

TRUE

Customer 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.

ColumnAllowed ValuesDescription / Example Value
Billing: First NameTextThe first name of the Customer in the Billing Address.

John
Billing: Last NameTextThe last name of the Customer.

Required. If empty, the address will not be created.

Smith
Billing: CompanyTextCompany name, if the billing address is a company.

Smith Inc.
Billing: PhonePhone NumberPhone number to use for this address.

'+15417543002
Billing: Address 1TextAddress 1st line.

Required. If empty, the address will not be created.

Calvin Street 5
Billing: Address 2TextAddress 2nd line.

2nd Floor
Billing: ZipZipZip number of the address. Shopify doesn’t check its correctness.

ZIP-1234
Billing: CityTextCity name. Is not checked, so it’s possible to enter any city name.

Required. If empty, the address will not be created.

Dough
Billing: ProvinceProvince NameProvince 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.

Ontario
Billing: Province CodeProvince CodeIn 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.

ON
Billing: CountryCountry NameCountry 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.

Required Country or Country Code. If empty, the address will not be created.

Canada
Billing: Country CodeCountry CodeCountry international code, consisting of 2 letters.

If Country is filled, this can be left empty, then Country will be set from there.

CA

Customer Shipping Address Columns

All the following fields will be shown in the Order in the Shipping Address block. When imported, 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.

Customer 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.

ColumnAllowed ValuesDescription / Example Value
Browser: IPIP AddressThe IP address of the customer at the moment of purchase.

159.148.78.222
Browser: WidthNumberWidth of the customer browser window, in pixels.

1024
Browser: HeightNumberHeight of the customer browser window, in pixels.

768
Browser: User AgentTextFull 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

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 PageURLThe 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.

https://yourdomain.com/products/this-nice-tshirt-page
Browser: ReferrerURLThe site, where your customer came from when eventually made a purchase order. This allows you to understand, what are your most valuable traffic sources.

https://www.google.com?g=398798234
Browser: Referrer DomainDomainSince 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.

google.com
Browser: Search KeywordsTextKeywords 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.

blue tshirt with thousand colors
Browser: Ad URLURLThe 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.

https://yourdomain.com/some-page
Browser: UTM SourceTextSource, 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/

google
Browser: UTM MediumTextMedium, extracted from UTM parameter “utm_medium”.

cpc
Browser: UTM CampaignTextCampaign, extracted from UTM parameter “utm_campaign”.

spring_sale
Browser: UTM TermTextTerm, extracted from UTM parameter “utm_term” – identifies the paid search keywords entered by the customer, when showing your ad campaign.

tshirt
Browser: UTM ContentTextContent, extracted from UTM parameter “utm_content” – identifies the ad variation, or the specific element or version you are testing for your campaign.

button-a

Line Item Columns

Each Order must have at least one line item.

ColumnAllowed ValuesDescription / Example Value
Line: Type
  • Line Item
  • Discount
  • Shipping Line
  • Transaction
  • Refund Line
  • Refund Shipping
  • Fulfillment Line
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.

  • Line Item: Order line item.
  • Discount: Discount which is applied to the whole order. An Order can have only one discount line.
  • Shipping Line: Shipping cost of the whole order. An Order can have only one shipping line.
  • Transaction: Indicating that in this line the transaction columns are filled. Each transaction line represents one actual transaction for the Order. See more details in the Transaction Columns section. If the transaction is a refund transaction, then it can reference several refund lines – through common refund columns.
  • Refund Line: Line items that belong to a certain refund. Must have the same attributes filled for line items, and show the quantity and totals in reverse – depending on how much is being refunded. Also, the Refund Columns need to be filled for each refund line. One Order can have many refunds, with many refund lines per refund.
  • Refund Shipping: If you need to refund the Shipping Line, then those details need to be entered in this line. One refund can combine refund lines and refund shipping lines. Shipping can be refunded several times until the refunded amount fully exhausts the initial shipping line amount.
  • Fulfillment Line: Indicates that in this line the Fulfillment details are written. Each Order can have several Fulfillments. And each Fulfillment can have several Fulfillment Lines – one line per Line Item being fulfilled. An Order can have additional fulfillments until all line items fulfillable quantities are fulfilled completely. If you want the whole order to be fulfilled, leave the rest of “Line:” values empty.
Line Item
Line: IDNumberThe 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:

  • Line Item: each will have its own Line ID, generated by Shopify. When creating a new Order, Line:ID must be filled with any unique number.
  • Discount: has no Line ID
  • Shipping Line: has an ID, generated by Shopify. When creating a new Order, can leave empty.
  • Transaction: has no Line ID, because Transaction ID is stored in the “Transaction: ID” column.
  • Refund Line: has the same ID as the Line Item that is being refunded. That allows to precisely match line items and refund lines in reports. When creating a new Order, can leave empty.
  • Refund Shipping: has a specific ID taken from the shipping refund entry. When creating a new Order, can leave empty.
  • Fulfillment Line: has the same ID as the Line Item that is being fulfilled. That allows to precisely match line items and fulfillment lines in reports. When creating a new Order, can leave empty.
728763138063
Line: Product IDNumberProduct 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.

200005386255
Line: Product HandleTextThis 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.

tshirt-with-picture-of-me
Line: TitleTextHas a different value, depending on the Line Type:

  • Line Item: the title of the Order line item. When importing, even if the actual linked product will have a different title, the Order line item title will be set to this one.
  • Discount: “percentage” or “fixed_amount”. Used only in the export. Import always assumes the fixed_amount type of discount.
  • Shipping Line: the shipping title written in free form.
  • Transaction: empty
  • Refund Line: must match the “Line: Title” of the line item that is being refunded, because it is used as part the key identifying the refunded line item.
  • Refund Shipping: empty
  • Fulfillment Line: must match the “Line: Title” of the item that is being fulfilled, because it is used as part the key identifying the fulfilled line item.
Tshirt With Picture Of Me
Line: NameTextHas a different value, depending on the Line Type:

  • Line Item: full name of the Order line item, which includes the variant title. Formatted: <product title> – <variant title>. When importing – it will be saved, but will not be visible anywhere on the Order screen.
  • Discount: the free text comment that was written for the discount. If the discount code was used, then it will be here.
  • Shipping Line: the shipping code, usually has value “custom”.
  • Transaction: empty
  • Refund Line: in export will match the line item name. In import can be anything – it will be saved, but will not be visible anywhere on the Order screen.
  • Refund Shipping: empty
  • Fulfillment Line: in export will match the line item name. In import can be anything – it will be saved, but will not be visible anywhere on the Order screen.
Tshirt With Picture Of Me - Red / XS
Line: Variant IDNumberVariant ID of the line item, generated by Shopify – if the line item is a product.

In case of the custom line item, this ID will be empty.

When importing an Order, 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.

1834691428367
Line: Variant TitleTextHas a different value, depending on the Line Type:

  • Line Item: the title of the product variant included in the line item.
  • Discount: empty
  • Shipping Line: the shipping source, usually “shopify”.
  • Transaction: empty
  • Refund Line: must match the “Line: Variant Title” of the line item that is being refunded, because it is used as part the key identifying the refunded line item.
  • Refund Shipping: empty
  • Fulfillment Line: must match the “Line: Variant Title” of the item that is being fulfilled, because it is used as part the key identifying the fulfilled line item.
Red / XS
Line: SKUTextHas a different value, depending on the Line Type:

  • Line Item: the SKU of the product variant included in the line item.
  • Discount: empty
  • Shipping Line: empty
  • Transaction: empty
  • Refund Line: must match the “Line: SKU” of the line item that is being refunded, because it is used as part the key identifying the refunded line item.
  • Refund Shipping: empty
  • Fulfillment Line: must match the “Line: SKU” of the item that is being fulfilled, because it is used as part the key identifying the fulfilled line item.
SKU-1234567
Line: QuantityNumberHas a different value, depending on the Line Type:

  • Line Item: the quantity of the line item purchased.
  • Discount: empty
  • Shipping Line: empty
  • Transaction: empty
  • Refund Line: the quantity restocked back if a refund is done with restocking. The number is negative.
  • Refund Shipping: empty
  • Fulfillment Line: the quantity being fulfilled in that particular line of fulfillment. The number is negative.

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.

100
Line: PriceCurrencyHas a different value, depending on the Line Type:

  • Line Item: the price of one line item purchased.
  • Discount: empty
  • Shipping Line: the price of the shipping applied to the order.
  • Transaction: empty
  • Refund Line: must match the “Line: Price” of the line item that is being refunded, because it is used as part the key identifying the refunded line item.
  • Refund Shipping: the amount of shipping being refunded. Positive number.
  • Fulfillment Line: must match the “Line: Price” of the item that is being fulfilled, because it is used as part the key identifying the fulfilled line item.
5.49
Line: DiscountCurrencyHas a different value, depending on the Line Type:

  • Line Item: line item discount. Can be entered only through Shopify Admin, and will be filled for the export. When importing, this value will be automatically added to the order discount, because Shopify doesn’t allow setting line item discounts through API. The amount is negative.
  • Discount: the whole order discount amount. The amount is negative.
  • Shipping Line: empty
  • Transaction: empty
  • Refund Line: empty
  • Refund Shipping: empty
  • Fulfillment Line: empty

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.

49.00
Line: TotalCurrencyHas a different value, depending on the Line Type:

  • Line Item: line item total as Quantity * Price + Discount (because Discount is negative).
  • Discount: the whole order discount amount. The amount is negative.
  • Shipping Line: the price of the shipping applied to the Order.
  • Transaction: empty
  • Refund Line: refunded line item total as “refunded quantity” * Price (result will be negative because refunded quantity is negative).
  • Refund Shipping: the total amount of shipping being refunded. Negative number.
  • Fulfillment Line: empty

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.

500.00
Line: GramsNumberHas a different value, depending on the Line Type:

  • Line Item: weight per line item sold (weight of one item).
  • Discount: empty
  • Shipping Line: empty
  • Transaction: empty
  • Refund Line: must match the “Line: Grams” of the item that is being refunded, because it is used as part the key identifying the fulfilled line item.
  • Refund Shipping: empty
  • Fulfillment Line: must match the “Line: Grams” of the item that is being fulfilled, because it is used as part the key identifying the fulfilled line item.
250
Line: Requires Shipping
  • TRUE
  • FALSE
Has a different value, depending on the Line Type:

  • Line Item: TRUE if the line item requires shipping.
  • Discount: empty
  • Shipping Line: empty
  • Transaction: empty
  • Refund Line: TRUE if the line item requires shipping.
  • Refund Shipping: empty
  • Fulfillment Line: TRUE if the line item requires shipping.
TRUE
Line: VendorTextVendor name of the product purchased.

Puma
 Line: Properties Lines of “name: value”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:

Color: Red
Engraving: My Name
Line: Gift Card
  • TRUE
  • FALSE
TRUE if the line item is a gift card.

If set to TRUE Shopify will send Gift Card email to the customer.

FALSE
Line: Taxable
  • TRUE
  • FALSE
TRUE if the line item is taxable.

TRUE
Line: Tax 1 Title
Line: Tax 2 Title
Line: Tax 3 Title
TextTitle of each applied tax.

Each order can have up to 3 different taxes applied, therefore all those are split into separate tax columns.

VAT
Line: Tax 1 Rate
Line: Tax 2 Rate
Line: Tax 3 Rate
Decimal NumberThe rate of the tax applied.

0.21
Line: Tax 1 Price
Line: Tax 2 Price
Line: Tax 3 Price
CurrencyThe calculated tax in the order currency.

105.00
Line: Fulfillable QuantityNumberThe quantity of the line item that is still left to be fulfilled.

Only exported. For import this is calculated from actual fulfillments.

50
Line: Fulfillment Service
  • manual
  • amazon
  • shipware
  • <other privider handle>
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.

Line: Fulfillment Status
  • fulfilled
  • partial
  • <empty>
Summary of the line item fulfillment status:

  • fulfilled: line item is fulfilled completely
  • partial: part of line item is fulfilled
  • <empty>: line item is not started to fulfill

Exported only because it is actually calculated from the Order data.

Product Data Columns

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.

ColumnTypeDescription / Example Value
Line: Product TypeTextProduct Type
Line: Product TagsTextProduct Tags
Line: Variant SKUTextProduct Variant SKU
Line: Variant BarcodeTextProduct Variant Barcode
Line: Variant WeightNumberProduct Variant Weight
Line: Variant Weight UnitTextProduct Variant Weight Unit
Line: Variant Inventory QtyNumberProduct 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.

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.

ColumnAllowed ValuesDescription / Example Value
Shipping Origin: NameTextThe shipping origin name. If no shipping provider us used, then this will be the Shopify store contact address.
Shipping Origin: Country CodeText2 character country code
Shipping Origin: Province CodeText2 character province code
Shipping Origin: CityTextName of the city
Shipping Origin: Address 1TextAddress line 1
Shipping Origin: Address 2TextAddress line 2
Shipping Origin: ZipTextZip code

Refund Columns

Refund columns extend the rows of line type “Refund Line”, and contain additional data about refunds.

ColumnAllowed ValuesDescription / Example Value
Refund: IDNumberThe ID of the refund, generated by Shopify. Only exported.

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.

1000
Refund: Created AtDate & TimeDate and time when the refund was created and processed.

2018-02-06 23:03:00 UTC
Refund: NoteTextNote attached to the refund, usually explaining the refund reason.

Agreed with customer.
Refund: Restock
  • TRUE
  • FALSE
TRUE if the line items are being restocked. Meaning that quantities are increased back in stock.

TRUE
Refund: Restock Type
  • cancel
  • return
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
Refund: Restock LocationLocation NameIf you have the “Refund: Restock” set as TRUE, then 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.

Main Warehouse
Refund: Send Receipt
  • TRUE
  • FALSE
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.

FALSE

Transaction Columns

Transaction columns extend the rows of line type “Transaction”, and contain data about transactions.

ColumnAllowed ValuesDescription / Example Value
Transaction: IDNumberThe ID of the transaction, generated by Shopify. Only exported.

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.

280328273935
Transaction: Kind
  • authorization
  • sale
  • capture
  • refund
  • void
  • authorization: represents an amount reserved against the cardholder’s funding source. Money does not change hands until an authorization is captured by “capture” transaction. There can be only one authorization transaction per Order.
  • capture: transfer of the money that was reserved during the authorization stage. There can be several capture transactions per order, until they reach the total sum of authorization transaction.
  • sale: an authorization and capture performed together in a single step, usually by pressing the “Paid” button in the Shopify Admin. In this case there will be no associated card transaction. There can be only one sale transaction per Order.
    You can import sale transaction for existing order with remaining amount to mark the order as paid.
  • refund: a partial or full return of captured funds. A refund can only happen after a capture or sale transaction, and cannot exceed their amount. There can be several refund transactions – one per Refund.
  • void: cancellation of a transaction.
Transaction: Processed AtDate & TimeDate and time when the transaction was created.

Can be also imported – and reports should show the Transactions on the correct dates.

2018-02-06 23:03:00 UTC
Transaction: AmountCurrencyThe 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.

Transaction: CurrencyCorrency code in ISO 4217 formatThe 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.

USD
Transaction: Status
  • success
  • pending
  • failure
  • error
The transaction status is mainly for the export.

When importing, there is no point importing transactions with statuses other than success.

Transaction: MessageTextTransaction comment generated by the Shopify. Usually the name of the reply of the used payment gateway. Only exported.

PayPal Confirmed
Transaction: GatewayTextThe name of the used payment gateway.

When importing void or refund transaction, must match the same gateway as the original transaction.

paypal
Transaction: Test
  • TRUE
  • FALSE
TRUE if this is a test transaction.

FALSE
Transaction: AuthorizationNumberAuthorization code provided by the payment gateway. Only exported.

53433
Transaction: Error Code
  • incorrect_number
  • invalid_number
  • invalid_expiry_date
  • invalid_cvc
  • expired_card
  • incorrect_cvc
  • incorrect_zip
  • incorrect_address
  • card_declined
  • processing_error
  • call_issuer
  • pick_up_card
The error code, indicating the reason for failed transaction. The same codes for any payment gateway. Only exported.

expired_card
Transaction: CC AVS ResultOne characterThe Response code from AVS the address verification system. The code is a single letter; see this chart for the codes and their definitions. Only exported.
Transaction: CC BinNumberThe 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. Only exported.

1
Transaction: CC CVV ResultOne characterThe 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.
Only exported.
Transaction: CC NumberMasked Card NumberThe customer’s credit card number, with most of the leading digits masked with •. Only exported.

•••• •••• •••• 4242
Transaction: CC CompanyTextThe name of the company who issued the customer’s credit card. Only exported.

Visa

Risk Columns

Each Order has an associated fraud risk evaluation from Shopify or other risk assessment service, which is exported to risk those columns.

ColumnAllowed ValuesDescription / Example Value
Risk: SourceTextThe source of the risk assessment.

Shopify
Risk: ScoreDecimal numberA 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.

0.5
Risk: Recommendation
  • cancel
  • investigate
  • accept
Suggested recommendation for the action to take on the Order in terms of mitigating the risk:

  • cancel: This order should be cancelled by the merchant.
  • investigate: This order might be fraudulent and needs further investigation.
  • accept: This check found no indication of fraud.
Risk: Cause Cancel
  • TRUE
  • FALSE
This will indicate that this risk was severe enough to force cancellation of the order.
Risk: MessageTextA message explaining the risk.

This order came from an anonymous proxy.

Fulfillment Columns

Each Order can have none, one or many fulfillments, which are essentially – the deliveries of ordered line items. Each fulfillment can contain one or many line items of different quantities.

ColumnAllowed ValuesDescription / Example Value
Fulfillment: IDNumberFulfillment 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.

311117676559
Fulfillment: Status
  • pending
  • open
  • success
  • cancelled
  • error
  • failure

 

The status of the fulfillment. Valid values are:

  • pending: The fulfillment is pending.
  • open: The fulfillment has been acknowledged by the service and is in processing.
  • success: The fulfillment was successful.
  • cancelled: The fulfillment was cancelled.
  • error: There was an error with the fulfillment request.
  • failure: The fulfillment request failed.
Fulfillment: Created AtDate & TimeDate and time when the fulfillment was created. Only exported.

When importing, the fulfillment will be dated at the moment when it is being imported.

2018-02-06 23:03:00 UTC
Fulfillment: Updated AtDate & TimeDate and time when the fulfillment was last updated. Only exported.

2019-02-09 15:28:11 UTC
Fulfillment: Tracking CompanyTextThe name of the tracking company – either empty or written exactly as in this list:

  • 4PX
  • APC
  • Amazon Logistics UK
  • Amazon Logistics US
  • Australia Post
  • Bluedart
  • Canada Post
  • China Post
  • Correios
  • DHL Express
  • DHL eCommerce
  • DHL eCommerce Asia
  • DPD
  • Delhivery
  • Eagle
  • FSC
  • FedEx
  • FedEx UK
  • GLS
  • Globegistics
  • Japan Post
  • New Zealand Post
  • Newgistics
  • PostNord
  • Purolator
  • Royal Mail
  • Sagawa (EN)
  • Sagawa (JA)
  • TNT
  • TNT Post
  • UPS
  • USPS
  • Yamato (EN)
  • Yamato (JA)
FedEx
Fulfillment: LocationLocation NameThe 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.

Main Warehouse
Fulfillment: Shipment Status
  • confirmed
  • in_transit
  • out_for_delivery
  • delivered
  • failure
The status of the shipment.

delivered
Fulfillment: Tracking NumberTextTracking number provided by the shipping company.

112345Z2345
Fulfillment: Tracking URLURLThe 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.

https://track-chinapost.com/startairmail.php?code=112345Z2345
Fulfillment: Send Receipt
  • TRUE
  • FALSE
Tells whether to send a notification Email to the Customer when the Fulfillment is created or updated.

The default is 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: TitleTextThe shipping title, written in free form.

Worldwide
Shipping Line: CodeTextThe shipping code usually has value “custom”.

custom
Shipping Line: SourceTextThe shipping source, usually “shopify”.

shopify
Shipping Line: PriceNumberThe price of the shipping applied to the order.

5
Shipping Line: Tax TitleTextTitle of the applied tax.

VAT
Shipping Line: Tax RateNumberThe rate of tax applied.

0.21
Shipping Line: Tax PriceNumberThe calculated tax in the order currency.

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 Command) only the following columns for Orders:

ColumnComment
Note
Tags
Processed AtYou can change the date when Order was created. Will work only for orders created through the Apps.
Cancelled AtIn 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: ReasonCan 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 ReceiptSet 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 AtMakes 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: IDCan only set to empty ID – that will remove the Customer from the Order. Cannot switch the Order to another Customer.
Customer: EmailThis will change the Contact Information of that Order.
Customer: PhoneThis 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.

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.