Connect Power BI to Mailchimp

Welcome to the Mailchimp Email Marketing Power BI app’s official instructions page. The app can be installed on Microsoft AppSource here with a Power BI Premium license. To purchase a custom version of the app or to obtain rights to use the source code, contact us here.

New Features:

  • Member Analysis: A new page to analyze audience by members’ open rates, CTR and 5-stars rating
  • Member Details Activity: A new page to analyze members’ activity by campaigns. Select a specific campaign and see the members that opened and clicked it. Select specific members and see the specific campaigns they opened or clicked.
  • Members: A new page for export scenarios: All members including the merged fields: company, job title, gender, age and activity fields count of opens, open rate, count of clicks, and click rate (CTR)
  • Members – Decomposition Tree: This page for member analysis using a decomposition tree
  • Members Merge Fields: A page to review all merge fields (these are the custom member fields on Mailchimp). Analyze the distribution of members by the merge fields and their values

Background

After the successful release of our ActiveCampaign app, DataChant and Daily Cookie partnered to deliver another awesome Power BI app for email marketing. This time for Mailchimp users. The app is available on Microsoft AppSource for free here and can be used as a Mailchimp connector and be connected from a new Power BI Desktop or Excel. The free version has its limits – It allows you to import your last 2 months of recently-created campaigns, up to 500 members per list, up to 5 unsubscribers per campaign, and 100 recent activities. Do you want to load more data? To lift these limitations, you can subscribe to the paid version here. The paid subscription will allow you to import all historical data.

Here is a public version of the app:

How to set up the Power BI App

Login to your Power BI account and install the app on Microsoft AppSource. When your new app is ready, click Go to App. You will also notice the new Mailchimp tile on the Apps view.

On the app, you will notice the notification bar. Click Connect your data.

In the Connect to Mailchimp window, you will need to provide Domain Name and API Key.

How to find your Domain Name on Mailchimp?

