Support Portal ContactGet in touch

Reporting on Marketing Interactions with Dynamics 365 Marketing

   Words by Paul McQuillan

   on 02/04/2020 10:00:00

Dynamics 365 Marketing gives excellent Insights on each Contact or Customer Journey involved in a Campaign.

We can see this by opening our Action or a Contact to see the timeline of engagement we have had:

image

image

The Insights that Dynamics 365 Marketing shows for a Contact included in our Marketing Activities through Segmentation.

This gives us lots of useful data about that Contact or Marketing Activity – but what if we want to build a deeper reporting perspective?  Potentially looking all Interactions across multiple Campaigns over a longer period of time?

For this we need a fuller report on all the different Marketing Interactions over time.

Within Dynamics 365 Marketing, this cannot (as yet) be pulled back from the usual Dynamics Advanced Find.

However, we can configure Dynamics 365 Marketing to push the full set of Interaction Data to us and then connect this data to our preferred Reporting Technology to produce into a Report or Dashboard. (such as Qlikview, Power BI or other Dashboarding tech)

We can configure this push of data in the following steps:

STEP 1 – Create a Blob Container in Azure

Dynamics 365 Marketing will export the raw analytics data to a Storage Location in Microsoft Azure – so to start this connection we must first have a Storage Account setup in an Azure Subscription.

Create a File Location in Microsoft Azure that will be our receiving location of the data from Dynamics 365 Marketing.

We can do this by setting up our account in Azure and then using the Azure File Storage Explorer to configure the File Share in our Azure Storage Account:

image

Opening our Azure Storage Account in the Azure Storage Explorer to setup a new File Share that will be the receipt location of our Marketing Interaction Data

STEP 2 – Configure a Shared Access Signature

Now our File Share is ready, we need to configure the security for how Dynamics 365 Marketing is going to send us the data.

One way of doing this is by adding a new Shared Access Signature to the File Share – this gives a common key that can be used for a remote Application to drop files into the File Share.

image

Creating the Shared Access Signature

When we add this ‘key’, we must configure two points:

1. Define how long the key will be open for – in my example above, I am setting this for a week but this can be set for a longer period of time to support the regular feed of data from D4M to this Location.

2. Define the permissions available to the key – we want to ensure READ, CREATE, ADD, WRITE and LIST are all ticked to allow the sender (i.e. D4M) using this key to both add and write data to this location.

This will then give us the URL that will allow an Application to communicate with our Storage via this Key.

image

We can note these details as this is what we will need in Dynamics 365 Marketing to setup the feed of Interaction Data.

The simplest way to do this is by clicking the ‘Copied’ button above for the URL field, this will give us the full URL in our Clipboard to then insert into Dynamics 365 for Marketing.

NOTE: The article here is a good brief explainer on Azure Blob Storage and Shared Access Signatures: https://microsoft.github.io/AzureTipsAndTricks/blog/tip89.html

To fully understand the Request String of the Shared Access Signature, this article provides a deeper look: https://4sysops.com/archives/granting-access-to-azure-storage-with-shared-access-signatures/

STEP 3 – Connect Dynamics 365 Marketing to our File Share

Within the Settings for Dynamics 365 Marketing, we can provide the URL for the Shared Access Signature as the key to where and how to send us our Marketing Interaction Data.

image

Settings >> Marketing Analytics Configuration

Initially Dynamics 365 Marketing may have setup an initial Configuration like the above, or we may need to create a new Configuration record here.

image

Within the ‘Export to blob storage URL’ we need to supply the full URL that was given to us when we setup our Shared Access Signature – this should look something like:

https://[my-storage].blob.core.windows.net/d4m-blob?st=2020-04-01T10%3A20%3A00Z&se=2020-05-01T10%3A20%3A00Z&sp=racwl&sv=2018-03-28&sr=c&sig=[my-key]

When we click SAVE, this will configure Dynamics 365 Marketing to pump the data into our Blob Storage in Azure – you will notice that the URL we have just entered immediately blanks-out, this is by design for good security in that we should never have a Access Key or Signature available in CRM or at the Database Level. (much in the same way we should never be able to see or store a Password in a Database)

“The URL you pasted here includes a connection string to connect to your blob storage. Dynamics 365 Marketing will use this to set up a connection to the blob storage but will then discard it to prevent other people from being able to find it. For this same reason, the connection string will be hidden on the form after submit. This URL won't be stored in the Dynamics 365 database. That means that if you later copy your instance to a sandbox or restore from a backup, then you'll need to re-enter this URL to reestablish the connection to your blob storage in Azure.” - https://docs.microsoft.com/en-gb/dynamics365/marketing/custom-analytics

This then configures Dynamics 365 Marketing to export its list of Marketing Interactions to your Storage Location in Azure on regular intervals.

You won’t see any data initially as it will take a few hours to reach its first report-down interval – typically a good 12 hours initially for populating all the interaction types involved.

But when this is done, you will see the following in the Azure Blob Storage Account:

image

This will generate a folder for ‘csv’ and then a series of folders within – one for each type of interaction that Dynamics 365 Marketing records.

Within each folder we will find a timestamped set of the interaction data, alongside a base file for ‘model.json’ which provides the definition of the csv files and locations to allow the data to be read back in a known format and interpreted.

If we look at the Email Clicked data as an example – this contains the following Columns of Data for us to work with:

LinkId

ContactId

AccountId

LinkFriendlyName

OriginalLink

OriginalUrl

MessageI

SendingId

EmailDomain

EmailAddressUsed

InteractionType – NOTE: fixed at ‘EmailClicked’ for the data from this folder

We can pull these columns and data into a Reporting Tool such as Power BI, Qlikview or other to then produce useful reports on this data.

This needs some definition of the reports that will put this data to use.

However there is a set of useful pre-fab templates for Power BI to translate the data into useful Dashboards

https://github.com/microsoft/Dynamics-365-for-Marketing---Power-BI-Reporting 

https://github.com/Microsoft/Dynamics-365-for-Marketing---Power-BI-Reporting/releases

This then produces comprehensive reporting on the outcomes and level of engagement resulting from our Marketing.

To do this, we need to connect Power BI to the Data we now have in our Azure Storage – and do this using one of the Dynamics 365 Marketing Templates.

Further Reading

  • "Paul has made a real difference to how my team of 24 people record and store valuable customer data and sales opportunities. Highly recommended."

    James, Operations Director

  • "Understanding your business allows us to advise when to implement aspects of CRM and, likewise, when not to."

    Paul McQuillan, Managing Director

  • "Dynamics 365 and CRMCS have made a real lasting difference to our business, allowing us to replace older systems that were holding back our performance."

    Grahame, Chief Operating Officer

  • "James worked well with us to help connect CRM with Outlook and relate how this might benefit our team using CRM for Property Care."

    Natalie, Property Care Supervisor

  • "Matt was really good with helping us run User Testing on the new Compliance Module of our CRM System."

    Tom, Compliance Administrator

Prefer to go old-school?

Write to us using the below addresses.

Head Office
CRM Consultancy
61 Oxford Street
Manchester
M1 6EQ

London Office
CRM Consultancy London
Grosvenor Avenue
London

Content © CRM Consultancy.