[MUSIC PLAYING]
LEIGHA JARETT: In this video, I'll
be showing you how to create an experience management data
warehouse by transforming and centralizing
your survey responses.
My name is Leigha Jarett.
And I'm a developer advocate from Google Cloud.
Surveys are a powerful tool for all different businesses.
Survey platforms allow organizations to easily collect
large amounts of data around customer satisfaction, employee
happiness, brand awareness, and more.
However, surveys are often used once
to solve a specific problem and then
the data is forgotten about.
Additionally, the shape of the data
can be pretty difficult to work with using
standard tools like SQL or business intelligence
platforms.
Here, I'll show you how to use Cloud Dataprep by Trifacta
to transform and load survey responses from Google Forms
into BigQuery, where you can join them onto additional data
sets and perform deeper analysis using tools like Looker or Data
Studio.
Let's imagine that I'm performing market research
for my data analytics company.
I sent out a Google Forms survey that
asked respondents questions about
their analytics knowledge.
Google Forms will automatically push the responses
into a Google Sheet.
However, if I pushed the sheet directly into my warehouse,
the format would make it difficult to query.
Let's take a closer look at this checkbox grid question.
In the Google Sheet, one row is recorded
for each response, where each grid column is its own column.
And the selections are all in one cell, separated
by a semicolon.
Now, what we really want is for one row
to represent each selection so that we can easily do things
like count the number of people who selected each platform
project combination.
In Cloud Dataprep, I've created a flow
that transforms and loads my responses into BigQuery.
I've connected the Google Sheet that
contains my survey responses and then created a recipe
to clean the column headers.
Next, the data is pushed into four different recipes, one
for each question type, which transforms the responses based
on the format.
Let's take a deeper look at the multiple-choice checkbox grid
question.
In my recipe, I can preview the data
at each step in my transformation process.
I've already deleted the columns that I don't need.
And now I want to extract each element from my selection list.
First, I'm going to extract the selections
into their own individual columns.
Next, I can combine these columns
into a single array for each response.
Now that I have all of my selections
in an array, what I need to do is restructure this data
so that each selection becomes its own row in the database.
And there we have it.
My data is now un-nested.
I can repeat similar transformations
for my other formats until I have all of my data loaded
into BigQuery.
In BigQuery, I can see that Trifacta
has pushed those responses into the four tables representing
my different types of questions.
I can preview the data and see that it's now in the format
that I would expect it to make it easier for doing analytics
on.
And since all this data is centralized in my warehouse,
I can easily join it back onto other data sources,
like the accounts table from my CRM.
In Looker, I've created my own dashboard
that surfaces insights about this survey to my team.
Here, I can drill in and see which respondents correspond
to accounts already in our CRM and directly
reach out to the account owners to let them know.
I'm also going to jump down and drill
into my group of users who are data analysts
that leverage Python.
I'm going to hop into Looker's explore environment, where
I can easily now send this list of users to Marketo
and target them for an upcoming launch that we have.
And now you know how to create an experience management data
warehouse, where your organization can take
direct action on the insights that they
get from survey responses.
Thanks for watching.