With the recent announcement made about Salesforce Marketing Cloud (Marketing Cloud) using Microsoft Azure as its public cloud provider. I thought in this blog post it would be a great idea to talk about using Power Automate as a method of pushing Dynamics 365 records into a Salesforce Marketing Cloud data extension.
For those who don’t know what a data extension is. It’s basically Marketing Cloud’s version of data tables. It can either be used as a sendable data extension (where the records in the table are directly used to send emails, SMS etc.) or as a place to store related data for segmentation.
Now Marketing Cloud does already have a “Marketing Cloud Connector for Microsoft Dynamics CRM” solution available which allows for the use of Dynamics contact, lead or account data to be used as lists to send out EDMs from Marketing Cloud and also allowing the syncing back of emails stats. One key component it was missing was the adding of contacts, leads etc. from Dynamics to a Journey created in Marketing Cloud. To do this, records would need to, at minimum allow for the adding of said records to custom data extensions, which could then trigger the insertion of the record into a journey.
In this article we will see how we can utilise the Marketing Cloud REST API and Power Automate to integrate and upsert a test record into a data-extension. In order to set this up, there are three prerequisites:
- Setup an installed package in Marketing Cloud with “Server-to-Server integration type to generate Client ID and Client Secret to use in the O-Auth 2 authentication
- Create a Microsoft Automate Flow which will act as a reusable function to utilise O-Auth 2 and generate the access token
- Create a calling Microsoft Automate Flow which will perform the action of adding/upserting a lead or contact from Dynamics to Marketing Cloud (this will Flow will also call the first flow to retrieve the access token needed to authenticate)
Salesforce Marketing Cloud REST API Documentation and Resources
Below are some of the documentation from Salesforce Marketing Cloud used as reference for the integration:
- How to authenticate and example request code to request a access token
- Asynchronously Upsert records to Data Extension
Setting up a installed package in Marketing Cloud
Before we can use Power Automate to call the Marketing Cloud REST API and upsert a record. We first need to sort out the authentication to the API. In order to do this we’ll need to create a Server-to-Server installed package which will contain the credentials required to generate the access token. To do this:
1. In Salesforce Marketing Cloud go to Settings>Setup
2. Expand the “Apps” section on the side bar and select “Installed Packages”
3. You can re-use an existing data integration package or create your own Server-to-Server package.
4. Note on the package the Client Id, Client Secret and Authentication Base URI. These will be what’s used to generate the access token from O-Auth 2.
5. Also note the account ID/MID of the account. You can find this by hovering your mouse over your account name
Creating the reusable Flow function that will generate the access token
Once the Client ID, Client Secret, Authentication Base URI and account MID is noted you can now proceed with creating the Flow which will generate the token. Below is the Flow with the steps used to create the function.
1. When a HTTP request is received
This trigger creates a URL endpoint which can receive JSON payloads. You need to define the JSON schema and this will to act as a entry point for other Flows, webhooks etc. to call this function to generate the access token.
Note that in the schema I decided that instead of storing the client Id, secret and MID values in this function, it will instead receive these values as part of the JSON payload sent to trigger this flow. This will allow the flow to be reusable across different installed packages.
2. Call API endpoint to get Access Token
We will then use the HTTP Flow step to perform a POST request to the “Authentication Base URI” we previously got from the Installed Package in Marketing Cloud (remember to add “/v2/token” to the end of the URI). In the Body, we’ll create a JSON object and pass along the client_id, client_secret and account_id which we would of received from step 1.
3. Parse Access Token
We will then take the returned JSON payload which has the access token and parse it so that we can use the results in Dynamic content within the Flow.
Included in the returned JSON payload are:
access_token: the actual access token required to authenticate API calls
token_type: this will be of type “Bearer”
expires_in: the time in seconds before the token will expire
rest_instance_url: the base API endpoint which we’ll call to perform calls to
There are a few other returned values but I will not go through them as we’ll not be using them.
4. Return Access Token and API endpoint to calling Flow
We will then create a Response step to return in JSON format the access token and REST based API endpoint to the calling Flow.
Create a calling Flow which will call and utilise the returned access token to perform an API call to Marketing Cloud
Once we’ve created the reusable function to get a access token, we can then create the actual calling Flow which will be performing a action to Marketing Cloud (e.g. upserting a contact or lead from Dynamics into a data extension). Below is a sample Flow created to test upsert a record into a data extension.
1. The trigger
As this is a test flow, we’ll be using the manually trigger a flow trigger to test the upsert in action. This can then easily be replaced by a CDS trigger which will run upon create, update, delete etc.and pass the contact or lead information through this Flow.
2, 3, 4 and 5. Store the credentials and data extension key required to create the API request in variables
These steps utilise the “Initialize Variable” steps to store all the credentials and data extension key needed to get the access token and upsert a record into said data extension. Storing them in variables makes them easier to change and update.
6. Get Access Token
This uses a HTTP post step to call the previously created Flow function that generates the access token. We’ll be calling the URL endpoint created in the previous Flow’s trigger step and passing across a JSON object which will include the client id, secret and account id which the previous flow will use to generate the access token and return it to this current flow.
This steps calls the previous flow and returns the following values as an example (we can then use the access token and rest instance URL in our next step):
7. Parse returned access token
This steps takes the returned access token and parses the JSON into usable Dynamic content.
8 Upsert lead value
This final step will now take the returned access token and base REST API URL endpoint to construct a post request which will pass across a record value to upsert into a data extension.
This will then upsert the record into the data extension.
Note that as a reminder. When passing the record value through. The names will need to match the field names created in the data extension.