How to Gain Email Campaign Insight with Power BI

Connecting Microsoft Dynamics to marketing automation services like Click Dimensions, dotmailer and Mailchimp means that CRM is continually refreshed with email open and click actions.

Using CRM, sales and marketing teams can identify the top prospects from each campaign, or use these actions to trigger automated campaigns.

In previous posts we've demonstrated how CRM reports email marketing activity but with the advent of Power BI new insights can be more quickly gained across all email marketing campaigns. 

How easily can you take a step back to assess your own recent email marketing performance?

Get in touch with Preact and we'll help you gain more insight from your CRM and marketing data.

Using your marketing automation service you'll already have access to reports for individual campaigns that detail open and click rates, unsubscribe data and other data detailing how people interact with your email.

But what about reporting across multiple email campaigns over the past year, or the last few months?

Is your email marketing in growth? For example, how many emails did you send last month and how did that compare to the same period last year?

What trends are you seeing in email clicks? What time of day are people interacting with your email?

Power BI connects with Microsoft Dynamics enabling marketing teams to quickly gain insight that drives action by visualizing data from email campaigns - without the need to export data to spreadsheets.

Let's take a look...

In these examples, dotmailer is integrated with Microsoft Dynamics CRM and selected email data has been published in a Power BI dashboard.

What Volume of Emails Are Sent?

If there is a target for the number of emails that need to be sent each month, or quarter, a Power BI chart will very easily monitor progress and provide a comparison to a previous period.

In this instance, the chart rolls up the total number of emails sent this year by month and compared this to the previous year.

From this display, the chart confirms that from January - July 2016 the total number of emails sent is significantly higher compared to the previous year. 

However, in 2015 the number of email sent sharply rose in August 2015 so a similar upturn in volume is needed in the current month if performance is to remain ahead of the previous year.

How Many Email Clicks Are Generated?

Similar to the above, you might have a KPI that tracks how many email click events occur each month.

In the example below, clicks from email marketing activity consistently exceeds the previous calendar year but this is on a downward trend which suggests a need for investigation and may highlight a need to adjust the email template or review the type of email links that are promoted. 


What Time of Day are Emails Opened?

Knowing what time of day your audience are most receptive to your emails is valuable insight for maximising email open and click rates.

This example report in Power BI utilises dotmailer data which tracks the precise time of the last open date on any email. This value will therefore be updated if recipients go back to the email after initially opening it. Once dotmailer is integrated Microsoft Dynamics and CRM is then connected with Power BI Pro marketers can begin to visualize these interactions:


In this instance most email opens occur between 10:00 and 10:59 so if we are sending a campaign in the morning this is likely to be most optimum time.

Scheduling email campaigns in the afternoon can often creates a better response as recipients are more likely to have caught up on their email, in this example between 15:00 and 15:59 appears to be when this company's audience are most receptive.

But how does this differ from one day of the week to another?

By using Power BI filters this chart can be focused on a specific day, in this instance we've narrowed the criteria to only show data where emails were last opened on a Tuesday. In this case, the optimum time is between 11:00 and Midday.


By comparison, if we need to send an email on a Monday, this report suggests that around 3pm is the optimal time.


This is just one illustration to demonstrate how Power BI enables users to drill down and gain helpful insights from data that already exists in CRM.

What Subject Lines Resulted in the Highest Open Rate?

Rather than just reporting a simple list of the top ten campaigns by open %, Power BI enables campaigns to be filtered as required.

In this example, the report lists campaigns that created the highest unique open rate that were sent to at least 250 contacts within the last 12 months.


What was the total unsubscribe rate last month?

It's easy enough to see if people have unsubscribed from an individual campaign but what about an aggregated metric showing the total unsubscribe rate for all campaigns within a single month?

Power BI rolls up email campaign data to provide an at a glance view of the overall unsubscribe rate and directly compares this to previous year.

This report can be adapted to roll-up and track any other campaign metric.


How is our Nurture Campaign Performing

Because nurture campaigns consist of multiple emails it can be challenge to quickly see an overview of how the entire campaign is performing rather than looking at individual messages.

Using a consistent naming format each of these campaign emails can be grouped and reported in Power BI.

In this example, the selected nurture campaign consists of 11 email messages and in total over 1000 emails have been sent resulting in a total open rate of just under 20% and a click rate of 6.1%.


So which messages are performing best in this campaign?

Using the Power BI filters it's amazingly easy to drill down and answer this question from the chart visualizations.

This selected message is working well above the campaign average, creating an open rate of 27.5% and averaging a user click rate of 8.4%:


Filters can be used to evaluate other emails in the nurture campaign to identify which messages might need attention. In example below, the email click rate is in line with the campaign average but its open rate % is significantly lower so there should be a quick win to be gained here by testing another subject line.


These are just a few examples to demonstrate the actionable insight gained by connecting email marketing and CRM data with Power BI. 

To bring your CRM data to life with Power BI you'll need a Pro Licence (currently priced £6.20 per user / month).

Preact provide consultancy expertise to translate your reporting requirement into bespoke Power BI dashboards and charts that fit your reporting needs. Get in touch to learn more and see further reporting examples.