Zay Aung

Dynamics 365, Power Platform, and everything in between

Tag: Dataverse

  • Migrate Contacts and Contact Point Consents (CPC) into Customer Insights – Real-time Journey

    Migrate Contacts and Contact Point Consents (CPC) into Customer Insights – Real-time Journey

    In this post, I’ll walk you through how to migrate Contacts together with Contact Point Consents (CPC) from an external source into Customer Insights – Journeys (CI-J).

    1. Requirements Covered in This Post
    2. Create Custom Raw Contact Table
    3. Create Power Automate Flow to Process Raw Contact from the External Source
      1. When a row is added – Raw Contact
      2. List rows – Contact Filter by Email
      3. Condition – Email Match Found
      4. List rows – Contact Point Consent
      5. Select – CPC
      6. Filter – Commercial Consent Status
      7. Condition – Commercial Consent Status Return Result
      8. Filter – Monthly Newsletter Consent Status
      9. Condition – Monthly Newsletter
      10. Delete a row – Raw Contact
    4. Testing the solution
    5. Considerations
    6. Summary

    If you ever come across a requirement to migrate or upgrade from a previous marketing system – and need to carry over existing consent records – you’ll need to plan how to migrate both Contacts and their related CPCs effectively into CI-J.

    If you’re only importing CPCs separately (i.e., Contacts have already been migrated to Dataverse), then you can use the out-of-the-box (OOTB) functionality, which is well documented in this post by Megan V. Walker: How To Import Contact Point Consent Records. In that case, feel free to skip this blog entirely.

    Requirements Covered in This Post

    In this post, we’ll focus on the following use case:

    • Both Contacts and CPCs are imported together using an Excel file.
    • Contacts are upserted – if a Contact doesn’t exist, it’s created; if it does, it’s updated.
    • CPCs are created or updated alongside the Contact.

    Notes:

    1. We’ll use email as the unique identifier for Contacts (a common practice).
    2. CPCs will include:
      • Purpose = Commercial
      • Topic = Newsletter
    3. The solution will only focus on Email channel.

    Below is the conceptual diagram of how to achieve the requirements.

    Fig: Solution Conceptual Diagram

    Create Custom Raw Contact Table

    We start by creating a custom table called Raw Contact, with the following columns:

    NameData TypeValuesNotes
    First NameSingle line of textN.A
    Last NameSingle line of textN.A
    Commercial Consent StatusChoiceNot Set
    Opted In
    Opted Out    		You can use OOTB Choice – Consent Value
    Monthly Newsletter Consent StatusChoiceNot Set
    Opted In
    Opted Out    		You can use OOTB Choice – Consent Value
    Raw Contact Form
    Fig: Raw Contact Form

    Create Power Automate Flow to Process Raw Contact from the External Source

    To process the data, we’ll use Power Automate to:

    • Upsert Contacts
    • Create/update related CPCs
    • Remove the processed Raw Contact record

    Below is the overview of the Flow.

    Fig: Raw Contact – Add Contact and Contact Point Consent PA

    When a row is added – Raw Contact

    First, the Flow will be triggered when the Raw Contact record is added (e.g., from Excel import).

    Fig: When a row is added – Raw Contact

    List rows – Contact Filter by Email

    Next, we will include List rows to filter Contact by Email. As you can see from the screen, I’ve included Sort By = modifiedon desc and Row count = 1. That means for some reason, if there’re multiple Contacts with the same email, we’ll only update the latest Contact.

    Fig: List rows – Contact Filter by Email

    Condition – Email Match Found

    These actions are pretty straight forward. As mentioned in the above, this will upsert the Contact.

    Fig: Condition – Email Match Found
    Fig: Email Match Found – Condition expression

    This action will list CPC rows with Contact’s email to check if CPC record already exists.

    Fig: List rows – Contact Point Consent

    Select – CPC

    This action will transform the shape of objects in an array so that I can reuse it easily to further filtering. One thing to call out here is that for _msdynmkt_topicid_value column, I’ve used the below expression to replace with null for the general Commercial Purpose without associating with Topic. 

    if(equals(contains(string(item()),'_msdynmkt_topicid_value'),true),item()?['_msdynmkt_topicid_value'],null)
    Fig: Select – CPC

    This action will filter the particular CPC record where:

    • Contact point value = [Contact’s Email] and
    • Purpose = Commercial and
    • Topic = null (the null value is assigned in the above step) and
    • Channel = Email

    Below is the expression I’ve used to filter the above conditions to check if the CPC record already exists.

    and(equals(item()?['msdynmkt_contactpointvalue'],triggerOutputs()?['body/bs_email']),equals(item()?['_msdynmkt_purposeid_value'],'[Replace with Commercial Purpose GUID]',equals(item()?['_msdynmkt_topicid_value'],null),equals(item()?['msdynmkt_contactpointtype'],534120000))
    Fig: Filter – Commercial Consent Status
    Fig: Example result of “Filter – Commercial Consent Status”

    Based on the result from the above filter – Commercial Conset Status – we will then upsert CPC record for Commercial Purpose that is not associated with any Topic.

    Fig: Condition – Commercial Consent Status Return Result
    Fig: Commercial Consent Status – Condition expression

    While “Update a row” and “Add a new row” actions are pretty straight forward, a few things to call out.

    • On “Update a row – CPC – Commercial Consent Status” action, below is the expression to get Row ID of the particular CPC record.
    first(body('Filter_-_Commercial_Consent_Status'))?['msdynmkt_contactpointconsent4id']
    Fig: Update a row – CPC – Commercial Consent Status action
    • On “Add a new row – CPC – Commercial Consent Status” action, make sure to follow the correct format to update Commercial Purpose lookup, and replace with Commercial Purpose GUID.
    Fig: Add a new row – CPC – Commercial Consent Status action

    Similar to the above filter action, this action will filter particular CPC record where:

    • Channel = Email and
    • Purpose = Commercial and
    • Topic = Monthly Newsletter and
    • Contact point value = [Contact’s Email]
    Fig: Filter – Monthly Newsletter Consent Status
    Fig: Example result of “Filter – Monthly Newsletter Consent Status”

    Condition – Monthly Newsletter

    Based on the result from the above filter – Monthly Newsletter Consent Status – we will then upsert CPC record for Monthly Newsletter Topic.

    Note: As mentioned in Condition – Commercial Consent Status Return Result, follow the same to get Row ID in “Update a row”, and follow the correct format to populate Commercial Purpose lookup.

    Fig: Monthly Newsletter – Condition expression

    Delete a row – Raw Contact

    Once upsert Contact and CPC record, the final action is to delete Raw Contact record that you’ve imported to Raw Contact table. This will enable business users to check easily if processing of Raw Contacts complete.

    After that, validate the Flow make sure if there’s no error, and once done, Publish it.

    Testing the solution

    Once published, import the data to Raw Contact using Excel data template. The Flow will be triggered and process Raw Contact record. If the Flow is run successfully, it will upsert Contact and associated CPC.

    Fig: Raw Contact Excel data template
    Fig: Contact form
    Fig: Contact Point Consent

    Considerations

    There are a few practical considerations to be aware of:

    • OOTB Data Import Limits: The amount of record you can upload to Dataverse using OOTB Data Import functionality is limited.
    • Power Platform Request Limits: The number of request Power Platform can handle within 24 hours also has limit – the limit varies based on which plan you’re on.

    Summary

    In this blog post, we’ve:

    • Created a custom Raw Contact table
    • Built a Flow to process and upsert Contacts and CPCs
    • Validated the results through testing
    • Shared considerations for volume and scalability

    Let me know if you’ve implemented something similar, or if you face any issues during your migration journey!

  • Sending Internal Email Upon Form Submission: Customise Real-time Marketing Form (Part 1 of 4)

    Customise Real-time Marketing Form

    In this blog series, I’ll walk you through how to send emails to internal users or teams based on a contact’s Location – for example, if the location is NSW, the system will send an email to the NSW Customer Service Team – upon Form submission in Real-time Customer Insights – Journeys. We’ll achieve this by using a Custom Trigger in combination with Power Automate.

    Note: Although this example is based on Form submissions, the same concept applies to other trigger points like Event Registrations.

    1. Series Overview
    2. Create Custom Columns in Dataverse
    3. Add Columns to the Form
    4. Configure Form Settings
    5. Configure Information Form
    6. Create the Contact Us Form
    7. Summary and Next Steps

    Series Overview

    I’ve broken this topic down into the following parts:

    1. Customise Real-time Marketing Form <- You are here
    2. Configure Custom Trigger
    3. Build Power Automate Flow
    4. Create Real-time Journey

    This is the first post of this series and will focus on Customise Real-time Marketing Form.

    Create Custom Columns in Dataverse

    Before customising the Form, let’s create some columns in Dataverse to store the internal email addresses.

    I won’t go into detail on how to create Dataverse columns using Power Apps, as there are already plenty of well-documented resources available.

    As best practice, create a Solution first and then add the new columns within that solution to enable easy packaging and deployment across environments.

    Note: Make sure you create in the correct Form table i.e., Logical name = msdynmkt_marketingform.

    In this example, I’ve created the following three columns – all are the same configuration: Data type = Single line of text and Format = Email. Column names:

    • NSW Customer Service Email
    • QLD Customer Service Email
    • VIC Customer Service Email

    Additionally, we will also create a custom column in the Contact table called Location where Data Type = Choice, which will store the Location of the Contact.

    Add Columns to the Form

    Now, let’s include the three email columns in the Real-time Marketing Form.

    1. Click Forms in Form table
    2. Click Add existing form
    3. Add Form settings
    4. Add Information

    Note: Make sure you select Form type = Main

    Configure Form Settings

    Now, we’re going to configure Form settings Form.

    • Open Form settings
    • Click Form settings tab
    • Drag and drop the following three columns just below of Target audience column
      • NSW Customer Service Email
      • QLD Customer Service Email
      • VIC Customer Service Email
    • Click Save and publish.

    Configure Information Form

    • Open Information Form
    • Drag and drop NSW Customer Service Email onto the Form
    • On the right panel, check Hide under Display options
    • (Optional Tip: Set Show hidden = On to verify if the column is already added.)
    • Repeat for the QLD and VIC email columns
    • Click Save and Publish

    Create the Contact Us Form

    Now, create a basic Contact Us form with the following fields: First Name, Last Name, Email and Location (the custom field that we created in the above) and Submit button.

    Follow the below steps.

    1. Go to Real-time journeys
    2. Go to Channels
    3. Go to Forms
    4. Click [+New]

    Before adding fields, go to Form settings and confirm that the three internal email columns are included.

    Then drag and drop the necessary fields onto the canvas.

    Once done and no error, click Publish to be ready to be used. We’ll come back to the Form once we’ve set up other components for the result.

    Summary and Next Steps

    In this post, we’ve:

    • Created three internal email fields in Dataverse.
    • Created a Location field in the Contact table.
    • Customised the real-time marketing form to include internal fields.
    • Created the basic Contact Us form.

    In the next post – Configure Custom Trigger (Part 2), we will walk through how to create a Custom Trigger to use within the Real-time Journey – extending functionality to Power Automate Flow.