Our client had just completed a large Worldwide survey which had cost many millions to do. The agency which had coordinated the survey were using SPSS to calculate the results and they presented many hundreds of pages of cross tabulations to the client. Consequently, our task was to see if could take raw data and then analyze the survey on the fly.
We automated the tasks of cleaning and weighting the data and then computing the metrics. Filters could change the calculations. This allowed us to automate survey reporting.
There were so many ways to cut the data and our client wished to perform data mining. For example, each survey respondent lived in a different country, worked in different sizes of company, worked in different industries, had different qualifications, sex, age etcetera. The survey agency had prepared summaries by each country and by industry, but it was not practical to delve deeper. It was hard to visualize what was going on. We needed a better way to analyze the survey.
The Challenges in Analyzing Survey Data
We started by designing a database schema which could hold all the results of the survey. We then build some tools to pivot the data into an easier format to use. Each respondent has many different attributes. For example, the country where they work, their educational background, the industry they work in and size of company. The answer to specific questions gives us the attributes’ values. We scan the responses to the survey to allocate the attributes against each respondent. This makes it much easier to calculate results for a set of attribute filters on the fly.
The shaped data was then loaded into an OLAP cube and metrics were defined.
Finally, we produced a custom Power BI dashboard to show the results of the survey along with color coding to warn about small sample sizes and statistically significant movements.
By creating activity logs the central team were able to perform an analysis of query activity. This told us the degree to which the system was being used but also told us what the business was interested in knowing. This great feedback was used to plan for the next surveys so that we could control costs in reducing unwanted data and focus on the facts in which the business had the most interest.
Our Approach to Analyzing Survey Data
Technologies Used to Analyze Survey Data