- [Instructor] Customer Voice includes built-in integration
with Dynamics 365 and Power Platform
to enable you to create Power BI dashboard,
based on the survey responses
and customer records in Dynamics 365.
For this demo, I am going to use bank satisfaction project,
based on a real life survey from one of our bank customers.
This project has two surveys,
an account opening survey for new customers
which has several satisfaction metrics
mapped to the question.
The second survey is a home loan survey
which is sent to customers after completion of home loan.
The questions from the home loan survey are mapped
to the same satisfaction metrics.
So the bank can aggregate the customer feedback
across the interaction points.
Let's give it a try
by sending the home loan survey to customers in Dynamics.
In Dynamics 365, I created contact list
called High Value Customers.
I'm going to send the survey to this list of customers.
One of the customer is Cameron.
As soon as the survey invite is sent to the list,
each contact's activity timeline is updated
so that any sales person who works with Cameron
knows that a survey is sent.
I'm going to switch to Cameron inbox
and fill out the survey.
When Cameron submitted the survey,
the survey appears on the activity timeline,
and any sales person who has access to Cameron's record
in Dynamics will be able to see the survey results.
Notice that Cameron contact record
is associated with Boston branch and the Boston branch
is part of Northeast area.
For the remaining of the video I'm going to walk through
how to create a custom Power BI dashboard
that aggregates the survey results
based on the branch and branch region.
Selecting region or a branch will automatically filter
the aggregated satisfaction metrics and survey results.
For this demo, I'm going to use a dashboard template
that you can download from Github
at CustomerVoice/PowerBI.
When you've open the file you can find
a formatted empty dashboard.
You can start by getting the data
from common data services or CDS.
Customer Voice stores survey responses
to your organization's CDS.
You can find the CDS location of the project
from the all project view.
Append the CDS name
with .crm.dynamics.com
for the full CDS URL location.
Customer Voice stores survey results
in entities prefixed with msfp.
Select survey response, question response,
satisfaction metrics, survey, project and question.
If you are not using Dynamics 365,
then those are all entities you need
to create Power BI dashboard.
In our example, since the survey is connected to Dynamics,
you can pull additional Dynamics 365 contact information.
The contact is connected to the survey
through an entity called ActivityParty.
I will explain a little bit later
on how these entities are related.
For now, let's also get branch information.
As you recall, the contact record in Dynamics
has branch information and the branch area.
Let's bring those entities as well.
After the data are downloaded, you need to do
a bit of data transformation.
Open msfp-questionresponse menu option
and select edit query.
We're going to join this entity with another entities
and let's create a new table for the join.
Join the questionresponse with msfp_question.
The questionresponse store responses
for each survey question as separate rows
but do not actually store the question text.
The question text is stored in msfp_questions,
which is linked through questionid column.
Give the new table a name
as Customer Voice Question Response.
Expand the table
to bring question text and survey ID
associated with the response.
Let's do another merge to get the survey name.
Join with survey table using surveyid column.
Expand the table to include msfp_project.
Let's do another join
with msfp_project table, to get the project name.
Filter the data using the project name
to include survey results
only from bank satisfaction project.
Let's select the column that we need,
which include survey response id, question text,
response and survey name.
As I mentioned earlier,
response from each question is stored as a separate row.
To make it easier for reporting,
you can transform the rows into columns using Pivot column
functionality in power BI.
Select the column with question text
then click pivot under the transform tab.
Select the response column as the value
and choose do not aggregate under the advanced option.
Each survey question now appear as a separate column.
Since many of the survey response are rating number,
let's change the type into whole number
so Power BI can automatically aggregate them.
Let's do another transformation on survey response,
which store satisfaction metrics data.
Join the survey response
with msfp_survey and create a new table.
Name the new table Customer Voice Survey Response.
Expand the table and include survey name and project.
Join the project table to get the project name
so we can filter the results to include
only from Bank satisfaction project.
Let's choose columns to include activity ID`,
satisfaction metrics value and submit date.
The satisfaction metrics
store the calculated result of the NPS in a JSON format.
As there can be multiple metrics defined for each survey,
you can use parsed JSON value
to extract the results and select expand to new row option.
Select ID and value by deselecting the type.
Similar to what we previously did
with the question response table, you can use
pivot column functionality to transform
the satisfaction metrics value
from different rows into separate column.
The column appeared as IDs and to identify
which satisfaction metrics the IDs are mapped to,
you can use the satisfaction metrics table.
Let's rename the ID so you can identify them later on.
One last transformation is on activity party.
Activity Party table stores association between any type
of activities related to a contact,
including survey activities.
For the survey response,
the activity party stores also other type of information
which we do not need.
To avoid duplication we need to filter the activity party
to only include type =1,
which indicate sender relationship.
Let's close and apply the data transformation
to the Power BI file.
Before we can start creating dashboard visualization,
we need to create the relationship among the tables.
You can find the relationship from
aka.ms/CustomerVoiceDoc
under entity reference section.
Let's move the tables we need close together.
Remove relationship with other tables
and create relationship
between branch area to branch,
contact to activity party
and activity party to Customer Voice
survey response activity ID.
Finally, let's connect Customer Voice Question Response
survey response ID to the activity ID.
Now that we have the data model completed,
we can create measures for the satisfaction metrics.
The NPS Scores is calculated based on percent of promoter,
minus percent of detractor.
I shared a sample DAX function on the same GitHub project.
Make sure the name of the table and the column
is the same as in the measures.
Repeat the steps for each of the NPS metrics.
Drop the NPS scores measures
to the card control the top of the dashboard.
Select the Region area and drop the region name,
which you can rename to make it more user friendly.
Then add the NPS score measures on the table.
Do the same thing for branch.
You can surface selected answer from the survey
to identify potential key driver for the NPS score.
If the NPS low or high, you can find visual relationship
with other questions in the survey to help explaining
why the score is low or high.
Finally, you can show the actual answer from the survey
in the table at the bottom of the page,
including text comments.
Since the tables are all related, you can filter
the survey results by selecting branch area and branch.
By creating Power BI dashboard
on your Customer Voice survey responses,
you can get up-to-date insights on your customer feedback.
For example, let's say one of your customers
from New York branch submitted feedback.
By refreshing the Power BI dashboard, you will get
instant results of your customer feedback and can react
more quickly to customer issues.
Hope you find this video useful.
Please contact us through our community forum
at aka.ms/CustomerVoiceForum
for questions or feedback, thank you.