the:behavioral:lab

Archive for the tag “question validation”

A better way to ask for currency responses in Qualtrics

Willingness to Pay questions (WTP) are ubiquitous in marketing research (my field), as well as many others. When asking for WTP in Qualtrics, you normally have to settle for some sub-par work-arounds. When asking in an open-answer text box, you can leave no validation, but you risk getting a lot of gibberish that both isn’t quite missing data, but isn’t quite usable data either. If you put a numerical validator participants may get annoyed at the inevitable error messages. They try to type something like $300, but to a computer, that’s not a number (it contains the dollar sign so it gets interpreted as a character string). You could also do things like slider bars, but that sets artificial anchors that could bias results.

I solve this problem with regular expressions. If you are not familiar with regular expression, they are a way of programming a pattern within a string of characters. For instance, an email address is any number letters, numbers, and certain special characters, followed by the @ symbol, followed any number of strings of letter, numbers and hyphens, that are optionally separated by periods, followed by a letter string with between 2 and 4 characters (its actually a little more complicated, but that covers 99% of email addresses). In regular expression coding, that is represented by the following: ^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$

If you think it looks complicated, its because it is. After 7 years of programming, when I write regular expressions, I still use cheat sheets. Recently, I decided I was sick of using Qualtrics’ numeric validator and inserting the instruction “Only use numbers and optionally a decimal point. Do not use dollar signs or commas.” every time I asked for a response in currency format. Qualtrics allow you to create your own validations, and one option is to match the inputted text to a regular expression. With some writing, some tinkering, some forum searches, some more tinkering, and a lot testing, I settled on the following expression to validate US currency (it is not difficult to change this to other formats).

^[+-]?\$?[0-9]{1,3}(?:,?[0-9]{3})*(?:\.[0-9]{2})?$

That regular expression optionally allows the person to specify positive or negative values ([+-]?), optionally followed by a dollar sign (\$?). The next part is a little complicated. The point of it is to optionally allow a thousands separator (a comma in the US, typically). I defined that as 1 to 3 numbers ([0-9]{1,3}). Optionally followed by a comma, and if it is followed by a comma, there must be 3 numbers after the comma ( (?:,?[0-9]{3}) ). That previous pattern can repeat infinitely ( * ). Optionally, it allows decimals by requiring a period followed by exactly 2 numbers ( (?:\.[0-9]{2})? ). The ^ at the beginning says that whatever is typed has to start there. The $ at the end says whatever is typed has to end there. Both together just means that the entirety of what they type has to match what is between the two symbols. Otherwise someone could type “asdlk;fjasdf$300.00” and it would validate, because it can find a valid string within the whole of the text.

I explained it out in detail in case you are in a different country with different currency symbols and formats. To change the dollar sign, just change the first dollar sign to your currency symbol (other dollar signs have special meaning and have nothing to do with currency, so leave them). If you don’t use commas as thousands separators, but use something else, change the comma within ?:,? to your thousands separator. If you don’t use thousands separators at all, you could just leave it the way it is assuming no one would use them if they don’t know they exist, or you could remove the ?:,? entirely. If you use something besides a period as a decimal indicator (e.g. a comma) replace the \. in ?:\. with your decimal indicator (e.g. ?:,).

The regular expression above matches as many formats that I could think of.

30
$30
$30.00
3030
3,030
$3,030.00
etc.

It isn’t perfect, however. Someone could put in a value like $3000000,000, and it would match, even though with only 1 thousands separator it hard to know what the person meant. I couldn’t figure out a way to require proper thousands separators if they are used. However, this kind of problem would be so rare, that I can’t imagine ever seeing it.

To use it, click on your text entry question. In the Validation Type area of the menu on the right, select Custom Validation. The logic should read IF [your question] [theres only 1 option for the second drop down menu] [MATCHES REGEX] [^[+-]?\$?[0-9]{1,3}(?:,?[0-9]{3})*(?:\.[0-9]{2})?$]. Note the brackets are only there to separate out the different drop down and text entry menus. Do not leave the brackets in place when pasting in the regular expression.

The final piece of the puzzle is how the data is stored. You could do some javascript work to have Qualtrics save the currency as a number. However, Excel is good at noticing currency and changing it to a number, so I figured I’d do the conversion in the data cleaning phase rather than adding complicated javascript to every question.

Advertisements

Post Navigation