Using external files with Sample Manager

The External File tab allows you to provide a list of panellists from which the sample should be selected (or a list to be excluded), for example with event-based research, or if the Q_Panel database does not hold suitable filtering data.

You can include or exclude sample according to a list of panellists and data imported from a file on your PC.  The data can be Boolean, date, text and/or numeric (integers only).  Panellists can be identified by Panellist Id (also called External Customer Id) or by matching the panellist by First Name, Last Name and/or Email Address.  Partial matches can also be made, although if more than one panellist matches the criteria then all matching panellists will be included (or excluded).

We will use the following scenario to illustrate the process:

Your Q_Panel holds a database of all new car buyers in 2013 from the X-Car Corp in the UK.

A project is commissioned to research customer satisfaction amongst those customers who had their 2013 car recalled, due to a steering problem discovered in one particular production run.  X-Car Corp are able to supply a list of those customers affected.

Clearly a random sample based on the whole Q_Panel database would not be appropriate.

Instead, by importing the list of panellist ID’s and data into the Q_Panel database, the subset of individuals required for the research can be identified and the data becomes available for use in profile filters, dimensions, and in the sample output file.

Please contact your system administrator if you are unsure about what your organisation uses as the panellist ID. This is defined at the time that Q_Panel is implemented.

External file format and content

The file must be an ASCII file (DOS/Windows) in a comma-separated (.csv) or tab-separated (.tab) format.

The file must contain one column for panellist ID OR one or more columns to allow a match to the panellist first name, last name and/or email address.  The file may also contain one or more data columns. An optional column header row may be included.

Column headers

