Shopify Customers Excel Screenshot

Excel Sheet Name

Customers

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 Allowed Values Description / Example Value
ID Number 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.

44920047631
Email Email Address Customer primary e-mail address.

If store is configured to require Customer login, then this will be 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.

Email can be empty, if First Name is filled.

you@somemain.com
First Name Text The first name of the Customer.

First Name can be empty if Email is filled.

John
Last Name Text The last name of the Customer.

Smith
Phone Phone Number Phone number must be full – with the country code, and must be valid for respective country.

You can prefix phone numbers with ‘ (apostrophe) to allow Excel to show the + (plus) sign. But the + (plus sign) is not required.

'+15417543000
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.

If you will leave the State column empty when importing, it will be set to “disabled”.

Warning: Shopify doesn’t allow to change State from “disabled” to “enabled” by update. The only ways to change that is:

  1. When Customer approves the invitation and activates her account.
  2. If you delete that Customer, and create new, with enabled state from the very beginning.
enabled
Accepts Marketing
  • TRUE
  • FALSE
Whether Customer accepted that he can be sent Marketing e-mails.

TRUE
Created At Time This column will only be exported.

It will be set automatically when Customer is created.

2017-10-06 23:03:00 UTC
Updated At Time This column will only be exported.

It will be automatically updated, whenever the Customer data is changed.

2017-10-06 23:05:00 UTC
Total Spent Decimal Number This column will only be exported.

The total amount of money this Customer has spent, calculated from actual Orders in the Store.

48234.93
Total Orders Number This column will only be exported.

The total number of orders this Customer has purchased.

5
Last Order Name Text This column will only be exported.

The name of the last order, taken from the actual last order in this Store.

#1001
Note Text Whatever is written in the Customer card, accessible from Shopify Admin.

Your notes go here.
Verified Email
  • TRUE
  • FALSE
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
Tax Exempt
  • TRUE
  • FALSE
Tells whether this Customer has tax exempt.

By default the value is FALSE.

FALSE
Tags Comma Separated List 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.

Friend, Migrated
Primary Row TRUE / FALSE Since all the base rows values are repeated for all item rows, in the export the primary row is set to TRUE, so that it can be used in sorting, to have that primary row on top.

 

Address Columns

Each Customer can have several addresses, which are used for shipping. One of them is Primary address, marked in column “Address Is Default”.

All those addresses can be imported, as well as exported.

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). 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 Allowed Values Description / Example Value
Address ID Number 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.

44920047999
Address First Name Text Can be different from First Name. This name will be used when shipping to this particular address.

Eric
Address Last Name Text Last name of this address. Will be used when shipping to this particular address.

Smith
Address Phone Phone Number Phone number to use for this shipping address.

'+15417543002
Address Company Text Company name, if shipping is done to a company address.

Smith Inc.
Address Line 1 Text Address 1st line.

Calvin Street 5
Address Line 2 Text Address 2nd line.

2nd Floor
Address City Text City name. Is not checked, so it’s possible to enter any city name.

Dough
Address Province Province Name 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 dropdown 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.

Ontario
Address Province Code 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.

ON
Address Country Country Name 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 dropdown to choose the Country.

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

Canada
Address Country Code 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.

CA
Address Zip Zip Zip number of the address. Shopify doesn’t check its correctness.

ZIP-1234
Address Is Default
  • TRUE
  • FALSE
Set this to TRUE, if this address needs to be set as Customer primary address.

FALSE

 

Functional Columns

There are some functional columns, which allow to manipulate sensitive Customer information.

 

Column Allowed Values Description / Example Value
Account Activation URL URL 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 to create smooth migration experience for Customers, when migrating from 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 mass-mailing list, and use it in the main Call To Action button like “Activate Your Migrated Account”.

https://yourstore.com/...
Send Account Activation Email
  • TRUE
  • FALSE
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 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 update, with this value set to TRUE.

TRUE
Send Welcome Email
  • TRUE
  • FALSE
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 imported customer will be activated, e.g. when you set the password in the import for the new customer.

FALSE
Password Text 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 of existing Customers.

You cannot export passwords, because those are strongly encrypted, and even Shopify doesn’t know them.

a898w3424&*&(
Multipass Identifier Text Available only for Shopify Plus stores.

Allows to set the identifier of the Multipass login.

Read more information about Multipass.

bob123

Metafields

Read the description on Metafields page.

 

Good To Know

  • To repeat rows for Addresses, you can just copy 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 empty value, then this column will be changed to empty.