Creating an experience management (XM) data warehouse with survey responses

Organizations are realizing that experience management and analysis are important aspects of understanding needs and providing the best level of service to customers, employees, and vendors. Surveys are a powerful vehicle within the experience management space for data collection within organizations of all shapes and sizes. According to Verified Market Research, Experience Management, which includes tools like surveys, is a USD $17.5B market that is expected to grow 16.8% annually (CAGR) from 2020 to 2027 (Source). 

Tools like Google Forms, Survey Monkey, Qualtrics, and TypeForm allow companies to get answers fast from groups and organizations with whom they interact. The growth in technology options and the ease and effectiveness of survey platforms means that many companies create a lot of surveys. Oftentimes, these surveys are used once to solve a specific problem, the results are analyzed and shared, and then the survey and resultant data are forgotten. A natural opportunity exists for companies to instead capture and store those results in a place where they can be used for survey-over-survey analysis and comparison against other first and third party data to better understand cause and potential options for improvement.  

So, what barriers exist to creating this experience management data warehouse? Surveys by nature are flexible vehicles, and many survey systems provide data in a question-answer, column-row format, or as delimited results. This data shape, while good for human consumption, is not great for wide-scale analytics, and the process for getting it to a better format can be daunting. Over the course of this blog, we’ll demonstrate how Google Cloud and Trifacta have partnered to create a design pattern to easily shape, display, and use this data.

Focusing on scale and flexibility

Survey datasets often require pivoting or parsing of data so that questions can be compared, analyzed, and augmented in a more structured format. This survey analytics pattern walks through the process for transforming the data, putting it into a data warehouse, and using that warehouse to analyze and share findings. This pattern can be extended to a variety of survey types and different iterations of the surveys, providing you with a platform that can be used to do deeper and more consistent analysis.

To illustrate this pattern, we’ll leverage Google Forms as our capture mechanism (Note: although we’re using Google Forms for this example, the concepts are transferable to other survey engines that have similar export schemas). Google Forms allows users to structure questions in a variety of ways, from multiple checkboxes to a ranked list of items to a single, freeform answer, each producing slightly a different output. Based on the question category, the survey analytics pattern provides a specific approach to structure the data and load it in BigQuery tables.

For example, with multiple choice questions, the results may appear as a list of values with semicolon separator (e.g. “Resp 1; Resp 4; Resp 6”). Using Google Cloud Dataprep by Trifacta, a data preparation service found on the Google Platform, we can take those values and parse the extract into a cleaner format for analysis where each response is a new row.

Starting Question

Original Export

Transformed Export and Results in the Data Warehouse

On the Google Cloud Platform, your experience management data warehouse will leverage three main technologies: Cloud Dataprep by Trifacta, BigQuery, and Data Studio or Looker. As mentioned above, Cloud Dataprep is the key technology involved in normalizing, cleaning and enriching data for analytic use. BigQuery, Google Cloud’s enterprise data warehouse, will store your survey data alongside other data sources to allow stakeholders to dig deeper and uncover hidden trends. With Data Studio or Looker, users can explore and visualize the data, taking immediate action on the insights they uncover. You can also take your analysis further by combining this data preparation with AI/ML to more easily identify and respond to anomalies or trends in your data (to learn more, please take a look at Google Cloud’s AI Platform and some of our AI/ML reference patterns for analytics). 

The benefits of an Experience Management Data Warehouse 

Building an experience management data warehouse is one giant step toward a culture of more robust analytics. At a high level, an experience management data warehouse allows companies to:  

• Compare multiple surveys or responses (without manual combination) 

• Find new insights by isolating responses from a variety of surveys—for example, identifying a team’s satisfaction correlation to the size of teams or regions 

• Enrich survey results with other data in your warehouse—for example, combining survey results on product usage with sales data to surface new marketing segments with high conversion probability

• Represent and visualize the survey results in new ways using Looker, Data Studio, or another BI tool 

• Share out survey results to a broader audience in a compelling and personalized visualization format

• Build a scalable survey practice, that allows for iterating and improving over time

• Apply more advanced analytics and decision making to survey data using Artificial Intelligence (AI) and Machine Learning (ML)

Use cases for the survey analytics pattern 

So, how does this play out in the real world? Here’s a few ways companies can leverage an  experience management data warehouse. 

1. Employee Experience: An HR department might normally send out quarterly surveys to get a pulse check on the general well-being of employees. Yet with the onset of the pandemic, HR decides to send out surveys with more frequency. With the results of these employee satisfaction results in one place, this HR department can now compare satisfaction levels to pre-pandemic times, as well as isolate the surveys taken during the pandemic to see if satisfaction levels have improved over time. 

2. Product Experience: A product team sends out surveys to track the use of specific products or features. With all of these surveys stored in a data warehouse, the product team may decide to enrich this data with sales data to understand if product usage correlates with sales success. Since sales data is already stored in BigQuery, it’s easy to access and combine this data. 

3. Customer Experience: A market research team would like to better understand customer journeys. These surveys can help analysts understand user sentiment, and they can also be a lead generation source. By joining the results of the survey with other insightful datasets like CRM tools, marketers can use analytics to create targeted campaigns for users with a high propensity to buy. 

Get started

Ready to take your surveying to the next level and build an experience management data warehouse? Get started today by visiting our Codelab, digging into the technical guide, or checking out our video walkthrough. You can access all of our patterns on our Smart Analytics Reference Patterns page.