Intermediate | Flow of The Week: Using Flow to notify you based on customer ratings from a Forms survey.
This post is written by Kelli Waiss, Program Manager on the Microsoft Flow Team. You can follow her on Twitter at @KelliWaiss
Hi Flow Community!
Have you ever received a survey asking you to rate your experience regarding a particular product or service? This week’s Flow will look at the results of a survey and if the average rating for any of the questions is below 5 (on a scale of 1-10), an email will be sent to inform the necessary person(s). This Flow can allow a person, team, or organization to track the results of the surveys they send out but not necessarily have to focus a lot of time or energy analyzing them unless it requires their attention (based on the specifications they configured in their Flow). If less than ideal results occur (in this case an average rating of 5 or less), the Flow will kick off an email informing the user that they may want to review the outcomes of the questionnaires and dig deeper into why their customers may be dissatisfied with a particular area(s).
Let’s get started!
This Flow uses Microsoft Forms, Excel Online (Business), and Office 365 Outlook.
Note: In my Flow I’m using a survey I previously created in Microsoft Forms which includes the following questions:
- On a scale of 1-10 (1 being poor and 10 being excellent), how would you rate your overall experience with our product?
- On a scale of 1-10 (1 being poor and 10 being excellent), how would you rate the service you’ve received from us?
- On a scale of 1-10 (1 being poor and 10 being excellent), how would you rate the cleanliness of our stores?
Okay, first things first, let’s jump into the Flow portal, click My Flows, then New, then click Create from Blank to start a new Flow.
We’ll first need to set up the trigger for our Flow. We want this flow to run every time a new survey is completed so let’s type Microsoft Forms and select When a new response is submitted. In the Form Id field, I’m selecting the form I created in MS Forms.
Next, I’m going to add the action Get response details from MS Forms. Select the Form Id. In the Response Id field, you’ll select List of response notifications Response Id from the dynamic content. (You may need to click See more next to When a response is submitted in order to find this.)
You’ll notice Flow is smart enough to recognize this is a list and you’ll probably want to act on each response so it adds the Apply to each wrapper around this action for me automatically which is great!
I want all the survey responses to be added to an Excel table so after I get the responses, I want to add the action Add a row into a table using Excel Online (Business). I’ll select the location of my file which is stored in OneDrive and select my table.
Note: I previously created a table in Excel to store my survey results. I have a column to store the Responder’s email and 3 columns to store the ratings for each of the 3 questions listed above. I also created 3 other columns (one for each question) where I am configuring the average rating for each of the questions using Excel’s Average function.
| Responder’s Email | Product rating | Avg. product rating | Service rating | Avg. service rating | Store rating | Avg. store rating |
In the Add row into a table card, I can see fields for each of the columns in my table. I’m going to use dynamic content from MS Forms Get response details to fill in all the fields appropriately. However, I’m going to leave the three Avg. rating fields blank. It’s easiest to name your table columns similar to what the survey questions are so you can easily map which dynamic content goes in which field.
Now after you’ve added the survey results to the table in your Excel spreadsheet, you’ll want to add another action from Excel Online (Business) to Get a row so that you can get the average ratings you’ve configured. Select the Responder’s email in the dropdown for the Key column. The Key Value will also be the Responder’s email which you’ll grab from the dynamic content.
Once we have the data we want, let’s add a condition. Add an action and type in condition. Select the Condition action. Choose the value of the average store rating under the Get a row dynamic content. Set it to is less than or equal to and add 5 as the value. Do this for the two other averages you’ve configured as well. Make sure you select OR in the condition dropdown instead of AND.
Note: If less than or equal to is not in the dropdown, your data may be of a type that cannot use this operator (e.g., string). There is a workaround below if you need it.
If the average rating for any of the survey questions is 5 or less, let’s kick off an email to inform us that we’re getting a low rating(s). Under If Yes, Add an action, type Office 365 Outlook, and select Send an email. Fill in the appropriate fields based on who you want to notify and what you want to say in the body of the email.
If the average rating for all of our questions is 6 or greater, we aren’t concerned and therefore won’t require any action or notification so we’ll leave the If No part of the condition blank.
Notice: It’s possible your data may be of a type you aren’t expecting and therefore your Flow might throw an error or not include the operators you’re expecting. For example, the average rating configured in my Excel table was returned back as a string rather than an integer or float. As a result, the if less than or equal to operator was not available for me to select in the condition I set up.
There is a way around this by initializing these values at the beginning of the Flow then setting them as the type you want. Here are the steps you can take if you encounter this:
Below your trigger When a new response is submitted at the top of your Flow, click the + symbol and Add an action. Type in variable and select Initialize variable. Name your variable. In my case, I named it Avg store rating. For Type, select Float. Leave the Value field blank. Do this step for each of your three averages.
Next go into your Apply to each card and below the Get a row step, click the + symbol and Add an action. Type in variable and select Set variable. For Name, select Avg store rating from the dropdown.
In the value field, you’ll add an expression. To do this, first type float() in the expression field next to dynamic content.
Inside the parentheses, you’ll add dynamic content from Get a row (mine is labeled Avg. store rating). Then click update to add the expression. Do this for each of your variables.
This isn’t the prettiest solution, but it allows you to take data from one type and convert it to another type in order to use it how you need to.
That’s it! I hope you are inspired to use Flow to help you collect data, analyze that data for you, and alert you only when you want to be alerted.
Simple Flows like this can really help you focus on things that actually need your attention and do some of the busy work for you which absolutely improves your productivity!
