Dynamics 365 Campaign Roll-up

Data Sync has many uses and one that isn't a standard use case but does make for an interesting one is the ability to be able to roll-up contacts that are linked to campaigns via marketing lists.

You might need a list of contacts and the campaigns they are part of and either sync these back to Dynamics into a custom text field or export the results to an Excel Spreadsheet for use elsewhere. The campaigns will be joined in an array and split with a ; e.g. Campaign1;Campaign2.

The following blog will walk you through how you can get a list of contacts and their campaigns without any code.

Requirements

Before getting started you need to ensure you have the following:

• Windows 10 or Windows Server
• Downloaded & Installed Data Synchronisation Studio
• Access to Dynamics 365
• Configured your connection to Dynamics 365 in Data Sync

If you do not have Data Synchronisation Studio you can get a free evaluation edition to try this out.

Connect & Format the Source Data

The source data we are using for this is a list of campaigns with the associated contact name listed in another column.

You can use FetchXML to find the campaigns a contact is related to and connect directly to this. Alternatively if you have a spreadsheet containing the data you could connect your source to that dataset.

To connect using FetchXML you need to expand the connection library tree to show your saved Dynamics Connection. Once this has been expanded right click onto FetchXML and select Connect to Source (A).

If you have not already created and saved your connection to your Dynamics site please take a look at our documentation to explain how.

This will load the FetchXML provider connected to your Dynamics site into the source window. To add your Fetch XML statement click onto the ellipsis in the FetchXML property field.

An example of the fetchXML you can use to get all the campaigns and marketing lists for a contact is:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="list">
    <attribute name="listname" />
    <attribute name="type" />
    <attribute name="createdfromcode" />
    <attribute name="lastusedon" />
    <attribute name="purpose" />
    <attribute name="listid" />
    <order attribute="listname" descending="true" />
    <link-entity name="campaignitem" from="entityid" to="listid" visible="false" intersect="true">
      <link-entity name="campaign" from="campaignid" to="campaignid" alias="ab">
        <attribute name="name" />
      </link-entity>
    </link-entity>
    <link-entity name="listmember" from="listid" to="listid" visible="false" intersect="true">
      <link-entity name="contact" from="contactid" to="entityid" alias="ac">
        <attribute name="contactid" />
        <attribute name="emailaddress1" />
      </link-entity>
    </link-entity>
  </entity>
</fetch>

Once the FetchXML has been added refresh the connection by clicking onto the refresh button. This will load the columns into the source window.

If we preview the data using the preview button it will return data similar to this:

We now need to configure this data to be in the format we need. I.e. Grouped by Contact and Campaigns listed in an array.

To do this we use the inbuilt function Project Analysis Grouping. To use this go to Tools > Use Project with Analysis Grouping.

This will open a window where we can define the grouping rules. We need to group by contact id, create a string array of campaigns and list the contact email address.

This will open new Data Sync Analysis project with the columns we defined a moment ago. If we now preview the data we can see the contact id in one column, the associated campaigns contained in an array column, and the contact email in another.

We can change the name of the value columns by clicking onto the ellipsis in the ValueColumns property field to open the editor. Click onto the column to edit, under the name property change the column name to be what you want and then click OK.

Once you have changed the column names, refresh the connection and the new names will be loaded. You will need to add these columns back to the schema map, as they will have been removed after the change.

Export to Excel

There are multiple ways to export to excel depending on how you want the project to run. If this is a one time project and you only need this export once then you can simply preview the data and export the results using the Export Excel button.

If you are going to want this project to run on a regular basis and need to create a new excel spreadsheet then you can use the quick create option from the tools menu. Make sure to select the location you want the file to be saved to and set a name for the workbook when prompted by file explorer.

Alternatively if you already have an excel spreadsheet you can connect the target to this by dragging and dropping it onto the target window from the file explorer.

You then need to map the columns between the source and target and set the Contact ID to be the key column.

Then run the compare, preview the results to check they are as expected, and then run the synchronisation to add the data to the spreadsheet.