API for Overquotas

This page is a result of this JIRA card during which we created the below video.

The main motivation for this service is that Qualtrics charges us for data collection when respondents are marked as “overquota”. An overequota respondent means that a respondent is screened out (not eligible) of the survey. We don’t want to get charged excessively for these responses, but we do want to store their responses to the screener questions. The solution we developed involve redirecting a respondent to an API through a WebHook, and storing the responses in a database.

We have a small PostgreSQL database stored on AWS RDS called overquotas. You can read more about AWS RDS at the Survey Database page. Every time a machine with (check out the list of our machines) an Over Quota API) is called properly, one respondent is stored in the database.

This currently stores all respondents from a survey with their PID and you can detect which were overquotas because their PIDs are not stored in the Qualtrics survey.

The database was created in tools/plumber/over-quotas and that’s where also the API code lives.

Below you can see the code used to

• Making a connection to the database

  library(RPostgres)
  library(DBI)
  library(g6tr.db)
  library(dplyr)
  library(dbplyr)
  db_creds <- g6tr.db:::g6tr_db_creds()
  con <- 
    dbConnect(
      RPostgres::Postgres(), 
      dbname = "overquotas", 
      host = "overquotas.cg2k9ofki22t.us-east-1.rds.amazonaws.com", 
      port = db_creds$port, 
      user = db_creds$user, 
      password = db_creds$pass
    )

• Creating a table

  # dbSendQuery(con,
  #             "CREATE TABLE overquotas(
  #               respondent_id TEXT PRIMARY KEY NOT NULL,
  #               survey_id TEXT,
  #               race TEXT,
  #               income TEXT,
  #               gender TEXT,
  #               state TEXT,
  #               age TEXT
  #             );")

• Creating an example insert to the table

  # example_data <-
  #   data.frame(
  #     respondent_id = 'RV_Marcin',
  #     survey_id = 'SV_sfnsjdfn9',
  #     race = 'White',
  #     income = '$25,000-50,000',
  #     gender = 'Male',
  #     state = 'NY',
  #     age = '18',
  #     stringsAsFactors = FALSE
  #   )
  # 
  # dbWriteTable(con, name = "overquotas", value = example_data, append = TRUE)

• Extracting data from the table

  tbl(con, 'overquotas') %>% collect()
  tbl(con, 'overquotas') %>% collect() %>% tail()

You need to create a few embedded data fields in your survey and call the below Web Hook to be able to store respondents in the database. This JIRA card aimed to incorporate that in the baseline survey