The Panel Administration System

, I The Panel Admipistratjon System Andrew Beggs, Unilever Research Laborator' UK Abstract· As part of Unilever's Resea...

0 downloads 291 Views 1MB Size
, I

The Panel Admipistratjon System Andrew Beggs, Unilever Research Laborator' UK Abstract· As part of Unilever's Research & Development program, panels of people are recruited from the general public to take part in appraising and testing new products. These panels range in size from a few hundred to several thousand and are administered by the local consumer research centres. The management of the panels has been transformed from an assortment of computer based systems of varying complexity, to a single SAS/AF • application. One of the great benefits of the new system is that it uses a very simple file structure which is both easy for the user to visualise and makes for a flexible approach to the way in which data can be recorded. Panel selection based on criteria input by the user, is rapid and is coupled to procedures which allocate products to panellists based on the type of test being carried out. Analytical routines are also linked in to generate reports on the results of trials. The system takes advantage of many of the features offered by recent releases of SAS/AF including object orientated programming and the new GUI facilities in FRAME Entry •. The system is straightforward to maintain and easy to use, requiring little training. The system and database design is sufficiently generic ,to allow this single application to be applied across a wide range of Unilever products.

Introduction : Unilever recruits panels of people from the general public to appraise and test new products. People are selected from these panels to participate in tests based on their meeting certain criteria. For example, in the case of a shampoo trial, it may be desireable to use people with dry or normal hair texture. With a fabrics washing test, the panel might be selected based on the type of washing machine used and the local water hardness being less than a certain value. In order that appropriate panels can be identified for such trials, data is held on each paneIIist concerning their demographics, personal details and information about the home. Once a suitable panel has been selected to take part in a test, the products to be appraised are allocated and mailed to each panellist. Having used the products, the paneIIist completes a questionnaire which records how products are perceived and how they compare to other brands. The completed questionnaires are scanned using an optical mark reader to get the data into digital format, from which it can be analysed.

413

The nroblem :

To develop a computer based system which is able to realise effective panel management, taking account of the following factors :The nature of the individual panels varies considerably from one region to another with particular regard to the following :- " 1. Size of panels - a few hundred to several thousand. 2. Product areas - Detergents, Personal products. 3. Local differences & preferred ways of recording data :a) some panels use codes as opposed to full descriptions b) type of data c) local language Clearly with such variability between panels, any single computer based system which is built to work for all panels, requires a vcry open data structure. The systems which were originally being used to administer the panels ranged from simple spreadsheets to complex programs which worked to varying extents on the local data. However, none of the systems were flexible enough in their design to be used globally and did not mcet all of the required functionality. The need to develop a harmonised panel administration system was identified with the following overall specifications :1. 2. 3. 4. 5. The SAS

>10

Common system which can be used by all consumer panels. Sufficiently generic to cater for the wide range of Unilever products. Capable of responding and adapting to future needs. Able to link panel data and questionnaire data to analysis routines. Menu driven and easy to use.

system which has been developed to meet these requirements has three main attributes :-

Flexibility, Utility & l\inintainability.

414

"':I.

