AU2003100880A4 - Export of survey response data from an online survey management system - Google Patents

Description

I<

P/00/011 Regulation 3.2

AUSTRALIA

Patents Act 1990 COMPLETE SPECIFICATION INNOVATION PATENT Invention Title: Export of survey response data from an online survey management system The following statement is a full description of this invention, including the best method of performing it known to me/us: Freehills Carter Smith Beadle Melbourne 004384265 2 EXPORT OF SURVEY RESPONSE DATA FROM AN ONLINE SURVEY MANAGEMENT SYSTEM The present invention relates generally to the field of online survey management systems that enable the creation, distribution, reporting and management of a survey of one or more groups of survey respondents, and in particular to the use and exporting of survey response data from such online survey management systems.

Existing online survey management systems are used as authoring tools to generate a survey consisting of a succession of questions desired to be posed to one or more groups of survey respondents. The online survey management system typically includes a central server including a processing unit and associated memory for maintaining computer program code for providing the survey management system with required functionality, and a database operatively connected to the server for storing survey information and survey response data.

Questions are created online by the survey manager, who accesses the survey management system via the Internet from a remote terminal. The survey management system enables the survey manager notably to control the number of questions in the survey, the type of questions (open ended, estimate, multiple choice with single answer, scale, dichotomous, multiple choice with multiple answer), question order, the text forming part of each question, and the logical subdivision of questions into survey parts.

The survey manager is also able to create one or more groups of respondents intended to respond to one or more of the questions forming part of the survey.

Each of the several respondent groups forming part of a single survey may be presented with a different selection of questions or wording, and the survey maniager is able to select the different groups of questions or wording for each of the different respondent groups by use of the survey management system.

Once the survey has been created by the survey manager, and the desired Melbourne\004384095(Copy) Printed 24 October 2003 (15:04) 3 respondent list prepared, respondents are notified of the existence of the survey by email or other notification means. Respondents then access the survey management system from a remote terminal and respond to the survey questions via a web-based interface.

Once survey respondents have responded to the various survey questions online, the survey response data is maintained in the database forming part of the survey management system. The survey manager then accesses the survey management system from a remote terminal and uses various software tools provided by the survey management system to report on and analyse the stored survey response data. The software tools enable the presentation to the survey manager of survey results data in the form of summary tables, and various type of pie and bar charts.

However, it is often desirable to analyse the survey response data in a manner that exceeds the capabilities of an online survey management system. It is then desirable that the survey response data maintained by the survey management system is exported to a statistical analysis program in order to extract greater value from the survey response data and is possible by use of the survey management system alone. Unfortunately, a significant amount of data manipulation is often required to be performed to the data exported by the survey management system in order that the data can be used by a statistical analysis program. Moreover, when such manipulation is performed, the manner in which the survey response data is captured and maintained by the survey management system may limit additional statistical analysis being able to be performed.

It would therefore be desirable to provide a means of formatting the survey response data for export from an online survey management systems that enables more meaningful statistical analysis to be performed on the survey response data than is currenltly possible.

It would also be desirable to provide a method of formatting survey response data for export from an online survey management systems that ameliorates or Melbourne\004384095(Copy) Printed 24 October 2003 (15:04) overcomes one or more advantages of existing survey management system data export functions.

With this in mind, one aspect of the present invention provides a method of formatting survey response data for export from an online survey management system, the online survey management system including a database for maintaining survey response data categorising responses from one or more survey respondents to a plurality of survey questions, each survey question requiring a forced choice response or an open ended response, the method including: creating a survey data file including a response group file for each respondent group, each respondent group file including a first record set of discrete value response data from each respondent within that respondent group, and a second record set of open-ended response data from each respondent within that respondent group.

The database may further maintain respondent attribute data categorising one or more selected attributes of survey respondents.

The method further including: adding respondent attribute data for each survey respondent in each record of the first and second record sets.

The database may further maintain respondent identifiers representative of the survey respondents.

The method may further include: adding one or more respondent identifiers corresponding to each survey respondent in each record in the first and second record sets when respondent anonymity is not required.

The method may further include: adding question identifiers corresponding to each survey question in each respondent group file, and associating survey response data from each respondent for each survey question with the relevant question identifier.

The method may further include: Melbourne\004384095(Copy) Printed 24 October 2003 (15:04) adding to the survey data file a question index listing each survey question and question option.

The method may further include: providing online access to the database to enable export of the survey data file.

Access to the database to enable export of the survey data file may be provided after completion of the survey by at least one respondent.

Another aspect of the present invention provides an online survey management system, including a processing unit and associate memory device for storing computer program code to enable the online survey management system to perform a method as described above.

Another aspect of the invention provides a computer program including a computer program code for use with an online survey management system, the online survey management systems including a processing unit and associated memory device or storing the computer program code, the computer program code causing the online survey management system to perform a method as described hereabove.

The following description refers in more detail to the various features of the present invention. To facilitate an understanding of the invention, reference is made in the description to the accompanying drawings where the online survey management system and method of formatting survey response data for export from the online survey management system is illustrated in a preferred embodiment. It is to be understood, however, that the invention is not limited to the preferred embodiment as illustrated in the drawings.

In the drawings: Figure 1 is a schematic diagram of one embodiment of an online survey management systems according to the present invention; Figure 2 is a schematic diagram illustrating the broad functional aspects of the online survey management systems of Figure 1; Melbourne\004384095(Copy) Printed 24 October 2003 (15:04) 6 Figure 3 is a schematic diagram of inter-related objects forming part of a database of the online survey management systems of Figure 1; Figure 4 is an exemplary graphic user interface presented to a respondent to a survey by the online survey management system of Figure 1; Figures 5 to 7 are flow charts illustrating the operation of the online survey management system during the formatting and exporting of the survey response data from the online survey management system of Figure 1 to a statistical analysis product; Figure 8 is an exemplary table illustrating the manner in which prior art online survey management systems export survey response data; Figures 9 and 10 are exemplary tables respectively of a first and a second record set of response data, the two record sets forming a respondent group file within a survey data file; Figure 11 is an exemplary table illustrating a question index forming part of the survey data file; and Figures 12 and 13 are exemplary tables respectively of a first and a second record set of response data in which respondent identifiers have been omitted for some survey respondents.

Referring now to Figure 1, there is shown generally an online survey management system 1 including a server 2 and database 3 operatively connected to the server 2. Access to the server 2 is provided by way of the Internet 4 or other communications network. The server 2 includes a processing unit and associated memory device for storing computer program code to cause to cause the survey management system 1 to carry out desired functionality. The computer program code notably encodes business logic rules that control access levels and privileges of a survey manager accessing the server 2 via the Internet 4 from a survey manager machine 5, as well as access levels and privileges of respondents to the online surveys that access the server 2 via the Internet 4 from a client machine 6.

The computer program code additionally encodes a presentation layer that Melbourne\004384095(Copy) Printed 24 October 2003.(15:04) 7 allows survey manager operational use of the management functions provided by the survey management system 1, and a respondent interface providing a presentation layer to allow respondent use of and participation in the survey via the online display provided from the server 2.

The database 3 maintains survey information, including survey response data, in the form of a series of tables. The tables store data relating to the questions, information relating to the respondents and respondent groups and information relating to the respondent results. The database 3 is governed by relationship rules ensuring data integrity between survey questions, respondents and access privileges.

Figure 2 illustrates the broad functional aspects of the creation, distribution, reporting and management of a survey using the online survey management system 1. The database 3 maintains information relating to various types of surveys 10 to 13 that may be conducted by the online survey management system 1. From these survey types, different surveys 14 to 16 are created. For each of these surveys, one or more groups of respondents are defined. In this example, the survey 15 includes the three respondent groups 17 to 19. The survey manager additionally defines one or more respondent attribute categories 20 to 22 respectively for the respondents within each of the respondent groups 17 to 19.

The online survey management system 1 enables the selection by a survey manager of six question types 23 to 28, as follows: Text Question Type Response Data Type Open-ended Text Estimate Text Multiple Choice Single Answer Numeric Response Value Scale Numeric Response Value Dichotomous Numeric Response Value Multiple Choice Multiple Answer Dichotomous for each possible response Questions 29 corresponding to one, some or all of the six question types 23 Melbourne\004384095(Copy) Printed 24 October 2003 (15:04) 8 to 28 are then formed by the survey manager. Different selections of these questions 29 are selected to be presented to the respondents within each of the survey groups 17 to 19. A category 30 can be associated with each question and various response options 31 associated with each forced choice question may also be defined by the survey manager. Whilst in the described embodiment, one of the response data types is an ordinal numeric response value, it will be appreciated that in other embodiments this response data type may be an alpha or alphanumeric response value or other discrete response value.

Having thus created the survey 15, the survey manager then defines the manner in which the survey respondents are notified of, and provided with access to, the survey. Depending on the survey type 10 to 13 respondents may be provided with a username and password, and may be notified about the survey via an email.

The survey manager definable anonymity levels 32 are defined to determine whether the survey respondents are identified when question responses are coded.

The completion date 34, or alternatively the period of time during which the survey 15 is to be conducted is able to be determined by the survey manager. The URL by which respondents will access the server 2 is provided by the online survey management system. Data 36 collected from the responses of the respondents to the survey 15 are maintained in the database 3 in the form of survey data 37, and summary results 38 may be derived from the survey data 37.

As can be seen in Figure 3, the database 3 maintains a series of inter-related objects or tables for storing data categorising the survey questions, question types, respondent questions for a particular survey, respondent groups, respondents and respondent attributes, as well as response data from the respondents to the various surveys posted by the survey management system 1.

Specifically, the object 40 assigns a type number and description to each of the six above described types of questions that may be included in any particular survey. The various types of questions are then transformed into specific questions.

The object 41 maintains the question ID and text of each question, together with Melbourne\004384095(Copy) Printed 24 October 2003 (15:04) 9 survey identifier, creator identifier, question type, category, annotations and response options.

Respondents are represented in the object 42 by one or more respondent identifiers. The respondent identifiers may include the first name, last name, staff number or other identifier enabling identification of the survey respondent. This object also stores a respondent group identifier and survey creator identifier.

Depending upon the definable anonymity levels 32, one or more respondent identifiers may be included in data exported for subsequent statistical analysis if respondent anonymity is not required, or one or more respondent identifiers may be omitted in exported data in order to protect the privacy of one or more respondents.

Attributes of each of the respondents to the survey are maintained in the object 43. The attributes may include the department, branch and gender of each survey respondent. This object includes a respondent ID, survey identifier, and an identifier and value for each selected attribute of survey respondents that is desired to be collected.

Data identifying each respondent group is maintained in the object 44, which includes a respondent group ID, group name, project identifier and survey creator identifier. The respondent attributes from each of the various respondent groups is maintained in the object 45. This object includes an attribute identifier, group identifier and survey identifier.

The selection of questions maintained in the object 41 that are posed to respondents within each respondent group maintained in the object 44, are recorded in the object 46. This object includes a group identifier, question identifier and survey identifier, as well as an indication of the position order of each question within a particular series of questions posed to each group within the survey, and an indication of whether a response to that question was compulsory.

The data struiture shown in Figure 3 is able to accommodate all three types of response data that may be generated in response to the six types of questions that may be included in each survey, by use of an object 47 for maintaining ordinal and Melbourne\004384095(Copy) Printed 24 October 2003 (15:04) dichotomous numeric response data, and an object 48 for maintaining open-ended response data. In the context of the present invention, the phrase "dichotomous" is intended to refer to response data based on a series of choices between alternate answers and is intended to include responses to multiple choice/single answer, multiple choice/multiple answer, scale and dichotomous question types. Similarly, the phrase "open-ended" is intended in the context of the present invention to refer to questions that are not limited in the choice of answers made by survey respondents.

The numeric answers object 47 includes an answer identifier, question identifier and respondent ID, together with an answer field for recording a numerical value assigned to each dichotomous response, and a field indicating the date and/or time at which each answer was provided by a survey respondent.

The open-ended answer object 48 includes an answer identifier, a question identifier, a respondent ID, the first 250 characters of the open-ended response, a code field, and a date field indicative of the date and/or time that an answer was provided by a survey respondent. The database also includes an object 49 for maintaining various codes corresponding to a broad categorization of the nature of an open-ended response.

The objects 41 to 49 maintained in the database 3 are populated both during the creation of the surveys in the survey management system 1, and from response data provided by respondents to the various survey questions, and during coding of open-ended responses by the survey manager using the survey management system 1. Respondents provide data by way of form variable provided to the survey management system 1 ;.via a web-based interface presented to each survey respondent. An exemplary graphic user interface displayed at the client machine 6 to a respondent is referenced 50 in Figure 4. The graphic User interface displays to each respondent questions, such as those reference 51 (a multiple choice question) and 52 (an open-ended question) requiring the selection of one of a limited number of responses 53 or the entry of text into a text entry window 54. Data entered by the Melbourne\004384095(Copy) Printed 24 October 2003 (15:04) 11 recipient is then transmitted to the server 2, where an SQL query is used to extract and insert information from the respondent answer into the relevant object maintained by the database 3.

Relevant data maintained in the objects 41 to 49 can be exported for subsequent analysis by a statistical analysis program by selection of an export data function provided to the survey manager. As seen in Figure 5, upon selection of the data export function, the computer program code maintained in the server 2 causes the online survey management system to check for the existence of survey data in the database 3 that corresponds to the selected survey, at step 60. Provided at least one respondent has answered one question, requiring either an open-ended response or a numeric response, that respondent identifier and respondent attributes will appear in both of the objects 47 and 48. At step 61, a determination is made as to whether any response data exists for that selected survey. If no data exists, a message is displayed to the survey manager to advise that there is no data to export, at step 62. Otherwise, the survey manager is provided, at step 63, with a link inviting the download of the requested survey data.

The survey data is then extracted from the database 3 at step 64, which is illustrated in more detail in Figure 7. As seen in that latter figure, once the survey manager clicks on the link 63, a survey identifier 80 corresponding to the selected survey is identified by the survey management system, and a temporary data table is extracted. A first respondent group identifier corresponding to one of the respondent groups to whom the survey was directed is retrieved at step 81. At step 82, the attribute identifiers for each of the selected attributes of respondents within that first respondent group are then retrieved. At step 83, the question identifiers of all questions requiring a numeric response from survey respondents forming part of that first respondent group are then retrieved from the temporary table. At step 84, the respondent group identifier, attribute identifiers and question identifiers are then used together with other data maintained in the database 3 including the temporary table, to generate an SQL statement for all respondents who belong to the first Melbourne\004384095(Copy) Printed 24 October 2003 (15:04) 12 respondent group and have completed/submitted survey responses requiring numeric answers. At step 85, that SQL statement is executed in the data maintained in the objects 41 to 49. If the statement is not successfully executed, an error message is displayed at step 86, however, if at step 87 it is determined that the statement was successfully executed, a first record set 88 of numeric response data from each respondent in that respondent group is generated.

At step 89, the attribute identifiers for all respondents who belong to the first selected respondent group are retrieved, and at step 90, the question identifiers corresponding to open-ended questions posed to survey respondents from within that first respondent group are retrieved. At step 91, an SQL statement for all respondents who belong to that selected respondent group that have completed/submitted responses to the survey including open-ended answers is generated. At step 92, the SQL statement is executed. Unless an error at step 93 is generated, the statement will have been successfully executed at step 94, and a second record set of open-ended response data from each respondent within that first respondent group is generated at step 95. At step 96, a determination is made as to whether there is a further respondent group that formed part of the survey for which response data is desired to be exported. If at step 97 that is the case, then steps 81 to 96 are repeated and the process of populating the spreadsheet is repeated until there are no more respondents groups. Otherwise, at step 98 it is determined that there are no further respondent groups within that survey, then the program module illustrated in Figure 7 is terminated at step 99.

Returning again to Figure 5, now that step 64 has been terminated, the two record sets generated at steps 88 and 95 for each respondent group are firstly identified, and retrieved at step 66. After each respondent group name is retrieved from the objects 41 to 49 at step 67, the two record sets created at steps 88 and are then combined, at step 68 into a single respondent group file for that respondent group. At step 69, that respondent group file is reformatted as a spreadsheet including a number of worksheets with each record set on a different sheet, by using Melbourne\004384095(Copy) Printed 24 October 2003 (15:04) 13 Microsoft® Excel® Writer, or other readily available program.

At step 70, a determination is made as to whether further record sets exist for another respondent group forming part of the survey. If it is determined at step 71 that this is the case, the two record sets for that next respondent group are retrieved at step 66 and then step 67 to 70 repeated. If, however, there are no new respondent groups forming part of that survey, as determined at step 72, the retrieved respondent group files are saved as a single survey data file at step 73. At step 74 a message displayed to the survey manager to advise that the survey response data has been successfully been prepared for exporting. At step 75, the user is presented with a choice of either cancelling the survey response data file download at step 76, or alternatively proceeding at step 77, with the download and opening of the survey response data at step 78. At step 79, if the survey manager elects at step 79 to save the survey response data, they are presented with a dialogue box inviting them to save the survey response data at step 84. This action may be cancelled at step 81, or proceeded with at step 82, in which case the survey response data will be downloaded and saved in a file on the survey manager machine 5 at step 83.

Figure 8 illustrates an example of the survey response data exported by a prior art online survey management system. In this case, the raw data was exported in a comma delimited format, and opened using Microsoft® Excel® for display in a tabular form. It will be noted that whenever a survey respondent completes a response to a survey question, the time and date at which the response was provided to the online survey management system, the text of the question provided, and the answer to that question are captured. No distinction is made between numeric responses or open-ended responses.

This is to be contrasted with the survey response data exported by the survey management system 1. Figure 9 illustrates a first record set of numeric response data for a first respondent group within a particular survey, whilst Figure illustrates a second record set of open-ended response data from respondents within that same respondent group. The record sets of Figures 9 and 10 together form a Melbourne\004384095(Copy) Printed 24 October 2003 (15:04) 14 respondent group file forming part of a survey data file that may include one or more like respondent group files. It will be noted that in both record sets, the respondent within each respondent group is identified by a respondent ID (reference numerals 13733 to 13746), and respondent attributes of "department", "branch" and "gender" are included in the record set. Respondent attribute data of "sales", "technical", "planning" and "sales" have been recorded against the respondent attribute identifier "department" for relevant respondents.

Similarly, the respondent attribute data of "CA" and "DC" have been entered against the respondent attribute identifier "branch" for each respondent, and respondent attribute data of "male" and "female" entered against the respondent attribute identifier "gender" for each respondent.

In the first record set of numeric response data shown in Figure 9, question identifiers corresponding to each survey question requiring a numeric response are included. Single answer questions include only one field in which a numeric value corresponding to the dichotomous response is provided (Q1, Q2, Q5, Q6). Multiple answer questions are provided with a field corresponding to each response option (Q3 1 toQ3 Moreover, numerical values corresponding to a categorisation of open-ended response data provided in response to open-ended questions are included in the first record set shown in Figure 9 (Q4).

The second record set of open-ended response data shown in Figure includes the first 250 characters of each open-end responses provided by each relevant respondent in response to the question identified by the relevant openended response question identifier (Q4).

Figure 11 is an exemplary table illustrating a question index listing each survey question and.question option included in a particular survey. This question index may be included in the data exported from the survey management system 1.

Figure 12 and 13 illustrates alternative embodiment of the first and second record sets shown in Figures 9 and 10. In Figure 12 and 12, the respondent identifiers enabling identification of some of the survey respondents has been Melbourne\004384095(Copy) Printed 24 October 2003 (15:04) omitted from the data exported by the survey management system in order to protect the privacy of one or more survey respondents. When no respondent anonymity is required, either by design or choice, respondent identifiers for all survey respondents may be includes.

It will be appreciated from the foregoing that the above described export data function provides an efficient solution to a common user requirement to obtain survey response data in a raw format in order that additional value may be extracted from that data by a statistical analysis program. The export data structure is able to accommodate in the one file a variety of types of response data.

The layout of the two record sets illustrated in Figures 9 and 10 enhance a user's understanding of the data structure, and remains consistent through successive downloads of real-time response data. However, the content of each of the columns in the tabular form of the survey response data illustrated in Figures 9 and 10 is dynamic for each survey depending on the number of respondent attributes selected and the types of questions asked of each respondent group.

Survey response data is provided separately for each response group on separate record sheets since the number and type of respondent attributes, the questions asked and the question order can vary between respondent groups.

Moreover, the representation on a separate pair of record sets of response data for each respondent group prevents response data from different questions being aligned or grouped because of the common question number. This is to accommodate the fact that although groups may have common questions, the order may vary and each respondent group may also have been asked unique questions.

The representation of respondents by a respondent identifier (such as a respondent ID number), rather than the use of the respondent's name protects the respondent's privacy. Existing online survey management systems use the date/time that the .response was provided (see Figure 8) to differentiate responses in data extracts, however this does not allow for pre-prepared attribute lists to be included for further detail analysis.

Melbourne\004384095(Copy) -,'inttd 24 October 2003 (15:04) 16 The inclusion of respondent attribute details in the record sets enables further detailed analysis to be performed on the survey response data by sub groups within each respondent group. Only attribute details from select-list attributes are provided against each respondent's respondent identifier where there is anonymity by choice or design. Attributes that are imputed by respondents, and therefore may be unique to each respondent (such as an employee number or phone number) are thus excluded to protect the respondent privacy. Where there is no anonymity by choice or design there is inclusion of attribute details from select-list attributes and attributes that are imputed by respondents.

The above described export data function also allows response data to be exported at any time after completion of a survey. This enables up to date information gathering, allowing a survey manager to analyse a response trend without waiting until completion of the project.

The exporting of the survey response data is dynamic for each survey, but is always consistent throughout the duration of the survey. Hence, the survey manager can establish methodologies for further analysis of the data in the knowledge that the data structure will not change with time.

Finally, it is to be understood that various modifications and/or additions may be made to the above described online survey management system without departing from the spirit or ambit of the invention as described above.

Melbourne\004384095(Copy) Printed 24 October 2003 (15:04)

Claims (4)

1. A method of formatting survey response data for export from an online survey management system, the online survey management system including a database for maintaining survey response data categorising responses from one or more survey respondents to a plurality of survey questions, each survey question requiring a forced choice response or an open ended response, the method including: creating a survey data file including a response group file for each respondent group, each respondent group file including a first record set of discrete value response data from each respondent within that respondent group, and a second record set of open-ended response data from each respondent within that respondent group.

2 A method according to claim 1, wherein the database further maintains respondent attribute data categorising one or more selected attributes of survey respondents, the method further including: adding respondent attribute data for each survey respondent in each record of the first and second record sets.

3. A method according to claim 2, wherein the database further maintains respondent identifiers representative of the survey respondents, the method further including: adding one or more.. respondent identifiers corresponding to each survey respondent in each record in the first and second record sets when respondent anonymity is not required.

4. A method. according to any one of the preceding claims, and further including: Melbourne\004384095(Copy) Printed 24 October 2003 (15:04) 18 adding question identifiers corresponding to each survey question in each respondent group file, and associating survey response data from each respondent for each survey question with the relevant question identifier. A method according to any one of the preceding claims, and further including: adding to the survey data file a question index listing each survey question and question option. Dated: 24 October 2003 FREEHILLS CARTER SMITH BEADLE Patent Attorneys for the Applicant EVALUATION SOLUTIONS Melbourne\004384095(Copy) Printed 24 October 2003 (15:04)