Retrieve Member Data from Customer Insights - Journeys Segments in Power Automate
Sometimes it’s hard to come up with a catchy, succinct title for a post…
Segments in the ‘real-time marketing’ segment builder of Customer Insights - Journeys (Dynamics 365 Marketing) are great for executing seriously impressive and complex data queries for Leads or Contacts in Dynamics 365, as well as all the associated interaction data too. It’s not so great at allowing you to interrogate that data to verify your segment and also to understand finer details about the segmented audience.
In a previous post I showed you how to List Segment Members from Customer Insights - Journeys in Power Automate and return the contact/lead GUIDs.
This post goes a step further to turn the GUIDs into data from the contact/lead which can then be used in lots of other useful ways such as building a beautiful custom page to browse and search segment members or export segment members (blog post pending!).
Alternative title: how to query Dataverse with an array of GUIDs
This method will work in any other context where you are trying to query data in Dataverse using an array of GUIDs. It’s not very sexy to look at/visualise, but its seriously impressive!
It starts from a custom page in a model-driven app
The context here in this blog post using a custom page but you can start this from wherever suits you.
Start with a ‘When Power Apps calls a flow (V2)‘ trigger
There are three text inputs that you need to add:
VirtSegGUID - the msdynmkt_virtualsegmentid column from the msdynmkt_virtualsegment table
UserAzureGUID - the calling users Azure/Entra ID
Table - the table name of the segment members which is lead or contact
Find the calling user in Dynamics 365
I don’t care much about the row that gets returned, the only reason for this action is to obtain the environment URL to use in the next step. It’s guaranteed that the user will have access to read their own user record so it’s reasonably bomb proof. Add a ‘Dataverse - List Rows’ action.
Action name: GetUser
Table name: Users
Select columns: systemuserid
Filter rows: azureactivedirectoryobjectid eq '[UserAzureGUID]' and azureactivedirectoryobjectid ne null
Row count: 1
Retrieve segment member GUIDs
We can query the segment to return the GUIDs of all the segment members using a HTTP Request. Add a ‘HTTP with Microsoft Entra ID (preauthorized) - Invoke an HTTP request’ action.
Action Name: GetSegmentMembers
Method: POST
Url of request:
https://@{urihost(body('GetUser')?['@odata.context'])}/api/data/v9.0/msdynmkt_MembersListBody of the request:
{
"SegmentId": "@{triggerBody()['text']}"
}
If you haven’t used this connector before you will need to create the connection, using your dynamics 365 base URL in both boxes like this.
Initialize an array variable
We will use this to capture the data to return at the end of the process. Add a ‘Variable - Initialize variable’ action:
Name: MemberArray
Type: Array
Define your data query
These two compose steps are used to define the contact or lead specific data elements that you want to retrieve. Note that if its a data item that has the same name on the contact or lead you don’t need to add it here as we will insert it only once further down in the flow. So any unique fields to lead or contact that you want to return need to go here in FetchXML data query format. The queries below use only out of the box fields so this should help to get you started.
Add a ‘Data Operations - Compose’ action:
Action Name: LeadAttributes
Inputs:
<attribute name="subject" /> <attribute name="companyname" /> <attribute name="parentaccountid" /> <attribute name="campaignid" /> <attribute name="leadsourcecode" /> <attribute name="industrycode" /> <attribute name="statuscode" /> <link-entity name="account" from="accountid" to="parentaccountid" link-type="outer" alias="ParentAccount"> <attribute name="address1_country" /> <attribute name="transactioncurrencyid" /> <attribute name="industrycode" /> </link-entity>
Add another ‘Data Operations - Compose’ action:
Action Name: ContactAttributes
Inputs:
<attribute name="parentcustomerid" /> <attribute name="originatingleadid" /> <attribute name="preferredcontactmethodcode" /> <link-entity name="account" from="accountid" to="parentcustomerid" link-type="outer" alias="ParentAccount"> <attribute name="address1_country" /> <attribute name="transactioncurrencyid" /> <attribute name="industrycode" /> </link-entity>
Chunk it
I loathe to have to use the function chunk() because it causes some seriously visceral reactions in my mind associated to this (for reference see ‘blow chunks’). Anyway, full credits to George Doubinski for working this bit out, and using chunk()…
The reason for chunking here is because we are going to dynamically create a FetchXML query to retrieve the rows from Dataverse using the operator ‘in’ on contactid or leadid. There is a limit to the character length of a FetchXML query of 2 KB aka 32,768 characters so the chunk method ensure we don’t blow that limit. Based on your query as to other data attributes being requests you may need to adjust this limit to be bigger/smaller.
Add a ‘Control - Apply to each’ action.
Action Name: ChunkIt
Select an output from previous steps:
chunk(json(body('GetSegmentMembers')?['ResultText'])?['members'],400)
Retrieve member data using the fanciest FetchXML query you have ever seen
By using dynamic inputs we can make the action work for both lead and contact in a single swoop without any branching. We use the table name from the flow inputs, attributes from the compose steps above and ‘chunks’ of GUIDs from the segment membership HTTP request. Inside the Apply to each action, add an action ‘Dataverse - List rows’:
Action Name: ListRows
Table name:
concat(triggerBody()['text_2'],'s')
Fetch Xml Query:
<fetch>
<entity name="@{triggerBody()['text_2']}">
<attribute name="firstname" />
<attribute name="lastname" />
<attribute name="emailaddress1" />
<attribute name="fullname" />
<attribute name="jobtitle" />
<attribute name="ownerid" />
<attribute name="msdynmkt_marketingformid" />
<filter type="or">
<condition attribute="@{triggerBody()['text_2']}id" operator="in" >
<value>{@{join(item(), '}</value><value>{')}}</value>
</condition>
</filter>
@{if(equals(triggerBody()['text_2'],'contact'),outputs('ContactAttributes'),if(equals(triggerBody()['text_2'],'lead'),outputs('LeadAttributes'),null))}
</entity>
</fetch>
Merge your chunks
After each Dataverse query we need to combine the current items in the MemberArray with the additional chunk of data. Inside the Apply to each action, add an action ‘Data Operations - Compose’:
Action Name: Merge
Inputs: (below)
union(variables('MemberArray'),outputs('ListRows')?['body/value'])
Set the MemberArray value
Update the MemberArray variable with the new combined set of data, make sure you use ‘Set’ not ‘Append’. Inside the Apply to each action, add an action ‘Variable - Set variable’:
Name: MemberArray
Value: outputs('Merge')
Respond with data
Finally, we need to send the data back to our custom page (or wherever else you want to send it), using a ‘Response’ action gives us the ability to return an array rather than individual outputs of text/number/boolean. It is really important that you define the Response Body JSON Schema otherwise the Custom Page/Canvas app does not recognise the data as being available and you won’t be able to build a gallery to display the data. The response body needs to include the lead and contact data attributes, it’s ok if the data isn’t returned it will just show as blank. Remember if you add attributes into the FetchXML, you need to also define them here so that they are available as data.
Status Code: 200
Body: variables('MemberArray')
Response Body JSON Schema: (example below)
{
"type": "array",
"items": {
"type": "object",
"properties": {
"contactid": {
"type": "string"
},
"leadid": {
"type": "string"
},
"firstname": {
"type": "string"
},
"fullname": {
"type": "string"
},
"_ownerid_value@OData.Community.Display.V1.FormattedValue": {
"type": "string"
},
"lastname": {
"type": "string"
},
"emailaddress1": {
"type": "string"
},
"jobtitle": {
"type": "string"
},
"preferredcontactmethodcode@OData.Community.Display.V1.FormattedValue": {
"type": "string"
},
"industrycode@OData.Community.Display.V1.FormattedValue": {
"type": "string"
},
"_parentcustomerid_value@OData.Community.Display.V1.FormattedValue": {
"type": "string"
},
"_transactioncurrencyid_value@OData.Community.Display.V1.FormattedValue": {
"type": "string"
},
"_originatingleadid_value@OData.Community.Display.V1.FormattedValue": {
"type": "string"
},
"ParentAccount._transactioncurrencyid_value@OData.Community.Display.V1.FormattedValue": {
"type": "string"
},
"ParentAccount.industrycode@OData.Community.Display.V1.FormattedValue": {
"type": "string"
},
"ParentAccount.territoryid@OData.Community.Display.V1.FormattedValue": {
"type": "string"
},
"ParentAccount.address1_country": {
"type": "string"
},
"subject": {
"type": "string"
},
"companyname": {
"type": "string"
},
"statuscode@OData.Community.Display.V1.FormattedValue": {
"type": "string"
},
"_msdynmkt_marketingformid_value@OData.Community.Display.V1.FormattedValue": {
"type": "string"
},
"leadsourcecode@OData.Community.Display.V1.FormattedValue": {
"type": "string"
},
"_campaignid_value@OData.Community.Display.V1.FormattedValue": {
"type": "string"
},
"_parentaccountid_value@OData.Community.Display.V1.FormattedValue": {
"type": "string"
}
}
}
}Note here the annotation is different depending on the field type to allow you to get back the human readable text versions of the data
Plain text attributes format:
"jobtitle": {
"type": "string"
}
Lookup format:
"_parentcustomerid_value@OData.Community.Display.V1.FormattedValue": {
"type": "string"
}
Option set/currency/date format:
"preferredcontactmethodcode@OData.Community.Display.V1.FormattedValue": {
"type": "string"
}
It’s a chunky wrap!
That’s it, no more mentions of chunk for a while. As mentioned, the why/more visually appealing context for this can be found here.
Here is the flow layout top to bottom below for reference. Drop a comment below if you have an questions!
Ps. Please send all complaints about the fact I’m using the classic Power Automate designer to cleo@ameyholden.com