Bulk Importing Panelists
Bulk Import is an easy way to add or update a large number of Panelists in a single step using only a single Excel spreadsheet.
An important time-saving aspect of the Bulk Import process is the ability to create New Custom Fields and New Basic Segments. Additionally, you can use the import process to bulk add or bulk remove people from Studies and Segments. No matter how much data you have about your Panelists, Recollective can handle your import.
Please review our Bulk Importing Participants webinar.
To begin, select Bulk Import from the Panelist tab in Site Administration. The Bulk Import option is also available within a Study from the Add Participants page. The process is identical except it will automatically assign everyone to that Study. Importing from within a Study is only required for Moderators as they cannot access the Site Administration area.
Preparing 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).
The first row of your spreadsheet must contain a header row which simply labels each column (e.g. First Name, Username, Email, etc.). A header row is required and it can only be the first row. 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.
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).
You can update existing Panelists by specifying existing Email or External ID values. Use External ID if you wish to update Email addresses.
Step 1: File Upload
Once you have created an Excel spreadsheet with a header row and panelist data that meets the minimum requirements, it's time to upload the file. Select the spreadsheet file on the Bulk Import page and it will be uploaded and processed automatically.
Step 2: Column Review
Recollective will inspect the Excel sheet provided and summarize how many columns and rows it has detected. Each column detected will be listed on the page along with sample data for that column.
To complete this step, select a Column Type for each column or select Dot Not Import to skip that column. Even though Recollective will pre-select the column type for you, you're welcome to make your own selections.
The available column types are:
- Standard (e.g. first name, email)
- Custom Field
- New Custom Field
- Assign Segment
- New Segment
- Assign Study
After selecting a column type, you may be asked for additional information. Areas that need your input will be highlighted in yellow.
For example, if you select New Custom Field you will be prompted to selected the field type (e.g. Poll, Plain Text, etc.). For polls, Recollective will summarize how many additional choices will be created.
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) or a "0" (indicating that the Panelist should be removed from the Segment or Study)
- 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").
- State 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.
Step 3: Verify Data
Once every column has been configured (i.e. none of the fields in the Column Review step appear yellow), the Continue button will become selectable. Press the button to verify your data.
Recollective will validate your file and output a summary. For each column in the sheet, it will indicate how many rows have values, how many rows lack values and outline any special actions that will take place (such as creating custom fields or segments).
If there are any issues with the data in the sheet, one or more validation messages will appear. Use this guidance to fix your Excel sheet and try again.
You can safely re-uploaded your Excel sheet without losing your settings from the Column Review step.
Step 4: Import Settings
If there are no issues preventing your Bulk Import from proceeding, a set of Import Settings will now appear.
Use these settings to configure options that impact the Panelists being imported:
- By default, new Panelists will be asked to define their own secure and memorable password upon their first visit (this option can be disabled)
- You can also prompt new Panelists to verify their username upon their first visit (this gives them a chance to make their username more unique or generic depending on their preference).
- When importing within the Site Administration area, you can also choose to assign all imported Panelists to one or more Studies (importing from with a Study will automatically assign everyone to that Study)
Step 5: Import Process
When you're ready, press the Start Import button below the Import Settings. Note that Bulk Imports are fast but not instantaneous.
You will be redirected to the All Panelist area filtered on the users being imported (or Participant Directory with prompts to send or schedule email invitations if being done from the Study level). The page will indicate when the import has been completed. If you do not see everyone listed, try refreshing the page.
Site Log of Imports
All Bulk Imports are tracked in the Site Log. To view the log, go to the Site Setup: Site Log area in Site Administration.
A new entry is added to the log when a Bulk Import is started and the same entry is updated upon its completion. The site log details will provide a link to the original Excel file along with an import summary.
Please note that no emails are sent during the bulk import process. Once you're ready and your Study is OPEN, you'll want to send an invitation email from inside the Study to all participants deemed Added but not yet invited.