Account Based Marketing – Building Simple Reports 📊

Recently I was asked by a client if there was a way to view all Email Activities from Contacts that belonged to an Account. Initially, I thought “No problem – that’s a simple Advanced Find” however when I started diving into things, I very quickly ran into some problems. In this blog we’ll look at the solution I came up with and how to implement it!

When I first built out my view of Sent Emails where the Recipient has a related Account record, I thought that I could simply add this to the Account record as a subgrid. However, I quickly learned that this would not give the desired effect. Since you can send emails directly to an Account’s listed email address, there was an existing relationship there. If the email was sent to a Contact, that Account value on the Sent Email is blank:

After much trial and error, I decided that the best thing would be to populate the Account value with the Recipient’s related Account each time the Sent Email record is created. To achieve this, I turn to Power Automate of course! In my example, I chose to run the flow each time a Sent Email record is created. If this is too resource intensive for you, I also recommend using a Scheduled Flow and listing all Sent Emails that were created on that day.

Let’s look at the flow. As always, we’re using the Common Data Service (current environment) (or whatever it’s called at the time of reading this blog, it’s changed three times in the last month 🙃)

I have also set a Filter on this action that will only trigger on records where the Contact value is not empty. If you’re not sure how to achieve this, check out this blog by Sara Lagerquist. Once we have the correct record, we need to retrieve the Contact record via the Get Record action. This will allow us to reference values on the Contact related to the Sent Email record.

Now we need to check if the Contact has an associated Account record. I decided to use a Control action:

Here I am checking if the “Company Name” field on the Contact record has a value. If the condition is true, I’m going to update the Sent Email record with this value:

So now when a Sent Email record is created, it will now be populated with the Contact’s Account:

I have also implemented similar flows for other entities like Email Events, Website Visits and Posted Forms. This allows me to create a complete picture of all activities happening at the Account level!

UPDATE – Roll up Related Scoring!

Adding a simple Roll Up field to the Account record to show a total score value for all related Contacts can be a great way of determining how “hot” an account might be. The formula for the roll-up field is simple and can be set up as follows: