Match contacts and accounts by email domain with Power Automate

If you’re using Dataverse to manage account and contact data in a B2B scenario, you’ll most likely want to associate all contacts with a parent account. After all, most of the activities like emails and appointments that you have with an individual are likely to be about the organization they represent, not just that single individual.

Sometimes contacts may get created without the parent account information, though. Let’s say you are tracking an email from a new contact by using the Dynamics 365 App for Outlook. It’s easy for the users to forget or ignore the field values of resulting entries in the CRM database. You can therefore end up with the important email thread being tracked in the system, yet you won’t see it in the correct account’s timeline view.

Could we automate a part of this process and improve data quality without imposing any new actions for the end users to take? That was the quest that I set out to solve with Power Automate cloud flows.

Defining the matching logic

Let’s start by looking at the data model. By default the account table in Dataverse has a column for website URL. This can sometimes contain the same domain information as the email addresses, but not always. To automate things, we really should have a dedicated field for the email domain. Let’s add one:

Are we going to have to manually add the domain string into all the accounts then? No, we’ll automate that as part of our process, too.

Let’s think about the logic next. Our scenario should cover contacts that are either brand new or where their email field is modified. Although we’re particularly interested in not leaving any orphan contacts behind, we can also do a bit of data cleansing for those contacts where the parent account field is populated.

First we should deal with the orphan contacts, though. Now that we’ll have the email domain information available on the account records in the custom field we created, we can search for accounts that have the same domain as the currently orphan contact’s email address. If a single match is found, we’ll just set that account to be the parent of the orphan contact.

If no matches are found, or there are more than one account with the same email domain, we’ll need to consult a human being to resolve the issue. Today we’ll leave that path empty, but you could send a Card to the contact owner in Teams as a call to action, for example.

What about the path where a contact’s parent account field is populated? This might be a good moment to lay out the complete flowchart of how our business logic looks like:

In the case where a parent account is mapped to the contact, we should first of all check whether the account’s email domain field is populated. If it isn’t, then we can take the domain of the current contact and fill in the data, so that potential future orphan contacts will get mapped to an account automatically.

What about a scenario where the contact and account are linked together but their email domain field values (non-empty) don’t match? That’s again a potential issue for the human eyes to review, so sending an email or other type of notification might be a good idea.

Building the cloud flows

Now we have the business logic described. Time to launch Power Automate and start turning that logic into boxes and expressions on the cloud flow canvas.

The Dataverse trigger condition for our flow was already mentioned: creation of contact OR modification of email address.

The first action of our flow is an interesting one: Initialize Variable ContactDomain. We need to extract the domain part from the email field. Taken from this answer on the community forums, we can achieve this with the following expression:

last(split(triggerOutputs()?['body/emailaddress1'],'@'))

The first condition is simply to check whether the contact has data in the email address field to begin with. If not, our exercise would be pointless, meaning the “If no” branch is empty.

Let’s look at what we have under the “If yes” branch:

We have a second condition, to check whether the contact has data in the parent account (Company Name) field. If it does, we’ll do again a conditional check: on that account: does it have data in the newly created custom field for the email domain?

If there isn’t any data in the field, meaning we end up down the “If no” branch, then we simply update the account’s field with the ContactDomain variable string, and we’re done.

If there is data in the account’s email domain field, we should check whether the string in that field is equal to the one we grabbed from the contact’s email address with our expression. If yes, everything’s cool & we’re done. If not, someone should check things out, so we retrieve the user who last modified the contact and send them an email notification in this example cloud flow.

We’re now done with the top level branch of our logic where an account value was already found on the contact. Time to handle the orphan contact path, meaning the “If no” branch of our check on whether the Company Name field was null.

This is the part where we actually do what the subject of this blog post promised: matching the contact with an account. First, we list all the records from the Dataverse account table where the custom email domain field value equals the string stored in our ContactDomain variable.

Then we look at the length of the array from the accounts listing step (thanks to Power Wiki for this expression):

length(outputs('ListAccountsWithMatchingDomain')?['body/value'])

If it is exactly 1, we’re confident that we can use this account as the parent for the contact.

Updating the contact table requires setting the Company Name field (meaning the lookup reference to the account table) to be the accountid we found. Since it’s an array we know to have a single item, we can directly grab it from this record by using this following expression (thanks to Paul Murana):

accounts(@{first(body('ListAccountsWithMatchingDomain')?['value'])?['accountid']})

We’re done! Now any non-parented contact added to Dataverse will get automatically associated with an account if the email address domain part matches that of any existing companies stored in your CRM database.

Updating your existing accounts’ domains

At the beginning I promised that you wouldn’t need to manually enter data into the email domain custom field for accounts. While the above cloud flow will take care of adding the missing domain strings into any accounts where a new contact is linked to, that’s of no use if the first contact to be added would be an orphan. So, we need to ensure every possible account has data in the email domain field.

To achieve this, we can create a one-off flow that’s going to run against all the existing accounts. This could therefore be a manually triggered flow. You’ll need to consider how many accounts you’re dealing with, to ensure you catch them all. In our own CRM we had only a few hundred accounts, so this was easy to run. If you need to go beyond the default 5000 item limit, check out this blog post from Linn Zaw Win.

Our flow to do a one-time update of account email domain field will look like this:

First we should list all the accounts we want our process to update. In this case I went for all active accounts.

Then it’s time to start an apply to each loop for the results. The first condition is to check that the email domain field is indeed null. If it is, we proceed to listing contacts related to the account:

Our contact listing filter checks that the account is equal to what we’re looping through, that the contact is active and that it’s email address field is not null:

_parentcustomerid_value eq '@{items('Apply_to_each')?['accountid']}' and statecode eq 0 and emailaddress1 ne null

For the sake of efficiency, we can set the row count filter to be “1” as well only need to retrieve one qualified contact for any account. Still, before proceeding further we need to check that there actually was one contact found with that criteria. Meaning, this expression’s result is not equal to true:

empty(outputs('ListActiveContactsWithEmail')?['body'])

If there was a valid contacts found, we’re going to do a combination of the expressions used in our main flow. We’ll combine the email domain part extraction together with the retrieval of the first item from the contact array, or more precisely its emailaddress1 field value.

Our final action of updating the account row in Dataverse (UpdateAccountDomain step) will therefore use the following expression to populate the custom field for account email domain (not shown in the screenshot above due to flow’s generous usage of screen real estate):

last(split(first(body('ListActiveContactsWithEmail')?['value'])?['emailaddress1'],'@'))

All good! After we run this one-off flow and update our existing accounts, we’ve got plenty of data for our future contact matching flows to search for, whenever new contacts are added or their email addresses are modified.

This post demonstrated the general concept of how Power Automate can be used for associating contacts and accounts based on email domain strings. Hopefully it gave you some ideas on how such matching logic could be useful when managing your customer data, in combination with other validation rules that would be relevant for your specific CRM system and business processes.