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.
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.
Go to the Customers page
Select the checkbox to the left of a customer
At the bottom of the page, select the Choose an Action... dropdown
Select Export > Customer Data Export [.csv]
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.
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.
If you want more examples of your customer data, you can Export All Customers by following these steps:
Go to the Customers Page
Select the Actions dropdown
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.
green = Required field. All green fields need to be filled in before importing.
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.
NOTE: If you are adding or updating data for existing customers using an import spreadsheet, you do not need to include the required headers, only the ones you want to update.
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. Inactive customers will see an inactive message when logging into their online account. |
created_date (*created) | date format YYYY-MM-DD (ex. 2024-11-21)
| Required field. *Change the header name to "created" on your import sheet. 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. |
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. |
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) | 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) | 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) | 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. |
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) | 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 Customers
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.
Select the Customers tab
Select the Actions dropdown
Choose IMPORTS > Customers CSV
Select the Choose File button
Choose your customer import file
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.
Download your customer template or export an individual customer
Delete any columns you don't want to import, but keep the id data
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
Fill out or adjust any customer data you would like to import according to the formatting conventions
To import, select the Customers tab
Select the Actions dropdown
Choose IMPORTS > Customers CSV
Select the Choose File button
Choose your customer import file
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.