Skip to main content
All CollectionsFigure: Detailed UseProducts
Importing Customers to Offset Commerce
Importing Customers to Offset Commerce
Bobby Gibson avatar
Written by Bobby Gibson
Updated over a month ago

Whether you are a new or existing client, you may have a list of customers you want to add to Offset rather than entering them one at a time. When adding multiple customers or making bulk changes to customer data, you can save a lot of time by importing customer information using a spreadsheet template. This article describes the steps to complete these bulk actions.

If this is your first time doing a bulk import, please reach out to our Client Experience Team through the support chat or by emailing [email protected]. We are happy to answer any questions about this process or review your work.

New Customer Imports

As your business grows you may start to gather new signups from tasting events, trade shows, or other offline events. Now that you have this list of customers, it is time to load them into Offset. When you collect information we recommend gathering the customer information for the Required columns listed below. If you plan on sending them marketing emails, we recommend that the customers review your terms of service and opt-in, providing express consent to receive marketing communications.

IMPORTANT: Only add customer contacts to Offset who have asked to be added to your mailing list and agree to your terms of service. Purchasing or borrowing email lists is unethical and has potential legal consequences. Sending marketing emails to customers who have not opted in, can severely damage your sending reputation, which permanently affects email deliverability, even for your legitimate customers. Excessive complaints can even result in suspension from the Campaign Monitor platform. Click on this article from Campaign Monitor to learn more about How Spam Complaints Work. For more information about email compliance, please review this Federal Trade Commission guidance from the CAN-SPAM Act.

For new customers, at a minimum, you will need to fill out these Required columns:

*first_name

*last_name

*email

While we recommend entering all the available customer info you have at the time of the import, we highly recommend adding the headers listed below. These fields are easy to collect and will help you track opt-ins, sign-up dates, and other referral information that can help you segment contacts for future marketing. Details about formatting and recommendations are available below in the Customer Data Import Formats section.

active_account

created_date (*change this header name to created)

customer_source

referral_source

referral_note

marketing_opt_in

Download The Customer Import Template

To download the import template, export a customer to get all the import headers you need in the Customer Data Export format. As new features are created, new headers will be added to this export, so this process will help ensure you have the most up-to-date column information.

  1. Go to the Customers page

  2. Select the checkbox to the left of a customer

  3. At the bottom of the page, select the Choose an Action... dropdown

  4. Select Export > Customer Data Export [.csv]

  5. Save the file as an MS-DOS [.csv]

Downloading your template in Customer Data Export Format

The Customer Data Export will serve as your import template and the single customer export will serve as a good example as you fill out the required information.

If you want more examples of your customer data, you can Export All Customers by following these steps:

  1. Go to the Customers Page

  2. Select the Actions dropdown

  3. Choose EXPORTS > All Customers CSV

NOTE: New headers will be added to the export as new features are created. If you are using the customer data export to map to external macros, to avoid any disruption, please be sure to map to the column header, NOT the column letter.

Customer Data Import Formats

The next step is to input data for all the required fields and ensure your entries meet the formatting requirements. If your import doesn't match the required conventions, the data will not import and the settings will not engage. Always make sure to double-check your entries for any autoformats that could alter the formats within your spreadsheet editor.

Below is a guide to each column with the requirements for each cell.

IMPORTANT: When populating your import file, do not change the names as they appear in the column headers. The headers must match the exact text that appears in the export, or the data in those columns will not import.

  • green = Required field. All green fields need to be filled in before importing.

  • yellow = Recommended field.

  • red = Import unavailable. Information in these fields may be present upon export, but they are NOT available for import. Please see Notes and Instructions for details

  • white = Optional information. Add information to these fields as needed.

  • blue = Custom Feature. These features are only available for select clients.

Column Header

Format

Notes and Instructions

id

numeric (ex. 99)

Most clients will leave this field blank and the system will automatically assign an id number during import. If you import an update to an existing data set, then the id is required so the system can match your updates to the existing customer id.

active_account

Limited options (ex. Yes or No)

