1. Home
  2. Application Programming Interface (EN)
  3. Filter interview data using the API

Filter interview data using the API

Collecting data via large surveys can become onerous. Therefore, Survalyzer has several capabilities within the API to easily filter interview data. These include:

  • Row filtering
  • Column filtering
  • Use of metadata
  • Content compression

API Endpoints

https://api.survalyzer-swiss.app/ (API help for Swiss Customers)

https://api.survalyzer-eu.app/ (API help for EU Customers)

Survalyzer NG endpoints:

  • /publicapi/Interview/v2/ReadInterviewList
  • /publicapi/Interview/v2/ReadInterviewListCompact

For legacy purpose we also support the Survalyzer Classic endpoints:

  • /publicapi/Survey/v1/ReadSurveyData
  • /publicapi/Survey/v1/ReadSurveyDataCompact

All following examples are for the NG endpoints. For new integration projects only these should be taken since they have much more capabilities.

Row Filtering

With Survalyzer NG we introduces a comprehensive interview data filter which gives access to all variables regardless of the type. The following code block shows the most common use case of reading all completed interviews since a certain date.

"conditions": [{
	"conjunction": "And",
	"identifier": "State",
	"conditionOperator": "IsEqualTo",
	"value": "Completed",
	"conditionType": "SQL"
},{
	"conjunction": "And",
	"identifier": "EndDate",
	"conditionOperator": "IsGreaterThanOrEqualTo",
	"value": "2021-01-01",
	"conditionType": "SQL",
}]

Another major use case is the delta loading since the last call.

"conditions": [{
	"conjunction": "And",
	"identifier": "State",
	"conditionOperator": "IsEqualTo",
	"value": "Completed",
	"conditionType": "SQL"
},{
	"conjunction": "And",
	"identifier": "UpdatedAt",
	"conditionOperator": "IsGreaterThanOrEqualTo",
	"value": "2021-07-22 16:00",
	"conditionType": "SQL",
}]

If the timestamp contains a time, it needs to be in UTC format to avoid time shifts.

The conditionType is SQL for System fields like State, CreatedAt or UpdatedAt. Depending on the filter the following values are valid:

  • Question
  • Panel
  • UrlVariable
  • CustomVariable
  • SQL

Paging

Large scale surveys can contain hundreds of thousands interviews. But the API requests are limited to 230 seconds duration. After that time the server will terminate the request to protect the server health.

This is when paging comes into play. To avoid exceeding the timely constraint the large request needs to be slized into several smaller requests. How many records could be queried at a time depends on the amount of variables. In the following example we take 10k records per page.

"paging": {
    "pageSize": 10000,
    "page": 1,
    "orderField": "Id",
    "orderDirection": "Ascending"
}

The first response contains the field totalRows to determine how many pages should be iterated: totalRows / pageSize = pages

Column Filtering

The property FieldsToDownload contains a list with column names. As the columns are dynamic for each survey, this shows filtering for questions with the codes q1, q2 and q3:

"fieldsToDownload" : ["q1","q2","q3"]

This could help to significantly reduce workload if chunky fields like “user agent” or “showanswers” are not required for further processing.

Turn off metadata

The Code Plan is a powerful instrument to understand the structure of data. However, for BI or analytics tools this is just a surplus overhead. Therefore, an application was introduced to turn off metadata. Here is an example of how it works:

"loadCodePlan": false

For large surveys with many questions, especially matrix questions, or those with many variables, this could again significantly reduce the data download.

Content compression

When the API returns only that data which is essential, this could still potentially be a large data set – particularly if several thousand responses have been collected. To address this, we introduced content compression to our APIs. To enable content compression, add the following header to the request:

Accept-Encoding: gzip

This triggers a mechanism which compresses the result using a zip algorithm. Statistically, the content can be reduced by 85% for json.

Two different methods for different needs

The ReadSurveyDataCompact method is the equivalent to the deprecated method GetRawDataAsJson. It allows to receive data in a dynamic javascript object form which doesn’t follow any fixed interface. This format proofed very handy in conjunction with BI tools and dashboards. Therefore, we reintroduced this method. The query interface is the same as for ReadSurveyData.

With both methods in place customers have the choice to use the right method for the given use case. Here are a few examples for the use of the different methods.

ReadSurveyData:

  • In Web Application Firewall (WAF) scenarios
  • If data is processed from a contract based system (Exporter)

ReadSurveyDataCompact:

  • In BI Tools like PowerBI or QlikView
  • In JavaScript Portals or Dashboards

Improvement of the v2 endpoints

In the v1 endpoints the variable names were used inconsistently. For Filter and FieldsToDownload the same variable name as in the excel sheet (with spaces) are used. The CodePlan and the DataRows itself didn’t contain spaces in the variable names. For backward compatibility reasons we can’t change that to avoid breaking client implementations.

In the v2 endpoints the variable names were consistently used in all places without spaces. All variable names are camel cased.

Client Programming interfaces

Survalyzer offers customers a comfortable way to use the public API without caring about interfaces. Since the methods are following the OpenAPI Standard the client interfaces could be generated in all popular programming languages using https://editor.swagger.io/.

Additional to this possibility Survalyzer maintains the client interface for .NET based languages which could be downloaded here.

Updated on juli 23, 2021

Was this article helpful?

Related Articles