Bulk Import: Preparing a Spreadsheet

The first step in the Bulk Import process is to prepare your spreadsheet. If you are not using Microsoft Excel, use a tool that can export to the Excel file format. Many free tools such as Google Sheets, OpenOffice and LibreOffice can export to the Excel file format (the .xlsx file extension is preferred). 

Formatting

The first row of your spreadsheet must contain a header row which simply labels each column (e.g. First Name, Username, Email, etc.). 

Recollective will compare the header row labels in the spreadsheet to any Fields or Segments already configured on your site. If headers match existing Fields or Segments, they will update automatically. If the header does not match any existing Fields or Segments, Recollective may pre-select the Column Type for you, or you will need to select from one of the available column types.

If you need assistance formatting the header row of your spreadsheet, select Create Template on the Bulk Import page. Here, you will select the fields you want to include in your template from a list of Standard Profile Fields or Segments and Custom Profile Fields configured on your site. 

Minimum Requirements

• Each Panelist being imported must be represented as a single row in the spreadsheet.
• At a minimum, each Panelist will need to be assigned a Unique Identifier. This can come in the form of an Email Address OR an External ID.

Optional Inclusions

Segment and Study Assignments

The Bulk Import tool can be used to assign or remove Panelists to and from Studies and Basic Segments. Each Study or Segment will require its own column containing a "1", “0” or be left blank.

• A “1” indicates that the Panelist should be assigned to the Study or Segment. 
• A “0” indicates that the Panelist should be removed from the Study or Segment.
• A cell that is left blank indicates that a Panelist should not be assigned to a Study or Segment, but should not be removed either.

Profile Fields

Bulk Imports can also be used for Panelist Profiling, both for adding Panelist data to Standard Fields, and for creating New Custom Fields. 

• Columns containing non-binary data: Custom Field
• Columns containing binary data: Segment

Set and Assign Passwords

Important Considerations

Please be aware of the following:

• If First Name or Last Name are omitted, Panelists will be prompted to complete these missing fields upon their first visit
• If Usernames are provided, they must be unique across all Panelists and Admins. They cannot contain spaces or use special characters (dashes and underscores are permitted) 
• If Usernames are omitted, Recollective will create one using the pattern FirstName_LastInitial or just FirstName if no last name has been provided.
• An External ID is any unique value assigned to the users from an external system. This identifier must be unique across all Panelists and Admins.
• Multiple Poll Choices must be delimited by commas (e.g. cats,dogs,birds,fish,frogs) - to include a comma in the choice label, wrap the choice in double quotes (e.g. "Cats, dogs and birds","Fish, frogs and reptiles").
• Dates: Date Fields must be typed in the one of the following formats:
  • MM / DD / YYYY
  • DD / MM / YYYY
  • YYYY / DD / MM
  • YYYY / MM / DD
• Account Status can be used to bulk activate or deactivate Panelists (use "1" for Active and "0" for Inactive)
• Incentive Points is available only when importing within a study that has Points enabled. A positive value will add points to the participant's total points balance in the study while a negative value will subtract points. 
• Timestamps field values must date-time values in Excel (e.g. "2022-03-21 8:00:00 PM") but they can also be the number in milliseconds since Unix epoch time (e.g. "1647907200000" is March 21, 2022 at 8 PM in Eastern Standard Time). Unix epoch is the arbitrary date of January 1st, 1970. Such timestamp values must be milliseconds and not seconds (for an example, visit https://currentmillis.com).

Additional Information

• Segment: Segment name
• Study: Study alias

For new custom Profile Fields, the prefix can indicate the field type and the field name. For example:

• poll-multiple[pets]:What pets do you own?

In the example above, a Poll field of the Multiple Choice type will be configured with the Question set as What pets do you own? and the Field Name as pets. Note that the [field name] part can be omitted.

The other custom field type prefixes are:

• poll-single:
• poll-multiple:
• text-plain:
• text-formatted:
• number:
• date:
• timestamp:

If you wish to bulk update email addresses of existing accounts with External IDs, contact us for assistance.