Bulk Import: Preparing a Spreadsheet
The first row of your spreadsheet must contain a header row which simply labels each column (e.g. First Name, Username, Email, etc.). The exact labels found in the header row are not important as you will able to identify each column after the file has been uploaded.
Recollective will use to the header row labels to make an educated guess about the column's usage (i.e. it will compare the label to standard account fields, custom Profile Fields, Basic Segments and Studies). If you need assistance creating a spreadsheet with a header row, select Create Template on the Bulk Import page.
The Excel file must be encoded with the UTF-8 character set to avoid issues with extended latin and non-latin characters. This is the default character set for Excel.
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).
Each Panelist being imported must be represented in the Excel sheet as a single row. Each Panelist must be given an email address OR a unique ID (External ID).
To help Recollective automatically detect columns related to Studies, Segments and Profile Fields, you can optionally add a prefix to the column label:
- 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:
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 Password is omitted, a temporary password will be generated that must be changed upon the Panelist's 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
- Segment and Study assignment columns must contain either a "1" (indicating that the Panelist should be assigned to the Segment or Study), a "0" (indicating that the Panelist should be removed from the Segment or Study), or be left blank (indicating that a panelist should not be assigned to a Segment or Study, but should not be removed either).
- 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").
- 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).
If you wish to bulk update email addresses of existing accounts with External IDs, contact us for assistance.