Hello and welcome!
Today we will learn to create this interactive customer feedback dashboard in Power BI using
Microsoft Forms or Google Forms survey. What's new in this tutorial? You will learn,
How to set up a survey in MS Forms or Google Forms, and collect customer feedback just
by sharing a feedback link or QR code. How to design a highly visual dashboard, just
by using feedback data, to get more insights, without any manual data entry. So once you set up this complete process,
just with refresh the data your dashboard will be updated. The complete tutorial is divided into the
following sections. Make sure to watch the complete tutorial.
But you can jump to any section by clicking on the timestamp provided in the description. Also, in the previous videos, lots of viewers
suggested keeping the slower pace for the tutorials, which I tried to maintain here.
But still, if you find this faster, you can adjust the playback speed like this. Please keep suggesting more and more improvements
in the comment section and we will try to improve upon them. So let's get started with the first section.
We are going to use the hotel guest feedback for the demonstration, and these are the 9
questions that we are going to set up in the survey. Let's see them one by one.
First, the name of the guest. Second, gender.
Third, date of birth. Fourth, Hotel checkout date.
Fifth, the purpose of the booking. like, a business visit, vacation, function, or for
some other reason. Sixth is the source of information. like:
learn from someone or book by an organization or found us on a hotel booking website Etc.
Next is about General feedback. like, how was the staff Behavior? how was the check-in
process? how was the room service? Etc. Then the second last, the overall rating on
a scale of one to five. And the final one is for the Net Promoter
Score, which we will discuss in detail later during dashboard design. You can change the questionnaire as per your
requirement or the type of customers. Now let's go to the Office 365 window online
to set up this survey in Ms forms. This is the Office 365 interface.
To open the Forms, click on these nine dots. If you can't find it here, just click on all
apps, and then select forms. Also, you can access the ready-made template
by clicking here on More Templates. Let's go back and we will create a blank one
since we already have our questionnaire. Arrange both windows side by side, and start
creating a survey. Let's add the first question. Select text
for the guest's name. For gender, select choice.
Once you type the gender in question, it will automatically suggest the options.
Just click add all. You can delete the option which you don't
want in this list. Also, you can make it a mandatory question
by clicking on required. Let's make the first question also compulsory. Now, for the date of birth select the date Next, select the date again for the checkout
date. For the Purpose of the Visit, select a choice
and add the options. You can select the "Other" option too,
where the feedback provider can make the manual entry if the option is not listed here. Next source of information. Instead of entering options one by one, you
can copy the list and paste it here. Let's quickly delete the checkboxes in front
of the options. Delete option 2 and add the "Other" option. Now for the feedback select the link from
the drop-down. Here horizontally you can write options for
rating and vertically statement to be rated. Let's write down the options first. Let's
copy the statements. Alright, now let's add the "Overall Experience"
by choosing the rating option. We can use it as a number rating also but
let's keep it as a star rating. For level, we will keep five only. And for the last question, let's add Net Promoter
Score option Let's understand, what is a net promoter score?
and how is it calculated? A Net Promoter Score is a single question used to measure customer satisfaction on a
scale of 0 to 10. The respondents are divided into three categories. 1. Promoters, Those who responded with the
9 or 10 rating 2. Passives, Those who responded with the
7 or 8 ratings. And the rest are Detractors. And it is calculated as a percentage of promoters
minus a percentage of detractors. You can learn more about net promoter score
on the internet or watch on YouTube. Alright, let's go back. So, the survey is ready now.
Once we start collecting feedback from users or customers, we will be able to see the responses here
in the responses tab. First, let's see the various options to collect
the feedback click on Send. It will show the available options to Share, like you can copy the link by clicking here. Also, you can share via mail or create a QR code, download and use it. You can produce HTML code that can be directly
embedded into the website page. or you can share it on social media by selecting
social media icons So let's try the first option. copy the link and open it in the browser The survey form will look like this in the
browser. Let's fill it up. The moment we click on submit, it will be capturing the response in real-time. Which we can see in this tab. As we can see here, the first response is being added here. Now, let's try one more option, which is a
QR code. Before that, let's open the QR code scanner
on the Mobile and point over the screen, so that we can
observe the real-time feedback collection process Click on the QR code icon. Here you can download it as an image and use it wherever you want to collect feedback. Let's scan it on the Mobile screen. click on the link, and it will redirect to
the feedback page. Let's complete it. The moment we click on submit. We can see the response added here in real
time. Alright, now let's quickly see the process
to set up the survey in Google forms. open new browser and type in the URL bar forms.google.com it will open this page which is nearly the
same as MS forms. Here also, we can access the readily available
templates from this template gallery. let's go back and click on the blank to start
the new one. Let's add a few questions for demonstration You can complete it similar to MS forms and
view responses in this tab. Now, let's go back to MS forms for the next step. We can
download it in Excel by clicking here. As you can see it is downloaded. Let's open it. So, here is the result. The output is in the table format and we can see the two responses in the Rows The first five columns are by default columns. First is ID, which represents a sequence of
feedback Then the date and time of the start of the
survey. Next, the survey completion date and time. In the email column, it is mentioned anonymous and the name column is blank. But if the survey Creator and feedback provider
belongs to the same office organization account then the email and name will be Auto filled But in this case, the feedback providers will
be hotel guest. so it will not be auto-generated That's why we added the full name to the questionnaire
itself. The rest of the fields are self-explanatory. So now using this format as an the input we
will Design our dashboard this is the sample feedback data created for the dashboard with
the same headers It's randomly generated data that's why I kept these columns empty anyway we are not going to refer to these
columns in the dashboard addition to this, we are going to use these two reference table
the first one is to further categorize the feedback and the second one to convert rating
to numbers for further calculation Now, let's open a blank Power BI report We can import
the Excel data by clicking here or here or from getting data drop-down or just click here Let's select all three tables and load Here
we can see the three imported data tables Let's do some data transformation in the power
query To access it, we can click on these three
dots and select Edit Query or just select transform data here So, these are the same three tables we imported
Now first, we will separate the feedback table one for general questions. like, Purpose
of the Visit, source of information overall rating, NPS Etc. And one for the feedback. Like, Staff, Room,
Restaurant facility, Etc... So, right-click on the feedback table and
click on the duplicate. let's rename it a General. Since the ID column has a Unique list of numbers, we will use it as the primary key to link
both tables in the model. Now, let's remove the columns, which are
no longer required in this table we will keep the ID column. So let's keep
it selected. and select the remaining columns by pressing the Ctrl key on the keyboard. Right-click and select Remove Other Columns. Let's rename a few columns Now, let's go to the feedback table and select here again the required columns
and remove others. right click on the ID column and select unpivot
others it will transform the data into three columns only first ID, second feedback, and the third rating let's use the merge queries
to look up the feedback categories and rating scores let's rename the columns accordingly Alright, the data transformation is completed
Click on close and apply to go back to the Power BI report let's check the table relationship in the
model so the relationships are already Auto detected between all the tables. Let's organize it first. since we already have merged feedback category
and rating range table in power query we no longer required this relationship let's delete
it here we can see the general and feedback table
is linked with the ID column and the relationship is one to many Alright, let's go back to report again and we will set up the Dashboard Background go to Canvas background and browse the image These are some pre-designed dashboard background
images suitable to this dashboard theme let's select the first one
change the transparency to 50 percent to give it a better look let's change the wallpaper
color too for the time being let's select this color from the theme color only and later we are going to change the complete
theme colors so the wallpaper color also will change automatically let's add one shape for the title background
and organize it let's copy it for the background of overall
score visual Alright, during survey setup in MS forms, we learned about what is Net Promoter Score?
and how it is calculated? Now we will add some columns and measures,
to calculate it. first, we will add a column in the general
table to categorize Promoters, Detractors, and Passives using
IF conditions if the rating is greater than 8 means 9 or
10. then Promoters else another if condition that is the rating is less than seven means
0 to 6, then Detractors, else Passives Now, let's create separate measures for Promoters
percentage, and Detractor's percentage. Let's copy the formula for Detractors percentage
add a new measure. Paste it and modify it accordingly Last, add the measure for the NPS score which is simply subtracting detractors' percentage
from promoters' percentage. Alright, let's go back to report and complete adding visuals
to the dashboard Let's add the slicer for the purpose of visit and then format it copy and paste it for the gender Now, let's add the remaining visuals OK, so now we will format the visual's color. but before that, let's first set up the dashboard
theme colors so that we save time on formatting it. And can be easily selected from the color
palettes to set up the theme color go to view. Under the theme drop down and click on customize
the color theme here we are going to use this color scheme
let's start copying this color code and paste in the power bi theme colors as we click on apply the visuals and background
color will change automatically accordingly let's complete rest of the formatting so whatever the changes we have made for this
visual we can quickly apply the same for other visuals using format painter let's organize the visuals properly now let's add overall rating visuals let's add a bar chart for overall rating adjust
it properly and then we will add an image over it to represent it is as the star rating to create an image let's go to PowerPoint
and we will create it using shapes you can watch the detailed tutorial here on
using merge shapes in Powerpoint you can save it as an image by clicking here and make sure
to save it as a PNG format since I have already created it before let's go to Power bi and
import it place this image over the bar chart adjust
chart an image properly so that we can visualize this star rating all right now let's import the icons for the
titles these all icons are from PowerPoint icon library and saved as an image in same
way we did is for star rating now let's complete the dashboard header section
like title subtitle date slicer logo Etc thank you all right the dashboard is almost
ready let's give it a final touch up and complete if anything pending if you like the video, give it a thumbs up
and subscribe to the channel if you have not yet subscribed yet.
Thank you for watching
