Excel Import Format
Why This Format Matters
CoLoop’s Open End Analysis product is designed to handle diverse data from various sources efficiently. By adhering to this standardized format, you ensure that your data is processed accurately, regardless of its original structure. This approach allows us to provide a consistent, high-quality analysis experience for all users.
File Requirements
- File format: .xlsx (Excel)
- Maximum responses: 10,000
Files exceeding 10,000 responses (n.b. not respondents) will trigger an error. Contact support if your use case exceeds this limit.
Excel Structure Overview
Your Excel file should be structured with the following sheets:
- Respondent Data (Optional)
- Conversation Data
- Question Data
You must include at least one Conversation Data sheet or one Question Data sheet.
Detailed Sheet Specifications
1. Respondent Data Sheet (Optional)
This sheet contains information about individual respondents.
Required column:
- “Respondent ID”: Unique identifier for each respondent
Optional columns:
- “Respondent Name”: Name of the respondent
- “[Segment]” or “[Segment] SegmentName”: Demographic or other segmentation data
Example:
Respondent ID | Respondent Name | [Segment] Age | [Segment] Gender |
---|---|---|---|
R001 | John Doe | 25-34 | Male |
R002 | Jane Smith | 35-44 | Female |
2. Conversation Data Sheet(s)
This sheet represents conversational data, such as interview transcripts.
Required columns:
- “Respondent ID”: Matching the ID from the Respondent Data sheet
- “Turn Number”: Integer representing the order of conversation turns (must start at 1 and increment sequentially for each respondent)
- “Question”: The question asked in that turn
- “Answer”: The respondent’s answer
Optional columns:
- “[Tag]” or “[Tag] TagName”: Tags for each conversation turn
- “[Segment]” or “[Segment] SegmentName”: Additional segmentation data
Example:
Respondent ID | Turn Number | Question | Answer | [Tag] Sentiment | [Segment] Device |
---|---|---|---|---|---|
R001 | 1 | How was your experience? | It was great! | Positive | Smartphone |
R001 | 2 | What did you like most? | The quick setup. | Positive | Smartphone |
3. Question Data Sheet(s)
This sheet represents survey-style question and answer data.
Required columns:
- “Respondent ID”: Matching the ID from the Respondent Data sheet
- Question columns: Headers contain the question text, cells contain answers
Optional columns:
- “[Tag]” or “[Tag] TagName”: Tags applied to all responses for that respondent
- “[Segment]” or “[Segment] SegmentName”: Additional segmentation data
Example:
Respondent ID | What improvements would you suggest? | [Tag] Satisfaction | [Tag] Survey Round | [Segment] Customer Type |
---|---|---|---|---|
R001 | Add more integrations | Very satisfied | Round 1 | New |
R002 | Improve Wi-Fi connectivity | Somewhat satisfied | Round 1 | Returning |
Best Practices and Tips
- Focus on Open-Ended Data: Ensure that your Excel file only includes open-ended questions and responses. Close-ended questions should be formatted as tags or segments instead.
- Data Cleaning: Before import, thoroughly clean your data:
- Remove any blank rows or columns
- Ensure consistent formatting across cells
- Check for and remove any hidden sheets or data
- Validate Respondent IDs: Use Excel’s data validation tools to ensure Respondent IDs are unique and consistent across sheets.
- Check Turn Numbers: For Conversation Data sheets, verify that Turn Numbers start at 1 and increment sequentially for each Respondent ID.
- Use Clear Naming Conventions: Choose descriptive names for your segments and tags to facilitate analysis.
- Keep It Simple: If a column doesn’t apply to your data, leave it out rather than including empty values.
- Review Before Import: Double-check your file for accuracy and completeness before attempting to import it into CoLoop.
Common Pitfalls to Avoid
- Mixing Open and Closed-Ended Data: Don’t include closed-ended questions as separate columns. Instead, use tags or segments to represent this information.
- Inconsistent Respondent IDs: Ensure that Respondent IDs match exactly across all sheets where they appear.
- Non-Sequential Turn Numbers: In Conversation Data sheets, make sure Turn Numbers are sequential without gaps for each Respondent ID.
- Exceeding Response Limit: Remember that projects with more than 10,000 responses will by default trigger an error.
- Using Unsupported File Formats: Only .xlsx files are supported. Convert your data if it’s in a different format.
- Incomplete Data: Ensure all required columns are present in your sheets.
- Inconsistent Naming: Use the exact column headers specified in this guide, including the brackets for [Tag] and [Segment] columns.
By following these guidelines, you’ll ensure that your data is correctly formatted for import, enabling smooth and accurate analysis of your open-ended responses.
If you encounter any issues or have questions about formatting your data, please don’t hesitate to reach out to our support team.
Was this page helpful?