Help Structuring Customer Survey database
We would like to generate reports based on customer satisfaction surveys we receive each month in a single csv file that we import into FileMaker. Each survey contains about 15 fields including a Comments field containing free form text.
After import, we classify each survey with one or more metatags. Metatags are used to standardize comments entered by the customer. One customer may use "complicated." Another may use "complex", Another may say, "not too easy." etc. Our code for all of these would be, "Complex." We have 25 of these codes but the list might grow. Once classified, we want to generate reports showing trends.
In principle, the process is simple but I'm having a hard time with the relationships.
To Classify New Surveys:
- Find New, Unclassified Surveys.
- Go to Layout showing Customer Comments (from Survey table) and checkbox list of metatags from Issues list
- Create an Evaluation by assigning one or more metatags using standard tags from Issues table.
- Mark Survey as Classified.
- Go to Next Unclassified Survey.
- Summary table showing ranking of most commonly used metatags.
- Chart showing same.
- Evaluation showing how each Survey was classified.
- Similar reports based on info in the Survey table (product used, date of survey, customer size, etc.)
Table Structure So Far
I currently have three tables.
- Surveys: Approximately 700 records holding surveys already imported. Each Survey is assigned a unique Survey ID.
- Issues: Approximately 1,200 records. Each record contains a field referencing the Survey ID. Each record in Issues contains a field holding the Survey ID and a single value for the Issue.
- Codes: This is the standard set of metatags we currently use. 25 records.
Here’s where I'm stuck.
How to associate the records already in the database. e.g. Do I need an Evaluations table joining the Surveys and Issues?
What table and relationship structure, table occurrences, etc, do I need to create new Evaluations? Similar question to above? Do I need to create a new table, Evaluations, to link Surveys, Issues and Codes? If so, how is this table linked to the others?
What fields are needed to count and rank the issues and to generate the summary reports?
Thanks for any and all help.