Support Portal ContactGet in touch

Power BI Setup for Dynamics 365 Marketing

   Words by Paul McQuillan

   on 22/04/2020 07:30:00

Assuming we have followed the previous guide to connect the Interaction Data from Dynamics 365 Marketing to our Azure Storage, we can connect this Data to Power BI to start producing detailed analytics for Dynamics 365 Marketing.

This guide will focus on how to get started with Power BI using the Dynamics 365 Marketing Template Packs.

First, we open Power BI in our Tenant of the Microsoft Cloud here: https://app.powerbi.com/

This will give us our initial view of Power BI to start from:

image

Power BI in the Cloud

Ultimately this is where we will be publishing our Power BI Reports to – but for the moment, we can simply use this to download and install the Power BI Desktop App.

image

This will be our main tool to connect to and then report on our D4M Data.

With the Desktop App at the ready, we can then open a .pbit Reporting Template to connect the Model Template to our set of Interaction Data.

We can obtain a variety of pre-built Templates for Dynamics 365 Marketing as these come with all the relevant Connections to connect to the right sources of data, and Reports to then present that data via Power BI.

These are available here: https://docs.microsoft.com/en-us/dynamics365/marketing/marketing-analytics/analytics-gallery-email

I would suggest starting with the Email Marketing Analytics Report, as many organisations start using Dynamics 365 Marketing from an initial Email Marketing standpoint and then move into the more detailed Interaction Data for Web Tracking, Events and detailed Customer Journey’s – and this Report is a good place to do the same when taking your first steps into Power BI and D4M.

Email Marketing Analytics Report

https://docs.microsoft.com/en-us/dynamics365/marketing/marketing-analytics/analytics-gallery-email

https://github.com/microsoft/Dynamics-365-for-Marketing---Power-BI-Reporting/raw/master/PowerBI-Templates/MarketingAnalyzers%20-%20Email%20Marketing.pbit

Once we have the pbit file downloaded, we can simply double click to launch the Power BI Desktop App and get started.

image

This will open the Model in our Desktop App, which will immediately then ask how we want to connect to Dynamics and the Interaction Data in Azure:

image

For a quick primer on what the Power BI Template is looking for from us here:

@CRMOrgUrl

The URL of our Dynamics Marketing App - https://mycrmorg.crm4.dynamics.com/

@AzureStorageAccountName

The name of the Azure Storage Account we connected our D4M Data to – in my example in these guides, this would be ‘crmcsstorage’

@AzureStorageBlogContainerName

The name of the Container in our Azure Storage Account – in my examples here and the previous guide, this would be ‘d4m-blob’

image

@LoadInteractionsForNumberOfDaysBack

This controls how many days history of interactions to read back from your Azure Storage into the Power BI App running locally on your PC – defaults to 7 days for a week of back history.

In Big Data terms, this is the quantity of data to read back from your Data Warehouse (in Azure) into your local Reporting Cube to work with.

The higher the number of days, the longer the next step will take in reading the data from the cloud to your local dev studio.

@MarketingAppId

This the GUID ID of the App in Dynamics that you are using to access CRM – the drop down should give a selection of the Apps it can read from your Tenant of Dynamics, but you can also find this in the Request String when accessing Dynamics 365 Marketing.

image

NOTE: If you have experience with SQL Reporting Services (SSRS), then you might notice the @ symbol notation – yep, their parameters for the way we launch the Model. (the more things change, the more they stay the same)

We then click LOAD to launch the process of the Power BI Desktop App connecting the Model, Dynamics and the Data in Azure.

When prompted you will need to connect to your Azure Blob Storage using your Account Key – this Account Key can be obtained from your Azure Portal or the Azure Storage Desktop App

image

This will then be ready to connect between Power BI on your Desktop, Dynamics in the Cloud, and the Marketing Interactions stored in Azure – this connection period can take 5 to 10 minutes to suck the data from Azure and build the initial model and takes a spot of patience. (or in the IT world, time to offer a round of coffees)

image

When this finishes – we will have something like this presented to us:

image

One thing to note – this will bring back the quantity of Marketing Interactions based on the number of Days we specified when first connecting our Model up to D4M.

So if we want to look beyond the initial 7 days, then we need to go back and increase this duration.

In Dynamics 365 Marketing Terms, this means that if we want to see all of the Email Opens or Clicks for this Insight Chart:

image

We need to make sure we are bringing back more than 7 days – so our Number of Opens reflects the full history of the Marketing Email in our Journey’s, and not just the last 7 days.

image

Viewing the Report Parameter for the Number of Days – after which, we can reload our Data and pull back a longer duration of Marketing Interactions

If in doubt, and looking to avoid getting into the technicalities of Power BI Parameters, we can always re-open the pbit file again and repeat these steps with a larger number of days.

Publish to Power BI

Before we look at how to make changes to the Report in Power BI, we can Publish up to our use of Power BI so we can see and share the report in our Cloud.

From the Power BI Desktop App, we can click Publish and this will publish up to our Power BI area of our Tenant in the Cloud.

image

This will then show the Report in Power BI where we started this guide:

image

This gives us a way of using the D4M Templates to enable a good initial layer of Reporting for our Marketing Team.

What next?

We can look at either surfacing the Power BI Report within Microsoft Teams, or making our changes to the Report to suit how we may want to report on Marketing Email Performance.

We have been an early adopter of helping organisations get the best from Dynamics 365 Marketing and so have a wealth of information, articles and handy tips on our site here.

  • "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.