""""""""""""""""==, ~s~'--~O'"s

1"[...............

I

I ,~

I ~.

/.

F ~~ I: r;,

Flexibility : The key to designing a system which meets the specifications listed above, was to have a very open data structure. Two broad categories of data were identified :

1. Demographic data includes information such as:Panellist No., Name, Address, Telephone No., Age Group, . Occupation & No. in family. Such information is common to all panels and the file is therefore structured in this way. 2. Household details I Personal details - this covers a wide range of information pertaining to the home and lor the panellist. Due to the variable nature of this data, the following open file structure was conceived. Household Details File Panellist No 1011 1011 1011 1011 1221 1221

Parameter 1 Tum ble Dryer Kitchen Floor Washing Machine Bathroom Suite Dishwasher Kitchen Sink

Parameter 2 Philips Tiled Hoover Ceramic AEG Stainless

Personal Details File

560 560 560 560 560 684 684

Hair Style Hair Colour Hair Texture Hair Products Hair Products Hair Damage Hair Condition

Wavy Dark Brown Fine Shampoo Conditioner Minor Dry

Performance Rating File Each time a panellist participates in a test, a performance rating is allocated to that person. As this tends to generate a large volume of records, the information is stored in a separate file. Panel Status File Information regarding the status of selected panels, together with their size and completion date is also stored separately from the main Household Details file. Bc:>th the performance rating data and panel status information could be included with the main Household details file, but it would greatly increase its size and therefore the time taken to access the data.

415

.. .. .

. .. ",--

____ .. __

._~

__

._:~:_~~_C'"_--'_":

__

'~"_._._

.

~

-

.-

."",,_,__ , _ " , " . _ . _ " _ . _ '.

J""

'~_'Jr

._~

•••.•

~-

-.

"_.

-

-

____ •

____

,:::~~-.; .••.;_;.~~:.. •..;_:.~~...,._ _ _ • •

r

__

~-:-~-.J

File Structure used for Panel Administration System Detergents Consumer Panel

Personal Procilcts Consumer Panel

...... :.:::::::::::.

HOJse"':M Details

:m::Pe(~(''''

·:"·.·ti~~iis::::::im.

.·.··::::;.((:ji:i:f:"'::::::·

Advnntnges of this file structure: 1. 2. 3. 4. 5.

Does not require users to adhere to a rigid database structure with fixed field names. Allows users freedom to record data in the form which is appropriate to their panel. Any national language can be used. New data items can be added at any stage without requiring any system modifications. Avoids redundancy within fields.

Disndvnntnges : 1. The file becomes very large in the case of panels containing several thousand panellists. The way round this problem is to index the file. 2. The other potential problem with this data structure is that there is no restriction on what values are input and how words are spelt. For example, there are many ways of inputting the text for 'Washing Machine' • W.Machine, Wash Mach., Washing Mach. etc... . If such synonyms are generated then searches on the data for panellists who have a 'Washing Machine' will not give the correct number. Use of selection lists is therefore mandatory to ensure correct spelling. The above file structure gives the required flexibility and is the key building block of the application. However, indexing of files is essential to avoid unacceptable response times. Also the user should ideally have some appreciation of the underlying file. structure, in order to ensure that data is entered correctly through the use of selection lists.

416

Utility:

To make the panel data easily access able by the user who has little or no knowledge of SAS Software·, the database is front ended by a SAS/AF • application which delivers complete functionality through the Graphical User Interface environment of displays and menus. Like any system which is built for use by people with varying levels of computer literacy, it will only be accepted if it is straightforward to use and is not accompanied by an armful of manuals and training requirements. Through the use of frames, icons and selection lists, the panel system has been designed to be intuitive, particularly to those people who are involved in consumer testing. .

417

-- . - -':',-,-'-- -_ -___ -.;--. '_' ___ ~.-

_.

• - . ' ~ r_.' _<

~~.

_ -.

.-.- -; ::".:" =--''';,.:.-;...:::-:.~...:: ~'~;"'

""

~~:- ~f

~

The utilities which are available to the user include :1. Facilities to View, Edit, Add & Delete data.

Frames containing icons are used to present a menu of these options to the user. The associated SCL code calls SASIFSP • Programs in FSBROWSE·, FSVIEW • or FSEDIT • mode to give the user the required access to Demographic and Household Data. Appropriate push buttons are defined to make the task of viewing and updating the database straightforward. These include buttons such as 'Next Panellist', 'Previous . Panellist', 'Add', 'Delete', 'Go Back', 'End' & 'Cancel'. 2. Targetted Selection of pan.els. a) Panels are selected based on criteria defined by the user. This can be a single criterion such as the Area from which the panel is to be selected, or it may be a combination of Demographic and Household criteria. If insufficient panellists are generated, then the user is given the option to carry out further selections with modified criteria. Frames are used to capture the selection criteria which is then submitted through SAS/SQL • statements to create a new table of selected panellists. b) Panel selection may be controlled so that it meets specified Demographic / Household / Personal details targets. For example in a Shampoo trial, it may be desireable to get 50% of the panel having Dry hair texture and 50% having Normal hair texture. This is achieved by force fitting the required attribute(s) to the initial panel selection and then selecting the appropriate number of people required. c) Test products are allocated to the selected panel based on the type of test being carried out [Common Control, Monadic, Paired Comparison etc]. d) If required, the allocation of products to panellists may be balanced across a particular characteristic such as Water Hardness or any Demographic / Household detail. This is achieved by assigning a random number to each panellist, sorting the panel by the random number and then fitting the required profile of product codes to the resulting dataset. e) Panellists who have been selected to participate in a test are flagged as such, so that they may be excluded from further panel selections.

3. Printing Distribution Lists & Address Labels for selected panels. Having selected a panel for a test and allocated products to panellists, the user can print a distribution list for the delivery drivers - listing names, addresses and product codes. Address labels can also be printed.

418

~;,:~·rn(J;U£X!iijiB.!iWimrSti!f!h~E;giB:;'-;o;;:ii!._ii_il;p'd,i •.,,-ff_.!iiE?,,'C;*L..fu~ft\t,,:n~,;'e'!fu.€.,_.~~~ti'ffi~~";:'~~,:rr':'1:;::x"l'~~i1~R,~~~","~t,l~"''':~?':;';'~~~'''~''':..~''''''~~''_~-:':''J'':;'r-,r,,~;,.:-,·.o.~~'5."""·~":-~i""':,"~_-~~-··~]';','·">:~"""Y'~:::="i.~*~.;~~<,:,;?-·:"".":"~~"'!J':",:.'<~·'~·;-';'~-<~""">.':l\"'~~~...,:(,~-.o~'i""-'~~::::::"!.-_::;-:r,>.;:.,,!:u_~1".r;.;;'L,If~=~.....,.,.,--=:E..;l-"--'.. '.3i"''''_'''-'''''~.~.~..n:o>.<:;p-'''-.......

~

Panel Selection Process

"

':-

~

System displays frequency of Demo I HHDetail

, ;~

:r

ft .'

:':

r"*,·~t~---,:

User customises this to meet requirements

:'\"

i i Allocate products to i

I pa:,:I;:*~."

m

"""':':':':':':"':':':':'1':':':':':':':':':':':.:.:.:.:.:.:.:.:.:.".:.:.::'

:------11 ...... ~ ..... (0

::::

'.',. '.:.:.:.:.:.:.:.:' '.:.:.:.: . :.:.:.:.:':':':':':':':':':.:.:':':':':':':':':':':~j~ ..::.'

System Constructs m Profile for i!: allocating Product m

;:i

.; ~

Setup Panel?

m iii

!:!

L~z,J

Create Panel

Flag panellists being used for test

Stop :\.... ,..--~---

.--:;'-.,-.~~--:-'--'-----

-_._--"

--"-~-,

". ---' _ .. -- - .. ~,=,-

- ----

J No . :,.: .:. .:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.: . .:. :. .:.

:.:; :. ~.<\,: .:. :. .:. . .:. :., .Stop . . . . . . :. . .: .: .: .:. :. :. ,. .:. . . .

I

Maintainability:

1. Re organising data for performance To ensure that the Demographic and Household I Personal details files do not become cluttered with duplicate records or records which are marked for deletion, the user can run a procedure which sorts and deletes such records, thereby reorganising the files to give optimum database performance: 2. Param1 I Param2 Files to speed up selection ·"f

To help the user specify selection criteria and input data, lists of existing values for parameter 1 & parameter 2 in the household details file can be viewed and selected. As well as speeding up data entry, this ensures that values are input correctly with the right 'syntax' ie 'Washing Machine' is entered and not 'W.Machine'. The lists are maintained as files which can be updated with new values at any stage. This is far preferable to regenerating them each time they are queried, as selecting the distinct occurences of paramo 1 & paramo 2 from the Details file which might contain several thousand records is very time consuming. 3. Unique Indexes Both the Demographic Details file and the Household I Personal Details file are indexed on the Panellist No field which has the UNIQUE attribute. This realises considerably faster response times when updating and querying the datasets. 4. Non Missing indexes to generate Panellist No So that the user does not have to look up the next sequential panellist No. when adding new panellists to the database, an index on the Demographic Details file is used to generate it automatically. The index has the NON MISSING attribute on Panellist No. to ensure that its size and therefore its access time are minimised. 5. Keeping track of open I closed panels Once a panel has been set up, a record is added to the Panel Status File, detailing its name, size and whether it is open or closed. When a panel test is complete, the completion date is input. The user is given complete control over selected panels through a series of menu options :Display a panel - produces a full listing of panel members and product codes. Close a panel. used to flag that a test is complete. Re-open a panel. Copy, Delete, Merge a panel. List all tests that a particular panellist is involved in.

420

6. Panellist Ratings How well a panellist performs in a test is recorded in the performance rating file which may be viewed and modified as required. 7. File Checking Procedures On initialising the Panel Administration System, a 'Primary' program runs which checks for the existence of specific files, without which the system cannot 'run. These files concerned are :Performance, Panel Status, Paramo I & Paramo 2 files. As the various consumer panels are responsible for their own data, it possible that these files may not exist, particularly with a new panel, in which case the system creates the necessary files. For the same reason, the program also checks that the variable lengths in the Demographic and Household I Personal Details files are correct.

t

~.: "~

.~

8. Interfacing Panel Data with Test Results

r.

I~

i

To permit a full data analysis on the results of tests - allowing any possible links to be drawn between product performance and household factors, it is necssary to generate a kernal file of test results merged with household details. This is done by selecting all household details for the panel members and transposing them. The resulting dataset can then be merged with the results file and analysed.'

,.

~,

.::" ~.~

[; ~.\ ~": 1\

9. Analysis

1:



~~.

t:"

Analysis of test results is carried out by calling a separate SAS application which front ends a range of methods used to analyse consumer data. The analytical routines range from simple cross tabulations to multivariate analyses .

~.

,

r ., ~

~

0

i;

In Summary;

{{~

~f~ &:

The Panel Administration System is very much an open system and is designed in such a way that it may be applied to any scenario concerned with recording data on a varied and inconsistent range of values, where a traditional normalised set of tables would realise excessive field redundancy and require continual maintenance. By taking advantage of many QUI features offered by SAS, the system is easy to navigate and requires little training. Frames and Icons may be modified easily to meet requirements of new applications. Collectively these features give the Panel Administration System enormous potential in the business, as the key to solving a wide range of data management problems.

~"

i, ",Ii

It

t~ ,:~

~ 1~ ~

~:

~'

~ N:

I ~,

~

I~

~;

~,

"il i

, ~

\ .,

421

Acknowledgments: The author wishes to thank Dr Phillip Whittall, who has been key to the development, success and support of tIiis system. SAS Institute Trademarks: All trademarks below are registered trademarks or trademarks, as indicated by their mark, of SAS Institute Inc., Cary, NC, USA. SAS Software, SAS/AF, SASIFSP, FSBROWSE, FSEDIT, FSVIEW, FRAME Entry.

422