Login to your Mailchimp page (https://login.mailchimp.com). Copy the domain name prefix that is followed by “.admin.mailchimp.com” from the URL bar. For example, in the highlighted screenshot, I used us13 as the domain name. Do not copy the entire URL, only the prefix.

How to find your Api Key on Mailchimp?

In your Mailchimp portal, go to your Account. Click Extras and API Keys, then copy one of the API Keys in Your API Keys section. Learn more about Mailchimp API Keys here.

After entering your domain name prefix (e.g. us13) in the Domain Name box and the API Key in API Key box, click Next.

Keep Mask PII parameter TRUE, if you don’t want to access the actual email addresses and other personal information of your Mailchimp audience members. By keeping the parameter set to TRUE, all personally identifiable information will be masked. If you change the settings to FALSE, you will be able to use export functionality in Excel, or connect to your dataset via Excel live connection or via other Power BI reports or 3rd party XMLA tools to read the actual members’ information, including merge fields.

Keep Subscription Email as none, if you wish to use the free version of the app. The free version has the following refresh limitations:

  1. Up to 500 members per list
  2. Up to 5 unsubscribers per campaign
  3. Recent campaigns that were created in the last 2 months
  4. Recent 100 activities

To remove the limitations above, you can subscribe to the premium version here, and then enter the email address used for the subscription in the Subscription Email below.

The next parameter is Import Content. We have introduced two new pages for content analysis and search which analyze the campaigns’ email body and calculate the number of words and links. We are also using a WordCloud visual for the email body plain text. The needed data is not extracted by default (when the parameter is FALSE). To turn it on, enter TRUE as the value for Import Content.

Note: When set to TRUE, the dataset’s refresh time will significantly increase as the app extracts more content per campaign.

To avoid refresh timeouts when you work with a very large number of campaigns or members, we have added two new parameters: Campaigns Sent Since and Members Modified Since. Set these parameters with dates in the format YYYY-MM-DD to load only recently sent campaigns or recently modified members.

If you keep scrolling down, you will find four new optional parameters. If you use custom fields to track your members’ company name, job title, gender, or age, you can configure the name of the corresponding fields. For example, if in Mailchimp you have a field name COMPANY to track members’ company name, keep the field Company Field Name as COMPANY.

The first three fields you will find when you scroll down are Job Title Field Name, Company Field Name, and Gender Field Name.

The last parameter you will find when you scroll down is Age Field Name. Click Next when you are done.

In the next step, make sure that the Authentication method is set to Anonymous and that the Privacy level setting for this data source is Organizational as shown in the screenshot below. Then, click Sign in and connect.

In the next step, make sure that Authentication method is set to Anonymous and that Privacy level setting for this data source is Organizational as shown in the screenshot below. Then, click Sign in and connect.

Note: The URL that is used here collects the Subscription Email parameter to determine if you have the Premium subscription and unblock the refresh limitations. The app doesn’t send any other data to this URL, and your Mailchimp data remains within the boundaries of your organizational Power BI account.

You will now see that the app Refresh is now in progress notification. Once the refresh completes you will see your account analysis instead of the sample data we used for demo purposes.

You can explore the various pages on the left sidebar to analyze different aspects of your campaign, including Campaign Performance, Tagged Audience, Campaign View, Performance Analysis, and Unsubscriptoion Reasons. The main page, Campaign Performance captures the main insights of your campaigns including Open Rates, CTR (Clickthrough Rate), and CTOR (Click-to-Open Rate).

The Tagged Audience page allows you to analyze your Mailchimp audience members by their associated tags over time.

The app provides interactive exploration capabilities including right-clicking on a campaign, selecting Drill through in the shortcut menu, and then clicking the Selected Campaign will navigate to the relevant campaign page. In the drill-through page of the selected campaigns, you can click the back arrow button to go back to the previous page. You can always go directly to the Campaign View page and select your preferred campaign for analysis using the Filters on the right pane.

The app includes the Performance Analysis page to analyze campaigns by their open rates, CTR and CTOR.

Right-clicking on any box on the scatter charts, selecting Drill through in the shortcut menu, and then clicking Selected Campaign will navigate to the relevant campaign on a dedicated page.

On the Unsubscription Reasons page, you can analyze the main reasons your audience unsubscribe events over time and location and drill down to the main messages that led to it.

Member Analysis: A new page to analyze audience by members’ open rates, CTR, and 5-stars rating.

Member Details Activity: A new page to analyze members’ activity by campaigns. Select a specific campaign and see the members that opened and clicked it.

Members: A new page for export scenarios: All members including the merged fields: company, job title, gender, age, and activity fields count of opens, open rate, count of clicks, and click rate (CTR).

Members – Decomposition Tree: A new page for member analysis using a decomposition tree.


Members Merge Fields: A new page to review all merge fields (these are the custom member fields on Mailchimp). Analyze the distribution of members by the merge fields and their values.

On the new Content Analysis page, you can analyze the effect of subject length, the number of words, and the number of links on open rates, CTR, and CTOR. By default, you will have the last two scatter charts empty. To be able to load this data in the report refresh, enter TRUE as the value of Import Content parameter.

Note: When set to TRUE, the refresh time will significantly increase as the app extracts more content per campaign.

Finally, on the Content Search page, you can search for specific keywords in the campaigns’ email bodies, and explore the relevant campaigns. When the Import Content parameter is FALSE, you will be able to search for keywords in the email subject line only.

To learn more about Power BI apps and your administration options, go here https://docs.microsoft.com/en-us/power-bi/connect-data/service-template-apps-overview.

In your Mailchimp workspace, you can go to the dataset Settings as shown below to set scheduled refresh or change the parameters of the app.

On the Settings page, you can update the Domain Name, API Key, or set Import Content to TRUE or FALSE. To reduce timeout issues when you work with a very large number of campaigns or members, set Campaigns Sent Since and Members Modified Since with dates in the format YYYY-MM-DD to load only recently sent campaigns or recently modified members. You can also set a scheduled refresh.

If you purchase the Premium version of the app, you can enter the email address that you used to subscribe in the Subscription Email box of the Parameters section.

Special thanks to Daily Cookie who partnered with DataChant to build this app. Contact us for any questions on customized Mailchimp Power BI reports.