See what each field/column from our “Customers” 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.
Excel Sheet Name
Customers
To tell the app that you are importing Customers you need:
- If using CSV file then the file name needs to contain the word “Customers”, for example, “my-shopify-customers.csv”
- If using XLSX file then the sheet/tab name in the file needs to be “Customers”. The file name for the XLSX file does not matter.
Summary
In this export, as you see Customers in Admin – each customer has its primary First/Last name and Phone, and each address has their own First/Last Name and Phone – for delivery purposes.
All this data is exported and imported exactly as you see it in Admin.
If Client will have several addresses, all those will be exported, each address in new Excel row. The same principle can be used to import Customers with several addresses, keeping the Email field as the unique Customer identifier (the same as Handle for Products).
Columns Available For Export And Import
Basic Columns
| Column | Description / Example Value |
|---|---|
| ID
Exported only |
ID is automatically generated number by Shopify to identify the item. It is filled in by an Export. When creating new Customers, leave this value empty.Number 44920047631 |
| Customer primary e-mail address.
If the store is configured to require Customer login, then this will be the username. Email must be unique in this Store, which means that each e-mail can be used only once in this column across the Store. When customer puts an order and has e-mail filled, that e-mail is used to identify to which customer the order will be assigned. Email can be empty if First Name is filled. Email is used as the secondary way to identify customers and to check if they already exist. In the import results the Email field can also be called “Handle” (as for all other items secondary identifiers). Email Address [email protected] |
|
| Command | When importing Customer row – tell the app, what to do with it:
If no Command is specified, then the app will assume the UPDATE command. NEW MERGE UPDATE REPLACE DELETE IGNORE MERGE |
| First Name | The first name of the Customer.
First Name can be empty if Email is filled. Text John |
| Last Name | The last name of the Customer.
Text Smith |
| Phone | Phone number must be complete – 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 phone number must be unique – Shopify allows one phone number to belong to only one Customer. Shopify uses the phone number to identify to which customer to assign the order – in case the customer doesn’t have the e-mail. Phone Number '+15417543000 |
| State | The Customer status – one of those:
The state is always “calculated” from the actions that are done with this Customer. When you import it, it will always be “disabled” at the beginning. If you send the invitation (or import the column “Send Account Activation Email” as TRUE), then the State will be “invited. To import the Customer as “enabled” State initially, the only way is to do is – to import it with the “Password” set to something. Generally you would want to not set the Password, but send an invitation and allow the customer to approve it. Warning: Shopify doesn’t allow to change State from “disabled” to “enabled” by an update. The only ways to change that is:
disabled invited enabled declined disabled |
| Accepts Marketing | Whether Customer accepted that he can be sent Marketing e-mails.
TRUE FALSE TRUE |
| Created At
Exported only |
It will be set automatically when Customer is created.
Date & Time 2017-10-06 23:03:00 UTC |
| Updated At
Exported only |
It will be automatically updated, whenever the Customer data is changed.
Date & Time 2017-10-06 23:05:00 UTC |
| Note | Whatever is written on the Customer card, accessible from Shopify Admin.
Text Your notes go here. |
| Verified Email | Tells, whether Customer e-mail is validated.
Is set to TRUE, when Customer received the confirmation e-mail and pressed the confirmation link. This value can also be imported, when creating new Customer. By default, the value will be FALSE. TRUE FALSE TRUE |
| 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.
See this tutorial on how to bulk manage your Tags. Comma Seperated List Friend, Migrated |
| Tags Command | Tell the app, what to do with Tags:
You can also have Tags listed in multiple rows within the item and with different Tags Commands. If Tags Command is not set, the app will assume the REPLACE command by default. See this tutorial on how to bulk manage your Tags. MERGE DELETE REPLACE REPLACE |
| 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. |
First and Last Order
Those columns are only exported.
| Column | Description / Example Value |
|---|---|
| Total Spent
Exported only |
The total amount of money this Customer has spent, calculated from actual Orders in the Store.
It is exported even when “First and Last Order” checkbox is not checked because the data is available always. 48234.93 |
| Total Orders
Exported only |
The total number of orders this Customer has purchased.
It is exported even when “First and Last Order” checkbox is not checked because the data is available always. 5 |
| First Order: ID
Exported only |
ID of the first Order of this Customer.
2387462873468 |
| First Order: Name
Exported only |
Name of the first Order of this Customer.
#12345 |
| First Order: Processed At
Exported only |
Date and time of the first Order of this Customer.
For imported Orders, this will be the processed date, not the date when the Order was imported. Date and time will be in the format which is recognized as Excel time. 2019-01-01 12:34:00 UTC |
| First Order: Price Total
Exported only |
The total price of the first Order of this Customer.
This is not the actually paid amount, it is the Total of the Order itself. 999.99 |
| Last Order: ID
Exported only |
ID of the last Order of this Customer.
If Customer has only one order, then this will be the same as the first Order. 2387462873468 |
| Last Order: Name
Exported only |
Name of the first Order of this Customer.
If Customer has only one order, then this will be the same as the first Order. #12345 |
| Last Order: Processed At
Exported only |
Date and time of the first Order of this Customer.
For imported Orders, this will be the processed date, not the date when the Order was imported. Date and time will be in the format which is recognized as Excel time. If Customer has only one order, then this will be the same as the first Order. 2019-01-01 12:34:00 UTC |
| Last Order: Price Total
Exported only |
The total price of the first Order of this Customer.
This is not the actually paid amount, it is the Total of the Order itself. If Customer has only one order, then this will be the same as the first Order. 999.99 |
Addresses
Each Customer can have several addresses, which are used for shipping. One of them is Primary address, marked in the column “Address Is Default”.
All those addresses can be imported, as well as exported.
A Customer with several addresses is organized the same way as Products with several Variants – you need to copy cells from Basic Columns to a new row for each additional Address. To identify that Address belongs to the same Customer, you must have ID or Email cells (whichever comes first), or if those two are empty, then at least “First Name” and “Last Name” must be repeated. At the same time, you can duplicate also all other cells, if that’s more convenient.
Those all are columns, which are accessible in Shopify Admin, when entering one Customer, and pressing “Change” button at Default Address card.
| Column | Description / Example Value |
|---|---|
| Address ID
Exported only |
ID is automatically generated number by Shopify to identify the Address. It is filled in by an Export. When creating new Customer Addresses, leave this value empty.When you need to update existing addresses, and you don’t want to replace them all, then keep the Address ID value filled – that way the import will know, which address needs to be updated.Number 44920047999 |
| Address Command | Whenever addresses are imported, there is always one of those commands executed for each of address rows:
If no Address Command is specified, then the MERGE will be assumed. MERGE DELETE REPLACE MERGE |
| Address First Name | Can be different from the First Name. This name will be used when shipping to this particular address.
Text Eric |
| Address Last Name | Last name of this address. Will be used when shipping to this particular address.
Text Smith |
| Address Company | Company name, if shipping is done to a company address.
Text Smith Inc. |
| Address Phone | Phone number to use for this shipping address.
Phone Number '+15417543002 |
| Address Line 1 | Address 1st line.
Text Calvin Street 5 |
| Address Line 2 | Address 2nd line.
Text 2nd Floor |
| Address City | City name. Is not checked, so it’s possible to enter any city name.
Text Dough |
| Address Province | Province names are checked against Shopify geographic classifier. Creation of Customer fails if the value is not valid.
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 Address 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 |
| Address Province Code | In Shopify classifier there are Province Codes. This is checked, and creation of Customer fails, if the value is not valid.
If Address 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 |
| Address Country | Country names are checked against Shopify geographic classifier. Creation of Customer fails if the value is not valid.
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 Address 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. Country Name Canada |
| Address Country Code | Country international code, consisting of 2 letters.
If Address 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 |
| Address Zip | Zip number of the address. Shopify doesn’t check its correctness.
Zip ZIP-1234 |
| Address Is Default | Set this to TRUE if this address needs to be set to Customer primary address.
TRUE FALSE FALSE |
Functional Columns
There are some functional columns, which allow manipulating sensitive Customer information.
| Column | Description / Example Value |
|---|---|
| Account Activation URL
Exported only |
This column can be only generated by Export when marking the checkbox “Activation URLs” for the Customer export.
When opening this link, the Shopify Store will ask Customer to enter her new password, and once this is done, Customer will have instantly activated his account, with all the profile ready to use, with all the data which you imported. This allows creating smooth migration experience for Customers when migrating from the old system. You can use those URLs to send invitation e-mails for newly imported Customers using your favorite mass-mailing provider, like MailChimp. To get Account Activation URLs, you need to do the Export after importing new Customers, then those URLs will be generated. All you need to do then is import this Exported Excel into a mass-mailing list, and use it in the main Call To Action button like “Activate Your Migrated Account”. Important: each time you generate new Activation URLs, the previous URLs get “expired” and become invalid. This is how Shopify organizes the Activations. The same thing happens whe you send individual activation invitations from Admin for each customer. https://yourstore.com/... |
| Send Account Activation Email | This column can only be imported.
If you will set this to “TRUE”, then as soon as Customer will be imported, she will receive a default invitation e-mail from Shopify to activate the account – the same one as she would get if you press the “Send account invite” in the Admin from Customer page. When migrating Customers, you might want Shopify to send out invitations to activate migrated accounts in bulk. Then set this to TRUE when importing. This can be used as smooth migration experience for Customers if you don’t want to send your own mass-mailing with Account Activation URL links. When the value is left empty, it is considered to be FALSE, so invitations will not be sent. You can send those activations also to your existing Shopify Customers, just do the export, and then import back as an update, with this value set to TRUE. TRUE FALSE TRUE |
| Send Welcome Email | If you will set this to “TRUE”, then as soon as Customer will be imported, she will receive a default Welcome e-mail, the same one as she would get if she registers herself.
This welcome e-mail is sent only if the imported customer will be activated, e.g. when you set the password in the import for the new customer. TRUE FALSE FALSE |
| Password | Imagine – you can import Customers and set their new passwords as you like. That way the Customer will be instantly activated and will be able to login with that password.
Also, you can use the same approach to change passwords for existing Customers. You cannot export passwords because those are strongly encrypted, and even Shopify doesn’t know them. Text a898w3424&*&( |
| Multipass Identifier | Available only for Shopify Plus stores.
Allows setting the identifier of the Multipass login. Read more information about Multipass. Text bob123 |
Metafields
Read the description on Metafields page.
Good To Know
- To repeat rows for Addresses, you can just copy the whole row as it is, and you don’t have to delete contents of Basic Columns – that makes copying easier.
- Shopify exported CSV file, pasted to Excel [Customers] Sheet, can be imported with the same columns. Just know that not all Customer data is exported, when exporting to CSV by Shopify default export.
- To not update some columns, remove those columns from Excel file. Then the Import will not touch them.
- If you will not have Address ID value, then all addresses will be replaced with imported.
- To update existing Address columns, remember to keep the “Address ID” column.
- If you will have the Column with an empty value, then this column will be changed to empty.