If using column headers, note the following requirements for column header names:

  • the first character of a column name must be a letter or an underscore “_” 
  • subsequent characters must be letters, numbers or any of the following special characters: at sign (@), underscore (_), hash (#) or dollar sign ($)
A letter in this context is defined by Unicode Standard 3.2, which includes Latin characters from a through z, from A through Z, and also letter characters from other languages.

Example 1

A simple example using panellist ids, without any data or column titles could look like this:

	444
	9387
	5
	[etc...]

Sample Manager will limit sample to be drawn only from within this list.  Alternatively you can decide to draw sample only from those not in the list.

Example 2

A file using panellist ids and also containing data (as in the example of the X-Car Corp example) might look like this:

Row	ID	RecallDate	Fixed	FixDate		RecallID	XCarCode
1	100281	23/03/2015	Y	23/03/2015	2081		XD-02
2	100290	23/03/2015	n			4091		XA-17
3	100313	23/03/2015	1	23/03/2015	0382		XK-08
4	100314	24/03/2015	N			1089		XA-13
5	100315	23/03/2015	0			2056		XD-03
6	100325	23/03/2015	1	23/03/2015	2041		XE-01
7	100332	24/03/2015	yes	23/03/2015	0819		XR-29
8	100377	23/03/2015	yes	23/03/2015	0071		XD-12
9	100400	23/03/2015	0			1083		XA-11
10	100401	23/03/2015	0			4011		XA-18
[etc...]

The Boolean data in the Fixed column is used to indicate whether the fault was fixed. Sample Manager will correctly interpret the following Boolean entries:

0, 1, No, Yes, N, Y, no, yes, n, y

All other entries (apart from a blank entry) will cause either the entire column to be ignored or the entire file to be rejected.

All dates in the external file must be in a consistent format.  This can be DMY, MDY, YDM or YMD.

This example also contains numeric data in the RecallID column, and text data in the XCarCode column.

All datatypes can contain empty entries with the exception of the Panellist ID.

If there is a panellist ID in the file which does not match one in the Q_Panel database then the entire row will be ignored. Panellist IDs duplicated in later rows are ignored.

Using this external file will limit sample to be drawn from within the list and will make the data available for use

  • in profile filters
  • in dimensions
  • as output file columns

Example 3

An external file which matches panellists by the first three characters of their first name and last name, using column titles but no additional data could look like this:

	First_Name,Last_Name
	ADA,ADD
	CHI,CHO
	DAV,FIN,
	JON,ALD
	[etc...]

Sample Manager will limit sample to be drawn only from panellists that match records in this list (the match criteria must be specified when file is imported – see later in article).

It is possible that more than one panellist will match per record of the external file, in which case all matching panellists will be included (or excluded). 

External files using matching may also contain data columns.

Importing and external file using External Customer Id

To add a new or updated file, on the External tab click Select and then click the Add External File icon in the top left corner of the Select External File dialog:

Click Select. This brings up a Windows dialog Choose File to Upload:

Navigate to the file you wish to use and click Open:

Here you see a preview of how Q_Panel will interpret the file.

In this example:

  • the Customer Identifier is External customer id and the ID column has been selected
  • the Separator is changed to tab
  • the First Row Has Column Headers is checked

Add a File Description which must be unique.

Click Next> to confirm the type of each column:

The following options are offered in the dropdown list:

Type Description
Boolean A ‘Yes/No’ marker
Date A date in the format selected on the Date Format dropdown.  All dates in the file must have the same format.
Numeric An integer.
Text A piece of text.
Ignore Ignore the data in this column.

Click Finish and the data file is imported:

Click Close to add the file entry to the top of the imported list, along with any other external files already in the system.

It is now available to be selected as the external file for your sample project. The file description that you entered appears in the External File field:

The data is now available within the list of variables that can be used in profile filters, dimensions, and output columns. 

If you wish to exclude the panellists in the external file, ensure that the Exclude based on file contents box is checked, otherwise Sample Manager will only include panellist from this file.

External File now appears as a folder in the Criteria Detail dialog when adding profile filters.  (Similarly it is available as a folder in the Choose Dimension and Choose Output Columns dialogs).

The data can now be used in the same way as the profile variables stored in the panel database.

Importing an external file using Custom Matching

To import an external file which uses custom matching (such as Example 3 above), select the external file as before, but this time select Custom as the Customer Identifier:

As before, enter a suitable file description and click Next> to define the custom matching criteria.

For each column that is to be used to match the customer, select whether it is to match the First Name, the Last Name or the Email Address stored in the panel database.  (Only a single source column can be matched to each of the Q_Panel identification fields).  Then select the Match Type required for each of these columns using the standard text matching options of Exactly, Contains, Begins With, Ends With and Sounds Like. 

Columns not required to match customers can be set to <ignore>.  Columns ignored at this stage may still be loaded as data columns in the next stage.

In this example, our external file contains the first three letters of the panellist first name and last name, so we will set our matching criteria as follows:

Click on Next> to continue:

We do not have any additional data columns to define in our file, so click on Finish to complete the import.

Selecting an imported external file

An imported external file must be associated with a sample project; therefore prototypes do not have an External tab.

A sample project can be associated with only one external file whereas one external file can be associated with many sample projects.

On the External tab, when Select is clicked, any files previously imported by other users, and therefore already present in Q_Panel, will be displayed and can be selected for use.

If there are a large number of external files already loaded, the search function can be used to find the required file.  Enter the text you want to search for, select the appropriate search type and click on Search.

By default, the search type is Contains, but this can be changed by selecting another search type from the dropdown list.

Search Type Description
Begins With Returns all files where the name or description begins with the characters entered.
Contains Returns all files where the name or description contains the characters entered.
Ends With Returns all files where the name or description ends with the letter or letters entered.
Exactly Returns all files where the name or description matches exactly the characters entered.
Sounds Like Returns all files where the name or description sounds like the characters entered.  This type of search is useful for finding files where the exact spelling is not known, or may have been entered wrongly.

Click on the Reset button to clear the search and show all external files again.

The external files displayed here have previously been imported by Q_Panel users.  Changing an external file outside of Q_Panel after it has been imported will not affect the data that Q_Panel holds

Errors when importing

If the file being imported contains errors, these will be reported, but the file may still be imported.  The user can review the error summary and decide whether to

  • proceed with the import
  • navigate back to the properties and ignore the column containing the error
  • navigate back to the properties and change the data type of the column containing the error
  • cancel the import and fix the errors in the external file - this is the recommended option

For example, if two rows of the X-Car Corp external file contain data errors they would be reported as follows:

  • To proceed with the import click the Proceed with the import and ignore the data errors checkbox.  In this case, the external file would still be imported and available for use in a sample, but may not have imported all rows and/or columns. 
  • To ignore the columns causing the error click on the Back button to return to the Data Format dialog, and set the data format for columns FixDate and Fixed to Ignore.  In this case, the external file will be imported and available for use without these two columns.
  • It may be that the wrong data type has been selected for these columns.  To change the data type click on the Back button to return to the Data Format dialog, and select the correct data format for columns FixDate and Fixed.  The external file should then be imported without any errors.
  • The recommended approach is to cancel the import, edit the external file to correct the errors reported and re-import the file.

Duplicates in the external file

If the external file contains duplicate records Sample Manager will ignore the duplicates, however, if the duplicate records have different values for imported data columns there is no guarantee which column Sample Manager will use, therefore it is recommended that non-duplicated files are loaded.

Sample Report

When using external files the sample report will show additional information.  For example:

The exclusions summary shows the following information: