Blueprint to Implement a Survey Data Warehouse Solution on Google Cloud

Technology   |   Bertrand Cariou   |   Apr 2, 2021

Designing a new cloud data warehouse on Google Cloud can feel like choice overload. There’s a variety of analytic services to consider that will help build your data pipelines, not to mention the many reporting solutions available for selection. And even after those choices are resolved, how do you integrate all these services? Or design a proper data model with ingestion rules that will feed the right metrics into your reports? Getting started requires time-consuming research, tests, and validation.

Recently, however, Google Cloud announced a shortcut that allows customers to bypass all of this trial and error: an analytics design pattern. Design patterns are comprehensive guidelines that customers can follow to implement and test an end-to-end solution in an instant. And if it’s not the right fit? Customers can build on top of the solution so that it meets their needs, or abandon it altogether. There’s no commitments and little investment required. For organizations that have no time to waste, design patterns are a massive accelerator to get solutions live and on solid foundation.

Trifacta has teamed up with Google Cloud to release a reference pattern in a Survey Data Warehouse form. Trifacta is the company behind Google Cloud Dataprep, an undeniably critical component of modern cloud data warehouse, which allows users to connect to data sources and then structure, clean, and combine those sources for ingestion into the data warehouse database.

By watching this short walkthrough video you’ll get to understand how this design pattern works and how survey results can be turned into valuable insight.

Why build a Survey Data Warehouse Design Pattern?

The ease and effectiveness of online survey solutions such as Google Forms, SurveyMonkey or TypeForm means that many companies end up creating lots of surveys. Historically, these surveys have been used once to solve a problem, analyzed, and then more or less forgotten. But treating surveys as single-use data points undermines their potential; modern organizations are beginning to seek out ways to reuse and draw connections across multiple surveys. 

The Survey Data Warehouse design pattern on Google Cloud guides you through the process of moving all individual survey results into a data repository for more robust survey analysis.  Investing in a central place to house survey results allows companies to spot larger trends across past surveys, compare results, and find deeper insights that one survey alone couldn’t reveal. Below, we’ll take a closer look at the pattern and how you can get started with it today. 

What’s in the Google Cloud Survey Data Warehouse pattern?

The Google Cloud Survey Data Warehouse pattern, which is part of the broader Smart Analytics Reference Patterns library, is a series of steps that guides your survey results into a survey data warehouse through the use of Dataprep by Trifacta, BigQuery, and Data Studio or Looker. In this post, we use Google Forms as the survey example, but the pattern would work similarly with your own survey solution. Steps are easy to follow and designed to anticipate the common challenges of working with survey solutions.

One such challenge is that Google Forms survey results aren’t immediately usable for analysis; they must first be standardized into a format for easy reporting, commonly known as SQL tables. Based on the survey question category, the Google Forms design pattern will show you how to use Google Cloud Dataprep by Trifacta to structure the results of that question and load the results into a standardized table. Users have the option of using predefined data preparation rules in Dataprep to tackle any Google Forms question or adapting and extending these rules to fit their own unique requirements.

And to give you a headstart in analyzing your results, Data Studio and Looker reports have already been designed to present the results in a format suitable for analytics exploration and insight discovery. Simply select the one you have more affinity with. 

Get started

Ready to take your surveying to the next level and build a survey data warehouse? Get started today by visiting the Codelab to try it out or you may also want to read the Google Cloud blog that extends the concept toward Experience Management (XM).

Start Trial