In this article
The Automated Database Management system (ADB) allows you to capture Sample data that is already available in a survey. For example, if you have a Sample file containing contact details for your participants - a First Name, a Last Name, and an e-mail address, you can use the ADB system to capture that data for each participant. The ADB system uses a unique variable assigned to each participant called source, to locate and match-up the corresponding data for them. This source variable is automatically generated by the Email Campaign Manager, once you upload your Sample list in order to send out survey invites.
Note: The source variable is the only one that can be used with the ADB system.
1: Creating/Uploading the Sample File
1.1: File Format
The format of the Sample File for the ADB set-up should conform to the same requirements as uploading a Sample File in the Campaign Manager - either an excel file or a tab-delimited text file. Each separate field in the file should have a column header. The Sample file should always have a list column, which needs to contain values corresponding to one of the sample sources. For example, if there is only one Sample Source that has list=”1” as an attribute, you will need to have a list column that contains 1 for each participant in the sample file. Click here to download an example structure file that you can upload to the Campaign Manager.
1.2: Uploading the File
Since you want the system to generate a unique source variable for each record in your sample file, upload your list to the Campaign Manager, and later reference it from the Campaign Manager in survey. In order to upload the file, you need to create a new e-mail campaign and upload the list into that campaign.
2: Referencing the List
Once the list is uploaded, reference it in the survey by adding the lists="mail" to the survey tag:
<survey alt="ADB TEST" autosave="0" compat="133" extraVariables="source,record,ipAddress,decLang,list,userAgent" lists="mail" mobile="compat" mobileDevices="smartphone,tablet,featurephone,desktop" name="Survey" setup="term,decLang,quota,time" state="testing">
Since the sample file is uploaded to the Campaign Manager, the location of that file is in a folder called mail. Specifying lists="mail" tells the system to look for files in that specific folder when trying to match up a participant’s source to a specific record. If multiple lists are uploaded to Campaign Manager, the system will look in each separate file to locate the source variable that a participant is using. When using lists="mail", the system will look for a match in any file that starts with the word “list” and is situated in the mail directory.
3: Setting up the Samplesources
3.1: Enabling the ADB System
For any samplesources that are going to use the Automated Database System to pull data for participants, add adb="1" inside the samplesource tag:
<samplesource adb="1" list="1">
<title>Client Sample</title>
<invalid>You are missing information in the URL. Please verify the URL with the original invite.</invalid>
<completed>It seems you have already entered this survey.</completed>
<exit cond="terminated">Thank you for taking our survey.</exit>
<exit cond="qualified">Thank you for taking our survey. Your efforts are greatly appreciated!</exit>
<exit cond="overquota">Thank you for taking our survey.</exit>
</samplesource>
3.2: Capturing the Source Variable
After the sample file is uploaded, a source value will be generated for each record in the file. This source value will be appended to each participant’s survey link. For example, if the survey link is:
http://uk.focusvision.com/survey/selfserve/214e/160701
A participant with a unique source variable coming into the survey will be coming using a link similar to the below:
http://uk.focusvision.com/survey/selfserve/214e/160701?source=6xEVWhULbJ
In order to use this source value as a key to reference the data for each participant, be sure you are capturing the source variable in your survey. Add source as a samplesource variable to any samplesources you have that will use the ADB system:
<samplesource adb="1" list="0">
<title>Client Sample</title>
<invalid>You are missing information in the URL. Please verify the URL with the original invite.</invalid>
<var name="source" unique="1"/>
<completed>It seems you have already entered this survey.</completed>
<exit cond="terminated">Thank you for taking our survey.</exit>
<exit cond="qualified">Thank you for taking our survey. Your efforts are greatly appreciated!</exit>
<exit cond="overquota">Thank you for taking our survey.</exit>
</samplesource>
4: Accessing the Data
4.1: Accessing the Data Using Python
You can use python code to access the data for the current participant. For example, if you want to pipe in the first name of your participant from a column called "fName", use the below syntax:
adb.fName
If you have a column header in your sample file that doesn’t follow python naming syntax (e.g. no spaces, or special characters), for example a column named “last name”, use the below syntax instead to access information for that column:
adb["last name"]
Putting all of the above together, you can create a personalized welcome message for each of your participants by using Python piping:
<html label="cm1" where="survey"> Welcome to the survey, ${adb.fName} ${adb["last name"]} !</html>
Additionally, you can use exec blocks to populate questions with the data from our sample file. If there is a column called segNo with 4 different segments for our participants, valued 1-4, the following question captures the segment each participant falls into:
<radio label="hQ1" where="execute,survey,report"> <title>Hidden Segment Tracker</title> <comment>Select one</comment> <row label="r1">Segment 1</row> <row label="r2">Segment 2</row> <row label="r3">Segment 3</row> <row label="r4">Segment 4</row> </radio>
And use the following exec block to populate the question:
<exec> hQ1.val = int(adb.segno)-1 </exec>
Note: All values read from the sample file are read in as string variables, so if you want to extract numeric values, you will need to convert them to integer values first, using int().
4.2: Saving the Data Directly in a Question
An alternative way to store the data from the sample file is to build the questions in a similar fashion to using a <datasource> tag. Instead of assigning the value of a question with an exec block, you can use the dataSource="" attribute to specify where the question will pull its data from. If you add dataSource="adb" to a question, the system will automatically know to look for data for this question in the sample file. You then need to add another attribute called dataRef which would tell the system which row to reference when pulling the data. For example, if you want to reference the “segno” column in the sample file, add dataRef="segno" to our question, so that you pull the data for that specific column:
<radio label="captureSegmentNumber" dataSource="adb" dataRef="segno" where="execute,survey,report"> <title>Capture Participant’s Segment</title> <comment>Select one</comment> <row label="r1">Segment 1</row> <row label="r2">Segment 2</row> <row label="r3">Segment 3</row> <row label="r4">Segment 4</row> </radio>
The above method will only work for numeric data contained in the Sample file. If you want to capture alphanumeric data, or text data, add a dataValues="" attribute to the question. For example, if you want to capture your participant’s gender, and you have “male” and “female” as values in your gender column, create the below question:
<radio label="hGender" dataSource="adb" dataRef="segno" where="execute,survey,report"> <title>Capture Participant’s Gender</title> <comment>Select one</comment> <row label="r1" dataValue=”female”>Female</row> <row label="r2" dataValue=”male”>Male</row> </radio>
4.3: Capturing the Data in a Virtual Question
To create a virtual question pulling data from the sample file, build on the set up from section 4.2. To make a question that is already pulling from your sample file, add the dataVirtual="1" attribute to that question. Doing this to the previous question results in the below:
<radio label="vGender" dataSource="adb" dataRef="segno" dataVirtual=”1” where="execute,survey,report"> <title>Capture Participant’s Gender</title> <comment>Select one</comment> <row label="r1" dataValue=”female”>Female</row> <row label="r2" dataValue=”male”>Male</row> </radio>
5: Putting It All Together
Putting all of the previous examples together, the following is a survey that shows every example of pulling data from a sample file:
<survey
alt="ADB TEST"
autosave="0"
compat="133"
extraVariables="source,record,ipAddress,decLang,list,userAgent"
lists="mail"
mobile="compat"
mobileDevices="smartphone,tablet,featurephone,desktop"
name="Survey"
setup="term,decLang,quota,time"
state="testing">
<samplesource adb="1" list="0">
<title>Client Sample</title>
<invalid>You are missing information in the URL. Please verify the URL with the original invite.</invalid>
<var name="source" required="1"/>
<completed>It seems you have already entered this survey.</completed>
<exit cond="terminated">Thank you for taking our survey.</exit>
<exit cond="qualified">Thank you for taking our survey. Your efforts are greatly appreciated!</exit>
<exit cond="overquota">Thank you for taking our survey.</exit>
</samplesource>
<html label="cm1" where="survey"> Welcome to the survey, ${adb.fName} ${adb["last name"]} !</html>
<suspend/>
<exec>
hQ1.val = int(adb.segno)-1
</exec>
<radio
label="hQSegment"
where="execute,survey,report">
<title>Hidden Segment Tracker</title>
<comment>Select one</comment>
<row label="r1">Segment 1</row>
<row label="r2">Segment 2</row>
<row label="r3">Segment 3</row>
<row label="r4">Segment 4</row>
</radio>
<suspend/>
<radio
label="captureSegmentNumber"
dataSource="adb"
dataRef="segno"
where="execute,survey,report">
<title>Capture Participant’s Segment</title>
<comment>Select one</comment>
<row label="r1">Segment 1</row>
<row label="r2">Segment 2</row>
<row label="r3">Segment 3</row>
<row label="r4">Segment 4</row>
</radio>
<suspend/>
<radio
label="captureGender"
dataSource="adb"
dataRef="segno"
where="execute,survey,report">
<title>Capture Participant’s Gender</title>
<comment>Select one</comment>
<row label="r1" dataValue=”female”>Female</row>
<row label="r2" dataValue=”male”>Male</row>
</radio>
<suspend/>
<radio
label="vcaptureGender"
dataSource="adb"
dataRef="segno"
dataVirtual=”1”
where="execute,survey,report">
<title>Capture Participant’s Gender</title>
<comment>Select one</comment>
<row label="r1" dataValue=”female”>Female</row>
<row label="r2" dataValue=”male”>Male</row>
</radio>
</survey>