Yes = Active and No = Inactive. Not all wineries define Active and Inactive the same way, so you have some flexibility in how you apply this designation. Active members are typically current subscribers or purchasers. Inactive customers are typically past purchasers who are not current and will see "inactive messaging" when logging into their online accounts. Many clients make customers Inactive if they have unsubscribed from marketing communication.

created_date (*change this header name to created)

date format YYYY-MM-DD (ex. 2024-11-21)

Recommended field. *Change the header name to "created" on your import sheet or the date will not import. This is the date when the customer signed up and their account was created. If you don't have the exact data, use the date of the import. IMPORTANT: If this column header is on the spreadsheet then you have to have a date entered in the cell. If you delete the column before importing, the system will apply the current date as the created_date.

created_time

time (ex. HH:MM, 13:21)

Import unavailable. Leave this field blank or delete this column when importing new customers.

salutation

Limited options (ex. Mr., Mr. & Mrs., Mrs., Ms., Miss, Mx., Dr., Rev., Prof., Hon., Sir, Madam)

This data populates the Title field in a customer account.

first_name

alphanumeric (ex. Tyson)

Required field. This data populates the First Name field in a customer account. Enter "Friend" if blank as this info can potentially be included in an email with personalization. NOTE: First and last names totaling around 25 characters between both names can cause display issues on the customer pages.

last_name

alphanumeric (ex. Caly)

Required field. This data populates the Last Name field in a customer account. Enter "LAST" if blank.

suffix

alphanumeric (ex. Jr or III)

This data populates the Suffix field in a customer account.

nickname

alphanumeric (ex. Ty)

This data populates the Nickname field in a customer account.

email

username with an @ sign and a domain name (ex. [email protected])

Required field. The email is a unique identifier for a customer account. It must be a valid email. If importing a large customer list, remove duplicates, remove punctuation such as quotation marks, commas, apostrophes and spaces. Change any domain name errors such as .con to .com. Scan for domain name errors like @gnail and change them to @gmail, etc.

first_purchase_date

YYYY-MM-DD HH:MM:SS (ex. 2024-06-26 09:47:19)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field is automatically updated by customer order data.

first_purchase_amount

numeric (ex. 100)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field is automatically updated by customer order data.

last_login

YYYY-MM-DD HH:MM AM or PM (ex. 2024-06-26 09:47 AM)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field is automatically updated by the most recent customer login data.

last_order

YYYY-MM-DD (ex. 2024-10-21)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field is automatically updated by recent order data.

last_order_total

numeric (ex. 1020)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field is automatically updated by recent order total data.

order_count

numeric (ex. 43)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field is automatically updated by customer order data.

order_total

numeric with decimals (ex. 19056.60)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field is automatically updated by customer order data and reflects the total spent on all orders (aka. Customer lifetime value)

legacy_order_total

numeric with decimals (ex. 20568.56)

This amount is only visible on the customer export. It is typically used as a one-time import from a previous external sales source or commerce system if the order data is not migrated into Offset.

score

limited selection (ex. 0-10)

The score can rank a customer from a value of 0 to 10. This is not tied to any specific metric, so it is up to the organization to decide what these values represent.

clubs

alphanumeric (ex. Syrah Club, Syrah Club, Pinot Club or No Club)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field updates automatically when a customer joins a club and will display the name of the club they belong or multiple clubs separated by commas. If they are not a member of any club the field will say No Club.

groups

alphanumeric (ex. Mailing List or Waiting List, Test Group)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field updates automatically when a customer is added to a group and will display the name of the single group or multiple groups separated by commas and a space. Groups can be updated through a separate import process.

sub_group

limited options based on user-defined entries (ex. Hold - Financial Reasons)

The sub_group import data must match the options available in Settings > Configurations > Subgroup. Options must be added before importing.

customer_type

limited options based on user-defined entries (ex. Waiting List)

The customer_type import data must match the options available in Settings > Configurations > Customer Type. Options must be added before importing.

customer_source

limited options based on defaults and user-defined entries (ex. Internal, Web, Tasting Room or User-Defined Options)

Recommended field.

The customer_source import data must match the options available in Settings > Configurations > Source. Additional user-defined options must be added before importing.

website_source

URL with the Protocol, Subdomain, Second-level Domain, and Top-level Domain (ex. https://www.offsetwinery.com or https://www.aquire.offsetwinery.com)

If migrating customers from an existing website you can use the URL from that site. This field is automatically populated for new signups when they join using the Offset signup form on your website.

referral_source

limited options based on defaults and user-defined entries (ex. Friend/Colleague, Restaurant/Shop, Press/Website, Event/Tasting, Hotel/Other Winery, Social Media, Other or User-Defined Options)

Recommended field.

The customer_source import data must match the options available in Settings > Configurations > Referral. Additional user-defined options must be added before importing.

referral_note

alphanumeric (ex. Good Neighbor Winery)

Recommended field.

This is an open-source field that is typically used to further define the referral_source info.

referral_code

alphanumeric with no spaces (ex. SocialMedia)

This data populates the Referral Code field in a customer account. Make sure that there are no spaces. This code is added to the customer data by adding ?ref=YourCode to the end of a URL such as a signup form posted on social media.

vip_pickup

Limited options (ex. Yes or No)

A Yes option will enable the Vip Pickup feature and a No disables it. Blank responses are equivalent to No. NOTE: To use the VIP pickup feature, a member of the Offset team will need to activate Enable Pickup options for Specified Customers (VIP Pickup). This option makes it so that Pickup methods have the option of only being visible to customers who have the setting vip_pickup set to Yes

credits

numeric (ex. 10)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field displays the current credit balance without any special characters. Credits can be adjusted through a separate import process.

birthday

date format (ex. YYYY-MM-DD)

This is the primary birthdate for the contact on the account. This information may populate Shipping or Billing birthdates. If this info is not added at the time of import, the customer will be prompted to enter their birthdate when they place an order.

email_alt

username with an @ sign and a domain name (ex. [email protected])

This is the Alternate Email and is stored in the customer account. Note: This email is NOT connected to transactional emails.

phone

Three-digit area code and 7-digit subscriber number (ex. 707-444-5555 or 7074445555)

This phone populates the Primary Phone field in a customer account. If you are syncing phone number data with external text messaging such as SlickText, make sure to enter them with numbers only with no punctuation, even the dash.

work_phone

Three-digit area code and 7-digit subscriber number (ex. 707-444-5555 or 7074445555)

This phone number populates the Work Phone field in a customer account.

salutation_spouse

Limited options (ex. Mr., Mr. & Mrs., Mrs., Ms., Miss, Mx., Dr., Rev., Prof., Hon., Sir, Madam)

This data populates the Title field in the Family section of a customer account.

first_name_spouse

alphanumeric (ex. Cindy)

This data populates the Spouse's First Name field in a customer account.

last_name_spouse

alphanumeric (ex. Petrucci)

This data populates the Spouse's Last Name field in a customer account. Enter "LAST" if blank.

suffix_spouse

alphanumeric (ex. MFA or MD)

This data populates the Suffix field in the Family section of a customer account.

email_spouse

username with an @ sign and a domain name (ex. [email protected])

This email populates the Alternate Email in the customer account.

spouse_phone

Three-digit area code and 7-digit subscriber number (ex. 707-444-5555 or 7074445555)

This phone number populates the Spouse's Phone field in a customer account.

children

alphanumeric (ex. Jack and Diane)

This data populates the Children section of a customer's account.

pet

alphanumeric (ex. Dog named Spot and Cat named Buttons)

This data populates the Pets section of a customer's account.

hobbies

alphanumeric (ex. Home Winemaking)

This data populates the Hobbies section of a customer's account.

interests

alphanumeric (ex. Classic cars)

This data populates the Interests section of a customer's account.

sports

alphanumeric (ex. Curling)

This data populates the Sports section of a customer's account.

job_title

alphanumeric (ex. Chief Wine Inspector)

This data populates the Job Title section of a customer's account.

company

alphanumeric (ex. Offset Partners)

This data populates the Company section of a customer's address within their account which will populate the primary shipping address.

address

alphanumeric (ex. 1801 Old Sonoma Rd)

This data populates the Address section of a customer's address within their account which will populate the primary shipping address.

address_2

alphanumeric (ex. Suite 1-B)

This data populates the Suite / Apt. section of a customer's address within their account which will populate the primary shipping address.

city

alphanumeric (ex. Napa)

This data populates the City section of a customer's address within their account which will populate the primary shipping address.

state

Limited selection, Two Letter ISO 3166-2 State code for 50 States (ex. CA, OR, WA, etc.)

This data populates the State section of a customer's address within their account which will populate the primary shipping address.

zip

ZIP or ZIP+4 format (ex. 94559 or 94559-3714)

This data populates the Zip / Postal section of a customer's address within their account which will populate the primary shipping address.

billing_address

alphanumeric (ex. 1801 Old Sonoma Rd)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field updates when a new card is saved to an account.

billing_address_2

alphanumeric (ex. Suite 1-B)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field updates when a new card is saved to an account.

billing_city

alphanumeric (ex. Napa)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field updates when a new card is saved to an account.

billing_state

Limited selection, Two Letter ISO 3166-2 State code for 50 States (ex. CA, OR, WA, etc.)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field updates when a new card is saved to an account.

billing_zip

ZIP or ZIP+4 format (ex. 94559 or 94559-3714)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field updates when a new card is saved to an account.

mailing_company

alphanumeric (ex. Offset Partners)

This data populates the Company (optional) section of a customer's Mailing Address address within their account.

mailing_address

alphanumeric (ex. 1801 Old Sonoma Rd)

This data populates the Address Line 1 section of a customer's Mailing Address address within their account.

mailing_address_2

alphanumeric (ex. Suite 1-B)

This data populates the Address Line 2 section of a customer's Mailing Address within their account.

mailing_city

alphanumeric (ex. Napa)

This data populates the City section of a customer's Mailing Address within their account.

mailing_state

Limited selection, Two Letter ISO 3166-2 State code for 50 States (ex. CA, OR, WA, etc.)

This data populates the State section of a customer's Mailing Address within their account.

mailing_zip

ZIP or ZIP+4 format (ex. 94559 or 94559-3714)

This data populates the Zip section of a customer's Mailing Address within their account.

mailing_country

ISO 3166-1 alpha-2 codes formatted as two-letter country codes (ex. US, FR, GB, etc.)

mail_marketing_opt_in

limited selection (ex. Yes or No)

This data populates the Receive Mail preference of a customer's Contact Preferences within their account. A Yes response would opt-in to receive snail mail communications and a No response would opt-out.

mail_marketing_opt_in_update

YYYY-MM-DD date format (ex. 2024-10-24)

If you are importing from an outside source, this is the date when the mail_marketing_opt_in data was collected. This data updates automatically at opt-in if opting in through an Offset form.

marketing_opt_in

limited selection (ex. Yes or No)

Recommended field.

This data populates the Receive Email preference of a customer's Contact Preferences within their account. A Yes response would opt-in to receive Email communications and a No response would opt-out.

special_shipping

Format unavailable.

Custom Feature. This option must be configured by a member of the Offset team before you can import and use this feature.

phone_call_opt_in

limited selection (ex. Yes or No)

This data populates the Receive Calls preference of a customer's Contact Preferences within their account. A Yes response would opt-in to receive Email communications and a No response would opt-out.

receive_text_messages

limited selection (ex. Yes or No)

This data populates the Receive Texts preference of a customer's Contact Preferences within their account. A Yes response would opt-in to receive Email communications and a No response would opt-out.

mobile_phone

Three-digit area code and 7-digit subscriber number (ex. 707-444-5555 or 7074445555)

This phone number populates the Mobile Phone field in a customer account.

temporary_customer_notes

alphanumeric (ex. Call the customer for an updated credit card.)

This field populates the To Do notes where you can add a simple task or reminder you need to accomplish for the customer. Your customers do not see these notes, but be mindful of what you write. This will add an Attention! flag when you open a customer account that displays the message to admins until a user selects the Mark as Done button. Marking the task as done will also remove the note from the customer export.

alt_admin_notes

alphanumeric (ex. Miles likes Pinot Noir, he is not drinking Merlot.)

This field populates the Additional Admin Notes in the customer profile. It is typically used to note wine preferences but can be used for other notes. NOTE: These notes may be visible to your customer.

alt_payment_notes

alphanumeric (ex. Use the Visa ending in x3456 for all non-club orders)

This field populates the Payment Preferences in the customer profile. It is typically used to make notes about customer payments. NOTE: These notes may be visible to your customer.

alt_shipping_notes

alphanumeric (ex. Ship orders in 6-pack boxes or smaller.)

This field populates the Shipping Preferences in the customer profile. It is typically used to make notes about shipping. NOTE: These notes may be visible to your customer.

comments

Format unavailable.

Custom Feature. This option must be configured by a member of the Offset team before you can import and use this feature.

custom_message

Format unavailable.

Custom Feature. This option must be configured by a member of the Offset team before you can import and use this feature.

signup_ip_address

alphanumeric with special characters (ex. 12.123.45.678 or 2a12:2b:3::321d:e0e4)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field populates when a new customer signs up via your web form.

signup_user_agent

alphanumeric (ex. Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/18.0.1 Safari/605.1.15)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field populates when a new customer signs up via your web form.

signup_browser

alphanumeric (ex. Safari or Chrome)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field populates when a new customer signs up via your web form.

signup_browser_device_type

limited options (ex. Desktop or Mobile Phone)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field populates when a new customer signs up via your web form.

signup_browser_platform

alphanumeric (ex. MacOSX, iOS, Android or Win10)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field populates when a new customer signs up via your web form.

signup_browser_version

numeric with special characters (ex. 0)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field populates when a new customer signs up via your web form.

sc_order_split

Format unavailable.

Custom Feature. This option must be configured by a member of the Offset team before you can import and use this feature.

crm_customer_record

Format unavailable.

Custom Feature. This option must be configured by a member of the Offset team before you can import and use this feature.

legacy_customer_id

alphanumeric (ex. C5043)

This field is intended for new clients migrating from a previous commerce system. Offset customer ids are numeric, so this field can be used to import the customer id if it is alphanumeric.

assigned_owner

limited selection (ex. Ned Creed or Bobby Gibson)

This field is populated based on the Sales People entries on the Settings > POS & Admin page. The imported data must match the names listed on this page.

deactivation_date

YYYY-MM-DD date format (ex. 2024-10-24)

This date should match the date when the customer account was changed to Inactive.

deactivation_reason

limited selection (ex. Non purchaser)

This field is populated based on the Account Deactivation Reasons entries on the Settings > Configurations page. The imported data must match the names listed on this page.

deactivation_note

alphanumeric (ex. Has not purchased in over two years)

This field adds additional information entered in the Note field to provide more detail after choosing an Account Deactivation Reason.

deactivation_person

alphanumeric (ex. Johannes Schmied)

This field is populated based on the name Offset Team Members account on the Team Members page. The imported data must match the names listed on this page.

reactivation_date

YYYY-MM-DD date format (ex. 2024-10-28)

This is the date when the customer account was made Active again.

reactivation_note

alphanumeric (ex. Customer made a recent purchase)

This field is populated based on the Account Deactivation Reasons entries on the Settings > POS & Admin page. The imported data must match the names listed on this page.

transition_preference

limited options (ex. Yes, No, Maybe)

This transition preference is typically applied by using the Marketing Links in a Campaign Monitor email using the links in the Settings > Marketing Links > Preference options. This data populates the Transition Preference in account Groups & Segments. Note: You can delete the preferences in bulk by importing this column with blank data.

transition_preference_update

YYYY-MM-DD date format (ex. 2024-10-28)

This is the date the customer selected the transition_preference. It will automatically update to the date when the preference is selected via the Campaign Monitor email.

interest_1

Format unavailable.

Custom Feature. This option must be configured by a member of the Offset team before you can import and use this feature.

interest_2

Format unavailable.

Custom Feature. This option must be configured by a member of the Offset team before you can import and use this feature.

interest_3

Format unavailable.

Custom Feature. This option must be configured by a member of the Offset team before you can import and use this feature.

custom_1

Format unavailable.

Custom Feature. This option must be configured by a member of the Offset team before you can import and use this feature.

custom_2

Format unavailable.

Custom Feature. This option must be configured by a member of the Offset team before you can import and use this feature.

custom_3

Format unavailable.

Custom Feature. This option must be configured by a member of the Offset team before you can import and use this feature.

created_by

alphanumeric (ex. Bobby Gibson)

This field is populated based on the name Offset Team Members account on the Team Members page. The imported data must match the names listed on this page.

last_update

YYYY-MM-DD date format (ex. 2024-10-28)

Import unavailable. Leave this field blank or delete this column when importing new customers. This field populates when account updates occur, including import updates.

status

limited options (ex. Active or Suspended)

Coming soon to Offset. An Active status will grant a member full access to log into their account. A Suspended status will prevent the customer from logging into their account.

suspension_reason

alphanumeric (ex. Fraudulent account)

Coming soon to Offset. These are the additional notes to clarify why an account was suspended.

Import your Customer File

Once you have the minimum required fields and any additional info you want to add to your spreadsheet you are ready to import your file.

IMPORTANT: Double-check your formats before you import. If you are using a spreadsheet editor such as Microsoft Excel, your required formats could be altered by autoformat settings within the app. Dates are particularly susceptible to this issue. For example, if you have the birthdate formatted as 2025-01-01, you save the file, and then you later re-open the file for import, Excel will reformat the date as 1/01/2025 which will make the date appear incorrectly in Offset.

To address this issue, you can save the file in the correct format and leave the file open when you import. If you save the file but don't reopen it until after you import, this usually preserves the saved formats. Also, you can use an alternative spreadsheet editor that doesn't autoformat dates such as LibreOffice. If you are using a non-excel spreadsheet editor, make sure that you save the file as a Text CSV with UTF-8 encoding on all the text.

  1. Select the Customers tab

  2. Select the Actions dropdown

  3. Choose IMPORTS > Customers CSV

  4. Select the Choose File button

  5. Choose your customer import file

  6. Select the Upload button

NOTE: When the upload is complete, a green banner will display at the top of the page with the number of customers successfully imported. For example, "✓ 20 Customers Imported."

To review your import, we recommend that you export all your customers and compare the recently imported data to your original spreadsheet to check for any errors. You can also select the individual customers and review the settings directly in Offset.

Updating Existing Customer accounts using an Import Spreadsheet

If you need to make corrections to a recent import or you would like to update customer account data in bulk, you can complete this process with a spreadsheet import. Make sure to follow the formats listed above.

IMPORTANT: Please be mindful when making import updates to customers, as they cannot be undone. We highly recommend downloading a backup of your existing customers by going to Customers > Actions > EXPORTS > All Customers and saving the file in case you need to restore the existing data.

  1. Download your customer template or export an individual customer

  2. Delete any columns you don't want to import, but keep the id data

    1. IMPORTANT: When updating customers, the id is the unique identifier that ties the import row to the customer fields you want to update. Make sure that the id column is the first column in your import

  3. Fill out or adjust any customer data you would like to import according to the formatting conventions

  4. To import, select the Customers tab

  5. Select the Actions dropdown

  6. Choose IMPORTS > Customers CSV

  7. Select the Choose File button

  8. Choose your customer import file

  9. Select the Upload button

IMPORTANT: When updating customers make sure to delete the entire column for any fields you are not planning to update. If you include a column header with blank row data in your export it will delete existing data. Make sure to include all the column headers you need along with the data in the rows and don't worry about the column letters.

Example of an import update spreadsheet for active account status, birthday, and marketing preferences.


Recommended or Related articles

Did this answer your question?