Pinpoint Nearby Customers to a Location using Microsoft Flow & Bing Maps

Have you ever thought it would be handy to target your customers within a certain distance of something? Maybe you're throwing an event and want to know how many of your customers are within a 30 minutes drive so you can send them a personal invite? Maybe you're a recruiter and would like to know which of your potential candidates live within a 15 minute walk of the role you're trying to fill?

Sure, you could use an advanced find in Dynamics 365 to target customers in a specific city, but that doesn't give you that much info to go on. On the other hand, knowing that your customer is a 5 minute walk around the corner gives you that extra bit of knowledge to spark a conversation and improve your chances of success.

Read on to see how we can accomplish this by using Microsoft Flow and the Bing Maps API.

How it's done:

We're going to create a Flow that connects to Dynamics 365 data to pull the list of customers, use the Bing API connector to get the travel times for each customer, output the results to Excel and finally send an email notification when it's done.

Step 1 - The Trigger

Using a manual trigger, we can ask the user to input the address that they are targeting and the max travel time (we could also use travel distance if we wanted to).

Step 2 - List & Filter Customers in Dynamics 365

I've used the Common Data Service connector as the Dynamics 365 connector is being deprecated. I've selected the Accounts entity and used a filter query to limit only UK customers where the Post Code field has a value.

Step 3 - The "For Each Customer" Loop

Now we have a list of our targeted customers from Dynamics 365, we need to loop through each one to work out if they are within our target range. Let me break down each step:

Step 4 - The Bing Maps Connector

The first step inside the loop is to use the Bing Maps connector to return the distance and travel times from our customers' address to the target address.

We use the "Address 1: ZIP/Postal Code" from Dynamics as "Waypoint 1" and the input from the trigger in Step 1 as "Waypoint 2". Then we choose the options we want.

I've gone for the distance in miles, travel mode as driving and the route optimised for best time. We could add in things like avoid tolls or highways if we wanted to.

* You will need to create a Bing Maps api key to connect to the connector.

Step 5 - Add Customers to Spreadsheet

I've added in a condition here to filter out any customers that live too far away from our target destination, adding only the relevant ones to the spreadsheet. The condition states:

Travel Duration is less than or equal to mul(triggerBody()['number'] , 60)

The part in bold above is an expression that multiplies the Max Travel Time input from step 1 by 60. This is because the Bing API returns the travel duration in seconds which is useful to absolutely nobody.

When the condition above is met, the customer is added to a spreadsheet that I've already created and saved in OneDrive. You just need a basic table with headers and you can choose the fields you want to capture. You can go as detailed as you want as you have access to all the account fields in Dynamics as well as the Bing Maps fields to play with.

Step 6 - Handling Errors from Invalid Addresses

When I first tested this flow, the whole thing would fail if there was an invalid Post Code that Bing could not match. To get around this, we can add in a blank "Scope" action that is configured to run after an error. You can configure this by clicking on the 3 dots and choosing configure run after, as below:

You could get Flow to do something funky with the errors, like kick the owner of the record up the arse to keep their customers addresses accurate, but for now I'm happy for it to just skip that customer and move onto the next one.

Step 7 - Email Notification

Once the Apply to Each loop has done it's thing for all the customers, we might want to notify the user that the flow has finished. A nice touch is to put in a link to the Excel document hosted in OneDrive so they can jump straight into it from the email.

And that's it, job done! Set the flow running, go and have a coffee and wait for the results to roll in.

Here's the end result...