This feature allows users to import survey responses into a dataset from a CSV file. The questions in the CSV file must match the questions in the dataset - i.e. you cannot add additional questions to a survey with this feature (unless you define them in the survey first - see below), you can only add additional responses.
The primary use cases are:
-
Consolidating multiple waves or pulses (separate surveys) into one survey to present time-series dashboard reporting (line graph, etc)
-
Simulating responses over time for demo purposes
-
Integrating external data into a dashboard, such as non-Alida survey responses or sales data
-
Appending additional questions/responses to a survey that’s already been distributed
Dataset functionality
Surveys with status Pending can be added to a dataset. The reason for this is that you may want to create a survey that is never distributed but simply used as a structure for importing CSV responses. You may receive a yellow warning message about this - it can be ignored.
Importing for multiple surveys
A dataset can contain one of the following, but not both:
-
A single multi-response survey, plus up to 10 PVs for active community members
-
Up to 20 single-response surveys, plus up to 10 PVs for active community members
Based on this, it is important to plan the dataset design as early as possible in the project in order to avoid running into issues late in development.
To keep things simple, it is recommended to keep the dataset to one survey, unless there is a specific need for multiple surveys.
If a dataset contains multiple single-response surveys, then the CSV template exported will be formatted as follows:
-
ownerId
-
recordExternalId
-
[all survey 1 fields, including ResponseType, UserAgent, etc]
-
[all survey 2 fields]
-
[all survey 3 fields]
-
[etc…]
-
[all PVs]
Based on this, each row of the CSV should only contain ownerId, recordExternalId, fields for ONE survey, and optionally PVs. Including responses for more than one survey on a single line may not cause an error but should be avoided to keep the data separated and make data management easier.
How to use
Creating a skeleton survey
In many cases, you will be best served by creating a ‘skeleton’ survey that only contains questions, and then using that to create a dataset and CSV template. This skeleton survey will not be deployed or distributed and will have no responses completed – the only purpose of this survey is to create the structure to import the CSV data. This survey can either be created from scratch, or you can duplicate an existing survey that contains some/all of the questions required.
All questions in a skeleton survey should be marked as not required - there is no benefit to marking questions as required since no one is actually taking the survey; all you are doing is causing potential issues if some of your data is blank. Please see below for best practices on creating a skeleton survey.
Good use cases for this method:
-
All the survey data is coming from non-Alida systems and so there is no survey in Insight Community to begin with (action plan: create survey from scratch)
-
There is an existing survey that contains most of the questions but you want to add some (action plan: duplicate that survey)
-
The survey exists with the right questions, but you want complete control over the data and don’t want the existing responses in the survey to pollute the dataset (action plan: duplicate that survey)
Bad use cases:
-
You have an ongoing survey that you want to represent in a dashboard along with some external CSV data. By creating a skeleton survey you will need to transfer the data from the ongoing survey to the skeleton survey via CSV file every time you want to refresh the dashboard - this will be very cumbersome
Example scenario: you have 12 monthly surveys which each share the same nine questions, and then each have a unique 10th question. You want to create a dashboard trending the nine questions over the past year.
Solution: Duplicate one of the surveys (for example January) as a skeleton survey, then create a dataset based on it. Export the data for all 12 months and compile a CSV containing results for all 12 months, then import that into the dataset. In this way, all the data is coming from one place.
Integrating other Alida surveys
The easiest way to import data from another Alida survey is to export it from Insight Community to CSV. From there the data can be massaged into the correct format and imported into the target dataset.
-
Select the survey containing the required data and from the Analyze tab, export the results to CSV
-
Export the CSV template from the target dataset
-
Copy and paste the responses into the CSV template, following the template format
-
Save the file and import into the dataset
Integrating external data
If you have results from a survey that was conducted on another platform, follow these steps:
-
Ensure the data is in Excel or CSV format with one row representing a single survey response, and each column representing one questions or data field
-
Recreate the survey in Insight Community as a skeleton survey (see above)
-
Copy and paste the responses into the CSV template, following the template format
-
Save the file and import into the dataset
Appending additional questions to existing surveys
If you have a survey that's already received responses, but you want to integrate additional questions with this tool (rather than with PVs), you can follow this process:
-
Export the survey responses as CSV
-
Duplicate the survey
-
In the duplicate survey, add the additional questions
-
Create a dataset from the duplicate survey
-
Export the CSV template from the dataset
-
Manually adjust and copy/paste the survey responses from step 1 into the CSV template, and add the responses to the additional questions
-
Import the CSV file into the dataset
CSV template
After creating your dataset, you can select CSV File > Download Template, which will provide an empty CSV file containing all the columns you need to populate before importing back into the dataset.
When populating a CSV file, all questions marked as required in the survey(s) used to create the dataset will need to have a value.
In addition to the survey questions, the following columns will also be included:
-
ownerId: should be populated with the respondent’s MemberID. If the response does not tie back to any specific member, this can be any string, or left blank. If a CSV record is imported that contains a MemberID that does not exist in the target community, the data will be imported properly into the dataset, but that member will not be created in the community. There is no way to affect actual community data (members, PVs, etc) while using the CSV import.
-
recordExternalId: For multi-response surveys, represents a single response from a respondent that may have responded multiple times. This is only required for multi-response surveys but is still present when using single response surveys. This must be populated with a unique value for each record for a single ownerId - for example:
-
DisplayType: can be ignored - the column must be there but can be left blank
-
UserAgent: can be ignored - the column must be there but can be left blank
-
RespondentLocale: can be ignored - the column must be there but can be left blank
-
ResponseStatus: this should be “Completed” unless you are working with incomplete/invalid responses. Please note the value for this field is exported from the Reports tab as “Complete” (note the difference)
-
CompletedDate: the completion timestamp of the response, in UTC time. Format should follow 2020-07-31T15:00:00.000Z.
Including Profile Variables (PVs) in CSV upload
If PVs are included in the dataset, they will also be present in the template file. These are optional, but if they are populated in the CSV they will also populate the PVs for those responses in the dataset but not in the actual member record.
If a PV value is left blank in the CSV file, this will not clear out the value of the actual PV for the member.
As an example - community member 1234 has a blank value for the “Age” profile variable. You create a dataset that includes the Age PV, and populate the CSV template with a response for member 1234 and enter 25 for Age. After the data is imported, if you generate a dashboard you will see 25 populated for this respondent’s age, but if you go to the Members section of Insights Community, the value will still be blank.
Based on how complicated all the above is, it is recommended to avoid using PVs when creating a dataset to be populated through the CSV import, unless there is a specific need. Instead, survey questions should be used for the PV values, as in this example where the respondent’s city is represented as a survey question rather than a PV as would typically be used:
CSV import pros and cons
Method:
-
A csv file is created containing survey responses, and that file is imported into the Alida platform to visualize in dashboard form.
Pros:
-
Data is contained within a single dataset and does not clutter up PVs
-
Not subject to dataset PV maximum of 10 PVs per dataset
-
Can represent data as time-series - for example consolidating multiple monthly surveys into one contiguous line graph
-
Arguably more intuitive than PVs when importing survey responses
Cons:
-
For multi-response surveys, may need to populate the same info multiple times - for example if you want to include a user’s income as a question and they responded three times, you need to populate that value three times, vs once for a PV
-
Requires more Excel data manipulation than PVs
Profile Variable import pros and cons
Method:
-
A csv file is created containing PV data, and that file is uploaded into the Alida platform to populate PVs for respondents/members. A dataset which includes both PVs and survey responses is then created, and that dataset is visualized in dashboard form.
Pros:
-
Data imported as PVs can be used elsewhere in the instance
-
Arguably more intuitive than CSV import when importing per-respondent data (income, gender, etc)
-
May require less training since PVs have been around longer
Cons:
-
Limit of 10 PVs and 5 surveys in one dataset - so if you have 10 surveys with 10 questions each, it’s impossible to capture all that in one dashboard unless you use the CSV import
-
Cannot represent time-series data with PVs - so if you have:
-
April customer satisfaction (PV)
-
May customer satisfaction (PV)
-
June customer satisfaction (survey question)
-
You cannot chart this in a single line graph, you need three separate tiles
-
Can create excessive clutter within an instance, especially if capturing multiple years/months/waves for a question
Best practices
-
DO try to keep things as simple as possible - ideally, all your data is coming from a single CSV file populating a single survey in the dataset. Avoid PVs, multiple surveys, and other complications unless there is a specific need
-
The CSV import is case sensitive - so if you try to import “YES” for a question with the answer option “Yes”, you will receive an error message
-
DO NOT include PVs unless you have a good reason (see Including PVs in CSV Upload section)
-
DO NOT include multiple surveys in a single dataset, unless there is a specific need - multiple surveys will add complexity to the CSV template (for example you will have multiple CompletedDate fields)
-
Keep in mind that datasets can only pull data from Sparq, they can never push data back. This means if you are trying to add members or populate PVs within the community, you cannot accomplish this with the CSV tool. Conversely, this also is helpful in limiting the ‘damage’ you can do through error.
-
DO plan when designing your dataset. Datasets can not be edited after creation so late-stage changes can be very costly. For example, if you feel there is need where you might want either the full province name (British Columbia) or the abbreviation (BC) you should include both so that you’re prepared to switch.
FAQs and Troubleshooting
-
As of Jan 2023 you cannot import dates that are in the future. A request has been made to remove this limitation
-
When importing open text data, currently single and double quote characters in the responses will cause import errors - make sure to remove all of those in a text editor or Excel before importing. There is a fix coming for this soon
-
For date questions, they must be represented as a full timestamp - even though there is no time associated with a date question, the time should be included as 12AM. Format should follow 2020-07-10T15:00:00.000Z - note is different from the format date questions are exported from Analyze, so manual adjustment is necessary.
-
If you have a survey response that contains a comma (for example if the response is “No, I do not have a car”), you must wrap the response in double quotes, as per the below screenshot. Note that Excel does this automatically if you save your document as a CSV file.
-
If you make an error and import the wrong data into a dataset, you must start over with a new dataset. There will be a rollback feature in a future version.
-
If you receive an error message stating a pending survey must be open or closed (see below), you can ignore this error if you are not planning to distribute the survey and are simply populating results via CSV. This message only relates to syncing, and it is not necessary to sync a dataset that is only being populated by CSV, since syncing is a way to connect survey results to a dataset, and in this scenario the survey has no results.
Comments
0 comments
Article is closed for comments.