Mike Harrison

Understanding your customers’ needs and readiness is crucial for making sales. One powerful technique to achieve this is with a Scorecard, a tool that not only collects valuable insights but also provides personalised feedback to your audience. Here’s a step-by-step guide on how to build an automated scorecard using Microsoft Forms and Power Automate, helping you to create meaningful engagement and generate demand for your products and services.

I’m going to assume you know what questions you’d like to ask your customers, if not then you could always use a GPT to help you.

Make a form

Open Microsoft Forms and begin by creating your form. To assign scores, use ‘Choice’ questions with predefined answers, allowing for consistent scoring. Add all of the questions and answers you’d like to provide to the respondent.

Make sure you add a couple of ‘Text’ fields to capture name and email address, so you can send a personalised response back to the respondent.

Click on the settings icon, and change the settings to allow external access to your form by selecting ‘Anyone can respond’.

Click on ‘Collect responses’ copy the link, and share it with your customers.

Create a Power Automate flow

Now that your form is complete, you’ll want to head to Power Automate to create a flow.

Create a new Automated cloud flow, and pick the Microsoft Forms Connector ‘When a new response is submitted’.

Select the form that you created in the first step.

Next add a new step ‘Get response details’. Pick your form again, and add the ‘Response Id’ that appears in the list.

Initialise a variable called ‘varScore’ to capture the score of your form.

You’ll also need a variable called ‘varResult’ which will contain the custom result that you’d like to send to the respondent based upon their score. Additionally, you can initialise variables to capture the respondent’s name, email address, and other details.

For each of the questions on the form, you will need to add a ‘Switch’. In the ‘On’ field for that switch pick the relevant question from the form.

Add a ‘Case’ to that Switch for each of the possible answers. This must exactly match the text in the choices you’ve made available in your form (copying and pasting directly from the form will save some time).

Inside each Case add a step to ‘Increment variable’ and add the value of the score you’d like to give for answering that question with that answer. Complete these steps for all questions and answers.

In the example here there are three different responses given for scores in the following ranges 0-12 points, between 13 and 19 points, and 20 points +. We’ll need to add three Condition steps, each nested inside the previous ‘False’ condition.

Within each condition, compare the ‘varScore’ variable to the specified ranges.

• varScore ‘is greater or equal to’ 20
• varScore ‘is greater or equal to’ 13 AND varScore ‘is less or equal to’ 19
• varScore ‘is less or equal to’ 12

Inside the ‘True’ condition of each you will want to set your ‘varResult’ variable to the text you’d like to send to the respondent based upon their score.

Now add a ‘Send an email (V2)’ step. Compose an email to yourself containing all of the data collected from your respondent.

Add a second ‘Send an email (V2)’ step.

In the ‘To’ field add your respondents email address that was captured from the form.

In the ‘Body’ you can use the respondents name, and add the varScore and varResult variables within your own text as required.

By default you’ll see the information captured in the Microsoft Forms interface.

Optional extras:

• Collect the data received into a SharePoint list.
• Create a PowerBI dashboard using the SharePoint list as a data source to display the customer data collected.
• Make an API call to a GPT containing the response data, analyse the data, and send a bespoke response back to the respondent.

You can give this example a try here: https://forms.office.com/e/cMNhAzpk28

If you need more help, or would like a copy of the PowerAutomate flow, let me know in the comments.

Found this useful? Follow me on LinkedIn.