Linking zip codes to underlying data for display in survey

mthompmthomp Community Member - Trial User Qubie ✭

Linking zip codes to underlying data for display in survey

Hello!

I'm new to Qualtrics, and working on a survey that collects respondent information and then displays data based on this content back to the respondent. The survey company that I'm using will already have respondents' zip codes, but I'm having trouble figuring out how to link the zip code to my data spreadsheet so that later in the survey I can tell them the information that's linked to their zip code using piped text (I.e. the amount of money their zip code spends on pre school). I've tried using both a contact list and embedded data, but I've had trouble getting the data to display or figuring out where to store the spreadsheet that links respondents' zip codes to the spending data.

I've also tried following the link below https://www.qualtrics.com/community/discussion/1443/how-to-assign-an-embedded-variable-based-on-zip-code , but I'm using a huge spreadsheet with thousands of zip codes so I didn't want to hand code that into the embedded data field if I could avoid it.

Does anyone have advice on how to do this with a huge dataset?

Thanks so much in advance!!

Answers

  • TomGTomG Raleigh, NCCommunity Member Wizard ✭✭✭✭✭

    Upload the zip code data into a web based database table (e.g., MySQL). Write a web service script to query the zip code table and output the data in json format. Call the web service from the survey flow. It will return the zip code data in embedded data variables. Pipe the embedded data variables into your survey questions.

  • mthompmthomp Community Member - Trial User Qubie ✭

    Thank you!! Do you have suggestions for the best web based database table to use? I'm having trouble finding resources for how to set that up.

  • TomGTomG Raleigh, NCCommunity Member Wizard ✭✭✭✭✭

    If you are using a commercial zip code product, some of them include an API that you could call as a web service. If so, you could avoid setting up your own database and writing your own script.

    The answer to your question depends on your web server. If you have a web hosting account, most include MySQL.

  • mthompmthomp Community Member - Trial User Qubie ✭

    Thanks so much! I don't have a web hosting account already but I'm looking into trying to get one up and running with the zipcode csv spreadsheet. Is it possible to do this internally in Qualtrics instead, for example with a javascript object?

  • TomGTomG Raleigh, NCCommunity Member Wizard ✭✭✭✭✭

    Thanks so much! I don't have a web hosting account already but I'm looking into trying to get one up and running with the zipcode csv spreadsheet. Is it possible to do this internally in Qualtrics instead, for example with a javascript object?

    No good way. There are almost 42,000 zip codes. IMO, that is too many to put in a JS object or a CSV that you read with JS.

  • mthompmthomp Community Member - Trial User Qubie ✭

    Thank you!! That's really helpful!

    I think I have everything set up correctly, but I'm having trouble with the PHP code to get the rows of the table to qualtrics. I know it's connecting correctly to the web address because if I enter the password wrong, I get the error message that I entered into script, but if I enter my password correctly then I don't see anything on qualtrics. Do you have advice on how to fix this? Thanks again!


  • mthompmthomp Community Member - Trial User Qubie ✭

    (Sorry about the image of the code, for some reason I couldn't post my comment with the text of the code there!)

  • TomGTomG Raleigh, NCCommunity Member Wizard ✭✭✭✭✭

    There may be other issues, but you have an error in your query. Instead of:

    SELECT * FROM `TABLE 2'
    

    It should be:

    SELECT * FROM `TABLE 2`
    

    Run your script from a web browser to debug it first before trying to do it from Qualtrics.

  • rondevrondev Community Member - Trial User Wizard ✭✭✭✭✭

    Also, if still not works, then you may have to add below line as first line of script, to call API from different domain

    header('Access-Control-Allow-Origin: *'); 
    
    


Sign In to Comment